I was currently at the verge of running a report to see how much space I have I one of my databases. My colleague complained that he deleted about 1 - 2 gig of data and the free space report is still showing the same count. He was wondering how. The answer for the question helped me to explain the situation to him. Very helpful. Thanks.

Tom:
Is there anyway to reclaim the space(ie. reset the HWM) after a large delete?

I have inherited a database that had massive deletes. These tables are going to be static now. How can I reclaim some of the space allocated to this database to make room for another. My 'alter database datafile resize' returns data above size error. Is it not due to the HWM still high? Please advice. Thanks.

I moved the tables and was able to reset the HWM. However, I am still not able to resize the datafiles. Please see the following:

  1  select file_name, bytes/1024/1024 file_size, fs.free_spc,
  2  ((bytes/1024/1024-fs.free_spc)) diff
  3    from dba_data_files df, ( select file_id, sum(bytes)/1024/1024 free_spc
  4   from dba_free_space group by file_id ) fs
  5   where df.file_id=fs.file_id
  6   and df.tablespace_name='MOBIUS'
  7* order by df.tablespace_name, 1
SQL> /

FILE_NAME                                      FILE_SIZE   FREE_SPC       DIFF
--------------------------------------------- ---------- ---------- ----------
/u13/oradata/PPIIMG/mobius01.dbf                    2000 1966.40234 33.5976563
/u14/oradata/PPIIMG/mobius02.dbf                    2000 1949.11719 50.8828125
/u14/oradata/PPIIMG/mobius10.dbf                    1024 989.328125  34.671875
/u15/oradata/PPIIMG/mobius03.dbf                    2000 1626.30469 373.695313
/u16/oradata/PPIIMG/mobius04.dbf                    2000 1766.30469 233.695313
/u16/oradata/PPIIMG/mobius05.dbf                    1200 1160.34766 39.6523438
/u17/oradata/PPIIMG/mobius06.dbf                    1200 1160.35156 39.6484375
/u17/oradata/PPIIMG/mobius08.dbf                    1024 991.355469 32.6445313
/u18/oradata/PPIIMG/mobius07.dbf                    1100 1080.34766 19.6523438
/u18/oradata/PPIIMG/mobius09.dbf                    1024  995.34375   28.65625

10 rows selected.

SQL> alter database datafile '/u13/oradata/PPIIMG/mobius01.dbf' resize 200M;
alter database datafile '/u13/oradata/PPIIMG/mobius01.dbf' resize 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Why can't I resize this datafile to 200M when the only 33 meg of it is used? (as per dba_free_space). What is the mistake I am making? Thanks. 

Tom,

Instead of moving the table to a new tablespace,
can I move the table to the tablespace that it currently
belongs, ie. doing nothing but just moving it to itself for reclaiming the free space, is it possible ?

I use 8.0.4 database. After deleting a large amount of rows, I suppose that the tablespace will not grow for a period of time, as the space of the deleted rows should be filled by the new inserted rows. But the truth is that the tablespace keeps growing large, and run out of space.

This happens to both the data and index tablespaces. Should I move the table to a new tablespace and also rebuild the index to a new tablespace, so as to reclaim space?

Thanks

We use Oracle Application. We followed the instructions from Oracle to archive and purge the old data of our inventory system. After purging, we expect this will free enough space for new data. But our inventory tablespace continues to run out of space. I don't know whether the new data use those free space or not.

Thanks in advance.

Tom,

I appreciate your contribution to the Oracle community which has helped us
get a better understanding of the Oracle database.

reading this thread, i believe i have a similar issue wherein i couldn't see the data blocks being reused,
despite a 'delete' following an 'insert' of 1 rec.
After using block# 48072, it goes on to use block # 48073 and so on.....
If the rows are being deleted immediately, shouldn't block#48072 be the only block
that takes inserts?

show_space does show 3 blocks on the free-list.

Could it be that i'm missing something here?


Loop (for each ee being processed)
insert 1 rec in table <dpk_tmp1>;
process <......>;
delete from table <dpk_tmp1>;
End loop;

-- here is the table definition

CREATE TABLE dpk_tmp1
(
employee_number VARCHAR2(10),
count NUMBER,
jn_date DATE
)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE tmt_tab
STORAGE (
INITIAL 40960
NEXT 40960
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 505
)


