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

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

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

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

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

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

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

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