Tablespaces & 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:

export all the segments in the tablespace
DROP the tablespace
recreate the tablespace with the correct number of datafiles
import the segments
test to make sure things still work (e.g. are there missing Grants etc)

Oracle DataPump

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

Auto 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).

Advanced Compression Option

Bibliography

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:515222144417%20 (How to drop a datafile?)https://oracledbatips.com/alter-database-datafile-resize-a-datafile/https://svenweller.wordpress.com/2020/07/02/7-secret-oracle-db-features-you-might-not-know/https://www.dbasolved.com/2013/06/tablespace-space-used-am-i-really-full/https://www.pythian.com/blog/technical-track/how-to-retrieve-growth-history-for-oracle-tablespaces
DBA_TABLESPACE_USAGE_METRICShttps://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_TABLESPACE_USAGE_METRICS.html
Locally Managed Tablespaceshttps://oracle-base.com/articles/8i/locally-managed-tablespaceshttps://mindmajix.com/oracle-dba/locally-managed-tablespaceshttps://www.orafaq.com/node/3 (Locally vs. Dictionary Managed Tablespaces)
11.2https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles003.htm (Oracle Database Administrator's Guide - Changing Datafile Size)
11.1http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm (CREATE TABLESPACE)http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9004.htm (DROP TABLESPACE)
10.2https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams049.htm (DB_FILES)
9.0.1https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90117/tspaces.htm

Page updated

Google Sites

Report abuse