TIP 90# : Trace Unique constraint violation

I got a call from the client that an application was not function due to unique constraint violation. The client insisted on finding the cause of the problem. The challenge was that the database was a mission critical database and too many users were connected to the database which system wide tracing could not be an option. Also disabling unique constraint was not an option as the client did not know the impact of it on the application and also how to clean up skewed data.

For this, I chose to trace ORA-0001 using errorstack :

1. Enable tracing for unique constraint.
This only dumps the first occurance of unique constraint violation for each session,It has minimum side affect on mission critical database.stack level 2 and above captures bind variables.
alter system set events '1 trace name errorstack level 3';

2. Check udump for the generated trace when it occurs, you should be able to find SQL statement and the value for its bind variables if it has.The following shows a sample :

----- Error Stack Dump -----
ORA-00001: unique constraint (AVAIL.SYS_C00269375) violated
----- Current SQL Statement for this session (sql_id=d0p6uv6pamwk5) -----
.................... <---- SQL statement
----- Bind Info (kkscoacd) -----
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=ffffffff7a42c3f8 bln=22 avl=03 flg=09
value=2555 <--- Bind variable value ...
Frames pfr 0xffffffff7a478328 siz=3192 efr 0xffffffff7a478260 siz=3136
Cursor frame dump
enxt: 3.0x00000460 enxt: 2.0x00000020 enxt: 1.0x000007c0
pnxt: 1.0x00000038
kxscphp=0xffffffff7a450d58 siz=984 inu=464 nps=336
kxscbhp=0xffffffff7a4515c8 siz=984 inu=120 nps=0
kxscwhp=0xffffffff7a4512f8 siz=4056 inu=368 nps=0
Starting SQL statement dump
SQL Information
user_id=95 user_name=AVAIL module=SQL*Plus action=
sql_id=d0p6uv6pamwk5 plan_hash_value=0 problem_type=0

3. Turn off tracing when the issue is resolved.
alter system set events '1 trace name errorstack off';

TIP 89# : CPU cost is not shown in the execution plan

You may notice that sometimes that you get the execution plan, the CPU column is not shown. Also you may get the following messge : cpu costing is off (consider enabling it)
The followings could be the main reasons :

- old plan_table.
- optimizer_features_enable is still 9i.
[Bump up the parameter to DB version]
- System stats does not exist.
[Gather system stats]

TIP 88#: Progress of DataPump import with LOB data

If you have a big LOB to be imported through DataPump, you may realize that datapump session stays for a long time and you wonder if it is working and the client also may ask you how much more time is needed to be completed.
The sad news is that v$session_longops and query like the following can not help you that much.Also you can get too much info on how much longer impdp run from datapump views in database or datapump commands (like status).

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
opname like '%%' and opname not like '%aggregate%' and totalwork != 0 and sofar <> totalwork;

The best way is to check the size of LOB segment and see if it grows. Also you could compare the LOB segment size in a import DB with a source DB to get a better estimate of how much more work DataPump has to do :

select sum(bytes)/1024/1024,sysdate from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='&table_name');

TIP 87# : Create/Remove jobs for other users

I am sure that every DBA faces a situation to remove or submit a job for other user, If DBA knows the password of other user, life will be easy and it is as simple as login with the user and submit a job with dbms_job.submit or remove it with dbms_job.remove.

What if DBA has not the password, If DBA tries to remove other user jobs, the following error will be shown :

ORA-23421: job number nnnn is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 536
ORA-06512: at "SYS.DBMS_JOB", line 261
ORA-06512: at line 1

Also DBA can not submit job with dbms_job for other users with dbms_job if DBA does not know the password.

Fortunately, Oracle has undocumented dbms_ijob package which let you as DBA to submit jobs as other users and also remove jobs of other users.

Here is a sample on how to submit a job for user TEST.

