RE: DROP DEVELOPER not working - 10046 trace

Execution plan is not generated by using event 10046 for tracing.

If you using 8i+, there is a way to prevent owner of the table from performing ddl.
Here's a post by Joe Testa quite some time ago. And I have used this process successfully.

HTH,
- Kirti

On Thu, 6 Jan 2000, Joseph Testa wrote:

> Why would you want to do that, well, i'm at a place where the developers
> have the schema owner password but we DBAs(being retentitive as we
are)dont
> want them to make any ddl changes.
>
>
> here are the steps:
>
> Feel free to change the names to your liking
>
> create user schema_control identified by <passwd>
> grant create any trigger to schema_control;
>
> edit the 3 following triggers, changing the <SCHEMA_NAME> to the schema
you
> want to put the control on:
>
> create or replace trigger create_control_trigger
> before create on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO CREATE DDL ALLOWED');
> end;
> /
>
> create or replace trigger drop_control_trigger
> before drop on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO DROP DDL ALLOWED');
> end;
> /
>
> create or replace trigger alter_control_trigger
> before alter on <SCHEMA_NAME>.schema
>
> begin
> raise_application_error(-20001,'NO ALTER DDL ALLOWED');
> end;
> /
>
>
> revoke alter any trigger from <SCHEMA_NAME>;
>
> the previous line is needed so they dont alter the trigger to disable it
:)
>
> the next time they attempt to create, drop or alter anything in that
schema
> they get error msg.
>
> This all assumes they are not dba :)
>
> hht, joe

-----Original Message-----
Sent: Thursday, October 10, 2002 11:49 AM To: Multiple recipients of list ORACLE-L

And I Used oradebug at level 12 and set the file size to unlimited, and I am not running out of space in the udump destination. So, why dont I see the execution plan?

• Forwarded by Rajesh Rao/CHASE on 10/10/02 11:22 AM -----

                    Rajesh Rao

                                         To:     ORACLE-L_at_fatcity.com

                    October 10,          cc:

                    2002 10:14 AM        Subject:     RE: DROP DEVELOPER not
working - 10046 trace(Document link:   
                                         Rajesh Rao)

 

As I was investigating more on this issue, I took a close look at the select statement which runs just before the drop. Isnt this select statement supposed to have a STAT with the execution plan in the 10046 trace file. Why dont I see it here? The entries in the trace file are :

PARSING IN CURSOR #3 len=100 dep=0 uid=187 oct=3 lid=187 tim=339770733 hv=1446102633 ad='6890a490'
SELECT t.table_name FROM VANTAGE.pv_Tabs t WHERE t.Table_Name = 'SFI4' AND t.Table_Owner = 'VANTAGE'
END OF STMT
PARSE #3:c=2,e=2,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=4,tim=339770733 WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT #3: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 BINDS #3:
EXEC
#3:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=9223372041149743104,dep=0,og=4,tim=33977073 4
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 FETCH
#3:c=0,e=0,p=0,cr=16,cu=0,mis=0,r=9223376430606319617,dep=0,og=4,tim=3397707 34
WAIT #3: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 FETCH
#3:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=9223376430606319616,dep=0,og=4,tim=33977073 4
WAIT #3: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0  

                    Rajesh Rao

                                         To:     ORACLE-L_at_fatcity.com

                    October 10,          cc:

                    2002 12:34 AM        Subject:     RE: DROP DEVELOPER not
working(Document link: Rajesh Rao)     
 

 

Thats a nice idea. The problem here is that the owner of the table is the one executing the drop. And its not via SQLPlus. So, the use of product_profile is also ruled out. The SQLNet tracing suggested by Robert also seems a good idea, worth trying out. They have been pointing me to their log file which shows the DROP statement, which is the last line in the log file. Maybe its writing to the logs first.

Now, when does a 10046 trace write to the tracefile? Does it wait for the statement to finish executing, before it writes to the files. I dont think so. I remember the trace file showing me statements with bind variables even as a plsql block was running. Self doubt creeping in. In need of an expert opinion.

Thanks
Raj  

                    "Deshpande, Kirti"

                    <kirti.deshpande_at_ve        To:     Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>        
                    rizon.com>                 cc:

                    Sent by:                   Subject:     RE: DROP
DEVELOPER not working                                
                    root_at_fatcity.com

 

 

                    October 09, 2002

                    10:58 PM

                    Please respond to

                    ORACLE-L

 

 

Revoke the drop/delete privilege from role/userid, and ask them to run the process. That would confirm if the code ever encounters the drop/delete instruction.
The process could very well be data dependent...

• Kirti

-----Original Message-----
Sent: Wednesday, October 09, 2002 7:04 PM To: Multiple recipients of list ORACLE-L

"It worked fine in development!"

I can't believe anyone would still say that.

Has your duhveloper traced the code in the current environment, to ensure that the offending piece of code is actually being executed?

Jared

Rajesh.Rao_at_jpmchase.com
Sent by: root_at_fatcity.com
 10/09/2002 02:28 PM
 Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:
        Subject:        DROP DEVELOPER not working

We have a developer here, installing a third party application, who claims one of his "delete campaign" process is hanging. I looked at the wait events, saw nothing, and asked him to politely to go look at the code. After much analysys, the developer now complains, that Oracle is not executing a drop table command at the end of the process, and hanging there. He claims he can drop the table from SQLPLUS.

I asked him to rerun the process. I noticed no wait events for that session
in v$session_wait when he claims the process is hanging. I see no DROP statements in the v$sqlarea. I did a 10046 trace, and the last statement in
the trace file is a select statement. I looked at the sql addresses from v$session, linked it to v$sqlarea and the sql_text shows the same select statement as is seen in the trace file. I see no exclusive locks on the said table. I conclude that the application is not sending a DROP statement
to Oracle for execution. He claims that cannot be the case. They have done the same installation in a test environment and it worked fine. The jury seems to be taking sides. I scream SOS. What more should I be doing? And Does an Oracle 10046 trace write into the trace file after the statement has executed?

Thanks
Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).