better than CTAS

SQL> create table t1(x number primary key); Table created. SQL> desc t1 Name                    Null?    Type ———————– ——– —————- X                       NOT NULL NUMBER SQL> create table t2 as select * from t1; Table created. SQL> desc t2 Name                    Null?    Type ———————– ——– —————- X                                NUMBER The table T2 has the column X, but not the constraint (primary key / not null). […]

remote transaction timeout

If you access one table via database link and the row is locked, you may get a timeout SQL> update emp@l set sal=sal+1 where ename='SCOTT'; 1 row updated.           SQL> update emp@l set sal=sal+2           where ename='SCOTT';           update emp@l set sal=sal+2           *           ERROR at line 1:           ORA-02049: timeout: distributed           transaction waiting for lock           ORA-02063: preceding line from […]

Drop table if exists

The syntax that you are looking for is…/drop-table.html DROP [TEMPORARY] TABLE [IF EXISTS]     tbl_name [, tbl_name] …     [RESTRICT | CASCADE] Wait, this does not work ! SQL> drop table if exists t; drop table if exists t               * ERROR at line 1: ORA-00933: SQL command not properly ended Okay. It was the Oracle MySQL […]

comment in external table

Depending the files, you may use different signs for comments, typically # hash // slash slash /* slash-star star-slash */ : column — dash dash The latest is used in sql and pl/sql, but : CREATE TABLE t (x NUMBER) ORGANIZATION EXTERNAL (   TYPE ORACLE_LOADER   DEFAULT DIRECTORY data_pump_dir   ACCESS PARAMETERS (     FIELDS TERMINATED BY ';'  — […]

last partition

if you really need to quickly find the latest partition per table, I have written this little gem WITH FUNCTION d (b BLOB, len number) RETURN DATE IS   d DATE; BEGIN   IF DBMS_LOB.SUBSTR (b, 1, 1) = hextoraw('07') and len=83   THEN     DBMS_STATS.convert_raw_value (DBMS_LOB.SUBSTR (b, 12, 2), d);   ELSE     d := NULL;   END IF;   RETURN d; […]

one more stragg

