[20210325]SCHEDULER$_PROGRAM_ARG SCHEDULER$_JOB_ARG在哪里.txt

[20210325]SCHEDULER$_PROGRAM_ARG SCHEDULER$_JOB_ARG在哪里.txt --//上午在测试建立数据库触发器时执行expdp测试出现意外，提示。 $ expdp scott/book ... . . exported "SCOTT"."T"                                     0 KB       0 rows >>> ORA-31642: the following SQL statement fails: BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT(:1,1,1,'11.02.00.04.00'); END; ORA-20000: YOU CAN NOT TRUNCATE or DROP SCHEDULER$_PROGRAM_ARG TABLE! --//自己尝试分析看看。 1.环境： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production CREATE OR REPLACE TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE    BEFORE TRUNCATE OR DROP    ON DATABASE    DISABLE BEGIN    --//dbms_output.put_line( ora_dict_obj_type);    IF     ora_dict_obj_type IN ('TABLE', 'SEQUENCE')       AND ora_dict_obj_owner = 'SCOTT'       AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_JOURNAL\_%' ESCAPE '\'       AND ORA_DICT_OBJ_NAME NOT LIKE 'SYS\_EXPORT\_SCHEMA_%' ESCAPE '\'    THEN       raise_application_error       (          -20000         ,'YOU CAN NOT TRUNCATE or DROP ' || ora_dict_obj_name || ' TABLE!'       );    END IF; END; / SYS@book> alter TRIGGER SYS.TRI_PREVENT_DROP_TRUNCATE enable ; Trigger altered. 2.分析： SCOTT@book> @ desc_proc SYS DBMS_SCHED_EXPORT_CALLOUTS SCHEMA_CALLOUT INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER      PACKAGE_NAME               OBJECT_NAME    SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED ---------- -------------------------- -------------- -------- -------------------- -------------------- --------- ---------- SYS        DBMS_SCHED_EXPORT_CALLOUTS SCHEMA_CALLOUT        1 SCHEMA               VARCHAR2             IN        N                                                             4 VERSION              VARCHAR2             IN        N                                                             3 ISDBA                BINARY_INTEGER       IN        N                                                             2 PREPOST              BINARY_INTEGER       IN        N --//很明显参数1是schema。 SCOTT@book> @ 10046on 12 Session altered. SCOTT@book> BEGIN "SYS"."DBMS_SCHED_EXPORT_CALLOUTS".SCHEMA_CALLOUT('SCOTT',1,1,'11.02.00.04.00'); END; / PL/SQL procedure successfully completed. SCOTT@book> @ 10046off Session altered. 3.分析转储： $ grep -i "drop table" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60907.trc drop table "SCOTT".scheduler$_program_arg drop table "SCOTT".scheduler$_job_arg --//确实存在drop table!! $ grep -i "create" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_60907.trc  if (m_type='TRIGGER' and m_event='CREATE')      m_stmt:='call mderr.raise_md_error(''MD'', ''SDO'', -13391, ''GeoRaster reserved names cannot be used to create regular triggers.'')'; --//而没有找到建立表的语句，要跟踪整个expdp操作。 4.跟踪expdp看看： --//简单粗暴一点： create or replace trigger sys.TRACE_ALL_LOGINS after logon on scott.schema begin     execute immediate 'alter session set tracefile_identifier = expdp';     execute immediate 'alter session set events = ''10046 trace name context forever, level 12'''; exception   --   -- if something goes wrong, we still want to allow a login to proceed   --   when others then null; end; / --//SYS@book> alter TRIGGER TRACE_ALL_LOGINS disable ; --//Trigger altered. $ ll -l *EXPDP.trc -rw-r----- 1 oracle oinstall  4686780 2021-03-25 11:01:21 book_dm00_61296_EXPDP.trc -rw-r----- 1 oracle oinstall 20458765 2021-03-25 11:01:21 book_dw00_61298_EXPDP.trc -rw-r----- 1 oracle oinstall  6488787 2021-03-25 11:01:21 book_ora_61294_EXPDP.trc $ grep -i "scheduler\$_program_arg" *EXPDP.trc book_dm00_61296_EXPDP.trc:      AND ORA_DICT_OBJ_NAME not in ('SCHEDULER$_PROGRAM_ARG','SCHEDULER$_JOB_ARG') book_dw00_61298_EXPDP.trc:SELECT COUNT(*) FROM OBJ$ O, SYS.SCHEDULER$_PROGRAM_ARGUMENT A, USER$ U WHERE A.OID = O.OBJ# AND O.OWNER# = U.USER# AND U.NAME = :B1 book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARGUMENT" book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG_PK" book_dw00_61298_EXPDP.trc:SELECT COUNT(*) FROM ALL_TABLES WHERE OWNER = :B1 AND TABLE_NAME = 'SCHEDULER$_PROGRAM_ARG' book_dw00_61298_EXPDP.trc:drop table "SCOTT".scheduler$_program_arg book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG" book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG" book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG" book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG" book_dw00_61298_EXPDP.trc:  value="SCHEDULER$_PROGRAM_ARG" book_dw00_61298_EXPDP.trc:      AND ORA_DICT_OBJ_NAME not in ('SCHEDULER$_PROGRAM_ARG','SCHEDULER$_JOB_ARG') book_dw00_61298_EXPDP.trc:      AND ORA_DICT_OBJ_NAME not in ('SCHEDULER$_PROGRAM_ARG','SCHEDULER$_JOB_ARG') --//根本找不到建立的语句。 $ grep -i "drop *table" *EXPDP.trc book_dm00_61296_EXPDP.trc:DROP TABLE "SCOTT"."SYS_EXPORT_SCHEMA_01" PURGE book_dw00_61298_EXPDP.trc:drop table "SCOTT".scheduler$_program_arg book_dw00_61298_EXPDP.trc:drop table "SCOTT".scheduler$_job_arg $ grep -n -C2 -i "create *table" *EXPDP.trc book_ora_61294_EXPDP.trc-1807-===================== book_ora_61294_EXPDP.trc-1808-PARSING IN CURSOR #139941690952496 len=2288 dep=1 uid=83 oct=1 lid=83 tim=1616641253579955 hv=3427723960 ad='7e22e918' sqlid='4zf7a9764xups' book_ora_61294_EXPDP.trc:1809:CREATE TABLE "SCOTT"."SYS book_ora_61294_EXPDP.trc-1810-END OF STMT book_ora_61294_EXPDP.trc-1811-PARSE #139941690952496:c=3000,e=2640,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1616641253579953 --//"建立表的语句显示不全,应该是SYS_EXPORT_SCHEMA_NN. ===================== PARSING IN CURSOR #140254176584752 len=41 dep=3 uid=0 oct=12 lid=0 tim=1616641279637777 hv=1873832356 ad='7f8f7896d420' sqlid='c9zv0udrv0ud4' drop table "SCOTT".scheduler$_program_arg END OF STMT PARSE #140254176584752:c=1000,e=186,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=3,plh=0,tim=1616641279637776 ===================== --//手工执行看看： SCOTT@book> drop table "SCOTT".scheduler$_job_arg; drop table "SCOTT".scheduler$_job_arg                    * ERROR at line 1: ORA-00942: table or view does not exist $ grep err=942 *_EXPDP.trc book_dw00_61298_EXPDP.trc:ERROR #140254176584752:err=942 tim=1616641279773289 book_dw00_61298_EXPDP.trc:ERROR #140254176584752:err=942 tim=1616641279777653 book_ora_61294_EXPDP.trc:PARSE ERROR #139941689848416:len=42 dep=1 uid=83 oct=3 lid=83 tim=1616641253556750 err=942 --//跟踪book_dw00_61298_EXPDP出现2次err=942,也许对应正好是drop操作，也就是这两个对象根本不存在。 5.假设这两个表来之用户呢？ SCOTT@book> create table scheduler$_job_arg  as select * from emp; Table created. SCOTT@book> create table scheduler$_program_arg  as select * from emp; Table created. $ expdp scott/book Export: Release 11.2.0.4.0 - Production on Thu Mar 25 15:10:21 2021 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/a***** Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.062 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/POST_DATA_TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/JOB Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported "SCOTT"."SESSION_WAIT_RECORD"               386.7 KB    8122 rows . . exported "SCOTT"."LOCK_OBJECT_RECORD"                308.4 KB    8122 rows . . exported "SCOTT"."TEST_RID_TAB"                      171.2 KB   10000 rows . . exported "SCOTT"."TEST_TAB"                          7.156 KB     100 rows . . exported "SCOTT"."TY"                                97.67 KB    1000 rows . . exported "SCOTT"."DEMO"                              5.007 KB       1 rows . . exported "SCOTT"."DEPT"                              5.929 KB       4 rows . . exported "SCOTT"."EMP"                               8.562 KB      14 rows . . exported "SCOTT"."EMPX"                              8.078 KB      14 rows . . exported "SCOTT"."READ_STATS"                        11.64 KB       8 rows . . exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows . . exported "SCOTT"."SCHEDULER$_JOB_ARG"                8.578 KB      14 rows ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ . . exported "SCOTT"."SCHEDULER$_PROGRAM_ARG"            8.585 KB      14 rows ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ . . exported "SCOTT"."TZ"                                5.820 KB       1 rows . . exported "SCOTT"."BONUS"                                 0 KB       0 rows . . exported "SCOTT"."T"                                     0 KB       0 rows Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:   /u01/app/oracle/admin/book/dpdump/expdat.dmp Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 25 15:10:40 2021 elapsed 0 00:00:18 SCOTT@book> desc scheduler$_job_arg ERROR: ORA-04043: object scheduler$_job_arg does not exist SCOTT@book> desc scheduler$_program_arg ERROR: ORA-04043: object scheduler$_program_arg does not exist SCOTT@book> select * from tab where Tname like 'BIN%'; TNAME                          TABTYPE  CLUSTERID ------------------------------ ------- ---------- BIN$vlgc6Psq9QjgU05kqMAE2Q==$0 TABLE BIN$vlgc6Psr9QjgU05kqMAE2Q==$0 TABLE --//有2个表在回收站里面。 SCOTT@book> select * from "BIN$vlgc6Psq9QjgU05kqMAE2Q==$0" where rownum=1;      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20 --//很明显就是drop的两张表。