Home » RDBMS Server » Server Administration » CLOB's not reclaiming space (Oracle 11.2.0.3.8 Linux)
CLOB's not reclaiming space [message #614664] Sun, 25 May 2014 10:47 Go to next message
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 #614666 is a reply to message #614664] Sun, 25 May 2014 11:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm#ADMIN01401

> I Would expect my table to be smaller.
You expect wrongly.
Data consumes space in table.
The only way for table to become smaller is after data is DELETED from table.
You did no DELETE, so table remains the same size.
Re: CLOB's not reclaiming space [message #614667 is a reply to message #614666] Sun, 25 May 2014 11:26 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Swan,

Sorry my cut and paste did not pick up the delete step but I started with 200 rows and deleted 74 rows

select count(*) from xxx.tab2;

count(*)
-------
126
Re: CLOB's not reclaiming space [message #614668 is a reply to message #614667] Sun, 25 May 2014 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
did you take time to RTFM & consider how it pertains to your test case?
Re: CLOB's not reclaiming space [message #614676 is a reply to message #614668] Sun, 25 May 2014 12:41 Go to previous message
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.
Previous Topic: ALL DATAFILES ARE FUZZY
Next Topic: We have the requirment for character set conversion from WE8ISO8859P1 to AL32UTF8
Goto Forum:
  


Current Time: Fri Mar 29 05:54:11 CDT 2024