Here is the plsql code that I used for the test:

declare
v_blk_no number;
v_row_no number;
begin


dbms_output.put_line('*************************');
dbms_output.put_line('Calling show_space BEFORE');
dbms_output.put_line('*************************');
show_space('DPK_TMP1');

for i in 1..4000 loop

insert into dpk_tmp1 (employee_number, count, jn_date) values ('Emp '||i, i, sysdate);

select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),
dbms_rowid.ROWID_ROW_NUMBER(rowid)
into v_blk_no, v_row_no
from dpk_tmp1
where employee_number = 'Emp '||i;

-- ----------------------------------------------
-- Dump the block# and row# before the delete
-- -----------------------------------------------
insert into dpk_tmp_tab1 (block_no, row_no, employee_number)
values (v_blk_no, v_row_no, 'Emp '||i);

-- --------------------------------------------------------
-- Now, lets delete the inserted rec from dpk_tmp1
-- --------------------------------------------------------
delete from dpk_tmp1;
end loop;

dbms_output.put_line('*************************');
dbms_output.put_line('Calling show_space AFTER');
dbms_output.put_line('*************************');

show_space('DPK_TMP1');

exception
when others then
dbms_output.put_line('Error : '||sqlerrm);
end;
/


And here is the result from running the above plsql code:

MYDB> @1
"truncate table dpk_tmp1

Table truncated.

truncate table dpk_tmp_tab1

Table truncated.

select count(*) from dpk_tmp1

COUNT(*)
----------
0

select block_no, count(*) from dpk_tmp_tab1 group by block_no

no rows selected

Running the plsql code
*************************
Calling show_space BEFORE
*************************
Free Blocks.............................0
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................10
Last Used Ext BlockId...................48071
Last Used Block.........................1
*************************
Calling show_space AFTER
*************************
Free Blocks.............................3
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................10
Last Used Ext BlockId...................48071
Last Used Block.........................4

PL/SQL procedure successfully completed.

"select block_no, count(*) from dpk_tmp_tab1 group by block_no;"
BLOCK_NO COUNT(*)
---------- ----------
48072 1817
48073 1817
48074 366

*****************BLOCK/EMP/ROW******************
select * from dpk_tmp_tab1 where (block_no, row_no) in
(select block_no, max(row_no) row_no from dpk_tmp_tab1 group by block_no);

BLOCK_NO EMPLOYEE_NU ROW_NO
----------- ---------- ----------
48072 Emp 1817 1816
48073 Emp 3634 1816
48074 Emp 4000 365


thanks
deepak

slmak from Hong Kong,

You are saying the Inventory tablespace is growing even after running the purge routines. There could be other tables in the inventory tablespace that are growing contributing to the out of tablespace condition! Are you sure the purged tables are the ones that are growing?

Check the dba_segments and dba_extents tables for the purged table to see whether they are still growing or reusing the deleted space.

thanks tom....it works when i issue a commit.

then again, it appears to be a function of the commit-size.

( I hope i have presented sufficient information for your analysis).

