In Oracle : Oracle Tablespace: create, add a file, remove a file, drop, rename a tablespace

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Tablespace: create, add a file, remove a file, drop, rename a tablespace

 

 Oracle Tablespace: create, add a file, remove a file, drop, rename

 

 

Here are the main topics for this article:

Tablespaces & Datafiles: Overview

 

Data for Oracle tables, indexes, etc is stored in data files, but never when an object is defined, the object is associated with a file directly. All the time the Oracle objects are "located" in the tablespaces. The "tablespaces" are logical concepts and each tablespace is in relation with one or more physical file. So, when an object is created in a tablespace, the data will be stored automatically in the file(s) associated with this tablespace. 

 

 

Tablespace creation

 

=> Data tablespace (created for data objects like tables, materialized view, indexes)

 


          CREATE TABLESPACE DATA_1_TBS

          DATAFILE 'C:\oradata\data_1.dbf'

          SIZE 20M AUTOEXTEND ON;

 

       This tablespace named DATA_1_TBS has allocated 20M space on the file data_1.dbf and the size of the file will increase if the tablespace will need more space on the disk.

 

   => Temporary tablespace (keep temporary data for sort, join operations)

       

          CREATE TEMPORARY TABLESPACE temp_1

          TEMPFILE 'c:\temp01.dbf' SIZE 5M AUTOEXTEND ON;

 

       This tablespace named "temp_1" has allocated 5M space on the file temp01.dbf and the size of the file will increase if the tablespace will need more space on the disk. However, in general, the temporary tablespaces are not set to be "autoextend off", but have enough room for the database needs. 

 

  => UNDO tablespace (keep the old values for the transactions which are not commited)

    

         CREATE UNDO TABLESPACE undo1

       DATAFILE 'c:\oradata\undo1.dbf' SIZE 10M AUTOEXTEND ON

       RETENTION GUARANTEE;

 

      If you use the "RETENTION GUARANTEE" clause Oracle guarantees that whatever retention period you have set will be honored.

 

    NOTES:

  • EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;  --> creates a locally managed tablespace in which every extent is 128K

  • EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; --> creates a tablespace with automatic segment-space management (ASSM)

 


Add a file to the tablespace

 

                 ALTER TABLESPACE DATA_1_TBS

             ADD DATAFILE 'c:\oradata\data_file2.dbf' SIZE 30M AUTOEXTEND OFF;

          

To get more information on the files which are associated with a tablespace the following query could be  used: 

             

                SELECT TABLESPACE_NAME, FILE_NAME, FILE_ID, AUTOEXTENSIBLE, ONLINE_STATUS

            FROM DBA_DATA_FILES ORDER BY 1;

 

            

Remove a file from a tablespace (Resizing a tablespace)

 

Removing a file from a tablespace cannot be done directly. First, the objects must be moved in another tablespace, the initial tablespace will be dropped and recreated. After that the objects could be moved again in the tablespace which was resized. If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.

 

 

Add more space to a tablespace without adding a new file

 

ALTER DATABASE DATAFILE 'C:\oradata\data_1.dbf' RESIZE 25M;

 

 

Dropping a tablespace

 

DROP TABLESPACE DATA_1_TBS (if the tablespace is empty)

DROP TABLESPACE DATA_1_TBS INCLUDING CONTENTS;   (if the objects in the tablespace are no longer needed)

 

However the files must be deleted from the OS level 

 

 

Rename a tablespace

 

(in 10g)

ALTER TABLESPACE DATA_1_TBS RENAME TO DATA_10_TBS;    

 

(in 9i)

1.  Create a new tablespace NEW_TBLS.

2.  Copy all objects from OLD_TBLS to NEW_TBLS.

3.  Drop tablespace OLD_TBLS.

 

 

Moving the tablespace files 

 

=> for Data files, Log files:

 

1) Shutdown the database. 
2) Rename the physical file on the OS. ==> Win: SQL> HOST MOVE file1.dbf file2.dbf
3) Start the database in MOUNT mode. 

ALTER DATABASE RENAME FILE 'C:\OHOME_9I\ORADATA\DB9\REDO01.LOG' TO 'C:\ORACLE\data\REDO01.LOG';

ALTER DATABASE RENAME FILE 'C:\OHOME_9I\ORADATA\DB9\REDO02.LOG' TO 'C:\ORACLE\data\REDO02.LOG';

ALTER DATABASE RENAME FILE 'C:\OHOME_9I\ORADATA\DB9\REDO03.LOG' TO 'C:\ORACLE\data\REDO03.LOG';

 

ALTER DATABASE OPEN;

 

 

=> for Control File (SPFILE is used)

 

1) Alter control_files initialisation parameter in SPFILE

                  ALTER SYSTEM SET control_files = 'C:\NEW_PATH\RENAME_CONTROL01.CTL',

                                                                                     'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL02.CTL',

                                                                                     'C:\ORACLE\PRODUCT\10.1.0\ORADATA\DB10G\CONTROL03.CTL' 

                  SCOPE = SPFILE;


             2) Shutdown the database. 
             3) Rename the physical Controle file on the OS. ==> Win: SQL> HOST MOVE file1.ctl file2.ctl
             4) Start the database. 

 

=> for TEMP files

 

                1)  CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '...\temp_temp1.dbf' SIZE 2M

                  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

             2)  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

 

             3)   DROP TABLESPACE TEMP INCLUDING CONTENTS; -- TEMP = 1st temporary tablespace

 

             4)  CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '...\temp01.dbf' SIZE 2G

                  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M;

 

             5)  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

 

             6)  DROP TABLESPACE TEMP1 INCLUDING CONTENTS;

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Tablespace: create, add a file, remove a file, drop, rename a tablespace

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.