您好,登錄后才能下訂單哦!
--模擬數(shù)據(jù)插入
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
--說(shuō)明使用了新塊;
--查看表空間使用
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
--此時(shí)證明確實(shí)不能回收空塊,因?yàn)榉纸o表塊并沒(méi)有回收
--對(duì)表進(jìn)行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
--移動(dòng)到其它表空間
TEST88@nopdb>alter table TEST_TAB move tablespace users online;
Table altered.
--在移動(dòng)回來(lái)
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.
測(cè)試結(jié)果:只有移動(dòng)其它表空間才可以釋放已經(jīng)申請(qǐng)的塊。DROP 應(yīng)該也可以的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。