To find out the object(s) created by particular session_id

Hi all,

Does any body know how to find out all the objects created by particular session id in current session?

This is what I'm trying to achieve. I would like to bypass a trigger (ie. stop the trigger to be fired) for the current session (ie. doesn't affect other session from using the trigger).

Then if I find out a temp table is created. Then I would like the temp table to be deleted as soon as the session is ended.
Anyone have any idea how a procedure like this can be written or is there a better way I can bypass a trigger?

Thanks a lot!

I'd like to provide more information on my question.
I'm trying to bypass the trigger by checking if a temp table is created( or could be any other way, but the idea is if something is true, the trigger is bypassed), if so, then stop the trigger from firing, and at the same time delete the temp table since it is created only for this purpose.

The control whether the trigger will fire for a particular session is most elegantly achieved by using the supplied package DBMS_APPLICATION_INFO. The two procedures that are of interest for thios task are SET_CLIENT_INFO and READ_CLIENT_INFO.

If you don't want the trigger to be fired for your session, set a special info string by using SET_CLIENT_INFO procedure. Now in your trigger check if this special infostring is set by using READ_CLIENT_INFO. If you find the particular string to be set, skip ower the trigger logic, othervise execute the trigger normaly.

As far as the second part of your question is concerned, I don't understand exactly what you are tryin to achive, but maybe you should consider usin GLOBAL TEMPORARY TABLEs.

HTH,

Jmodic,

It sounds like exactly what I need, but can you make an example of how it is used. What kind of parameter does it need to be passed in? Thanks.

It would be something like:

CREATE OR REPLACE my_trigger BEFORE INSERT ON my_table FOR EACH ROW
DECLARE
msg_var VARCHAR2(2000);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(msg_var);
IF msg_var = 'Skip my_trigger' THEN NULL
ELSE

END IF;
END;

Now in your program, if you don't want the trigger to be fired, do something like:

BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO( 'Skip my_trigger');
INSERT INTO my_table VALUES(.....); -- Trigger won't be fired for this inserts
DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
INSERT INTO my_table VALUES(.....); -- Trigger will be fired for this inserts
.....
END;

HTH,

Damn, I've forgot that this forum doesn't like some of the characters, so some of my code was filtered out. The trigger body should be (using leading "-" to make it more readable):

CREATE OR REPLACE my_trigger BEFORE INSERT ON my_table FOR EACH ROW
DECLARE
--msg_var VARCHAR2(2000);
BEGIN
--DBMS_APPLICATION_INFO.READ_CLIENT_INFO(msg_var);
--IF msg_var = 'Skip my_trigger' THEN
----NULL;
--ELSE
---- ...trigger logic....
--END IF;
END;
/

Thanks jmodic. It's helpful, I'll definitely try it out.