aravindhreturns

 Profile Answers by aravindhreturns 

• Apr 23rd, 2010

 

In most environments, this is done using Oracle Import/Export utility since certain privileges are restricted to the DBAs.

Steps are below:-

1) Export the data
2) Drop the Tablespace,
3) Import the data back again, but it's a huge work-around.

If you have core (Sysdba) access, then follow the simple process below:-

To determine how many and which datafiles make up a tablespace, you can use the following query:

column file_name format a60

column tablespace_name format a15

Select tablespace_name,file_name from dba_data_files where tablespace_name in (select tablespace_name from dba_data_files);

If you have added the tablespace by mistake (And you intend to drop it now), then consider using the RESIZE command. Syntax below:-

ALTER DATABASE DATAFILE <file_name> RESIZE; (Substitute the file name from the query which is given above)

This is just to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then the extents will not be allocated for that in the future. When rebuilding the tablespace next time, the datafile will be excluded.