TIP 91# : Refresh of materialized view erros with overflow

When I showed up in office couple weeks ago, a client called and complained that a materialized view refresh failed, this refresh has been working with no errors in years. The client confirmed that there was no code change in the materialized view or any underlying tables.Refreshing the materialized view manually also failed in couple second with the same error :


ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 1



The errors first looked like it was due to data in underlying tables which can not be fit in the defined materialized view. As a test, I captured the select statement for the materialized view and populated it in a temporary table to see if any column data types in temporary table is different from the materialized view column data type.
Interesting enough, all columns in temporary table and materialized view were the same in terms of data type and the length.So it can not be data type overflow ?!!!
Tracing the refresh (10046) did not resolve anything.

Eventually found that the issue was not the materialized view by itself but it was due to overflow of dba_tab_modifications which keeps track of insert/update/delete for each object.
Since the materialized view is refreshed on regular basis, Oracle 's number which keeps track of changes were high for the materialized view and when the refresh was done they were overflowed. Metalink ID 882997.1 implicitly reported this issue.

Oracle released a one-off patch for this issue but in interim, gathering stats on the materialized view should reset the number in the dictionary and then refresh should run in success :



select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;

INSERTS DELETES
---------- ----------
4295948847 4283835810

exec dbms_stats.gather_table_stats('@owner','&mv_name',estimate_percent=>5,cascade=> false);
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
select inserts, deletes from sys.mon_mods_all$ where obj# = 189467;

select inserts, deletes from sys.mon_mods_all$ where obj# = &object_id;

no rows



For your info, this issue was reported in 11gR1 (11.1.0.7)

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) -----
Bind#0
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.  
          [@?/rdbms/admin/utlxpls.sql]
  - 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"
from
v$session_longops
where
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.


declare
job_num number;
nlsvar varchar2(4000);
envvar raw(32);
begin
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);
dbms_output.put_line(job_num);
end;
/
commit;

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.


dbms_ijob.remove(job_number??);