select   to_char(     sum(       power(100,rownum-1)*       deptno     ),     'FM99G99G99G99G99',     'NLS_NUMERIC_CHARACTERS=,;'   ) deptlist from dept DEPTLIST       ————— 40;30;20;10 I also wrote about distinct listagg. The same applies for sum distinct. select   to_char(     sum(power(1e3,d-1)*deptno),     'FM999G999G999',     'NLS_NUMERIC_CHARACTERS=,;'   ) deptsum,   to_char(     sum(distinct power(1e2,d-1)*deptno),     'FM99G99G99',     'NLS_NUMERIC_CHARACTERS=,;'   ) deptsumdist,   to_char(     sum(power(1e1,d-1)),     'FM9G9G9',     'NLS_NUMERIC_CHARACTERS=,;'   ) deptcount,   to_char(     sum(power(1e4,c-1)*comm),     'FM9999G9999G9999G9999G9999',     'NLS_NUMERIC_CHARACTERS=,;'   ) commlist from ( […]

rowid of the last insert

If you look for the last insert result, check returning into. Is it identity column, the rowid, any expression, get it back SQL> var r varchar2(24) SQL> var x number SQL> var d varchar2(30) SQL> insert into t values (default)   returning rowid,x,sysdate into :r,:x,:d; 1 row created. SQL> print R ——————– AAAaFTAAIAAAAILAAD          X ———- […]

select pdf from sqlplus

sqlplus 10gR2 and later allows you to select from a BLOB. If you use linux, you could convert the hex output to binary with xxd sqlplus -s scott/tiger <<EOF |xxd -p -r >doc.pdf set pages 0 lin 17000 long 1000000000 longc 16384 select document from emp where ename=user; EOF Obviously, it could also be a […]

distinct listagg

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache With one listagg SELECT   DEPTNO,   LISTAGG (JOB, ',')     WITHIN GROUP (ORDER BY JOB) JOBS FROM (   SELECT DISTINCT DEPTNO, JOB  FROM EMP) GROUP BY DEPTNO;     DEPTNO […]

Index suggestion from the access advisor

Test case : create table t(x varchar2(8) primary key,   y varchar2(30)); insert into t(x,y) select   to_char(rownum,'FM00000000'),   object_name from all_objects where rownum<1e4; commit; exec dbms_stats.gather_table_stats(user,'T') One user wants to filter on x but does not do the casting properly SQL> select * from t where x=00000001; X        Y                             ——– —————————— 00000001 CON$ He received the expected data. […]

Get the secondmax, again

Just bouncing on 2008/07/secondmax. Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL) WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number IS BEGIN RETURN c (n); END; SELECT   f(     CAST(       COLLECT(         CAST(           sal AS NUMBER […]

strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error. SQL> create table t(x varchar2(5000)); create table t(x varchar2(5000))                            * ERROR at line 1: ORA-00910: specified […]

Monitoring details on your explain plan

We know how to generate an explain plan. SET AUTOT ON, the good old ambulance in Toad (which is no longer an ambulance those days), the explain plan button in SQL Developer or simply EXPLAIN PLAN create table t1 partition by hash(object_id) partitions 16 as select * from dba_objects; exec dbms_stats.gather_table_stats(user, 'T1') explain plan for […]

Best practice : use double quotes, even in DBMS_STATS

Whenever you create a table, it is better to use double quotes to avoid invalid identified. SQL> CREATE TABLE /XXX(x number); CREATE TABLE /XXX(x number)              * ERROR at line 1: ORA-00903: invalid table name SQL> CREATE TABLE "/XXX"(x number); Table created. Even in DBMS_STATS you should use double quotes SQL> exec dbms_stats.gather_table_stats(user,'/XXX') BEGIN dbms_stats.gather_table_stats(user,'/XXX'); […]

When v$session_longops is not long enough

With large table scans, sometimes the estimated total work is far beyond reality SQL> select message from v$session_longops where target='SCOTT.EMP'; MESSAGE ———————————————————— Table Scan:  SCOTT.EMP: 7377612 out of 629683 Blocks done The total work is the Oracle estimation : SQL> select blocks from dba_tables where table_name='EMP';     BLOCKS ———-     629683 This may differ quite a lot from […]

How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'"W"IW-IYYY') compl, sum(b)/1024/1024/1024*8192 GB from (   select max(DATAFILE_BLOCKS) b, trunc(completion_time,'IW') d   from v$backup_datafile   group by FILE# ,trunc(completion_time,'IW') ) group by d order by d; COMPL        GB ——– —— W30-2012   3.73 W31-2012   4.84 W32-2012   5.00 […]

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ? Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=] SELECT * FROM TAB WHERE REGEXP_LIKE(C,'b[[=e=]]b[[=e=]]') not only [=e=] is easier to read and to type, but […]

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w,   dba_objects o WHERE    o.owner='SCOTT'    AND o.object_name='EMP'    and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, […]

Create database link and logon trigger

Today I could not understand why I was getting ORA-1031 with create database link. After analysis, I found out a logon trigger that changed the current schema. As Tom always says, triggers are evil… DEMO: SQL> create user u1 identified by xxx; User created. SQL> grant create session, create database link to u2 identified by […]

Drop database link in another schema

Today I wrote this script : drop_database_link.sql accept owner char prompt "Enter database link owner : " accept db_link char prompt "Enter link name : " begin   dbms_scheduler.create_job(     job_name=>'&owner..drop_database_link',     job_type=>'PLSQL_BLOCK',     job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'   );   dbms_scheduler.run_job('&owner..drop_database_link',false);   dbms_lock.sleep(2);   dbms_scheduler.drop_job('&owner..drop_database_link'); end; / I am using the scheduler to run a job as another user. […]

Dynamic number of columns

I used to believe you cannot have a dynamic number of columns. Today Tom referenced Anton on asktom. It leaded me there, back in time, 2006, on the OTN forums Difficult to write an article on this without copy-pasting most of Anton code, so just read it on the link above. Then you will […]

grant select on sys tables

I prefer to use a powerful named user with dba rather than sys. It is more conform to the security policies in place regarding accounting of administrator operations. Very occasionaly, my user get ORA-1031 insufficient privileges even if I have the dba role. Amoung others, I have “PURGE DBA_RECYCLEBIN” and DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE update, 2012-07-24 For purge […]

American = fast

I had the incredible behavior of having the same query running 10x faster in sqlplus depending on two different PCs. After analysis, I realised the super fast pc was American… at least in nls_lang Slow client: PC setup in German, NLS_LANG is set to GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>set NLS_LANG=GERMAN_SWITZERLAND.WE8MSWIN1252 C:\>sqlplus scott/tiger@db01 SQL*Plus: Release Production on Fr […]

My first .NET gui in Powershell

I managed to interface Oracle and a GUI via powershell. First, load the Oracle and the .NET assemblies [void] [Reflection.Assembly]::LoadFile("C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll") [void] [Reflection.Assembly]::LoadWithPartialName("Drawing") [void] [Reflection.Assembly]::LoadWithPartialName("Windows.Forms") Now, let’s retrieve EMP in a powershell array. I hope one of my reader will advise me on a better way 🙂 $connection=New-Object Oracle.DataAccess.Client.OracleConnection("Data Source=DB01; User Id=scott; password=tiger") $ $command=new-object Oracle.DataAccess.Client.OracleCommand("select […]