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 […]

FBI = function based index

Let me today tell you, I now hate FBI for real. Let’s start with an easy working example SQL> CREATE TABLE t(x NUMBER PRIMARY KEY) Table created. SQL> insert into t(x) values (1) 1 row created. SQL> insert into t(x) values (2) 1 row created. SQL> insert into t(x) values (3) 1 row created. SQL> […]

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 docs.oracle.com/…/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 ———- 6 […]

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; […]

disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit alter session DISABLE COMMIT IN PROCEDURE; According to the doc, it prevents procedure from committing your data Test case SQL> alter session DISABLE COMMIT IN PROCEDURE Session altered. SQL> create table t(x number) Table created. SQL> create or replace procedure p is […]

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 select * from t where x=00000001; X Y ——– —————————— 00000001 CON$ He received the expected data. Let’s check his plan SQL> explain plan for select * from t where x=00000001; SQL> select * […]

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 length […]

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 […]

One more obscure syntax

As 10g introduced MODEL, which is mostly used to impress your colleagues but seldom used in production, 12c has a new syntax, MATCH_RECOGNIZE I gave it a first try to recognize trends in EMPs salaries over hire date. SELECT ename, hiredate, sal, trend FROM emp MATCH_RECOGNIZE ( ORDER BY hiredate MEASURES CLASSIFIER () AS TREND […]

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’); END; […]

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 […]

BUILD DEFERRED takes ages

When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night. BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages. SQL> create materialized view mv1 build […]

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 […]

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, w.savtime; SAVTIME OWNER […]

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. […]

Difference between Paris and Zurich

When I was a child, I used to go skiing in the alps, and occasionaly cross the borders. I remember that late in the season (Eastern skiing) restaurants were already empty in France when we had lunch, because our neithbough countries introduced summertime before us. It is a long way back, namely summers 1976 to […]

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 https://forums.oracle.com/forums/message.jspa?messageID=1297717#1297717 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 11.2.0.3.0 Production on Fr […]

On deferred segment creation and truncate

One year ago, I wrote about a side effect of deferred segment creation and drop tablespace : on deferred segment creation Today I discoved one more side effect : In the good old days (I read once that you are old as soon as you start talking about the good old days) and according to […]

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”) $connection.open() $command=new-object Oracle.DataAccess.Client.OracleCommand(“select […]