oracle tablespace

[U.SivaKumar_2]

Hi,


We have a tablespace with 10GB size, We want to
delete old records out of it and free the space.

After deleting the records , will the TABLESPACE and TABLE size reduce immediately or we have to follow any procedure to get the
free space ?.

regards,
U.SivaKumar

[Tom Geudens]

Hi,
-- I used to be a DBA, so I'm sure of the concepts, but please CHECK all this information (it has been a while, you know :-).

1. The trick is in reducing the size of the datafile (OS-system file) that contains the tablespace. You can do this with a ALTER DATABASE-command (example from the manual) :
ALTER DATABASE
DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
RESIZE 100M;

2. However, the space in the tablespace is NOT automatically freed up. The above command may not work because the rows of your tables may be spread over all of the 10Gb (even though you only use 100Mb in total). You'll definitely have to take a look at the fragmentation in your tablespace. The procedure that is definitely going to work is :
- export the data
- drop the tables/indexes (and maybe even the tablespace itself).
- resize the datafile
- recreate the tablespace with smaller storageparameters
- recreate the tables/indexes with smaller storageparameters
- import the data

Note that this would be the procedure for a Oracle 8.0.x database. It is possible that better procedures exist for 8.1.x databases.

Regards,
Tom

[Kenneth_19 · Accepted solution]

To give you additional information of database reorg (The action of exp/imp or moving database objects around in order to reduce fragmentation or space reclaim) in Oracle8i (8.1.x) and newer databases, you can:

1. Create a new tablespace reorg (or whatever name you like) that can house all the objects in your existing 10Gb tablespace.

2. Run the following sql script to generate the commands to move all the tables from the existing tablespace to the new tablespace:

~~~ START ~~~

connect system/

set heading off
set linesize 256
set pagesize 0

spool /tmp/reorg_table1.sql

SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE REORG;'
FROM dba_tables
WHERE tablespace_name = 'DAT';
(change DAT to your existing 10Gb tablespace name)

spool off

spool /tmp/reorg_index1.sql

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE REORG;'
FROM dba_indexes
WHERE tablespace_name = 'DAT';
(change DAT to your existing 10Gb tablespace name)

spool off

~~~ END ~~~

3. You will get two spool files that contains sql commands to move all the tables and indexes away from your 10Gb tablespace, run the two files one by one starting from the tables first, followed by the indexes):

@/tmp/reorg_table1.sql
@/tmp/reorg_index1.sql

4. After that confirm your 10Gb tablespace do not contains any objects:

SELECT object_name
FROM dba_objects
WHERE tablespace_name = 'DAT';

it should return no rows.

5. Resize the datafile as according to the last reply provided by Tom. Make sure that the new size is big enough for all the objects to move back.

6. Run the following sql script to generate the commands to move all the tables from the new tablespace back to the resized tablespace:

~~~ START ~~~

connect system/

set heading off
set linesize 256
set pagesize 0

spool /tmp/reorg_table2.sql

SELECT 'ALTER TABLE ' || owner || '.' || table_name || ' MOVE TABLESPACE DAT;'
FROM dba_tables
WHERE tablespace_name = 'REORG';
(change DAT to your resized tablespace name)

spool off

spool /tmp/reorg_index2.sql

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE DAT;'
FROM dba_indexes
WHERE tablespace_name = REORG';
(change DAT to your existing 10Gb tablespace name)

spool off

~~~ END ~~~

7. You will get two spool files that contains sql commands to move all the tables and indexes back to your resized tablespace, run the two files one by one starting from the tables first, followed by the indexes):

@/tmp/reorg_table2.sql
@/tmp/reorg_index2.sql

8. After that confirm your REORG tablespace do not contains any objects:

SELECT object_name
FROM dba_objects
WHERE tablespace_name = 'REORG';

it should return no rows.

9. You can now drop the tablespace REORG:

DROP tablespace REORG;

This is a safe and time saving method for database reorg introduced in Oracle8i, since the objects are never removed from the database and risk of losing data is lower comparing with exp/imp.

Regards,
Kenneth

[Dave Chamberlin]

A TABLESPACE is a logical construct and not a physical one. Datafiles contain the data for tables and indexes. If the tables/indexes in the datafiles in question are dropped, the space is recovered to the datafile. The datafile can the be reduced in size with "alter database datafile '/u01.../data01.dbf' resize 10M etc. If you are only deleting rows, (you are better off using truncate) the space is not recovered and you would use export etc as others have replied in this post, move the stuff to other datafiles (in other tablespaces) then drop the stuff in the current datafiles. Oracle's Enterprise Manager has a tablespace manager that allows you to see what extents are in which datafiles, organized by tablespace.