Tablespaces & Datafiles

Check Tablespaces

SELECT tablespace_name

  FROM dba_tablespaces

 ORDER BY tablespace_name;

Check Tablespace Usage

Check Datafile

What is the maximum currently set to...

SHOW PARAMETER db_files

or use the script to the right...

Check Datafile Usage

Number of datafiles per tablespace also showing % usage taking into account maximum allowed autoextend of allocated datafiles...

CREATE TABLESPACE

CREATE TABLESPACE &tablespace_name;

NOTE: This assumes that the database is set up to use OMF (Oracle Managed Files).

DROP TABLESPACE

DROP TABLESPACE &tablespace_name;

DROP TABLESPACE &tablespace_name INCLUDING CONTENTS AND DATAFILES;

NOTES: Is the tablespace part of a physical or logical standby configuration? What is the impact of dropping the tablespace?Does the database use Oracle Managed Files (if not then appropriate steps need to be taken to ensure any datafiles are also removed).Does the data contained in the tablespace need to be exported (expdp) before it is DROPped (and is there sufficient filesystem space to do this?).

ADD Datafile

ALTER TABLESPACE &tablespace_name ADD DATAFILE;

By default you can add 200 datafiles to a database. If you need to increase this...

ALTER SYSTEM SET db_files = 300 SCOPE=SPFILE;

NOTE: This will not take effect until the instance is restarted. You can only modify this parameter in the configuration file. You cannot modify it in memory.

DROP Datafile

Once added, there is no easy way to drop a datafile.

If you really must do it, then you will need to:

Alternatively, consider SHRINKing the datafile to reduce the impact of its existence.

If you have accidentally deleted the datafile from the filesystem (or ASM), and you cannot restore/recover, then you can remove the datafile from the database using...

STARTUP MOUNT

ALTER DATABASE DATAFILE '/my/deleted/datafile' OFFLINE DROP;

ALTER DATABASE OPEN;

Obviously any data in this datafile will be lost

SHRINK Datafile 

ALTER DATABASE DATAFILE mydatafile RESIZE 10K;

 If the datafile is resized to smaller than 5 oracle blocks (40K, assuming an 8K Block Size), then it will never be considered for extent allocation 

Space Advisor

NOTE: Remember to check that you are licensed to use the actions recommended (e.g. "compress for oltp" requires the Advanced Compression Option).

Bibliography