How to recover unused space in oracle

I have a tablespace with datafile of 100GB.Which contains one table of 70 GB data data is inserted or deleted in this table on monthly basis .This month purging has completed we are not going to insert data in this table for next three month. There is other three tables with total size 25 GB which are still in growing stage , hence these tables need space soon. I have to create space or can use the space from table having a huge deleted data . For cost cutting I would like to use the space occupied by delted enries .But in Oracle when we create the tables and insert data into tables extents are allocated to tables . Data stores in block(rows store in blocks) if delete the rows the deleted data are still occupying the data block .

Data blocks are not become free to insert data.

i.e

UUUUDDDDDDUUUUUUUDDDDDDUUUUDDUD

where U -Used Space

D- Deleted Space

extents are not deallocated for use so we have to make this space available in continues form .

UUUUUUUUUUUUFFFFFFFFFFFFFFFFF

F- Free space

My problem is how can I get back the unused space . Lets works on a small example

alter table TABLE_NAME move;

and now I am able to use the space .which was occupied by the deleted entries.

.when we move a table then oracle create a new table with continues blocks and insert data into that new table and drop the first one .and space is deallocated .

Second case is how can we resize the file .First we query for the used and free space in the database , as database objects used less space . Hence we need to decide to resize the file (become smaller) but sometimes oracle denies to shrink datafile.

i.e

I created the tablespace having datafile of size 500m.

SQL> create table tab1 tablespace newtbs as select * from dba_objects;

Table created.

SQL> select bytes/1024/1024 from dba_segments where segment_name='TAB1';

BYTES/1024/1024

---------------

232

SQL> create table tab2 tablespace newtbs as select * from tab1;

Table created.

SQL> select bytes/1024/1024 from dba_segments where segment_name='TAB2';

BYTES/1024/1024

---------------

232

SQL>drop table tab2;

I checked the free space in the tablespace.

SQL> select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB"

from

(select tablespace_name,

round(sum(bytes) / 1024/1024 ) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/1024/1024) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name

and tu.tablespace_name='NEWTBS'

SQL> /

Tablespace Used MB Free MB Total MB

------------------------------ ---------- ---------- ----------

NEWTBS 232 268 500

As output of the above query show used space is 232 MB. To make the space on the disk I want to resize the file .

SQL> alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m;

alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

If free space is available then why I am unable to resize the file.

In Oracle if we create the tables and insert the data into them extents are allocated to the tables. Once data inserted data blocks (allocate the extents to any object) are become used block ,Oracle will not allow the resize the file size below these used block sum.

Now we have to follow some steps to fulfilled our requirements we will move table from one tablespaces to other tablespace .when we move a table then oracle create a new table with continues blocks and insert data into that new table and drop the first one .and

i.e

SQL> alter table tab4 move tablespace users;

Table altered.

SQL> alter database datafile '/home/oracle/oradata/mediamat/newtbs.dbf' resize 300m;

Database altered.

You can use the script for move all tables with rebuild index.

select decode( segment_type, 'TABLE', segment_name, table_name ) decode( segment_type, 'TABLE', 1, 2 ) , 'alter ' || segment_type || ' ' || segment_name ||

decode( segment_type, 'TABLE', ' move ', ' rebuild ' )

|| ' tablespace &1 ;'

from user_segments, (select table_name, index_name from user_indexes )

where segment_type in ( 'TABLE', 'INDEX' )

and segment_name = index_name (+)

order by 1, 2

1)Alter table TBALE_NAME move tablespace TABLESPACEA_NAME;

Now we have to rebuild the index because they are unusable .

2)Alter index IINDEX_NAME REBUILD TABLESPACE MT_RPT01_INDEX;

We can find the last block used by the file as follow.

SQL> select file_id, block_id, blocks,

2 owner||'.'||segment_name "Name"

3 from sys.dba_extents

4 where tablespace_name = upper('&1')

5 UNION

6 select file_id, block_id, blocks,

7 'Free'

8 from sys.dba_free_space

9 where tablespace_name = upper('&1')

10 order by 1,2,3

11 /

FILE_ID BLOCK_ID BLOCKS Name

---------- ---------- ---------- ---------------------------------------------

2 9 8 NEWTB.ABC1

17 8 NEWTB.ABC1

25 8 NEWTB.ABC1

33 8 NEWTB.ABC1

41 8 NEWTB.ABC1

49 8 NEWTB.ABC1

57 8 NEWTB.ABC1

65 8 NEWTB.ABC1

73 8 NEWTB.ABC1

81 8 NEWTB.ABC1

2 89 8 NEWTB.ABC1

97 8 NEWTB.ABC1

105 8 NEWTB.ABC1

113 8 NEWTB.ABC1

121 8 NEWTB.ABC1

129 8 NEWTB.ABC1

137 128 NEWTB.ABC1

265 128 NEWTB.ABC1

393 128 NEWTB.ABC1

521 5880 Free

7 9 51192 Free

When should rebuild index

I) when deleted or updated entries increase and query is base on range After deleting records from tables index does not rebalanced even Oracle leaves "dead" index nodes in the index when rows are deleted

    II.            when a number of rows are inserted in a table then index level is increases. Hence we have to rebuild the index if we continuously inserting a huge data.

III) To set the pct increase to zero we can rebuild the index as if we set the pct increase 20% then the next allocated extend size will increase with 20 % and if extend is very large as result wastage of the space. To make extent size uniform oracle recommended pctincrease should be zero.