首页 » ORACLE » 10046跟踪drop tablespace

10046跟踪drop tablespace

这两天一个库因操作了基表的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#为空,当再创建时这个编号还可以留给它使用。

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. admin | #1
    2012-03-23 at 14:33

    file$基表也会变化 drop后=1,正常为2

    status$ number not null, /* status (see KTS.H): */
    /* 1 = INVALID, 2 = AVAILABLE */