这是一张550G的大表，表上还包括了CLOB和BLOB对象；我们来观察下Oracle drop这样一个大表时的具体表现:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
/* 为了避免被flashback table骚扰，关闭了recyclebin回收站功能 */
SQL> conn maclean/maclean
Connected.
SQL> col segment_name for a20
SQL> select segment_name,bytes/1024/1024/1024 "size in Gbytes" from user_segments where segment_name='TV';
SEGMENT_NAME size in Gbytes
-------------------- --------------
TV 547.25
SQL> select NUM_ROWS,BLOCKS from user_tables where table_name='TV';
NUM_ROWS BLOCKS
---------- ----------
859150100 65649786
SQL> desc tv;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SPARE1 CLOB
SPARE2 CLOB
SPARE3 CLOB
SPARE4 BLOB
/* 该大表包含CLOB、BLOB2种大对象，共859150100行数据，占用65649786个块
其所在是一个大文件表空间(bigfile tablespace)，本地区间管理方式，区间大小统一为128MB
*/
SQL> col tablespace_name for a2
SQL> select relative_fno,header_block,owner,tablespace_name from dba_segments where segment_name='TV';
RELATIVE_FNO HEADER_BLOCK OWNER TA
------------ ------------ ------------------------------ --
1024 82 MACLEAN BF
/* 因为是用bigfile tablespace技术，故数据段所在相对数据文件号为1024 */
SQL> col segment_name for a30
SQL> col owner for a10
SQL> select owner,segment_name,segment_type,header_block from dba_segments where relative_fno=1024;
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_BLOCK
---------- ------------------------------ ------------------ ------------
MACLEAN TV TABLE 82
MACLEAN SYS_IL0000057409C00014$$ LOBINDEX 32850
MACLEAN SYS_IL0000057409C00015$$ LOBINDEX 65618
MACLEAN SYS_IL0000057409C00016$$ LOBINDEX 98386
MACLEAN SYS_IL0000057409C00017$$ LOBINDEX 131154
MACLEAN SYS_LOB0000057409C00014$$ LOBSEGMENT 16466
MACLEAN SYS_LOB0000057409C00015$$ LOBSEGMENT 49234
MACLEAN SYS_LOB0000057409C00016$$ LOBSEGMENT 82002
MACLEAN SYS_LOB0000057409C00017$$ LOBSEGMENT 114770
9 rows selected.
/* 该bigfile数据文件1024上的数据段如上包括TV表本身以及LOB对象和LOB索引 */
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
44989856
/* 获取当前scn以便闪回数据库 */
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> set timing on;
SQL> drop table tv;
Table dropped.
Elapsed: 00:00:01.21
/* 虽然是550G的大表，但drop也仅耗时1.21s再次证明了drop仅仅是修改数据字典 */
通过tkprof分析的trace文件信息:
drop table tv
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.12 0.29 28 9 30163 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.12 0.29 28 9 30163 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 64
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 28 0.00 0.00
rdbms ipc reply 10 0.01 0.06
reliable message 8 0.00 0.00
enq: RO - fast object reuse 8 0.00 0.00
write complete waits 9 0.04 0.10
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 7.22 7.22
以上可以看到少量的等待事件，drop要求所被drop对象先做object对象级别的checkpoint检查点，以便将脏块写出；故而会出现Write complete waits(A user wants to modify a block that is part of DBWRÂ’s current write batch. When DBWR grabs buffers to write, it marks them as ‘being written’. All the collected buffers are then written to disk. The wait ‘write complete waits’ implies we wanted a buffer while this flag was set. The flags are cleared as each buffer is written)。同时要求获取enq: RO – fast object reuse(快速重用对象队列锁)。
整个drop流程还包括以下修改数据字典的操作:
delete from object_usage
where obj# in (select a.obj#
from object_usage a, ind$ b
where a.obj# = b.obj#
and b.bo# = :1)
/* 删除该表在对象使用情况基表(object_usage)中的纪录 */
delete from sys.cache_stats_1$ where dataobj# = :1
/* Jonathan Lewis的CBO fundamentals对该基本做了寥寥几笔的描述，该基表用于监控缓存统计信息
--which is used to monitor lifetime caching statistics
*/
delete com$ where obj# = :1
/* 删除对象相关的comment，com$ --comment table */
delete from hist_head$ where obj# = :1
delete from histgrm$ where obj# = :1
delete from dependency$ where d_obj# = :1
delete from source$ where obj# = :1
delete from idl_ub1$
where obj# = :1
and part = :2
delete from idl_char$
where obj# = :1
and part = :2
delete from idl_ub2$
where obj# = :1
and part = :2
delete from ncomp_dll$ where obj# = :1 returning dllname into :2
delete from idl_sb4$
where obj# = :1
and part = :2
delete from objauth$ where obj# = :1
delete from col$ where obj# = :1
delete from icol$ where bo# = :1
delete from icoldep$ where obj# in (select obj# from ind$ where bo# = :1)
delete from jijoin$
where obj# in (select obj#
from jijoin$
where tab1obj# = :1
or tab2obj# = :1)
delete from jirefreshsql$
where iobj# in (select iobj# from jirefreshsql$ where tobj# = :1)
delete from ccol$ where obj# = :1
delete from ind$ where bo# = :1
delete from cdef$ where obj# = :1
delete from tab$ where obj# = :1
delete coltype$ where obj# = :1
delete from subcoltype$ where obj# = :1
delete ntab$ where obj# = :1
delete lob$ where obj# = :1
delete refcon$ where obj# = :1
delete from opqtype$ where obj# = :1
之后将出现多次update seg$(数据段基表)的操作:
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17),scanhint=:18
/* 通过将trace文件中的bind variable还原可以看到Oracle实际执行的update语句 */
update seg$
set type# = 3,
blocks = 16384,
extents = 1,
minexts = 1,
maxexts = 2147483645,
extsize = 16384,
extpct = 0,
user# = 64,
iniexts = 16384,
lists = decode(0, 65535, NULL, :13),
groups = decode(0, 6 5535, NULL, :14),
cachehint = :0,
hwmincr = 57411,
spare1 = DECODE(164161, 0, NULL, :17),
scanhint = 0
where ts# = 12
and file# = 1024
and block# = 82
/* 以上update语句被多次执行，每次变化的仅有block#变量，依次为114770，82002，49234...82，
与上述的1024RFN数据文件上的各数据段的header_block头块对应
*/
/* 由此可知drop过程中Oracle所需要做的是对段在seg$基表上的纪录做修改，
将type由原值修改为3，也就是临时段的标记；同时extents重置为1。
*/
sql.bsq文件纪录了seg$基表上type#列的含义:
/* 1 = UNDO, 2 = SAVE UNDO, 3 = TEMPORARY, 4 = CACHE, 5 = DATA, 6 = INDEX
7 = SORT 8 = LOB 9 = Space Header 10 = System Managed Undo */
delete from obj$ where obj# = :1
/* 每次update完成后，都会伴随有以上删除obj$基表中对应对象纪录的语句 */
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
/* 若关闭了recyclebin(回收站,flashback table闪回表特性)，则在最后此前台进程还会删除已经
被置为type#=3的临时段在seg$字典基表上的纪录;如果打开了回收站则不会有此delete from seg$操作；
*/
SQL> select block#,type#,extents from sys.seg$ where file#=1024;
no rows selected
可以看到drop数据表作为一种DDL语句，其所需要完成的主要工作是完整删除数据字典中该表相关信息，并在seg$字典基表上将原有段类型(type)和大小修改为临时段和仅有1个区间，之后在obj$对象字典基表上将该对象的纪录彻底删除，此外还将释放数据文件头的区间位图信息。
SQL> alter system flush buffer_cache; System altered. SQL> alter system dump datafile '/g01/bf.dbf' block 3; System altered. File Space Bitmap Block: BitMap Control: RelFno: 1024, BeginBlock: 17, Flag: 0, First: 0, Free: 63488 0000000000000000 0000000000000000 0000000000000000 0000000000000000
