A test to show how extents are deallocated in Nonclustered Tables.
It's said that "After you drop a nonclustered table, this space [comment:extents] can be reclainmed when other extents require free space" in ORACLE Concept Guide.
So I think if we drop a nonclustered table, if no one requires free extents, then the data in the extents will be reserved till some other objects wants free extents.
Let's do something to see if the way it works.
1> create a new table in SCOTT schema
SYS@ db10g> show user USER is "SYS" SYS@ db10g> connect scott/tiger Connected. SCOTT@ db10g> show user USER is "SCOTT" SCOTT@ db10g> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ----------
DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE
SCOTT@ db10g> create table test_extent ( 2 NAME VARCHAR2(30), 3 ID NUMBER);
Table created.
SCOTT@ db10g> INSERT INTO TEST_EXTENT VALUES ('GAOFENG, ITID',291);
1 row created.
SCOTT@ db10g> COMMIT;
Commit complete.
|
2> Check the data file and block id of this table
SYS@ db10g> show user USER is "SYS" SYS@ db10g> desc dba_extents; Name Null? Type ----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER
SYS@ db10g> select SEGMENT_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SCOTT';
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS ------------------------------ ---------- ---------- ---------- ----------
DEPT 4 0 9 8 EMP 4 0 25 8 BONUS 4 0 41 8 SALGRADE 4 0 49 8 TEST_EXTENT 4 0 385 8 PK_DEPT 4 0 17 8 PK_EMP 4 0 33 8
7 rows selected.
|
So we can see that the datafile's ID is 4, and block id is 385, which occupied 8 blocks.
Let's get the physical datafile
SYS@ db10g> desc dba_data_files; Name Null? Type ----------------------------------------------------------------- -------- --------------------------------------------
FILE_NAME VARCHAR2(513) FILE_ID NUMBER TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER STATUS VARCHAR2(9) RELATIVE_FNO NUMBER AUTOEXTENSIBLE VARCHAR2(3) MAXBYTES NUMBER MAXBLOCKS NUMBER INCREMENT_BY NUMBER USER_BYTES NUMBER USER_BLOCKS NUMBER ONLINE_STATUS VARCHAR2(7)
SYS@ db10g> select FILE_NAME from dba_data_files where FILE_ID=4;
FILE_NAME ------------------------------------------------------------------------------------------------------------------------
/opt/oracle/product/10.2.0/oradata/db10g/users01.dbf
|
3> To get the very blocks start with number 385
[oracle@yuzhong tmp]$ dd if=/opt/oracle/product/10.2.0/oradata/db10g/users01.dbf of=/tmp/test_extent.raw bs=8k skip=385 count=8 8+0 records in 8+0 records out [oracle@yuzhong tmp]$ file /tmp/test_extent.raw /tmp/test_extent.raw: data
|
4> To verify if the raw data contains the data we inserted into
[oracle@yuzhong tmp]$ strings /tmp/test_extent.raw|grep GAOFENG GAOFENG, ITID
|
5> To drop the test_extent table totally
SYS@ db10g> drop table scott.test_extent;
Table dropped.
SYS@ db10g> conn scott/tiger Connected. SCOTT@ db10g> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ -------------------
TEST_EXTENT BIN$TeGi2gzTGvDgQKjAMQEcng==$0 TABLE 2008-05-23:16:30:01 SCOTT@ db10g> purge RECYCLEBIN;
Recyclebin purged.
SCOTT@ db10g> show recyclebin; SCOTT@ db10g> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ----------
DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE
|
6> To check if the content we inserted into the table still there after the table be deleted.
[oracle@yuzhong tmp]$ date Fri May 23 16:31:45 CST 2008 [oracle@yuzhong tmp]$ rm /tmp/test_extent.raw [oracle@yuzhong tmp]$ dd if=/opt/oracle/product/10.2.0/oradata/db10g/users01.dbf of=/tmp/test_extent.raw bs=8k skip=385 count=8 8+0 records in 8+0 records out [oracle@yuzhong tmp]$ ls -l /tmp/test_extent.raw -rw-r--r-- 1 oracle dba 65536 May 23 16:31 /tmp/test_extent.raw [oracle@yuzhong tmp]$ strings /tmp/test_extent.raw|grep GAOFENG GAOFENG, ITID
|
Conclusion: that's it just like concept state.
阅读(760) | 评论(0) | 转发(0) |