Amardeep Sidhu's Oracle blog

2-3 days ago, I came across a code, intended to make delete faster. Just have a look

.
.
.
LOOP
SELECT COUNT (1)
INTO v_cnt
FROM table1
WHERE ROWNUM < 2;

IF v_cnt = 0
THEN
EXIT;
END IF;

DELETE FROM table1
WHERE ROWNUM < 1000;

COMMIT;
v_cnt := 0;
END LOOP;
.
.
.

Yesterday, one of my colleague asked that if he traced a wrap’ed PL/SQL procedure, would the SQL statements show up in the trace ? Very simple thing but at that moment i got, sort of into doubt. So i ran a simple test and yes they do show up

CREATE OR REPLACE PROCEDURE wrap1
AS
v_today   DATE;
BEGIN
SELECT SYSDATE
INTO v_today
FROM DUAL;
END;
/

C:\>wrap iname=wrap1.sql

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Fri Sep 18 21:07:49 2009

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing wrap1.sql to wrap1.plb

C:\>more wrap1.plb
CREATE OR REPLACE PROCEDURE wrap1 wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
65 96
uu93le0yJCtORZedJgcWflZ1Jacwg5nnm7+fMr2ywFwWlvJWfF3AdIsJaWnnbSgIv1JfNsJx
doRxO75ucVUAc2fTr+Ii4v+onq/3r8q9yOOsrLAP4yRZW6LbYoWa6q9sd7PG7Nk9cpXs+6Y5
tQR4

/

And here is the output from the trace file, showing the SQL statement:

BEGIN wrap1; END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.03       0.01          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.02          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************

SELECT SYSDATE
FROM
DUAL

Today i was refreshing a MVIEW (Oracle 9.2.0.1.0 on Windows 2000) and instead of writing

exec dbms_mview.refresh('SCHEMA1.MVIEW1','C'); 

i wrote

exec dbms_mview.refresh('SCHEMA1','MVIEW1','C');

And it gave me:

ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

which has nothing to do with the real error. Take care !

Today i was gathering stats on one schema (10.2.0.3 on AIX 5.3, 64 bit) and it said:

ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13336
ORA-06512: at "SYS.DBMS_STATS", line 13682
ORA-06512: at "SYS.DBMS_STATS", line 13760
ORA-06512: at "SYS.DBMS_STATS", line 13719
ORA-06512: at line 1

Little bit of searching on Metalink revealed that i had hit Bug no 6011068 which points to the base Bug 576661 which is related to function based indexes. There were 2 function based indexes in the schema. Before talking about the workaround let us re-produce the test case. Here i am doing it on my laptop (10.2.0.1 on Windows XP 32 bit)

SCOTT@TESTING >create table test1 as select * from emp;

Table created.

SCOTT@TESTING >create index ind1 on test1(comm,1);

Index created.

SCOTT@TESTING >

SYSTEM@TESTING>exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING>

As suggested in the metalink article let us set event 3001 before running the GATHER_SCHEMA_STATS command.

SYSTEM@TESTING >alter session set tracefile_identifier=stats1;

Session altered.

SYSTEM@TESTING >alter session set events '3001 trace name ERRORSTACK level 3';

Session altered.

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');
BEGIN dbms_stats.gather_schema_stats('SCOTT'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SYSTEM@TESTING >

Part of the trace file reads:

ksedmp: internal or fatal error
ORA-03001: unimplemented feature
Current SQL statement for this session:
select /*+ no_parallel_index(t,IND1) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"IND1") */ count(*) as nrw,count(distinct sys_op_lbid(51966,'L',t.rowid)) as nlb,count(distinct hextoraw(sys_op_descend("COMM")||sys_op_descend(1))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "SCOTT"."TEST1" t where "COMM" is not null or 1 is not null
----- PL/SQL Call Stack -----
object      line  object
handle    number  name
65AA77D4      9406  package body SYS.DBMS_STATS
65AA77D4      9919  package body SYS.DBMS_STATS

So the problem is being caused by the index ind1 we created on (comm,1). This bug has been fixed in 10.2.0.5 and 11.1.0.7. The available workaround for other versions is to create index using 1 as character instead of number.

SCOTT@TESTING >drop index ind1;

Index dropped.

SCOTT@TESTING >create index ind1 on test1(comm,'1');

Index created.

SCOTT@TESTING >

And now running GATHER_SCHEMA_STATS:

SYSTEM@TESTING >exec dbms_stats.gather_schema_stats('SCOTT');

PL/SQL procedure successfully completed.

SYSTEM@TESTING >

Today one of my colleague was working on development of a screen in Oracle Forms to give the end user an option to schedule a job using dbms_scheduler. With the hope that i would be able to explain it properly, the whole scenario is like this:

1. User will log in to the application with his username (Lets say USER01) and password (basically every application user is a database user).
2. He is provided with a screen where he can enter details about the job and the code behind the button calls a PL/SQL procedure in the main application schema (lets say APP1) which in turn uses DBMS_SCHEDULER.CREATE_JOB to schedule the new job.
3. The ultimate task of the job is to move data from one table in the first database to a table in the second database using a DB Link.
4. There is a VPD policy applied on all the application users to restrict the view of data. Policy function uses SYS_CONTEXT to fetch some information about the logged in user. The main application user APP1 is exempted from policy and can see the whole data.

Things seem to work fine till the schedule part. But when the job runs it hits ORA-02070: database does not support operator SYS_CONTEXT in this context as SYS_CONTEXT and DB link doesn’t go together.

I did a bit of troubleshooting and came to know that the job gets created with JOB_CREATOR (a field in DBA_SCHEDULER_JOBS) as the user who is logged in (ie USER001). Now when the job runs from USER001, there is a VPD policy which is going to append a where clause to the query and there is a DB link being used, hence ORA-02070.

So the way out would be to schedule and run the job from some user that has no VPD policy applied to it. The best choice would obviously be the main application user; APP1 but as the user logs in with his own username so the job would always be created with JOB_CREATOR as USER001. After a bit of thought provoking an idea hit me:

Create a table in the APP1 schema. Now when the user schedules the job, insert the values of the parameters required to schedule the job in the table. Schedule one master job in APP1 schema which would read this table and in turn call DBMS_SCHEDULER.CREATE_JOB to schedule the job required by the user. Now as there is no policy applied on the APP1 database user so the job is not going to hit ORA-02070. The frequency of the master job can be set as per the requirements. To identify which entries in the table have been processed either keep a flag which can be updated or delete the record from the table after scheduling.

That is how it clicked in my mind at that time. Suggestions about any other better (or worse ) methods are welcome

PS: About the title: Nothing really was coming into my mind so i picked up the all three words and titled it DBMS_SCHEDULER, DBMS_RLS and SYS_CONTEXT