Works ... sometimes
Michael, January 13, 2003 - 10:34 am UTC
Thanks Tom!
According to your answer i tried the following:
drop tablespace lobtest including contents;
CREATE TABLESPACE "LOBTEST"
LOGGING DATAFILE 'E:\APPS\ORACLE\ORADATA\ORA92DB\LOBTEST.ORA' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
create table t ( x blob );
declare
l_blob blob;
l_size number := 31191-7;
begin
insert into t values ( empty_blob() ) returning x into l_blo
for i in 1 .. 15
loop
dbms_lob.writeappend( l_blob, l_size,
utl_raw.cast_to_raw(rpad('*',l_size,'*')) );
l_size := 31191;
end loop;
commit;
end;
/
select dbms_lob.getlength(x) from t;
DBMS_LOB.GETLENGTH(X)
---------------------
467858
Table t is ready ...
CREATE TABLE "LOB_TAB" ("THEBLOB" BLOB)
TABLESPACE "LOBTEST"
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
LOGGING LOB("THEBLOB") STORE AS
( TABLESPACE "LOBTEST" STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1
MAXEXTENTS 2147483645 PCTINCREASE 0)
ENABLE STORAGE IN ROW NOCACHE CHUNK 8192 PCTVERSION 10)
/
The script with the dbms_outputs then reports exactly the same results as yours.
The next tests:
truncate table lob_tab;
begin
for i in 1 .. 7
loop
insert into lob_tab(theBlob) select x from t;
end loop;
commit;
end;
/
select dbms_lob.getlength(theBlob) from lob_tab;
DBMS_LOB.GETLENGTH(THEBLOB)
---------------------------
467858
467858
467858
467858
467858
467858
467858
select sum(dbms_lob.getlength(theBlob))/1024/1024, count(*)
from lob_tab;
SUM(DBMS_LOB.GETLENGTH(THEBLOB))/1024/1024 COUNT(*)
------------------------------------------ ----------
3,12328911 7
select a.tablespace_name,
(select sum(bytes)/1024 from dba_free_space where tablespace_name =
a.tablespace_name) free,
(select sum(bytes)/1024 from dba_extents where tablespace_name =
a.tablespace_name) used,
(select count(*) from dba_extents where tablespace_name =
a.tablespace_name) extents
from dba_tablespaces a
where tablespace_name = 'LOBTEST'
/
TABLESPACE_NAME FREE USED EXTENTS
------------------------------ ---------- ---------- ----------
LOBTEST 3904 6272 23
So, it reported exactly the same. Your next query (which shows the extents from the lob segment) reports exactly the "correct" result, too.
The (last) test:
delete from lob_tab;
commit;
select sum(dbms_lob.getlength(theBlob))/1024/1024, count(*)
from lob_tab;
SUM(DBMS_LOB.GETLENGTH(THEBLOB))/1024/1024 COUNT(*)
------------------------------------------ ----------
0
begin
for i in 1 .. 10000
loop
insert into lob_tab(theBlob) select x from t;
commit;
end loop;
end;
/
... ERROR about lob segment ...
select sum(dbms_lob.getlength(theBlob))/1024/1024, count(*)
from lob_tab
/
SUM(DBMS_LOB.GETLENGTH(THEBLOB))/1024/1024 COUNT(*)
------------------------------------------ ----------
4,90802574 11
Looks good, but then:
delete lob_tab;
commmit;
begin
for i in 1 .. 10000
loop
insert into lob_tab(theBlob) select x from t;
commit;
end loop;
end;
/
... ERROR about lob segment ...
select sum(dbms_lob.getlength(theBlob))/1024/1024, count(*)
from lob_tab;
SUM(DBMS_LOB.GETLENGTH(THEBLOB))/1024/1024 COUNT(*)
------------------------------------------ ----------
2,23092079 5
Strange ... sometimes i can insert 11 or 10 and sometimes only 4 or 5 blobs after delete+commit.
Why?
January 13, 2003 - 12:47 pm UTC
do you -- in a single transaction -- frequently delete all rows and then reuse them? If so, go for truncate.
If not, let's simulate what you do for real.... The space will get used and reused differently depending on what freelists things get placed on -- how the automatic segment space management feels like doing it - pctversion will kick in (for read consistency) many things.
bottom line -- over time, space is reused.
Space is reused
Michael, January 14, 2003 - 3:27 am UTC
Hi Tom!
I tested this case with a test table and simulated the "real world" with the following script:
declare
l_del_id number(38);
l_ins_id number(38);
begin
for i in 1 .. 100 loop
l_del_id := round(dbms_random.value(0.51, 6.49), 0);
delete test_tab
where id = l_del_id;
dbms_output.put_line('Deleted id = ' || l_del_id);
commit;
l_ins_id := round(dbms_random.value(0.51, 3.49), 0);
insert into test_tab(id, content)
select l_del_id, content
from source_tab
where id = l_ins_id;
dbms_output.put_line('Inserted id = ' || l_ins_id);
commit;
end loop;
end;
It worked without any problems. So the space gets reuse :-)
Of course the tablespace is quite "full".
When i delete nearly all the records (and blobs) (no truncate) is it possible (and how) to resize (= make smaller) the tablespace/datafile?
January 14, 2003 - 7:45 am UTC
once allocated -- extents stay with the table (they are complex data structures remember) until
o drop
o truncate
o explicit deallocate (but only works for NEVER USED extents)
so no, they are part of that table.
Good idea???
Michael, January 14, 2003 - 10:53 am UTC
Hi Tom!
What's your opinion of using the following statements to reclaim space:
For "normal" (= without lobs):
alter table t move;
Additional for tables with one or more lobs:
alter table lob_tab lob(theblob) store as (tablespace lobtest);
Is it a good/bad idea to use this statements after deletes that affected (= deleted) many rows to get free space for other tables/objects?
January 14, 2003 - 11:04 am UTC
If I was deleting a huge percentage of the table to begin with -- i would not have used delete!
create table as select <rows to keep>
or use partitioning to just drop old data.
but yes, if you delete a ton and want to reorg alter table move or dbms_redefinition (in 9i) would be the way to do it.
Query for used/total ratio
Michael, January 15, 2003 - 4:24 am UTC
One (last) thing:
Could you provide a query/procedure which shows the ratio/percentage (and absolute values) of really used space (=filled with data at the moment) and the total allocated space of a table, index etc.
e.g.:
TABLE TOTAL_BLOCKS USED_BLOCKS
-----------------------------------
EMP 200 100
DEPT 50 10
(I tried DBMS_SPACE package, but i didn't succeeded.)
January 15, 2003 - 8:28 am UTC
well, user_tables will give you that information after an analyze. but you can do this with a function if you like:
ops$tkyte@ORA920> create or replace function get_block_info
2 ( p_segname in varchar2,
3 p_owner in varchar2 default user,
4 p_type in varchar2 default 'TABLE',
5 p_partition in varchar2 default NULL )
6 return number
7 authid current_user
8 as
9 l_total_blocks number;
10 l_total_bytes number;
11 l_unused_blocks number;
12 l_unused_bytes number;
13 l_LastUsedExtFileId number;
14 l_LastUsedExtBlockId number;
15 l_LAST_USED_BLOCK number;
16 begin
17 dbms_space.unused_space
18 ( segment_owner => p_owner,
19 segment_name => p_segname,
20 segment_type => p_type,
21 partition_name => p_partition,
22 total_blocks => l_total_blocks,
23 total_bytes => l_total_bytes,
24 unused_blocks => l_unused_blocks,
25 unused_bytes => l_unused_bytes,
26 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
27 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
28 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
29
30 return l_total_blocks || '.' || (l_total_blocks-l_unused_blocks);
31 exception
32 when others then return NULL;
33 end;
34 /
Function created.
ops$tkyte@ORA920> set define on
ops$tkyte@ORA920>
ops$tkyte@ORA920> select table_name,
2 trunc(gbi) total_blocks,
3 to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
4 from ( select table_name, get_block_info( table_name ) gbi, rownum
5 from user_tables
6 )
7 /
TABLE_NAME TOTAL_BLOCKS USED_BLOCKS
------------------------------ ------------ -----------
A 8 3
AO 512 417
ATTRIBUTES 8 8
B 8 3
CLUSTERED 1152 1132
D 8 8
DEPT 8 4
DR$MY_CONTENT_IX$I 8 8
USER_TABLES
Michael, January 16, 2003 - 2:58 am UTC
Hi Tom!
Which columns of user_tables contain the information about used or empty blocks of a table?
I tried EMPTY_BLOCKS and NUM_FREELIST_BLOCKS, but it didn't delivered the data i would expect.
(Perhaps my expectations are wrong ;-)
January 16, 2003 - 8:35 am UTC
did you analyze the table -- they are the ones. only current right after an analyze.
USER_TABLES: It IS analyzed
Michael, January 20, 2003 - 2:40 am UTC
Yes, the table/fields are analyzed:
desc t
Name Null? Typ
---------------------------- -------- ----------------
X VARCHAR2(2000)
truncate table t;
analyze table t compute statistics
for table for all columns;
select num_rows, blocks, empty_blocks, num_freelist_blocks
from user_tables
where table_name = 'T';
NUM_ROWS BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
0 0 8 0
insert into t
select dbms_random.string('X', 2000)
from all_objects where rownum <= 1000;
commit;
analyze table t compute statistics
for table for all columns;
select num_rows, blocks, empty_blocks, num_freelist_blocks
from user_tables
where table_name = 'T';
NUM_ROWS BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
1000 370 14 0
delete t;
commit;
analyze table t compute statistics
for table for all columns;
select num_rows, blocks, empty_blocks, num_freelist_blocks
from user_tables
where table_name = 'T';
NUM_ROWS BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
0 370 14 0
It still shows the same information about the blocks. (NUM_ROWS is OK.)
I expected:
BLOCKS = 370, EMPTY_BLOCKS = 14,
and NUM_FREELIST_BLOCKS (= blocks which are "owned" by the table, but not used at the moment?) about 350-360.
Where is my error?
January 20, 2003 - 10:51 am UTC
maybe you are not using freelists. hard to say -- no version info, no environment issue. It does make it more of a brain teaser for me this way -- but does make it take longer to answer questions. Oh well...
I set up two tablespaces -- auto_tbs -- using automatic segment space management (NO freelists) and manual_tbs -- using manual segment space managment (freelists)
ops$tkyte@ORA920> create table t ( x varchar2(2000) ) tablespace auto_tbs;
Table created.
ops$tkyte@ORA920> insert into t
2 select dbms_random.string('X', 2000)
3 from all_objects where rownum <= 1000;
1000 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> exec show_space( 'T' );
Auto segment space managed...
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0<b>
FS3 Blocks (50-75) .....................1
FS4 Blocks (75-100).....................36
Full Blocks .....................333</b>
Free Blocks.............................
Total Blocks............................384
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................11
Last Used Ext BlockId...................60680
Last Used Block.........................128
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> delete t;
1000 rows deleted.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920> analyze table t compute statistics
2 for table for all columns;
Table analyzed.
ops$tkyte@ORA920> select num_rows, blocks, empty_blocks, num_freelist_blocks
2 from user_tables
3 where table_name = 'T';
NUM_ROWS BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
0 370 14 0
<b>0 on the freelist -- but since I have no freelists -- this is expected and OK</b>
ops$tkyte@ORA920> exec show_space( 'T' );
Auto segment space managed...
Unformatted Blocks .....................0
FS1 Blocks (0-25) .....................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................0<b>
FS4 Blocks (75-100).....................370</b>
Full Blocks .....................0
Free Blocks.............................
Total Blocks............................384
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................11
Last Used Ext BlockId...................60680
Last Used Block.........................128
PL/SQL procedure successfully completed.
<b>they are all marked as 75-100% EMPTY (free)</b>
ops$tkyte@ORA920>
<b>now, doing the same in a manual tablespace:</b>
ops$tkyte@ORA920> drop table t;
Table dropped.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( x varchar2(2000) ) tablespace manual_tbs;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t
2 select dbms_random.string('X', 2000)
3 from all_objects where rownum <= 1000;
1000 rows created.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec show_space( 'T' );
Dictionary space managed...
Free Blocks.............................2
Free Blocks.............................2
Total Blocks............................384
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................48
Unused Bytes............................393216
Last Used Ext FileId....................12
Last Used Ext BlockId...................68873
Last Used Block.........................80
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> delete t;
1000 rows deleted.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table t compute statistics
2 for table for all columns;
Table analyzed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> select num_rows, blocks, empty_blocks, num_freelist_blocks
2 from user_tables
3 where table_name = 'T';
NUM_ROWS BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ---------- ------------ -------------------
0 335 48 335
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec show_space( 'T' );
Dictionary space managed...
Free Blocks.............................335
Free Blocks.............................335
Total Blocks............................384
Total Bytes.............................3145728
Total MBytes............................3
Unused Blocks...........................48
Unused Bytes............................393216
Last Used Ext FileId....................12
Last Used Ext BlockId...................68873
Last Used Block.........................80
PL/SQL procedure successfully completed.
<b>different strokes for different things...</b>
LOB space usage
Geoff Smith, January 15, 2004 - 8:22 am UTC
Hi Tom,
Hope you enjoyed Denmark! I would have been there but had to hang back at the last minute to do a major re-org of a production database, which they cancelled on Sat am!!! any way, Many thanks for a great column. Your astute clarity is a joy to read and understand.
Geoff (BARF #31) Smith
Tablespace - Reorg
A reader, October 14, 2004 - 4:40 pm UTC
Hi Tom I have a tablespace containing LOB data . It was allocated with 32GB space . But only 7GB are used , how do i de-allocate the space ? I coudnt do resize - it gave me an error . Am On Oracle 9.2.0.3.0
October 14, 2004 - 7:50 pm UTC
my car wont start. gives me an error. I'm on version 1.0 of car. :)
search this site for maxshrink
that script will tell you the smallest you can make that tablespace (and the text in the q&a probably will answer your question)
How much overhead?
John Spencer, October 15, 2004 - 1:19 pm UTC
Tom:
In your response to the original post, you said "there is some overhead -- each blob will take more room then it would if it were just a file in the filesystem.". I can accept that, but how much overhead is there?
I ran your test on a database with 16Kb block size (9.2.0.5, Solaris 9.5). Creating the tablespace exactly as you did, resulted in 1Mb extent sizes. The results I got for the largest blob were:
SYS_LOB0000046374C00001$$ extent id 0 bytes 1048576
SYS_LOB0000046374C00001$$ extent id 1 bytes 1048576
SYS_LOB0000046374C00001$$ extent id 2 bytes 1048576
l_blob = 467858 bytes, allocated = 3145728 pct used = 14.87
----------------------------------------------------------------------
This was the highest pct used for all 15 sizes. Why 3 1Mb extents for 457K of data?
Thanks
October 18, 2004 - 10:32 am UTC
looks like it was side effect of ASSM spreading stuff out far and wide:
ops$tkyte@ORA9IR2> CREATE TABLESPACE "LOBTEST"
2 LOGGING DATAFILE SIZE 10M
3 blocksize 16k
4 EXTENT MANAGEMENT LOCAL
5 uniform size 1m
6 SEGMENT SPACE MANAGEMENT manual;
Tablespace created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE "LOB_TAB" ("THEBLOB" BLOB)
2 TABLESPACE "LOBTEST"
3 PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
4 STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
5 LOGGING LOB("THEBLOB") STORE AS
6 ( TABLESPACE "LOBTEST" STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
7 ENABLE STORAGE IN ROW NOCACHE CHUNK 8192 PCTVERSION 10);
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( x blob );
Table created.
ops$tkyte@ORA9IR2> declare
2 l_blob blob;
3 l_size number := 31191-7;
4 begin
5 insert into t values ( empty_blob() ) returning x into l_blob;
6 for i in 1 .. 15
7 loop
8 dbms_lob.writeappend( l_blob, l_size, utl_raw.cast_to_raw(rpad('*',l_size,'*')) );
9 l_size := 31191;
10 end loop;
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> declare
2 l_blob blob;
3 l_src blob;
4 l_bytes number;
5 begin
6 select x into l_src from t;
7
8 for i in 1 .. 15
9 loop
10 execute immediate 'truncate table lob_tab';
11 insert into lob_tab (theblob)
12 values ( empty_blob() )
13 returning theblob into l_blob;
14
15 dbms_lob.copy( l_blob, l_src, i * 32765 );
16 commit;
17
18 l_bytes := 0;
19 for x in
20 ( select segment_name || ' extent id ' ||
21 extent_id || ' bytes ' || bytes data, bytes
22 from dba_extents
23 where tablespace_name = 'LOBTEST'
24 and segment_type = 'LOBSEGMENT'
25 order by segment_name, segment_type, extent_id )
26 loop
27 dbms_output.put_line( x.data );
28 l_bytes := l_bytes + x.bytes;
29 end loop;
30 dbms_output.put_line( 'l_blob = ' || dbms_lob.getlength(l_blob) ||
31 ' bytes, allocated = ' || l_bytes ||
32 ' pct used = ' ||
33 round(dbms_lob.getlength(l_blob)/l_bytes * 100,2 ) );
34 dbms_output.put_line( rpad('-',70,'-') );
35 end loop;
36 end;
37 /
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 32765 bytes, allocated = 1048576 pct used = 3.12
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 65530 bytes, allocated = 1048576 pct used = 6.25
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 98295 bytes, allocated = 1048576 pct used = 9.37
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 131060 bytes, allocated = 1048576 pct used = 12.5
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 163825 bytes, allocated = 1048576 pct used = 15.62
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 196590 bytes, allocated = 1048576 pct used = 18.75
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 229355 bytes, allocated = 1048576 pct used = 21.87
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 262120 bytes, allocated = 1048576 pct used = 25
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 294885 bytes, allocated = 1048576 pct used = 28.12
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 327650 bytes, allocated = 1048576 pct used = 31.25
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 360415 bytes, allocated = 1048576 pct used = 34.37
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 393180 bytes, allocated = 1048576 pct used = 37.5
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 425945 bytes, allocated = 1048576 pct used = 40.62
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 458710 bytes, allocated = 1048576 pct used = 43.75
----------------------------------------------------------------------
SYS_LOB0000031997C00001$$ extent id 0 bytes 1048576
l_blob = 467858 bytes, allocated = 1048576 pct used = 44.62
----------------------------------------------------------------------
PL/SQL procedure successfully completed.
Reclaiming BLOB space
Tracy, July 29, 2005 - 10:23 am UTC
When you empty_blob() a blob I imagined that all the space taken by that blob would become available again but it does not seem to be the case. The BLOB_DATA tablespace here is a 1MB uniform LMT.
mct@db1> CREATE TABLE BLOBTABLE
2 (
3 ID NUMBER(12) NOT NULL,
4 IMAGE BLOB
5 )
6 TABLESPACE USER_DATA
7 LOB (IMAGE) STORE AS
8 ( TABLESPACE BLOB_DATA
9 DISABLE STORAGE IN ROW
10 CHUNK 16384
11 PCTVERSION 10
12 NOCACHE
13 STORAGE (
14 INITIAL 1M
15 NEXT 1M
16 )
17 );
Table created.
mct@db1> insert into blobtable select * from oldblobtable;
1212 rows created.
mct@db1> drop table oldblobtable;
Table dropped.
mct@db1> CREATE TABLE BLOBTABLE2
2 (
3 ID NUMBER(12) NOT NULL,
4 IMAGE BLOB
5 )
6 TABLESPACE USER_DATA
7 LOB (IMAGE) STORE AS
8 ( TABLESPACE BLOB_DATA
9 DISABLE STORAGE IN ROW
10 CHUNK 16384
11 PCTVERSION 10
12 NOCACHE
13 STORAGE (
14 INITIAL 1M
15 NEXT 1M
16 )
17 );
Table created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 1,048,576 (THIS IS THE BLOB SPACE FOR THE EMPTY TABLE)
SYS_LOB0000038263C00002$$ 28,311,552 (THIS IS THE BLOB SPACE FOR THE BLOB TABLE WITH 1212 ROWS)
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 28,311,552 (SPACE INCREASE AS EXPECTED)
SYS_LOB0000038263C00002$$ 28,311,552
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 55,574,528 (SPACE INCREASE AS EXPECTED AGAIN)
SYS_LOB0000038263C00002$$ 28,311,552
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 82,837,504 (SPACE INCREASE AS EXPECTED AGAIN)
SYS_LOB0000038263C00002$$ 28,311,552
mct@db1> update blobtable2 set image = empty_blob();
3636 rows updated.
mct@db1> commit;
Commit complete.
(Now I would expect to be able to insert from BLOBTABLE three times without an increase in space)
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 82,837,504
SYS_LOB0000038263C00002$$ 28,311,552
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 82,837,504
SYS_LOB0000038263C00002$$ 28,311,552
mct@db1> insert into blobtable2 select * from BLOBTABLE;
1212 rows created.
mct@db1> select segment_name,bytes from dba_segments where tablespace_name = 'BLOB_DATA' and segment_type = 'LOBSEGMENT';
SEGMENT_NAME BYTES
------------------------------ ----------------
SYS_LOB0000038266C00002$$ 91,226,112 (This third insert has claimed more space - why?)
SYS_LOB0000038263C00002$$ 28,311,552
July 29, 2005 - 2:35 pm UTC
You have a pctversion in there, blobs are not put into undo, they are left in their tablespace as you modify them and versioned out there. There will be old versions of blob data out there for this reason (and will be why you'll not be reusing blob space WITHIN a transaction, the old blob data is your UNDO)
BLOB
Tracy, July 30, 2005 - 6:58 am UTC
Thanks for your help, as always. I had forgotten all about PCTVERSION.
I've been a DBA for many years and happily I'm learning new things about Oracle every day - trouble is I'm also forgetting almost as many things every day too.
July 30, 2005 - 8:59 am UTC
Funny (to me) story.
Hotsos conference, two or three years ago.
Funny fellow by the name of Connor is giving a presentation on "9i bits you might have missed". Just a collection of little, often overlooked "new features"
He is describing this one feature, select for update wait N -- a select for update that blocks and waits for some unit of time and then continues (raising an error if it could not get the row)
I write that down, that's pretty cool.
But then he says "and since he's in the audience, I'll point out that it was Tom Kyte that told me about this"
And I had forgotten I had known :)
Clarification on LOB and index
Frank, March 13, 2006 - 3:19 pm UTC
I found this looking for a way to reorg a table with LOB in it, but I'm unclear on something.
I'm going to re-org the table using this
alter table TABLE1 move LOB (LOB1) store as (tablespace OROGINAL_TABLESPACE)
basicaly, I'm only trying to reorganise a table without having to move it to a different tablespace.
Once I have done this, I will want to rebuild indexes using
alter index INDEX1 rebuild;
My questions:
1: Do I need to rebuild the indexes for the LOB?
2: Will reorging the LOB inside the same tablespace cause any issue if I am sure I have enough free space inside it?
3: Is there a way to shrink only the LOB, as a reorg was previously done on the table, but the LOB not taken into account.
March 14, 2006 - 9:52 am UTC
1) no, that is done for you. In fact, there is no way for you do reorganzie a lob index short of moving the lob.
2) no, not really.
3) not in current releases, no.
blob file name
D.Ghosh, March 17, 2006 - 6:43 am UTC
I am using following command to see the file name which is stored in blob column
SQL> select dbms_lob.filegetname(theblob) from demo;
SQL> /
select dbms_lob.filegetname(theblob) from demo
*
ERROR at line 1:
ORA-00904: "DBMS_LOB"."FILEGETNAME": invalid identifier
thanks for advance
DG
Still not reusing
Pavel, July 27, 2006 - 2:33 am UTC
I have the similar problem and hope that you, Tom, can help me to solve it.
The problem: we have a production system which has a table with a very large BLOB field. To not overflow the size of the database we decided to do next strategy: there is a job that runs every day and write nulls to BLOB field of all records that are older than month ago. This strategy seemed to be very good. But now we saw and were very surprised that our database grows constantly in time. After analyzing this problem I saw that the segment for this BLOB field is significantly larger than the data stored in it. Results of some selects follow:
SQL> select sum(dbms_lob.getlength(col))
2 from tab;
SUM(DBMS_LOB.GETLENGTH(DETAILS
------------------------------
2389289695
SQL> select avg(dbms_lob.getlength(details))
2 from ab_build_result
3 where details is not null
4 and dbms_lob.getlength(details) > 0;
AVG(DBMS_LOB.GETLENGTH(DETAILS
------------------------------
5917.78934580319
SQL> select bytes, blocks, extents
2 from user_segments
3 where segment_name = 'SYS_LOB0000026003C00008$$';
BYTES BLOCKS EXTENTS
---------- ---------- ----------
1554513920 1897600 416
SQL> select bytes, blocks, count(*)
2 from user_extents
3 where segment_name = 'SYS_LOB0000026003C00008$$'
4 group by bytes, blocks;
BYTES BLOCKS COUNT(*)
---------- ---------- ----------
65536 8 16
1048576 128 63
8388608 1024 120
14680064 1792 1
30408704 3712 1
65011712 7936 1
67108864 8192 214
7 rows selected
SQL> select * from user_tablespaces
2 where tablespace_name = 'USERS'
3 ;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
USERS 8192 65536 1
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FORCE_LOGGING
----------- ------------ ---------- --------- --------- --------- -------------
2147483645 65536 ONLINE PERMANENT LOGGING NO
EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION
----------------- --------------- ------------------------ -------------------
LOCAL SYSTEM AUTO DISABLED
(Last query I beautified a bit.)
So I see that we use 15Gb of disk space to store 2Gb of data and I can't understand were I lose extra space? I wonder that the problem is in definition of tablespace or table but I haven't admin priveleges in database (I'm only developer) and can't export this table to see its definition. Is there any ability to see it via select? I can say about this definitions only one thing: our tablespace is as described in a query shown above and it's 99% that all storage definitions wasn't specified during table creation and they are default for such tablespace.
And final question: can you make some suggestions and advices on why this problem happens and how to solve it? I'll forward your advices to our DBAs...
P.S. Our database is Oracle9i Enterprise Edition Release 9.2.0.6.0.
July 27, 2006 - 11:49 am UTC
what is the segment space management setting for the tablespace.
Re: Still not reusing
Pavel, July 27, 2006 - 1:52 pm UTC
SEGMENT_SPACE_MANAGEMENT = AUTO
This value shown in the above post is not the answer on your question?
July 27, 2006 - 2:27 pm UTC
I glance at these, didn't see that.
it is likely that you don't want to use ASSM for this, auto segment space management wastes space for concurrency - not really appropriate for lobs and warehouse implementations in general.
Can we create a new tablespace and using alter table move - move the lob to it? It'll get back all of the existing space (compact the storage) and will reduce the "wastage"
Re: Still not reusing
Pavel, July 28, 2006 - 1:13 am UTC
As we have no another way to heal this database we will try to find extra space for this operation. Am I right that this moving will use additional space equal to sum of the total size of the table and LOB? Or it will first prepare all moving data in the temporary tablespace and so will use additional space equal to moved size doubled? And how about undo data, how much undo data (hence additional disk space) will generate this moving?
July 28, 2006 - 7:46 pm UTC
moving will require space for the existing table data PLUS the space for the newly moved table at the same time, yes. There will be a point in time when both are there.
alter table move doesn't generate undo for the table - does generate redo in archivelog mode (unless you do it nologging, but backup right away if you do). The alter move is a direct path read from one place and a direct path write to another. skips undo generation for the table data being moved.
reclaim space of lob partition
A.K, April 15, 2008 - 8:40 am UTC
Hi Tom,
I have a table which has a lob column and the table is list partitioned. There are more inserts, deletes and updated on couple of partitions. We are on 10.1.0.5 on windows 2000.
My question is if I use:
alter table table_name enable row movement;
alter table table_name shrink space cascade compact;
ALTER TABLE table_name SHRINK SPACE;
It is only shrinking the table but not the partitions and this is invalidating the packages and procedures.
If I use:
ALTER TABLE table_name
MOVE LOB (lob_column) STORE AS INFO
( TABLESPACE DATAMEDIUM
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
INDEX(TABLESPACE DATASMALL)
)
This is taking long time and DML are not possible until it completes moving right?
One of the partitions in the table has these storage parameters:
initial: 25600 KB, next:25600 KB, %inc: 0, Extents:222 and maxextents:2147483645.
Is there anyway to reclaim the space of the lobpartition without invalidating packages and procedures and allowing DML when the process is going on.
Thanks
April 16, 2008 - 2:57 pm UTC
... It is only shrinking the table but not the partitions and this is invalidating
the packages and procedures.
...
that doesn't compute. First, the table in this case is a logical entity, the partitions are the only think that can be "shrunk". Second, it would not invalidate things.
so, I think you meant "log segments" where you wrote "partitions"
http://docs.oracle.com/docs/cd/B14117_01/server.101/b10739/schema.htm#sthref1783 in 10gr1, shrink space did not work for LOBS
... Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.
Segment shrink is not supported for LOB segments or for tables with function-based indexes. Please refer to Oracle Database SQL Reference for the syntax and additional information on shrinking a database object, including restrictions. ...
in 10gr2 - it is
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14231/schema.htm#sthref2143 You may use dbms_redefinition to perform an online redefine of the entire table.
reclaim space of lob partition
A.K, April 17, 2008 - 9:12 am UTC
Thanks for reply,
When I run : alter table table_name enable row movement;
It is invalidating packages.
Why is that happening.
Thanks
April 17, 2008 - 9:18 am UTC
The alter table is apparently doing it - but not so anymore in 11g, the invalidation logic is very much changed in 11g.
CONTINUATION TO ABOVE QUESTION
A.K, April 17, 2008 - 12:39 pm UTC
Sorry I forgot to include this in the above post:
There is no primary key on the table. Is it not possible to use online redefinition on table which doesn't have a primary key column?
Thanks
April 17, 2008 - 4:24 pm UTC
take this opportunity to FIX THAT please. man...
Yes, read the documentation - you can use the rowid, but - be prepared for each row to have the rowid associated with it after the redefinition - in the new table. We'll "un-use" that column - but it'll have consumed space already (meaning, the reason you are redefining - to reclaim space - will be subverted by your LACK of a primary key)
A.K, April 21, 2008 - 11:02 am UTC
Hi,
It is true that I can use the rowid instead of the primary key
for redefinition. But when I use "ALTER TABLE table_name SET UNUSED (M_ROW$$)" it is still invalidating the packages.
Can I leave rowid column in place? does it effect anything at all.
Thanks
April 23, 2008 - 5:12 pm UTC
if you coded "select *" or you use insert without a column list, eg:
insert into t values (a,b,c)
you will "fail"
so, you can use a view (going forward) to hide this column
CONTINUATION TO ABOVE QUESTION
A.K, April 21, 2008 - 5:13 pm UTC
Hi,
I am sorry again for not including this on the top. With the tables which has primary key, the primary key index on the redefined table is created with a different name altogether.
My question why index is created with different name and what should be taken care that the index would be created with the same same.
In the interim table the index is created with the same name though.
Thanks
April 23, 2008 - 5:23 pm UTC
if you use 9i, you create the stuff yourself and you name them and can rename them.
in 10g, it would rename them when you use copy table dependents and finish redef.
"show us"
ops$tkyte%ORA10GR2> create table t1
2 as
3 select * from all_users;
Table created.
ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(user_id);
Table altered.
ops$tkyte%ORA10GR2> select 'indexes', index_name from user_indexes where table_name = 'T1'
2 union all
3 select 'constraints', constraint_name from user_constraints where table_name = 'T1'
4 union all
5 select 'triggers', trigger_name from user_triggers where table_name = 'T1';
'INDEXES' INDEX_NAME
----------- ------------------------------
indexes T1_PK
constraints SYS_C007392
constraints SYS_C007393
constraints SYS_C007394
constraints T1_PK
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2
2 ( USERNAME VARCHAR2(30),
3 USER_ID NUMBER,
4 CREATED DATE
5 )
6 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
2 dbms_redefinition.copy_table_dependents
3 ( user, 'T1', 'T2',
4 copy_indexes => dbms_redefinition.cons_orig_params,
5 num_errors => :nerrors );
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print nerrors
NERRORS
----------
0
ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select 'indexes', index_name from user_indexes where table_name = 'T1'
2 union all
3 select 'constraints', constraint_name from user_constraints where table_name = 'T1'
4 union all
5 select 'triggers', trigger_name from user_triggers where table_name = 'T1';
'INDEXES' INDEX_NAME
----------- ------------------------------
indexes T1_PK
constraints SYS_C007392
constraints SYS_C007393
constraints SYS_C007394
constraints T1_PK
lob data segment constantly growing
Michael, August 11, 2009 - 8:33 am UTC
hi tom,
we have a table with clob columns where rows are constantly inserted and deleted. the clob data is constant in size but the lob data segment still constantly grows. shouldn't the size of the lob data segment remain constant as its space is simply reused?
I did a small test case which also shows this permanent growth. could you please shed some light on this growth?
[143] test@X102> create table tlob (c1 number, c2 clob) lob (c2) store as (chunk 8192 pctversion 20)
;
Table created.
Elapsed: 00:00:00.12
[143] test@X102> insert into tlob select object_id, rpad (object_name, 4001, 'X') from all_objects;
46905 rows created.
Elapsed: 00:00:50.14
[143] test@X102> select table_name, segment_name, index_name
2 from user_lobs
3 where table_name = 'TLOB';
TABLE_NAME SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------ ------------------------------
TLOB SYS_LOB0000086341C00002$$ SYS_IL0000086341C00002$$
Elapsed: 00:00:00.05
[143] test@X102> select segment_name, bytes
2 from user_segments
3 where segment_name in ('TLOB', 'SYS_LOB0000086341C00002$$', 'SYS_IL0000086341C00002$$');
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
SYS_IL0000086341C00002$$ 65536
SYS_LOB0000086341C00002$$ 394264576
TLOB 3145728
Elapsed: 00:00:00.13
[143] test@X102> commit;
Commit complete.
Elapsed: 00:00:00.02
[143] test@X102> delete from tlob;
46905 rows deleted.
Elapsed: 00:00:03.67
[143] test@X102> commit;
Commit complete.
Elapsed: 00:00:00.02
[143] test@X102> select segment_name, bytes
2 from user_segments
3 where segment_name in ('TLOB', 'SYS_LOB0000086341C00002$$', 'SYS_IL0000086341C00002$$');
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
SYS_IL0000086341C00002$$ 4194304
SYS_LOB0000086341C00002$$ 394264576
TLOB 3145728
Elapsed: 00:00:00.08
[143] test@X102> insert into tlob select object_id, rpad (object_name, 4001, 'X') from all_objects;
46903 rows created.
Elapsed: 00:01:14.41
[143] test@X102> commit;
Commit complete.
Elapsed: 00:00:00.02
[143] test@X102> select segment_name, bytes
2 from user_segments
3 where segment_name in ('TLOB', 'SYS_LOB0000086341C00002$$', 'SYS_IL0000086341C00002$$');
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- ----------
SYS_IL0000086341C00002$$ 4194304
SYS_LOB0000086341C00002$$ 469762048
TLOB 3145728
Elapsed: 00:00:00.08
[143] test@X102>
August 13, 2009 - 9:08 am UTC
... shouldn't the size of the lob data segment remain constant as
its space is simply reused?
...
no, not necessarily, lob data is managed (versioned) not using undo segments, but in place.
When you delete the LOB, we do not create undo data for it, we version the lob data in the lob segment itself. How that is managed - depends on how you created the lob. We need to keep the lob data around to satisfy your undo_retention for flashback query and the like - meaning, we might not reuse the lob data for some period of time (the undo retention period)
read this to gain an understanding of how lob data is/can be managed
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_tables.htm#sthref184
reclaim lob space
Michael, August 28, 2009 - 5:28 am UTC
hi tom,
thanks for your answer. I'm wondering how DELETE, set to NULL and set to EMPTY_*LOB() differs in space usage?
all of them would have to preserve the before delete version. so I guess the space usage afterwards will be the same? correct?
how long is the before delete version available? when will this space be reclaimed by the system?
PCTVersion for SecureFiles
A reader, March 14, 2012 - 7:14 pm UTC
For LOB segments, UNDO is managed in place based on the PCTversion parameter. Does this work exactly the same way whether the LOB segment is the traditional LOB segment or SecureFiles?
March 15, 2012 - 7:29 am UTC
Securefiles, as a superset of lobs, manage undo in the log segment - yes.
dbms_redefinition and space requirement
Venki, December 19, 2014 - 3:56 am UTC
Hi Tom,
You have said "moving will require space for the existing table data PLUS the space for the newly moved table at
the same time, yes. There will be a point in time when both are there."
Suppose I have a table 90G in size (has a lob column) but data is only around 35G. My client has some constraints in providing too much disk space at this time.How much space should I plan for - 90G more or 35G more i.e should I go by allocated space or actual space?
DB is 10g and used by SAP system and hence SAP provided Brtools would be used which internally uses dbms_redefinition
December 22, 2014 - 7:33 pm UTC
well, make sure the table + lob index + lob segment is 90gb. If you just measure the table - you'll be disappointed.
if you are tight on space - I'd put off maintenance until you are not. Otherwise, you have a large chance of just wasting your time.
You'll need the space the current table+lob index+lob segment takes PLUS whatever space the new table+lob index+lob segment will consume - plus space for all existing 'regular' indexes.
If you just want to reclaim some space, considering shrinking things - that is done in place and can release unused extents at the end of the newly shrunk segments to the free space of the tablespace