With a commit-size of 1 (ie., if i issue a commit after each delete),
the number of blocks on the freelist stays at 1 (and all records are inserted in block # 48072)

With a commit-size of 1500 and above, however, the number of blocks on the
freelist increases with each subsequent run (and the records are inserted
in multiple blocks.....9 blocks, to be precise)

For each commit-size, this is how i ran the test:
-------------------------------------------------
after choosing a commit-size (say, 2000) , i would run the 1st test with the
"truncate table" in place, and all subsequent runs for the same commit-size will not do the truncate table.

The number of blocks on the freelist stays at 1 upto a commit-size of 1000.

Here's what i mean:


////////////////////////////////////////////////////////////
when COMMIT SIZE = 1000
////////////////////////////////////////////////////////////

at the end of the 4th pass (ie., 4000 * 4 = 16000 records inserted/deleted)
*************************
Calling show_space BEFORE
*************************
Free Blocks.............................1
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................3
Unused Bytes............................24576
Last Used Ext FileId....................10
Last Used Ext BlockId...................48071
Last Used Block.........................2

COMMITing, i = 1000
COMMITing, i = 2000
COMMITing, i = 3000
COMMITing, i = 4000

*************************
Calling show_space AFTER
*************************
Free Blocks.............................1
Total Blocks............................5
Total Bytes.............................40960
Unused Blocks...........................3
Unused Bytes............................24576
Last Used Ext FileId....................10
Last Used Ext BlockId...................48071
Last Used Block.........................2

PL/SQL procedure successfully completed.

"select block_no, count(*) from dpk_tmp_tab1 group by block_no;"

BLOCK_NO COUNT(*)
---------- ----------
48072 16000


////////////////////////////////////////////////////////////
when COMMIT SIZE = 2000
////////////////////////////////////////////////////////////

at the end of the 4th pass (ie., 4000 * 4 = 16000 records inserted/deleted)

Running the plsql code
*************************
Calling show_space BEFORE
*************************
Free Blocks.............................19
Total Blocks............................20
Total Bytes.............................163840
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................8
Last Used Ext BlockId...................69064
Last Used Block.........................10

COMMITing, i = 2000
COMMITing, i = 4000

*************************
Calling show_space AFTER
*************************
Free Blocks.............................29
Total Blocks............................35
Total Bytes.............................286720
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................10
Last Used Ext BlockId...................74645
Last Used Block.........................10

(Tom: does this mean the HWM is at block 30 ? )

PL/SQL procedure successfully completed.

"select block_no, count(*) from dpk_tmp_tab1 group by block_no;"

BLOCK_NO COUNT(*)
---------- ----------
48072 1817
48073 2000
48074 2000
48075 2000
69064 2000
69069 2000
74645 2000
74650 183
178361 2000

(ie., the 16K records are spread across multiple blocks)

************** USER_EXTENTS ************
select extent_id, bytes, blocks
from user_extents where segment_name = upper('dpk_tmp1') order by extent_id

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 40960 5
1 40960 5
2 81920 10
3 122880 15

************** USER_EXTENTS ************


thanks
deepak

Hi Tom,

We have a table with about 26 million rows and have decided to archive everything older than three months. This means deleting about 21 million rows.

I would expect that a straight "delete" statement would take a considerable amount of time and, as mentioned above, would still not automatically release the disk space allocated to this table.

My current plan is to create a copy of the table in a new tablespace, truncate the old table and then populate it from the copy as required. The new table, together with its tablespace, can then be dropped.

Any thoughts on this approach?

Thanks for your assistance.

Hi Tom,

I have a table with a blob column with 10 million rows. I want to set all the blobs to null. This is a permanent update. How do I reclaim the space occupied by the blob column after updating it to null?

Thanks.

Hi Tom,

You said: "Doing a rolling window is trivial using partitions and aging old data out is a simple DDL operation that takes seconds to perform."

Would it be possible to give an example of how this would be done? I have a large table (> 1Tb) with a LAST_UPDATED column that I'd like to move to a read only tablespace if it is old.

Thanks.

Hi Tom,

Need your advice. WHat is the bottlenect from the following statistics. The query is making use of index for most of the join. Thanks

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 1.20 1.19 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 857 4029.16 4250.58 29940 10368552 0 12830
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 860 4030.36 4251.78 29940 10368552 0 12830


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 858 0.00 0.00
SQL*Net message from client 858 4065.70 4100.49
db file sequential read 29919 0.11 291.78
db file scattered read 5 0.01 0.05
latch free 13 0.00 0.00
SQL*Net more data to client 799 0.00 0.02
********************************************************************************

As you said
"When you delete the data from the table -- the blocks will go onto the freelist
for that table (assuming the amount of space on a block that was freed fell
below the pctused). These blocks will be used for subsequent inserts and
updates into this table."

For example, before the delete on PRIMARY a datafile had 1G. And after the delete, PRIMARY will have the same physical size, as you explained above. Question is

1. After the delete in primary database, is it possible to rclaim physical space on the other instance using RMAN to clone a database?

Thanks

Tom

maybe I'm looking at this to simplistically,,,however if I want to delete
a million rows from table A to reclaim blocks and reset HWM can I not simply
just:

(assuming a delete cascade was used for any child records, disable/enable constraints)

1) delete million rows from table A

2) create table B as select * from table A

