1Z0-052 - Managing Database Storage Structures

Main

Overview of tablespace and datafiles

From Exploring the Oracle Database Architecture

You cannot have one without the other. When creating a tablespace, you must specify an associated data file. Likewise, a data file is just a file if it is not associated to a tablespace.

There are 3 types of tablespaces:

• A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
• An UNDO tablespace is atype of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.

Create and manage tablespaces

CREATE TABLESPACE

CREATE TABLESPACE tablespace_01
  DATAFILE '/u01/oracle/db1/tablespace_01.dbf'
  SIZE 50M
  ONLINE;

ALTER TABLESPACE

Add a data file:

ALTER TABLESPACE tablespace_01
  ADD DATAFILE /u01/oracle/db1/tablespace_01_001.dbf'
  AUTOEXTEND ON;

Drop a(n empty) data file:

ALTER TABLESPACE tablespace_01
  DROP DATAFILE /u01/oracle/dba/tablespace_01_001.dbf';

DROP TABLESPACE

DROP TABLESPACE tablespace_01
  INCLUDING CONTENTS--must be specified if any objects exist
  AND DATAFILES--if you want the OS to remove the datafile as well
  CASCADE CONSTRAINTS;--if there is any RI to tables in another tablespace

Space management in tablespaces
Need more research here.

Main