Oracle database is having of one or more logical storage units called tablespaces, which collectively store all of the database’s data. Every tablespace at Oracle database having one or more files called datafiles, which can be find at the operating system in which Oracle is running.
This article will guide you for managing tablespaces with ease of administration.
Index
Create New Tablespace
SQL Command for Create Tablespace Having File System –
CREATE TABLESPACE RCAPSDATA DATAFILE '/RCAPS/data/RCAPS/RCAPS_DATA01.dbf' size 2G AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;SQL Command for Create Tablespace Having ASM –
CRAETE TABLESPACE RCAPSDATA DATAFILE '+DATA’;SQL Command for Create Tablespace Having OMF –
CREATE TABLESPACE RCAPSDATA;Adding Datafile to Tablespace
SQL Command for Adding Datafile to Tablespace Having File System –
ALTER TABLESPACE RCAPSDATA ADD DATAFILE '/RCAPS/data/RCAPS/RCAPS_DATA02.dbf' size 2G AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M; SQL Command for Adding Datafile to Tablespace Having ASM –
ALTER TABLESPACE RCAPSDATA ADD DATAFILE '+DATA’;SQL Command for Adding Datafile to Tablespace Having OMF –
ALTER TABLESPACE RCAPSDATA ADD DATAFILE;Steps to Add Datafile in Oracle ASM / OMF –
Please Note, If datafiles are in ASM or database is OMF, no need to provide “.dbf” extension. Only diskgroup name is sufficient to add datafiles. If datafiles are located on filesystem then provide the full location and name of the datafile.
Drop Tablespace
SQL Command for Dropping a Tablespace –
DROP TABLESPACE RCAPSDATA INCLUDING CONTENTS AND DATAFILES;DROP TABLESPACE RCAPSDATA INCLUDING CONTENTS CASCADE CONSTRAINTS;Resize Datafile in Oracle Database
SQL Command for Adding Autoextend to a Datafile –
ALTER DATABASE DATAFILE '/RCAPS/data/RCAPS/RCAPS_DATA03.dbf' AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M;SQL Command for Resizing of Datafile –
ALTER DATABASE DATAFILE '/RCAPS/data/RCAPS/RCAPS_DATA02.dbf' RESIZE 500M;Drop Datafile
Use DROP DATAFILE or DROP TEMPFILE to drop a single datafile.
SQL Command for Drop a Datafile –
ALTER TABLESPACE example DROP DATAFILE '+DATA/example_df3.f';ALTER TABLESPACE USERS DROP DATAFILE '/EIS/OraData/EIS/datafile/users01.dbf';ALTER TABLESPACE GLOBAL_TEMP DROP DATAFILE '/EIS/OraData/EIS/datafile/users01.dbf';Restrictions for Dropping Datafiles –
The following are restrictions for dropping datafiles and tempfiles:
- The database must be open.
- If a datafile is not empty, it cannot be dropped.
- 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.
- You cannot drop the first or only datafile in a tablespace.
- This means that DROP DATAFILE cannot be used with a bigfile tablespace.
- You cannot drop datafiles in a read-only tablespace that was migrated from dictionary managed to locally managed.
- You cannot drop datafiles in the SYSTEM tablespace.
- If a datafile in a locally managed tablespace is offline, it cannot be dropped.
Create TEMP Tablespace
SQL Command for Creating a TEMP Tablespace –
CREATE TEMPORARY TABLESPACE GLOBAL_TEMP DATAFILE '/RCAPS/data/RCAPS/GLOBAL_TEMP01.dbf' size 2G AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;Checking Datafile Details
SQL Command for Checking Datafile Details –
set pages 50000 lines 32767
col file_name for a60
select file_name,sum(bytes)/1024/1024,autoextensible,sum(maxbytes)/1024/1024 from dba_data_files where tablespace_name='USERS' group by file_name,autoextensible order by 1;
Check for Datafile Addition Date | Tablespace Audit
SQL Command for Checking Datafile Addition Details –
set pages 50000 lines 32767
col FILE_NAME for a50
col TABLESPACE_NAME for a20
col CREATION_TIME for a20
select a.file_name,a.tablespace_name,b.creation_time from dba_data_files a,v$datafile b where a.file_id=b.file# and a.tablespace_name='UNI_INDEXES' order by tablespace_name;