3) truncate table A

4) insert into table A select * from table B

5) drop table B

6) rebuild indexes

:~)

Tom,
How would I get an accurate representation of the size of each table if deleting/truncating the table isn't reflected in dba_extents? I don't have privs to drop the table or move it to another tablespace.

TIA,
ht

SQL*Plus: Release 9.2.0.5.0 - Production on Tue Aug 23 12:02:48 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

conn scott
Connected.

create table test_emp as select * from emp;

Table created.


select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------
.0625

1 row selected.


delete test_emp;

14 rows deleted.

commit;

Commit complete.

select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------
.0625

1 row selected.


drop table test_emp;

Table dropped.

select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------


1 row selected.

Tom,
I've modified by test script to truncate the table then deallocate. Is this what you were referring to? The table size still doesn't seem to be accurately reflected.

SQL*Plus: Release 9.2.0.5.0 - Production on Wed Aug 24 09:06:55 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

conn scott
Connected.

create table test_emp as select * from emp;

Table created.


select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------
.0625

1 row selected.


truncate table test_emp;

Table truncated.

alter table test_emp deallocate unused;

Table altered.

/*
DOC>delete test_emp;
DOC>commit;
DOC>*/
select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------
.0625

1 row selected.


alter table test_emp deallocate unused keep 0k;

Table altered.

select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------
.0625

1 row selected.


drop table test_emp;

Table dropped.

select sum(bytes/1024/1024) from user_extents where segment_name='TEST_EMP';

SUM(BYTES/1024/1024)
--------------------


1 row selected.

you said

"When you delete the data from the table -- the blocks will go onto the freelist"

that is true however I am not sure how does Oracle managed pctused and freelists in assm, we have some event tables which holds maximum 300000 rows and deleted everyday using DELETE statement, these tables has however huge sizes, most of them 2GB.

Why in ASSM Oracle cant reuse the spaces efficiently? My only workaround is going back to pctused and freelists?

That is the questions I asked the developers. Why not truncate the tables

They say they dont delete 100% of data based some conditions because the tables are constantly inserted by Tibco Middleware 24x7 so if they truncate the table they might lose data.

I suggested them to use manual segment manegement and setting 1 pctfree and 98 pctused since the tables suffers constantly inserts and deletes to use the space efficiently.

Hi Tom,

Useful article. I have a tablespace that contained tables owned by users which I have dropped (with cascade so the tables, etc. were removed). I ran your maxshrink script but, alas, the HWM is too high for me to resize and shrink the tablespace and so realise the free space.

There are a few hundred tables and indexes left in this tablespace - is the best method really to move each of the tables and rebuild each of the indexes?

Is there a tablespace-level action that I can take instead?

Thanks,

Si

Thanks for that.

The dba_segments table was difficult to understand even after recourse to the oracle documentation.

I went for moving the tables and indexes to a new tablespace in the end.

However this left me with problems as there are LOBS and LOB indexes.

In particular I could move the table:
ALTER TABLE CREATE$JAVA$LOB$TABLE MOVE new_tablespace

but not rebuild the index:

ALTER INDEX SYS_IL0000042332C00002$$ REBUILD TABLESPACE <new_tablespace>
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

Metalink Note:130814.1 tells me to use

ALTER TABLE create$java$lob$table MOVE LOB(lob) STORE AS lobsegment (TABLESPACE jpit_users2_ts);

but how does one choose "lobsegment"??

Thanks

Si

lobsegment: in the ALTER TABLE statement, the entry after "STORE TABLE" requires I name the segment to which I move the lob. I'm not sure how I would choose this, or is it just a case of naming?

Tom Thanx a lot for u'r artical and argument

I had the same problem having 40 million records in the table I wanted to delete the data for every 45days

This article sown me good way with reason

Thanks

Dear Tom,

It is really a great post. Thank you very much.
I have one more question want to ask you.

After truncating a table, the extents and blocks of that table will be released, that mean, dba_free_space will show new free space. Then, how about its indexes? if I truncate a table, should I also concern its indexes? Say, do I need to rebuild the indexes of that table so as to release more free space from the indexes?

Thanks

the truncate of a table (which can keep the storage as well as release it) will truncate the indexes for you as well.



