This discussion is archived
12 Replies Latest reply: Apr 16, 2013 11:07 PM by Hemant K Chitale RSS

CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"

1000194 Newbie
Currently Being Moderated
Hi,

In our Production environment we have an Trigger called DDL trigger to stop DDL operation from users except SYS user. We are getting the DDL error and its because of CREATE GLOBAL TEMPORARY TABLE "SYS" by some Job. We got this DDL error in alert log and i cant trace why this error is coming and what is the job is trying to create this temporary table . Need help to trace how this error occur.

Content in the trace file while error occur :
------------------------------------------------------------

Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 3892, image: oracle@startest (m001)

*** ACTION NAME:(Auto-DBFUS Action) 2013-03-31 05:59:00.588
*** MODULE NAME:(MMON_SLAVE) 2013-03-31 05:59:00.588
*** SERVICE NAME:(SYS$BACKGROUND) 2013-03-31 05:59:00.588
*** SESSION ID:(528.132) 2013-03-31 05:59:00.588
ORA-00604: error occurred at recursive SQL level 4
ORA-20009: WHO ASKED YOU TO DO DDL AT THIS TIME ?..Contact DBA
ORA-06512: at line 52
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB" ("C0" VARCHAR2(32),"C1" VARCHAR2(64),"C2" TIMESTAMP(3) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950912 ) NOPARALLEL
Current SQL statement for this session:
with last_period as
(select * from wrm$_wr_usage
where upper(feature_type) like 'REPORT'
and usage_time >= (select nvl(max(last_sample_date), sysdate-7) from wri$_dbu_usage_sample) )
select decode (count(*), 0, 0, 1),
count(*),
feature_list
from last_period,
(select substr(sys_connect_by_path(feature_count, ','),2) feature_list
from
(select feature_count,
count(*) over () cnt,
row_number () over (order by 1) seq
from
(select feature_name || ':' || count(*) feature_count
from last_period
group by feature_name)
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq)
group by feature_list
----- PL/SQL Call Stack -----
object line object
handle number name
4bac5c8b8 909 package body SYS.DBMS_SYS_SQL
4bac5cad0 39 package body SYS.DBMS_SQL
4bb730ad0 264 package body SYS.DBMS_FEATURE_USAGE_INTERNAL
4bb730ad0 518 package body SYS.DBMS_FEATURE_USAGE_INTERNAL
4bb730ad0 690 package body SYS.DBMS_FEATURE_USAGE_INTERNAL
4bb730ad0 787 package body SYS.DBMS_FEATURE_USAGE_INTERNAL
4ba4f9510 1 anonymous block



Regards,
Rajasekar
  • 1. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    sybrand_b Guru
    Currently Being Moderated
    WITH statements can create global temporary tables.
    The cause is the SQL right below the error message.
    For me it is clear your DDL trigger wrecks the correct operation of AWR and similar.
    Whether you want that is up to you, but I would drop the trigger.

    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    1000194 Newbie
    Currently Being Moderated
    Hi Sybrand Bakker,

    Thanks for the reply ....

    We cant drop the trigger ,because we have to stop unwanted DDL execution in business hours . While digging this up i found this error occurring every Sunday morning 05 - 06 AM and this is due to some automatic SYS job related to AWR only . As per trace file Action name (*** ACTION NAME:(Auto-DBFUS Action)) i tried to trace the job using dba_feature_usage_statistics. But at the particular interval of time more automatic sys jobs are running . So i cant trace it out . Our DDL trigger enable time is 05 AM, is there anyway to find that particular job and prepone to run earliest .


    Regards,
    Rajasekar

    Edited by: 997191 on Mar 31, 2013 5:25 PM
  • 3. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    sb92075 Guru
    Currently Being Moderated
    997191 wrote:
    Hi Sybrand Bakker,

    Thanks for the reply ....

    We cant drop the trigger ,
    Yes, you can drop the trigger; but choose not to do so.
    because we have to stop unwanted DDL execution in business hours .
    I can stop unwanted DLL during business hours by firing anyone who issue DDL during business hours;
    since DDL do not spontaneously erupt into the DB.

    You are fighting the symptom rather than the root cause.
  • 4. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You can modify the trigger to exempt
    a. the SYS schema
    b. Global Temporary Table creation

    GTTs creation may still occur in the course of execution of complex/large SQL queries where Oracle decides to create a GTT to hold intermediate data.


    Hemant K Chitale
  • 5. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    1000194 Newbie
    Currently Being Moderated
    Please find the trigger below ... Advice me how to write an exception ....

    CREATE OR REPLACE TRIGGER no_ddl_trig
    BEFORE DDL
    ON DATABASE
    DECLARE
    v_sid number(5):=0;
    v_osuser varchar2 (50):=0;
    v_terminal varchar2 (50):=0;
    v_machine varchar2(50):=null;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN

    BEGIN
    select sid,machine,terminal,osuser
    into v_sid,v_machine,v_terminal,v_osuser
    from v$session
    WHERE audsid = USERENV('SESSIONID')
    AND ROWNUM = 1;
    dbms_output.put_line(v_sid||':'||v_machine);
    exception
    when others then null;
    END;

    If (ora_login_user ='SYS')
    or
    ora_dict_obj_name like 'BIN$%'
    Then
    INSERT INTO isys.DDL_LOG
    (ddl_event, ddl_obj_owner,
    ddl_obj_type, ddl_obj_name,
    ddl_sql_text, ddl_user,
    ddl_dt,ddl_sid,ddl_machine,ddl_terminal,ddl_osuser
    )
    VALUES (
    ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_type, ora_dict_obj_name,
    'Failed attempt by sys', ora_login_user,
    SYSDATE,v_sid,v_machine,v_terminal,v_osuser
    );
    commit;
    --RAISE_APPLICATION_ERROR(-20009,'WHO ASKED YOU TO DO DDL AT THIS TIME ?..Contact DBA');
    else
    INSERT INTO isys.DDL_LOG
    (ddl_event, ddl_obj_owner,
    ddl_obj_type, ddl_obj_name,
    ddl_sql_text, ddl_user,
    ddl_dt,ddl_sid,ddl_machine,ddl_terminal,ddl_osuser
    )
    VALUES (
    ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_type, ora_dict_obj_name,
    'FAILED ATTEMPTS', ora_login_user,
    SYSDATE,v_sid,v_machine,v_terminal,v_osuser
    );
    commit;
    RAISE_APPLICATION_ERROR(-20009,'WHO ASKED YOU TO DO DDL AT THIS TIME ?..Contact DBA');
    END IF;
    END no_ddl_trig;
    /
  • 6. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    sb92075 Guru
    Currently Being Moderated
    997191 wrote:
    Please find the trigger below ... Advice me how to write an exception ....

    CREATE OR REPLACE TRIGGER no_ddl_trig
    Only DBA should be able to do DDL to start with.
    Trying to idiotproof against errant DBA means that management & policy has 100% totally failed.
    Any code you can implement, DBA can remove or otherwise defeat.

    Escape while you can.
  • 7. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If (ora_login_user ='SYS')
    Why do you have logins as SYS doing DDL ?

    A DBA who executes DDL should NOT be using the SYS account. He should be using a named DBA account or, at least, the SYSTEM account (if you don't have named DBA accounts).
    ora_dict_obj_name like 'BIN$%'
    What sort of actions did you intend to capture with this ?


    Hemant K Chitale
  • 8. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    1000194 Newbie
    Currently Being Moderated
    I need help to write an exception in this trigger to allow that create global temporary query execution.
  • 9. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    1000194 Newbie
    Currently Being Moderated
    some sys jobs are trying to do DDL operations so we wrote exception for sys user . $BIN is for exception for droping flashback tables.
  • 10. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    Justin_Mungal Journeyer
    Currently Being Moderated
    997191 wrote:
    I need help to write an exception in this trigger to allow that create global temporary query execution.
    I think you might be missing the point of what other users have suggested. Writing an exception to a trigger that prevents DDL won't fix your problem, as it's only a symptom of it. To fix your problem, work with management and your team to assure that DDL is not getting executed during business hours, and revoke privileges from users that don't need these permissions. You must understand, DDL creation during business hours is a problem that should not exist when security best practices have been followed, and when proper change control is in place.
  • 11. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    1000194 Newbie
    Currently Being Moderated
    For better understanding of my problem. Please find the below conversation happens b/w oracle support team.


    Hi,

    We have a DDL trigger setup here . It will prevent DDL operation from users except SYS user. It will enable from 5AM to 11PM. Every Sunday morning between 5AM to 6:30AM we are getting this DDL error because some internal sys job is trying to create GLOBAL TEMPORARY TABLE. Our sys exception in ddl trigger is not working because DDL_USER name is not capturing . We need to know which sys job is trying to create temp table .


    ###########

         
    Hi,
    Thank you for the update.
    How do you identify that some job is failing to create a temporary table?
    Did you try to query DBA_JOBS for this?

    Mahmoud,
    Sr. Support Engineer,


    ############################


    Hi Mahmoud,
    We identified that by analyzing the trace file (which has been already attached with this SR) and also we have analyzed the table dba_feature_usage_statistics but at the particular time (Sunday 5:10 am) many number of BG jobs were running and were not able to identify the particular job which tried to create a global temp table without a user.

    *** ACTION NAME:(Auto-DBFUS Action) 2013-01-27 05:36:16.850
    *** MODULE NAME:(MMON_SLAVE) 2013-01-27 05:36:16.850
    *** SERVICE NAME:(SYS$BACKGROUND) 2013-01-27 05:36:16.850
    *** SESSION ID:(365.12940) 2013-01-27 05:36:16.850
    ORA-00604: error occurred at recursive SQL level 4
    ORA-20009: WHO ASKED YOU TO DO DDL AT THIS TIME ?..Contact DBA
    ORA-06512: at line 52


    ##################################


    From the last update, we have below information:
    - The job procedure has 52+ lines of code.
    - It sets its action name to Auto-DBFUS Action.
    - The DDL call is at line 52.

    Please query the created jobs using any of these information, i.e. one containing "Auto-DBFUS", etc..


    ##################################

    Please provide us the query to find out the job in our database.

    ####################################


    You can allow this by modifying the trigger code to not prevent DDL when module name = MMON_SLAVE, which will allow all mmon slave jobs to not be interrupted.

    You can read it using below function:
    DBMS_APPLICATION_INFO.READ_MODULE (
    module_name OUT VARCHAR2,
    action_name OUT VARCHAR2);

    For more information please check:
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_appinf.htm#i999292


    ####################################

    I understand that you are giving me an option to fix the error with altering my customized trigger to prevent DDL action. I wanted to know if there is any possibility to change the module "MMON SLAVE" timing during which the customized trigger will be disabled by that time say 00 - 5 AM IST timing )


    ####################################


    This job relates to calculating DBA feature usage, There's no documented way to control the its jobs to prevent it from running at certain times.
    The best way is to allow them to execute as advised earlier.


    ########################################


    We are ready to apply the provided solution but we want to know by which ora event we can add exception based upon the module (mmon_slave).



    So Now i want to write an exception ... Any Help ????????
  • 12. Re: CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_F58F85EB"
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You are capturing DDL_OBJ_TYPE. Also lookup ORA_DICT_OBJ_TYPE. Unfortunately, a GTT is of type TABLE. You'll have to query DBA_TABLES to see if the created table is a GTT. Therefore, for TABLEs created by SYS, you'd have to exclude them from the BEFORE DDL trigger.


    Hemant K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points