Oracle Fundamental I - Chapter 8: Maintaining Tablespaces and Data Files

In this chapter, we are going to discuss some physical database componet for the database. The Tablespaces and Data files.
Oracle stores data logically in tablespaces and physically in data files.

• Tablespaces:
• Can belong to only one database at a time
• Consiste of one or more data files
• Are further divided into logical unites of storage
• Data files:
• Can belong to only one tablespace and one database
• Are a repository for schema object data

Type of tablespaces, the tablespaces can be basically categorized into two types:

• System Tablespace
• Created with the database
• Contains the data dictionary
• Contains the system undo segment
• Non-system tablespace
• Separate segments
• Eases space administration
• Controls amount of space allocated to a user

To create a tablespace, you issue the command as syntax below:
CREATE TABLESPACE tablespace
    [ DATAFILE clause]
    [ MINIMUM EXTENT integer[K|M]]
    [ BLOCKSIZE integer [K]]
    [ LOGGING|NOLOGGING]
    [ DEFAULT storage_clause]
    [ ONLINE|OFFLINE]
    [ PERMANENT|TEMPORARY]
    [extent_management_clause]
    [segment_management_clause]
Ex:

CREATE TABLESPACE data01 DATAFILE ‘/u01/oradata/data01.dbf’ SIZE 5M;

Space management in Tablespaces:
The space in datafile is managed either locally or by the data dictionary.

• Locally managed tablespace
• Free extents are managed in the tablespace
• Bitmap is used to record free extents
• Each bit corresponds to a block or group of blocks
• Bit value indicates free or used
• Since Oracle 9i, locally managed is the default
• Dictioinary-managed tablespace
• Free extents are managed by the data dictionary
• Appropriate tables are updated when extents are allocated or deallocated

Locally Managed TAblespaces

• Reduced contention on data dictionary tables
• No undo generated when space allocation or deallocation occurs
• No coalescing required

The tablespace is locally managed by default since 9i, and it's setup in extent clause:
    extent_management_clause:
      [ EXTENT MANAGEMENT [ DICTIONARY | LOCAL
      [ AUTOALLOCATE | uniform [  SIZE integer [K|M] ] ]
Ex:

CREATE TABLESPACE data01 DATAFILE '/u01/oradata/data01.dbf' size 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Dictionary-Managed Tablespaces

• Extents are managed in the data dictionary
• Each segment stored i the tablespace can have a different storage clause
• Coalescing is required

Ex:

CREATE TABLESPACE data01 DATAFILE '/u01/oradata/data01.dbf' size 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1m next 1m pctincrease 0);

To migrate a dictionary-managed SYSTEM tablespace, you need to use the Oracle supplied package to implement this:
Ex:

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

  

Undo tablespace:

• Used to store undo segments (Known as rollback segment in previous release of 8i).
• Cannot contain any other objects
• Extents are locally managed
• Can only use the DATAFILE and EXTENT MANAGEMENT clauses

Ex:

CREATE UNDO TABLESPACE undo01 DATAFILE '/u01/oradata/undo01.dbf' size 500M;

Temporary Tablespaces

• Ussed for sort operations
• Can be shared by multiple users
• Cannot contain any permanent objects
• Locally managed extents recommended

CREATE TEMPORARY TABLESPACE temp01 TEMPFILE '/u01/oradata/temp01.dbf' size 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Sort, or temporary segments are used when a segment is shared by multiple sort operations. Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory.
Note, non-standard block size cannot be specified when creatingn temporary tablespaces.

Default Temporary Tablespaces

• Specifies a database-wide default temporary tablespace
• Eliminates using SYSTEM tablespace for storing temporary data
• Can be created by using:
• CREATE DATABASE
• ALTER DATABASE

Ex:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;

You can also specified and create the default temporary tablespace.
Ex:

CREATE DATABASE db01 LOGFILE GROUP 1 ('/$HOME/ORADATA/u01/redo01.rdo') SIZE 100m, GROUP 2 ('/$HOME/ORADATA/u02/redo02.rdo') SIZE 100m, MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M UNDO TABLESPACE undo1 DATAFILE '/$HOME/ORADATA/u02/undo01.dbf' SIZE 200M DEFAULT TEMPORARY TABLESPACE temp01 TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M CHARACTER SET US7ASCII;

Specifies the Default Temporary Tablespace after database creation:
Ex:

ALTER DATABASAE DEFAULT TEMPORARY TABLESPACE default_temp2;

To find the default temporary tablespace, you can query the database properties:
Ex:

SELECT * FROM DATABASE_PROPERTIES;

Note: Default temporary tablespaces cannot be:

• Dropped until after a new default is made available
• Taken offline
• Altered to a permanent tablespace

A regular tablespace can be placed in read-only mode:

ALTER TABLESPACE userdata01 READ ONLY;

This command would cause:

• Causes a checkpoint
• Data available only for read operations
• Objects can be dropped from tablespace

However, it is possible to drop items, like tables and indexes, located in the read-only mode tablespace. Because the DROP command only changes data physically in the data dictionary, but not the database in the tablespace where those objects are located.

Taking a Tablespace Offline:
Whenever the database is open, a database administrator can take any tablespace, except the SYSTEM tablespace or any tablespace with active undo segments or temporary segments, offline as syntax below:
     ALTER TABLESPACE tablespace_name
         {ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVER]}