ops$tkyte@ORA10GR2> create table t
  2  as
  3  select * from all_objects;
 
Table created.
 
ops$tkyte@ORA10GR2> create index t_idx on t(object_name,owner,object_type);
 
Index created.
 
ops$tkyte@ORA10GR2> select segment_name, blocks
  2    from user_segments
  3   where segment_name in ('T','T_IDX')
  4  /
 
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T                                     768
T_IDX                                 384
 
ops$tkyte@ORA10GR2> truncate table t reuse storage;
 
Table truncated.
 
ops$tkyte@ORA10GR2> select segment_name, blocks
  2    from user_segments
  3   where segment_name in ('T','T_IDX')
  4  /
 
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T                                     768
T_IDX                                 384
 
ops$tkyte@ORA10GR2> truncate table t drop storage;
 
Table truncated.
 
ops$tkyte@ORA10GR2> select segment_name, blocks
  2    from user_segments
  3   where segment_name in ('T','T_IDX')
  4  /
 
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
T                                       8
T_IDX                                   8
 

Hi Tom,

Consider:

create table t (i int);

13:44:50 vasuv00@GRUMPY.WORLD> insert into t select object_id from all_objects;

23455 rows created.

Elapsed: 00:00:52.03
13:45:57 vasuv00@GRUMPY.WORLD> insert into t select * from t;

23455 rows created.

Elapsed: 00:00:00.02
13:46:00 vasuv00@GRUMPY.WORLD> /

46910 rows created.

Elapsed: 00:00:00.03
13:46:01 vasuv00@GRUMPY.WORLD> /

93820 rows created.

Elapsed: 00:00:00.05
13:46:02 vasuv00@GRUMPY.WORLD> /

187640 rows created.

Elapsed: 00:00:02.00
13:46:05 vasuv00@GRUMPY.WORLD> commit;

Commit complete.

Elapsed: 00:00:00.01
13:46:06 vasuv00@GRUMPY.WORLD> select segment_name, blocks, bytes from user_segments where segment_name='T';

SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
BYTES
----------
T 640
5242880


Elapsed: 00:00:02.08
13:46:17 vasuv00@GRUMPY.WORLD> delete t;

375280 rows deleted.

Elapsed: 00:00:15.00
13:46:41 vasuv00@GRUMPY.WORLD> commit;

Commit complete.

Elapsed: 00:00:00.00
13:46:46 vasuv00@GRUMPY.WORLD> select segment_name, blocks, bytes from user_segments where segment_name='T';

SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
BYTES
----------
T 640
5242880


Elapsed: 00:00:02.06
13:46:50 vasuv00@GRUMPY.WORLD> truncate table t;

Table truncated.

Elapsed: 00:00:00.02
13:46:57 vasuv00@GRUMPY.WORLD> select segment_name, blocks, bytes from user_segments where segment_name='T';

SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
BYTES
----------
T 640
5242880


Elapsed: 00:00:02.06
13:47:08 vasuv00@GRUMPY.WORLD> truncate table t drop storage;

Table truncated.

Elapsed: 00:00:00.02
13:47:12 vasuv00@GRUMPY.WORLD> select segment_name, blocks, bytes from user_segments where segment_name='T';

SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
BYTES
----------
T 640
5242880


Elapsed: 00:00:02.05
13:47:16 vasuv00@GRUMPY.WORLD> alter table t move;

Table altered.

Elapsed: 00:00:00.01
13:47:22 vasuv00@GRUMPY.WORLD> select segment_name, blocks, bytes from user_segments where segment_name='T';

SEGMENT_NAME BLOCKS
--------------------------------------------------------------------------------- ----------
BYTES
----------
T 128
1048576

Now the question: Why is it that only the alter table move reduced the number of blocks and bytes. Why didnt the truncate table drop storage achieve that?

Thanks for your time in advance ..

Regards,
Vikram Romeo

Thanks Tom,

But I guess as of now, I am not even equipped to answer your question. Can you please direct me to the right resource/link from where I could get to know about this?

Does your latest book on Expert one-one-one oracle cover this aspect?

Thanks for your help again ...

Regards,
Vikram

Thanks Tom,

But I have already ordered your book "Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions" .. will this book cover this topic?

