| CLOB's not reclaiming space [message #614664] |
Sun, 25 May 2014 10:47  |
 |
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
Can somebody review my test case below and explain to me why I did not get any space (extents) reclaimed? I am guessing
that there is still some data left in the extent after the shrink, which did not release the extent. Is there a way
to prove that? Or is there another issue at hand that I am missing as I Would expect my table to be smaller.
Thanks in advance to all who answer
CREATE TABLE XXX.TAB2
(
ID NUMBER,
CLOB_DATA CLOB,
CLOB_DATA1 CLOB
)
TABLESPACE XXX_DAT;
!ls -ltr *.dat
-rw-r--r-- 1 oracle oinstall 44172533 May 16 19:42 XXX.dat
cat load_tab2.sql
DECLARE
l_bfile BFILE;
l_clob CLOB;
l_clob1 CLOB;
BEGIN
FOR i IN 1..200
LOOP
INSERT INTO xxx.tab2 (id, clob_data, clob_data1)
VALUES (i, empty_clob(), empty_clob())
RETURN clob_data, clob_data1 INTO l_clob, l_clob1;
l_bfile := BFILENAME('XXX', 'Xxx.dat');
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);
l_bfile := BFILENAME('XXX, 'xxx.dat');
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob1, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);
COMMIT;
END LOOP;
END;
select count(*) from xxx.tab2;
COUNT(*)
----------
200
select segment_name, sum(bytes)/1024/1024/1024 GB, count(*) EXTENTS
from dba_extents
where segment_name in ('SYS_LOB0000185327C00003$$', 'SYS_LOB0000185327C00002$$')
group by segment_name
SEGMENT_NAME GB EXTENTS
---------------------------- ---- ---------
SYS_LOB0000185327C00003$$ 8 316
SYS_LOB0000185327C00002$$ 8 317
SQL> alter table xxx.tab2 enable row movement;
Table altered.
set timing on;
SQL> alter table xxx.tab2 shrink space cascade;
Table altered.
Elapsed: 00:19:34.26
alter table XXX.tab2 disable row movement;
SEGMENT_NAME GB EXTENTS
-------------------------------- ---------- ----------
SYS_LOB0000185327C00002$$ 8.32226563 317
SYS_LOB0000185327C00003$$ 8.30566406 316
|
|
|
|
|
|
|
|
|
|
| Re: CLOB's not reclaiming space [message #614676 is a reply to message #614668] |
Sun, 25 May 2014 12:41  |
|
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
Swan, the case is quite simple. I have a table with 2 clobs loaded with data. I delete rows from the table
expecting that I would see space returned from the clobs after the shrink space cascade; but it does not appear that way or I am looking in the wrong direction.
If your not willing to provide assistance or construcive criticsm let me thank you for your time and perhaps somebody else here can help me out.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
