How extents are deallocated

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.