It is still shipping .. on its way :-)

Would feel happy if it does cover this topic ..

Yes , Asking the DBA is there .. but I would also like to learn some of these myself ... so that I dont need to bug the DBA everytime ... Do you feel a developer should know these things? is it absolutely necessary?

Thanks,
Vikram Romeo

Hi Tom,

I have a partitioned table that I am purging using truncate with the drop storage clause to purge the table and reclaim the space free up by the truncate to the tablespace. The problem is that the truncate drop storage is not returning the free space to the tablespace. Do you know why this is happening?
The tablespace is a locally managed with system allocated extent sizes.
TABLESPACE_NAME : APP_INDEX
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : AUTO
DEF_TAB_COMPRESSION : DISABLED
Could you please tell me what I can do to allow the truncate drop storage to return the freed space to the tablespace again.

Thanks for any help on this.

"show me"

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   char(200)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /
CREATE TABLE t
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select partition_name, extents
  2    from user_segments
  3   where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                   1
PART2                                   1
JUNK                                    1

ops$tkyte%ORA10GR2> insert into t
  2  select to_date( '12-mar-2003', 'dd-mon-yyyy' )+mod(rownum,3), rownum, rownum
  3    from all_objects;

50064 rows created.

ops$tkyte%ORA10GR2> select partition_name, extents
  2    from user_segments
  3   where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                   8
PART2                                   8
JUNK                                    8

ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2> select partition_name, extents
  2    from user_segments
  3   where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                   1
PART2                                   1
JUNK                                    1
 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> CREATE TABLE t
  2      ( dt  date,
  3        x   int,
  4        y   char(200)
  5      )
  6      PARTITION BY RANGE (dt)
  7      (
  8       PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
  9       PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 10       PARTITION junk VALUES LESS THAN (MAXVALUE)
 11     )
 12  /

Table created.

SQL> select partition_name, extents
  2        from user_segments
  3       where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                   1
PART2                                   1
JUNK                                    1

SQL> insert into t
  2      select to_date( '12-mar-2003', 'dd-mon-yyyy' )+mod(rownum,3), rownum, rownum
  3     from all_objects;

31010 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name, extents
  2        from user_segments
  3       where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                  18
PART2                                  18
JUNK                                   18

SQL> truncate table t;

Table truncated.

SQL> select partition_name, extents
  2        from user_segments
  3       where segment_name = 'T';

PARTITION_NAME                    EXTENTS
------------------------------ ----------
PART1                                  18
PART2                                  18
JUNK                                   18

SQL> exec print_table('select * from dba_segments where segment_name=''T'' ');
OWNER                         : SCOTT
SEGMENT_NAME                  : T
PARTITION_NAME                : PART1
SEGMENT_TYPE                  : TABLE PARTITION
TABLESPACE_NAME               : APP_INDEX
HEADER_FILE                   : 26
HEADER_BLOCK                  : 43059
BYTES                         : 3145728
BLOCKS                        : 384
EXTENTS                       : 18
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
RELATIVE_FNO                  : 26
BUFFER_POOL                   : DEFAULT
-----------------
OWNER                         : SCOTT
SEGMENT_NAME                  : T
PARTITION_NAME                : PART2
SEGMENT_TYPE                  : TABLE PARTITION
TABLESPACE_NAME               : APP_INDEX
HEADER_FILE                   : 26
HEADER_BLOCK                  : 43067
BYTES                         : 3145728
BLOCKS                        : 384
EXTENTS                       : 18
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
RELATIVE_FNO                  : 26
BUFFER_POOL                   : DEFAULT
-----------------
OWNER                         : SCOTT
SEGMENT_NAME                  : T
PARTITION_NAME                : JUNK
SEGMENT_TYPE                  : TABLE PARTITION
TABLESPACE_NAME               : APP_INDEX
HEADER_FILE                   : 26
HEADER_BLOCK                  : 43075
BYTES                         : 3145728
BLOCKS                        : 384
EXTENTS                       : 18
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
RELATIVE_FNO                  : 26
BUFFER_POOL                   : DEFAULT


SQL> exec print_table('select * from dba_tablespaces where tablespace_name=''APP_INDEX'' ');
TABLESPACE_NAME               : APP_INDEX
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : AUTO
DEF_TAB_COMPRESSION           : DISABLED
-----------------

