Tablespaces in Oracle part 2

This second part will be focused on basic commands to manage ( create, modify and drop) a tablespace .


How to create a tablespace


To create a tablespace we must use the CREATE TABLESPACE statement:

SQL> create tablespace tbs_data;

Tablespace created.

This creates a locally managed tablespace with system allocated extent sizes, the db_create_file_dest parameter must be set before we can use this command without any other clauses , and oracle will create a datafile in that destination with a name like this O1_MF_TBS_DATA_257C3400_.DBF and a size of 100MB , this files are called Oracle Managed Files (OMF).


Permanent tablespace

SQL> create tablespace tbs_data                      
2  datafile 'D:\oraclexe\oradata\XE\tbs_data.dbf' size 10m;

Tablespace created.

Temporary tablespace

SQL> create temporary tablespace tbs_temp
2  tempfile 'D:\oraclexe\oradata\XE\tbs_temp.dbf' size 10m;

Tablespace created.

Undo tablespace

SQL> create undo tablespace tbs_undo
2  datafile 'D:\oraclexe\oradata\XE\tbs_undo.dbf' size 10m;

Tablespace created.

Locally managed

SQL>  create tablespace tbs_data_local
2  datafile 'D:\oraclexe\oradata\XE\tbs_data_local.dbf'  size 10m
3  extent management local;

Tablespace created.

'extent management local' is the default if omitted.

Dictionary managed

SQL> create tablespace tbs_data_dictionary
2  datafile 'D:\oraclexe\oradata\XE\tbs_data_dictionary.dbf' size 10m
3  extent management dictionary;
create tablespace tbs_data_dictionary
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

Oracle dont let you create dictionary managed tablespaces if your system tablespace is locally managed, dictionary managed tablespaces are more expensive than locally managed tablespaces and they may be totally deprecated in the future.


How to modify a tablespace

Take offline/online

SQL> alter tablespace tbs_data_l  offline;

Tablespace altered.

Adding space

SQL> alter tablespace tbs_data
2  add datafile 'D:\oraclexe\oradata\XE\tbs_data02.dbf' size 10m ;

Tablespace altered.

Rename datafile

The tablespace must be taken offiline or the database should not be open before doing this.

SQL> alter database rename file 'D:\ORACLEXE\ORADATA\XE\TBS_DATA_LOCAL.DBF' to
2  'D:\ORACLEXE\ORADATA\XE\TBS_DATA_LOCAL_COPY.DBF';

Database altered.

Rename tablespace

SQL> alter tablespace tbs_data_local rename to  tbs_data_l;

Tablespace altered.

How to delete a tablespace

If the tablespace is empty (has no objects)

SQL> drop tablespace tbs_data;

Tablespace dropped.

If is not empty

SQL> drop tablespace tbs_data including contents;

Tablespace dropped.

If we want to delete the datafile from the operating system

SQL> drop tablespace tbs_data including contents and datafiles;

Tablespace dropped.

If there are constraints

SQL>  drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>  drop tablespace users including contents and datafiles cascade constraints;

Tablespace dropped.


For more information, this is the best oracle architecture book:
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions