Oracle 数据文件回收

--模拟数据插入

DECLARE

I NUMBER(10);

BEGIN

FOR I IN 1 .. 50000 LOOP

INSERT INTO TEST_TAB VALUES (I, 'TESTSTRING');

END LOOP;

COMMIT;

END;

/

PL/SQL procedure successfully completed.

TEST88@nopdb>select count(*) from TEST_TAB;

COUNT(*)

----------

50000

SYS@nopdb>select file_id, max(block_id) from dba_extents where file_id=7 group by file_id;

FILE_ID MAX(BLOCK_ID)

---------- -------------

7 155776

SYS@nopdb>select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents where file_id=7 group by file_id;

FILE_ID HWMSIZE

---------- ----------

7 1217

--MOVE 操作

TEST88@nopdb>alter table TEST_TAB move online;

Table altered.

TEST88@nopdb>select count(*) from TEST_TAB;

COUNT(*)

----------

50000

SYS@nopdb>select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents where file_id=7 group by file_id;

FILE_ID HWMSIZE

---------- ----------

7 1290

SYS@nopdb>select file_id, max(block_id) from dba_extents where file_id=7 group by file_id;

FILE_ID MAX(BLOCK_ID)

---------- -------------

7 165120

--说明使用了新块；

--查看表空间使用

TABLESPACE_NAME AUTOEXTENSIBLE CURRENT_SPACE_G CURRENT_USED_G MAX_SIZE_G TOTAL_USED_PCT

---------------------------------------- --------------- --------------- -------------- ---------- --------------

TEST88 NO 2 .01 2 .00

SYS@nopdb>alter database datafile 7 resize 1g;

alter database datafile 7 resize 1g

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

--此时证明确实不能回收空块，因为分给表块并没有回收

--对表进行truncate

TEST88@nopdb>truncate table TEST_TAB;

TEST88@nopdb>select count(*) from TEST_TAB;

COUNT(*)

----------

0

SYS@nopdb>select file_id, max(block_id) from dba_extents where file_id=7 group by file_id;

FILE_ID MAX(BLOCK_ID)

---------- -------------

7 163984

SYS@nopdb>select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents where file_id=7 group by file_id;

FILE_ID HWMSIZE

---------- ----------

7 1281

Table truncated.

表空间使用率：

TABLESPACE_NAME AUTOEXTENSIBLE CURRENT_SPACE_G CURRENT_USED_G MAX_SIZE_G TOTAL_USED_PCT

---------------------------------------- --------------- --------------- -------------- ---------- --------------

TEST88 NO 2 0 2 .00

--移动到其它表空间

TEST88@nopdb>alter table TEST_TAB move tablespace users online;

Table altered.

--在移动回来

TEST88@nopdb>alter table TEST_TAB move tablespace TEST88 online;

Table altered.

SYS@nopdb>select file_id, max(block_id) from dba_extents where file_id=7 group by file_id;

FILE_ID MAX(BLOCK_ID)

---------- -------------

7 136

SYS@nopdb>select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents where file_id=7 group by file_id;

FILE_ID HWMSIZE

---------- ----------

7 1

SYS@nopdb>alter database datafile 7 resize 2m;

Database altered.

测试结果：只有移动其它表空间才可以释放已经申请的块。DROP 应该也可以的。