Thanks for any help you can give on this. 

This sounds similar to bug 4142932 - if you have MetaLink, you might want to check that as a possibility.

- Mark

Also, perhaps having a look at user_extents in addition to user_segments might help verify if the two views "match"...

- Mark

Hi Tom,
I am trying to understand the difference between deallocate unused and effect of 'alter table move' in respect to freeing up deleted space.
Please can you explain.
Regards,
Aru.

Hi Tom,
Earlier in the thread you said
'you can query dba_extents to see what segment is "furthest out", move that(it'll go nearer to the
front of the file - definitely releasing last extent) in the same tablespace - shrink - repeat (but
you might repeat a lot)'

For moving the table to front of file do need to go and move it to different tablespace and then back or is there a way to move the table within the same tablespace?

Regards,
RD

Good day Tom,

Is it possible that altering the table could reclaim some space?
eg:
ALTER TABLE [Table_Name] MOVE LOB([Column_Name]) Store
as (TABLESPACE [Table_Space]);

If so, is it possible to alter(move) it to the SAME Tablespace?

P.S.
I'm new to Oracle so excuse my ingorance

Hi Tom,
This is a best article.

I have a once concern regarding this.
I have deleted some millions of records from a table.
But still the table is showing the same space usage as it was before deleting the records.
Version is:10.2.0.4
Please suggest me how do i reclaim the space.

Thanks for the above information about the delete. We have an application that have 5 months of data. It started to be filled up from scrach since 5 months.

We decided to delete 4 months of data as a one shot; and then we will delete so that there will be keeping only the last 7 days of data in this application.

What would you suggest for us to do after those delete in order to re-organize those tables and linked indexes?

1) alter table t shrink space compact?

Thanks a lot

Dear Tom,

I have tablespace with one datafile of size 10M and autoextend on.
I have a table created with the definition

create table test( blob_id number(19) not null, content blob not null)

I inserted a couple of blobs of size 10 MB each. The datafile size has grown to 38MB. I then deleted both the rows and inserted another blob of size 10MB. The size of the datafile now reads 54MB.

1) Why did the datafile almost grew twice the size of the data ?
2) Why has the additional blob that i inserted not use the free space in the datafile ?

Dear Tom,

You said recently deleted lob data is not available for reuse, atleast for a while. I tried deleting 1GB blobs from my table and tried adding 1GB blobs after a couple of hours.
It still does not use the free space obtained on delete. It just extends the datafile even more.

If i have a bigfile tablespace whose maximum size is like
30000 GB, and pctversion for the blob table is default (10%). Do the blob versions consume 300GB of space at max ?. This is the tablespace and table that i have used for performing the delete.

Thanks a lot for your replies Tom.

But,

1) When i tried inserting blobs with pctversion 0, the datafile size was increasing exactly by the amount of the data inserted. However when the pctversion is set to default 10%, the datafile size was doubling. Doesnt it mean that the blob is already in reserve at the time of insert itself?. If i delete and insert, shouldnt it just use the free space ?. If the deleted blob is also held in reserve, that means that same version of the blob is being held in reserve twice, right ?.

2) If i dont use both pctversion and retention in my LOB column definition, the default is pctversion right ?. I think pctversion will work fine for me. Even if my datafile size has grown to 1000GB and 100GB is being retained because of pctversion. It will be okay, but only when the other 900GB is being used completely.

We already have a production installation where we have specified neither. And now we are receiving complaints of deleted space not being used for adding more data.

Thanks again for your help.

Hi Tom,

Iam really confused here.
As you said in your earlier replies, i was able to use the deleted space after some time (i tried it the next day). I was happy and tried the exercise again. Alas, iam now unable to use the deleted space at all.

Also, iam not sure whether this should go into some other thread. But, how will oracle scale with multiple users uploading blobs in the database simultaneously ?. If 50 users are uploading 10MB blobs into the database simultaneously, do you think it would have an adverse affect on the speeds. I got speeds that increase non-linearly with some quadratic factor also thrown in. Am I getting the wrong results ?

