[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的兩張表。