这是一张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
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277613
