[20231025]跟蹤rename操作2.txt

[20231025]跟蹤rename操作2.txt

--//做一個rename 操作，看看主要修改那些資料字典。

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

2.測試：
SCOTT@book> create table deptx as select * from dept;
Table created.

--//分析略.
SCOTT@book> rename deptx to depty;
Table renamed.

SCOTT@book> @ 10046on 12
Session altered.

SCOTT@book> rename depty to deptz;
Table renamed.

SCOTT@book> @ 10046off
Session altered.

SCOTT@book> @ t
TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_61833.trc
--//做兩次rename，主要是避免跟蹤到一些不必要的遞規語句。

3.檢視跟蹤檔案內容：

$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_61833.trc | egrep -i "^delete|^update|^insert"
update ind$ set flags = flags + 8 where obj# = :1
delete from idl_ub1$ where obj#=:1
delete from idl_char$ where obj#=:1
delete from idl_ub2$ where obj#=:1
delete from idl_sb4$ where obj#=:1
delete from error$ where obj#=:1
delete from obj$ where obj# = :1
~~~~~~~~~~~~~~~~~~
insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)
~~~~~~~~~~~~~~~~~~
update sdo_geor_sysdata_table set GEORASTER_TABLE_NAME=:1  where GEORASTER_TABLE_NAME=:2
update mdsys.sdo_tin_pc_sysdata_table set TABLE_NAME=:1   where TABLE_NAME=:2 and sdo_owner=:3
update mdsys.sdo_tin_pc_seq set TABLE_NAME=:1   where TABLE_NAME=:2 and sdo_owner=:3

--//主要看下劃線的2條DML語句,可以判斷rename是先刪除再插入obj$,而不是採用update完成的.
--//這也驗證了我測試rename IDL_UB1$操作的恢復時出現的情況。

--//附上extractsql.sh指令碼:
$cat  extractsql.sh
#! /bin/bash
awk '/PARSING IN CURSOR/,/END OF STMT/' $1 | egrep -v '^PARSING|^END OF STMT'