job_num number;
nlsvar varchar2(4000);
envvar raw(32);
select nls_env,misc_env into nlsvar,envvar from dba_jobs where rownum<2 and nls_env is not null and misc_env is not null ;
select max(job)+1 into job_num from dba_jobs;
sys.dbms_ijob.submit(job=>job_num,luser=>'TEST',puser=>'TEST',cuser=>'TEST',what=>'insert into tst values(1);' ,next_date=>sysdate+1/(24*60), interval=>'trunc(sysdate,''MI'')+1/48', broken=>FALSE,nlsenv=>nlsvar,env=>envvar);

nlsenv and env should not be passed NULL,In above sample to make it simple, I copied it from the existing jobs. puser (privileged user), luser (login user), cuser(schema user) are the same as what we have in dba_jobs. Job number needs to be passed to dbms_ijob, to make sure its uniqueness, a number higher than max job number is passed.

In order to remove any job , the following simple code can be run.


TIP 86# : What is missed when RMAN backuping up to tape ?

When backing up datafiles into backup sets, RMAN does not back up the contents of
data blocks that have never been allocated.
However, RMAN only skips unused blocks (Blocks which do not currently contain data but they had data) if the following conditions are all met :

■ The COMPATIBLE initialization parameter is set to 10.2
■ There are currently no guaranteed restore points defined for the database
■ The datafile is locally managed
■ The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup
■ The backup set is being created on disk.

Above means that if RMAN backing up to tape, it will backup all unused blocks (blocks which have been touched before but now are empty). In other words, backup to tape could waste space if there are many unused blocks.

Here is a demonstration to show the difference of backup between when it is sent to tape and when it is sent to disk :

Step 1: Create a new empty tablespace

Step 2 : Backup new tablespace to disk

Step 3 : Backup new tablespace to tape

Step 4 : Create a table in the new tablespace

Step 5 : Backup tablespace to disk when it has table with records

Step 6 : Backup tablespace to tape when it has table with the records

Step 7: Delete all records in table (unused blocks - Blocks with no data but had data before)

Step 8 : Backup tablespace to disk after deletion of all records in table

Step 9 : Backup tablespace to disk after deletion of all records in table

Step 10 : Checking backup size

As it is shown, Disk backup after truncate was reduced to 600K while tape backup did not change (~ 5M same before delete). This proves that unused blocks are only not backed up for disk backup. Maybe another reason to use Flash Recovery area !!!

TIP 85 # : ORA-4030 error in well tunned instance/well tunned SQL

A couple days ago, I was asked for a tuning exercise for a client. The instance and SQL statement were tuned very well, however a SQL statement was failing at runtime with ORA-4030.
PGA setting and maximum pga setting on instance (_pga_max_size) were tuned well, after investigation, I found that it is ulimit setting for Oracle OS user which prevents database session to get enough memory (PGA) to execute the query.
In my situation, ulimit was set to the followings :

time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 131072
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

---- Change data to unlimited

ulimit -d unlimited
ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 32768
memory(kbytes) 32768
coredump(blocks) 2097151
nofiles(descriptors) 2000

After restarting the listener to create a new session with the new ulimit settings, the issue was fixed.

TIP 84# : Create private DB link for a user without knowing password

Have you ever faced with a situation that you were asked to create a private database link or create a materialized view or submit a job as a user which you do not know its password?
As a DBA, you have the following options :

- Ask the password. (Usually it is not desired)
- Change password temporarily (This could break the application)
- Use dbms_sys_sql

With dbms_sys_sql, you are able to parse and execute SQL as other users.
Here is example on how to create private database link in scott user.

uid number;
sqltext varchar2(1000) := 'create database link test_link connect to target_user?? identified by target_user_password?? using ''target_DB_TNS??''';
myint integer;
select user_id into uid from all_users where username like 'SCOTT';
end ;

select owner,db_link from dba_db_links where db_link like 'TEST_LINK%';

------------------------------ ----------------------------------------