You cannot drop a tablespace that has been designated as the default tablespace for the database. You must first reassign another tablespace as the default tablespace and then drop the old default tablespace. You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. In this syntax: First, specify the name of the tablespace that you want to drop after the DROP TABLESPACE keywords. Second, use the INCLUDE CONTENTS to delete all contents of the tablespace. If the tablespace has any objects, you must Third, use AND DATAFILES option to instruct Oracle to delete.
Sometimes we need to drop the datafile or tempfile from the tablespace in Oracle. We have different procedure as per Oracle release. There is no direct SQL statement to drop datafiles from a tablespace.
In that case we need to drop the tablespace after all data has been moved to a new tablespace. With 10gR2, we can directly drop the datafile from tablespace if the datafile is empty i. You can check if the datafile is empty using the below command. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
If it is having table segment, we can drop the segment and recreate that oracle table from data from some test database. Related Articles alter tablespace add datafile : Check out this post on How to add datafile to tablespace in Oracle, add tempfile to temporary tablespace,how to how to decorate the church for your wedding datafile in ASM How to check Tablespace in Oracle : Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size how to change default tablespace in oracle :Default tablespace are the tablespace where the objects are created when no tablespace name is specified by users.
Find out how to check default tablespace How to check temp tablespace in Oracle :This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile,find the temp usage by Session shrink datafile in Oracle : Check out how to shrink the datafile and reclaim space on the filesystem.
How to resolve ORA Leave a Reply Cancel reply.
Steps to Recreate TEMP Tablespace
Below is the commands to drop a tablespace. 1. Drop a tablespace without removing the physical database files. SQL> select file_name from dba_data_files where tablespace_name='TESTING'; FILE_NAME /home/oracle/app/oracle/oradata/cdb1/loveescortus.com SQL> drop tablespace TESTING; Tablespace dropped. Let's look at a simple DROP TABLESPACE statement. This would drop tablespace called tbs_perm_01, delete all contents from the tbs_perm_01 tablespace, and drop all referential integrity constraints (Referential integrity constraints from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.). Jan 12, · There is no direct SQL statement to drop datafiles from a tablespace. In that case we need to drop the tablespace after all data has been moved to a new tablespace. Create a new oracle tablespace to hold moved objects. Move all tables to the new tablespace. Move all indexes to the new tablespace.
When you drop a tablespace, Oracle Database does not place it in the recycle bin. Therefore, you cannot subsequently either purge or undrop the tablespace.
You cannot drop a tablespace if it contains any rollback segments holding active transactions. You can drop a tablespace regardless of whether it is online or offline. Oracle recommends that you take the tablespace offline before dropping it to ensure that no SQL statements in currently running transactions access any of the objects in the tablespace. You may want to alert any users who have been assigned the tablespace as either a default or temporary tablespace.
After the tablespace has been dropped, these users cannot allocate space for objects or sort areas in the tablespace. Any objects that were previously dropped from the tablespace and moved to the recycle bin are purged from the recycle bin.
Oracle Database removes from the data dictionary all metadata about the tablespace and all datafiles and tempfiles in the tablespace. The database also automatically drops from the operating system any Oracle-managed datafiles and tempfiles in the tablespace.
You cannot use this statement to drop a tablespace group. However, if tablespace is the only tablespace in a tablespace group, then Oracle Database removes the tablespace group from the data dictionary as well. Restrictions on Dropping Tablespaces Dropping tablespaces is subject to the following restrictions:. You cannot drop a tablespace that contains a domain index or any objects created by a domain index.
You cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions. You cannot drop a tablespace that has been designated as the default tablespace for the database.
You must first reassign another tablespace as the default tablespace and then drop the old default tablespace. You cannot drop a temporary tablespace if it is part of the database default temporary tablespace group. You must first remove the tablespace from the database default temporary tablespace group and then drop it. For example, if the tablespace being dropped contains a primary key index, but the primary key column itself is in a different tablespace, then you cannot drop the tablespace until you have manually disabled the primary key constraint in the other tablespace.
You must specify this clause to drop a tablespace that contains any database objects. If you omit this clause, and the tablespace is not empty, then the database returns an error and does not drop the tablespace. For a partitioned index-organized table, if all the primary key index segments are in this tablespace, then this clause will also drop any overflow segments that exist in other tablespaces, as well as any associated mapping table in other tablespaces. If some of the primary key index segments are not in this tablespace, then the statement will fail.
If the tablespace contains a master table of a materialized view, then the database invalidates the materialized view. If the tablespace contains a materialized view log, then the database drops the log and any other direct-path INSERT refresh information associated with the table.
Oracle Database writes a message to the alert log for each operating system file deleted. If you omit this clause and such referential integrity constraints exist, then Oracle Database returns an error and does not drop the tablespace.