[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的兩張表。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2764893/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191104]禁止job以及SCHEDULER執行.txt
- react之schedulerReact
- 深入解析Scheduler
- Oracle Database Scheduler整理OracleDatabase
- Kubernetes Scheduler淺析
- Quartz job scheduler 學習quartz
- Quartz框架中的Schedulerquartz框架
- 定時任務scheduler
- The Linux Scheduler: a Decade of Wasted CoresLinuxAST
- Kubernetes 漫遊：kube-scheduler
- Kubernetes scheduler學習筆記筆記
- Reactor中的Thread和SchedulerReactthread
- 關於golang的goroutine schedulerGolang
- Apache Dolphin Scheduler - Dockerfile 詳解ApacheDocker
- React Scheduler 原始碼詳解（1）React原始碼
- Spring Boot 參考指南（Quartz Scheduler）Spring Bootquartz
- Scheduler in Oracle Database 10g(轉)OracleDatabase
- Java Scheduler ScheduledExecutorService ScheduledThreadPoolExecutor Example（ScheduledThreadPoolExecu...Javathread
- React Scheduler 原始碼詳解（2）React原始碼
- Leetcode-Medium 621. Task SchedulerLeetCode
- scheduler原始碼分析——preempt搶佔原始碼
- Apache Dolphin Scheduler - Docker Compose 詳解ApacheDocker
- 深入理解 RxJava2：Scheduler（2）RxJava
- Oracle定時任務dbms_schedulerOracle
- Kubernetes原始碼分析之kube-scheduler原始碼
- Kube-Scheduler外掛的自定義
- [20190505]ts 命令在哪裡.txt
- [20210325]如何避免sequence意外刪除.txt
- Kubernetes安裝之八：配置master之schedulerAST
- oracle排程程式作業dbms_schedulerOracle
- oracle使用DBMS_SCHEDULER排程作業Oracle
- golang 原始碼分析之scheduler排程器Golang原始碼
- MySQL 伺服器啟用 event_schedulerMySql伺服器
- React Fiber原始碼逐個擊破系列-schedulerReact原始碼
- kubernetes實踐之四十九：Scheduler原理分析
- [20201104]磁碟空間消耗在哪裡.txt
- “Hack”阿里雲Kubernetes的Scheduler的日誌級別阿里
- MySQL案例08:MySQL Scheduler Events帶來的風險MySql