Hi Tom,
       We have a oracle apps 9.2.0.8 DB. The table ar.hz_parma_tab is gone to size of 66GB and its index ar.hz_param_tab_N1 is around 100GB. We raised SR with oracle support and they told us to truncate this table.

It took us more than 10 hours to truncate this table.
But when we do the following, this is done in less than 30 min:

SQL> truncate table ar.hz_param_tab reuse storage;

Table truncated.

Elapsed: 00:22:55.20

SQL> alter index ar.hz_param_tab_N1 rebuild;

Index altered.

Elapsed: 00:01:17.71

SQL> alter table ar.hz_param_tab move;

Table altered.

Elapsed: 00:00:49.66

While rebuilding of index and table the space is reclaimed very quickly but if we try truncating the table with 'drop storage' or 'deallocate storage' clause it takes lots of time  and space is released slowly? what is the reason for it? 
Does this means that rebuilding the index and the table after truncating using reuse storage the best way to truncate a huge table?

For a table where the LOBs have been defined using RETENTION (default undo_retention of 900 seconds) is there a way to determine how much space is available for re-use after a delete has been run? DBMS_SPACE.SPACE_USAGE shows all of the blocks as being FULL, which I kind of expected, but I can't seem to determine how much space is consumed by LOBS that have been deleted and are 'expired', so to speak, and are therefore available for re-use.

Hi Tom,
In 10g and up we can shrunk the tablespace using "alter table tt shrink space" (there are restrictions though)
and we can deallocate unused space using "alter table deallocate unused"

how these both are different? Does the second statement have any restrictions such as can't use on the table which function based indexes or so?

thanks

There is something I do not understand.

We have a similar situation as the one indicated in the message where the user has a 60 GB data nd 100GB index.

Question I have is how this behave when using ASM. It looks that we are not gaing the space from te truncation. Is this due to the usage of ASM that doesn't freed the space? Thank you for your help.

ops$tkyte%ORA11GR2> select segment_name, segment_type, blocks 
  2  from dba_segments where owner = user and segment_name like 'BIG_TABLE%';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
BIG_TABLE                      TABLE                   15360
BIG_TABLE_PK                   INDEX                    2176

ops$tkyte%ORA11GR2> @free 1

                                                                                                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a ASSM                    1,024           64          960    6.3          960   33,554,416     .0
*a EXAMPLE               320,512      310,976        9,536   97.0        8,192   33,554,416    1.0
*a SYSAUX                904,192      810,304       93,888   89.6       30,720   33,554,416    2.7
*a USERS               1,518,336      199,232    1,319,104   13.1    1,013,504   33,554,416    4.5
*m MANUAL                 10,240        1,024        9,216   10.0        9,216   33,554,416     .0
*m SYSTEM                887,808      878,080        9,728   98.9        9,216   33,554,416    2.6
*m TMP                   512,000      512,000            0  100.0            0   33,554,416    1.5
*m UNDOTBS             3,977,216       38,016    3,939,200    1.0    2,211,840   33,554,416   11.9
                    ------------ ------------ ------------
sum                    8,131,328    2,749,696    5,381,632

8 rows selected.

ops$tkyte%ORA11GR2> truncate table big_table;

Table truncated.

ops$tkyte%ORA11GR2> @free 1

                 %
                                                                %                   MaxPoss    Max
Tablespace Name           KBytes         Used         Free   Used      Largest       Kbytes   Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*a ASSM                    1,024           64          960    6.3          960   33,554,416     .0
*a EXAMPLE               320,512      310,976        9,536   97.0        8,192   33,554,416    1.0
*a SYSAUX                904,192      810,304       93,888   89.6       30,720   33,554,416    2.7
*a USERS               1,518,336       59,072    1,459,264    3.9    1,153,664   33,554,416    4.5
*m MANUAL                 10,240        1,024        9,216   10.0        9,216   33,554,416     .0
*m SYSTEM                887,808      878,080        9,728   98.9        9,216   33,554,416    2.6
*m TMP                   512,000      512,000            0  100.0            0   33,554,416    1.5
*m UNDOTBS             3,977,216       38,016    3,939,200    1.0    2,211,840   33,554,416   11.9
                    ------------ ------------ ------------
sum                    8,131,328    2,609,536    5,521,792

8 rows selected.