• Not available for data access
• Tablespaces that cannot be taken offline:
• SYSTEM tablespace
• TAblespaces with active undo segments
• DEfault temporary tablespace
• To take a tablespace offline:

ALTER TABLESPACE data01 OFFLINE;

• To bring a tablespace online:

ALTER TABLESPACE data01 ONLINE;

 The size of tablespaces can be resized:
A tablespace can be resized by:

• Changing the size fo a data file:
• Automatically using AUTOEXTEND
• Manually using ALTER DATABASE
• Adding a data file using ALTER TABLESPACE

You can also specified the tablespaces to be resized automatically by enabling Automatic Extension of Data Files:

• Can be resized automatically with the following commands:
• CREATE DATABASE
• CREATE TABLESPACE
• ALTER TABLESPACE .... ADD DATAFILE
• Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.

Ex:

CREATE TABLESPACE data01 DATAFILE '/u01/oradata/data01.dbf' size 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

You may also manually resize a data file:

• Manually increasae or decrease a data file size using ALTER DATABASE.
• Resizing a data file adds more space without adding more data files.
• Manual resizing of a data file reclaims unused space in database.

                ALTER DATABASE [database]
                    DAATAFILE 'filename' [, 'filename']....
                    RESIZE integer [K|M]
Ex:

ALTER DATABASE DATAFILE '/u01/oradata/data01.dbf' RESIZE 400m;

Adding Data Files to a Tablespace

• Increases the space allocated to a tablespace by adding additional data files
• ADD DATAFILE clause is used to add a data file

Ex:

ALTER TABLESPACE data01 ADD DATAFILE '/u01/oradata/data02.dbf' SIZE 400M;

Moving Data Files by using rename:
Two ways to accomplish this,

• ALTER TABLESPACE
• Tablespace must be offline
• Target data files must exist

Ex:

ALTER TABLESPACE data01 RENAME DATAFILE '/u01/oradata/data01.dbf' TO '/u02/oradata/data01.dbf';

• ALTER DATABASE
• Database must be mounted
• Target data file must exist

ALTER DATABASE RENAME FILE '/u01/oradata/data01.dbf' TO '/u02/oradata/data01.dbf';

Last, you may also drop tablespaces:

• You cannot drop a tablespace if it:
• Is the SYSTEM tablespace
• has active segments
• INCLUDING CONTENTS drops the segments.
• INCLUDING CONTENTS AND DATAFILES deletes the physical data files
• CASCADE CONSTRAINTS drops all referential integrity constraints

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

Obtaining tablespace and datafile information can be obtained by querying the following:

• Tablespace information:
• DBA_TABLESPACES
• V$TABLESPACE
• Data file information:
• DBA_DATA_FILES
• V$DATAFILE
• Temp file information:
• DBA_TEMP_FILES
• V$TEMPFILE