这两天一个库因操作了基表的dictionary出错，测试一下正常的drop tablespace做了哪些操作
SQL> create tablespace tbsdrop datafile '/oracle10g/app/oracle/oradata/anbob/tbsdrop01.dbf' size 3m;
Tablespace created.
SQL> alter session set events '10046 trace name context forever,level 4';
Session altered.
SQL> drop tablespace tbsdrop;
Tablespace dropped.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oracle10g/app/oracle/admin/anbob/udump/anbob_ora_17320.trc
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anbob ~]$ grep -i "insert" /oracle10g/app/oracle/admin/anbob/udump/anbob_ora_17320.trc
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
[oracle@anbob ~]$ grep -i "delete" /oracle10g/app/oracle/admin/anbob/udump/anbob_ora_17320.trc
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE WHERE SDO_OWNER = :name';
stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
stmt := 'DELETE FROM SDO_NETWORK_METADATA_TABLE WHERE SDO_OWNER = :name';
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
stmt := 'DELETE FROM SDO_NETWORK_CONSTRAINTS WHERE SDO_OWNER = :name';
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
stmt := 'DELETE FROM SDO_NETWORK_USER_DATA WHERE SDO_OWNER = :name';
/**********************************
stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1'; 部分前文
IF cnt > 0 THEN
stmt := 'SELECT RDT_TABLE_NAME, RASTER_ID FROM SDO_GEOR_SYSDATA_TABLE' ||
' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
open vcur for stmt using dictionary_obj_owner, dictionary_obj_name;
loop
fetch vcur into rdt, rsid;
exit when vcur%NOTFOUND;
if instr(rdt, '.') = 0 then
rdt := dictionary_obj_owner || '.' || rdt;
end if;
stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
begin
execute immediate stm2 using rsid;
exception
when others then
if SQLCODE <> -942 then
raise;
end if;
end;
end loop;
************************************/
[oracle@anbob ~]$ grep -i "update" /oracle10g/app/oracle/admin/anbob/udump/anbob_ora_17320.trc
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select t_metrics_id, t_instance_name from WRI$_ALERT_THRESHOLD where t_object_type = :1 and t_object_name = :2 for update
STAT #10 id=1 cnt=0 pid=0 pos=1 obj=0 op='FOR UPDATE (cr=1 pr=0 pw=0 time=499 us)'
select t_metrics_id, t_instance_name from WRI$_ALERT_THRESHOLD where t_object_type = :1 and t_object_name = :2 for update
update ts$ set name=:2,online$=:3,contents$=:4,undofile#=:5,undoblock#=:6, blocksize=:7,dflmaxext=:8,dflinit=:9,dflincr=:10,dflextpct=:11,dflminext=:12,dflminlen=:13,owner#=:14,scnwrp=:15,scnbas=:16,pitrscnwrp=:17,pitrscnbas=:18,dflogging=:19,bitmapped=:20,inc#=:21,flags=:22,plugged=:23,spare1=:24,spare2=:25 where ts#=:1
STAT #3 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE TS$ (cr=4 pr=0 pw=0 time=851 us)'
update file$ set blocks=:2,ts#=DECODE(:3,-1,NULL,:3),status$=:4, relfile#=DECODE(:5,0,NULL,:5),maxextend=:6,inc=:7,crscnwrp=:8,crscnbas=:9,spare1=DECODE(:10,0,NULL,:10)where file#=:1
STAT #5 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE FILE$ (cr=1 pr=0 pw=0 time=817 us)'
update ts$ set name=:2,online$=:3,contents$=:4,undofile#=:5,undoblock#=:6, blocksize=:7,dflmaxext=:8,dflinit=:9,dflincr=:10,dflextpct=:11,dflminext=:12,dflminlen=:13,owner#=:14,scnwrp=:15,scnbas=:16,pitrscnwrp=:17,pitrscnbas=:18,dflogging=:19,bitmapped=:20,inc#=:21,flags=:22,plugged=:23,spare1=:24,spare2=:25 where ts#=:1
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE TS$ (cr=4 pr=0 pw=0 time=322 us)'
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
select baseobject,type#,update$,insert$,delete$,refnewname,refoldname,whenclause,definition,enabled,property,sys_evts,nttrigcol,nttrigatt,refprtname,rowid from trigger$ where obj# =:1
[oracle@anbob ~]$ ora
SQL*Plus: Release 10.2.0.1.0 - Production on 鏄熸湡鍏3鏈10 09:23:43 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select ts#,name,owner#,online$,contents$,INC#, PITRSCNWRP from ts$;
TS# NAME OWNER# ONLINE$ CONTENTS$ INC# PITRSCNWRP
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
0 SYSTEM 0 1 0 1 0
1 UNDOTBS1 0 1 0 2 0
2 SYSAUX 0 1 0 1 0
3 TEMP 0 1 1 1 0
4 USERS 0 1 0 1 0
5 UNDOTBS2 0 3 0 1 0
6 EXAMPLE 0 1 0 1 0
7 MYTBS 0 1 0 1 0
8 TEST 0 1 0 1 0
9 TBSDROP 0 3 0 3 0
10 rows selected.
SQL> select ts#,file#,maxextend,inc,blocks from file$ ;
TS# FILE# MAXEXTEND INC BLOCKS
---------- ---------- ---------- ---------- ----------
0 1 4194302 1280 38400
1 2 4194302 640 3200
2 3 4194302 1280 15360
4 4 4194302 160 640
6 5 4194302 80 12800
7 6 4194302 12800 12800
8 7 0 0 1280
8 8 0 0 128
9 0 0 384
9 rows selected.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------
1 /oracle10g/app/oracle/oradata/
anbob/system01.dbf
2 /oracle10g/app/oracle/oradata/
anbob/undotbs01.dbf
3 /oracle10g/app/oracle/oradata/
anbob/sysaux01.dbf
4 /oracle10g/app/oracle/oradata/
anbob/users01.dbf
5 /oracle10g/app/oracle/oradata/
anbob/example01.dbf
6 /oracle10g/app/oracle/oradata/
ANBOB/datafile/o1_mf_mytbs_7g1
stt2r_.dbf
7 /oracle10g/app/oracle/oradata/
ANBOB/test_1.dbf
8 /oracle10g/app/oracle/oradata/
ANBOB/test_2.dbf
8 rows selected.
SQL> create tablespace tbsdrop datafile '/oracle10g/app/oracle/oradata/anbob/tbsdrop01.dbf' reuse;
Tablespace created.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------
1 /oracle10g/app/oracle/oradata/
anbob/system01.dbf
2 /oracle10g/app/oracle/oradata/
anbob/undotbs01.dbf
3 /oracle10g/app/oracle/oradata/
anbob/sysaux01.dbf
4 /oracle10g/app/oracle/oradata/
anbob/users01.dbf
5 /oracle10g/app/oracle/oradata/
anbob/example01.dbf
6 /oracle10g/app/oracle/oradata/
ANBOB/datafile/o1_mf_mytbs_7g1
stt2r_.dbf
7 /oracle10g/app/oracle/oradata/
ANBOB/test_1.dbf
8 /oracle10g/app/oracle/oradata/
ANBOB/test_2.dbf
9 /oracle10g/app/oracle/oradata/
anbob/tbsdrop01.dbf
9 rows selected.
SQL> select ts#,file#,maxextend,inc,blocks from file$ ;
TS# FILE# MAXEXTEND INC BLOCKS
---------- ---------- ---------- ---------- ----------
0 1 4194302 1280 38400
1 2 4194302 640 3200
2 3 4194302 1280 15360
4 4 4194302 160 640
6 5 4194302 80 12800
7 6 4194302 12800 12800
8 7 0 0 1280
8 8 0 0 128
9 9 0 0 384
9 rows selected.
SQL> select ts#,name,owner#,online$,contents$,INC#, PITRSCNWRP from ts$;
TS# NAME OWNER# ONLINE$ CONTENTS$ INC# PITRSCNWRP
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
0 SYSTEM 0 1 0 1 0
1 UNDOTBS1 0 1 0 2 0
2 SYSAUX 0 1 0 1 0
3 TEMP 0 1 1 1 0
4 USERS 0 1 0 1 0
5 UNDOTBS2 0 3 0 1 0
6 EXAMPLE 0 1 0 1 0
7 MYTBS 0 1 0 1 0
8 TEST 0 1 0 1 0
9 TBSDROP 0 1 0 4 0
10 rows selected.
SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
note:
ts$表
online$ /* 1 = ONLINE, 2 = OFFLINE, 3 = INVALID */
drop tablespace 操作也并未真正的delete基表，这是她的一贯作为包括delete truncate table对数据块的管理,drop tablespace 是更新ts$表的online$更新为无效，同时更新file$表的原ts#为空,当再创建时这个编号还可以留给它使用。
file$基表也会变化 drop后=1，正常为2
status$ number not null, /* status (see KTS.H): */
/* 1 = INVALID, 2 = AVAILABLE */