Creating Tablespaces

Creating Tablespaces

A tablespace can be created using the following commands…
SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M;

“ Locally Managed Tablespases”

SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

“ Dictionary Managed Tablespases”

SQL> CREATE TABLESPACE userdata
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\USERDATA01.dbf’ SIZE 5M
EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE
( INITIAL 1M NEXT 1M PCTINCREASE 0 ) ;

“ Undo Tablespases”

SQL> CREATE UNDO TABLESPACE undo1
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\UNDO01.dbf’ SIZE 20M;

“ Temporary Tablespases”

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘C:\ORACLE\ORADATA\ORCL\TEMP01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Creating tablespace using Oracle Enterprise Manager

From OEM Console:

1- Navigate to storage > tablespace
2- Select Create form the right mouse menu.
3- Enter detail in the General tabbed page.
4- Select the type like Temporary or Undo.
5- Click the storage tabbed page and enter the storage information.
6- Click Create.
Migrating a Dictionary Managed tablespace into
Locally managed

To migrate any dictionary managed system tablespace into locally managed tablespace, following
package is used…
SQL> DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL( ‘name of
tablespace’ );
Altering Tablespaces

“Read-Only”
To make tablespace Read-only following command is used.
SQL> ALTER TABLESPACE userdata READ ONLY;

“Offline / Online”

To make tablespace Offline following command is used.
SQL> ALTER TABLESPACE userdata OFFLINE;


To make tablespace Online following command is used.
SQL> ALTER TABLESPACE userdata ONLINE;
NOTE:- SYSTEM , Active UNDO segments and default TEMPORARY tablespace cannot be
taken offline.

“Changing Storage Setting”

Use Alter tablespace commands to change storage setting.
1- SQL> ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
2- SQL> ALTER TABLESPACE userdata
DEFAULT STORAGE ( INITIAL 2M NEXT 2M MAXEXTENT 100 ) ;
NOTE:- Storage settings for locally managed tablespaces cannot be altered.

“Adding Datafile”

To add datafile in tablespace, following command is used.
SQL> ALTER TABLESPACE userdata
ADD DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
SIZE 100M ;

“Resizing Datafile”

To resize data file, following command is used.
SQL> ALTER DATABASE
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
RESIZE 200M ;
“Moving Datafile”

To move data file from one location to another, following command is used.
1- Through Alter Tablespace, Tablespace must be offline, And target file must exist.
SQL> ALTER TABLESPACE userdata RENAME
DATAFILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
TO ‘C:\user_data.dbf ’ ;
2- Through Alter Database, Database must be mounted, And target file must exist.

SQL> ALTER DATABASE RENAME
FILE ‘C:\ORACLE\ORADATA\ORCL\user_data.dbf ’
TO ‘C:\user_data.dbf ’ ;

Dropping Tablespaces

To drop any tablespace, following command is used.
SQL> DROP TABLESPACE tablespace_name
[ INCLUDING CONTENTS [ AND DATAFILES ]
[ CASECADE CONSTRAINTS] ]
where :
INCLUDING CONTENTS = Drops all the segments in the tablespace
AND DATAFILE = Deletes the associated operating system datafiles.
CASECADE CONSTRAINTS = Drops referential constraints outside the tablespace.
For Example:
SQL> DROP TABLESPACE user_data
INCLUDING CONSTENTS AND DATAFILES CASECADE CONSTRAINTS;
NOTE:- The SYSTEM tablespace and the tablespace having active segments can not be drop.

Managing Tablespaces Using OMF
“Creating an OMF tablespace”

SQL> CREATE TABLESPACE ts_name DATAFILE SIZE 20M ;
“Adding datafile to an existing tablespace”
SQL> ALTER TABLESPACE ts_name ADD DATAFILE ;

“Changing default file location”

SQL> ALTER SYSTEM SET
db_create_file_dest = ‘C:\new_location’ ;