Oracle9i DBA -- Database: Fundamentals I (9)

Managing Tablespace and Datafiles

##CONTINUE##

• The free extents are managed in the tablespaces via the bitmaps. Bit values are used to indicate free or used space.
• Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces.

  CREATE TABLESPACE data02

  DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M

  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K

  SEGMENT SPACE MANAGEMENT AUTO;

  Ps. Free space is managed in the undo tablespace for a tablespace created with automatic segment-space management.

• Using the CREATE TABLESPACE statement and specifying the blocksize clause can create tablespaces of non-standard block size. DB_CACHE_SIZE and DB_nK_CACHE_SIZE
• The sum of the size of all data files specified in the CREATE TABLESPACE statement determines the initial size of a tablespace.
• Datafiles can belong to only one tablespace and database
• Moving Data Files:

  O ALTER DATABASE :

  ALTER DATABASE RENAME

  FILE '/u01/oradata/system01.dbf'

  TO '/u03/oradata/system01.dbf';

  1. Shut down the database.

  2. Use an operating system command to move the files.

  3. Mount the database.

  4. Execute the ALTER DATABASE RENAME FILE command.

  5. Open the database.

  SYSTEM tablespace cannot be taken offline, you must use this method to move

  data files in the SYSTEM tablespace.

  O Moving Data Files: ALTER TABLESPACE

  ALTER TABLESPACE userdata

  RENAME DATAFILE '/u01/oradata/userdata01.dbf'

  TO '/u01/oradata/userdata01.dbf';

  1.Take the tablespace offline.

  2. Use an operating system command to move or copy the files.

  3. Execute the ALTER TABLESPACE RENAME DATAFILE command.

  4. Bring the tablespace online.

  5. Use an operating system command to delete the file if necessary.

• When a datafile is made read-only it can reside only on read-only media.
• The Read-only tablespace can be deleted in the same manner the READ WRITE tablespace is.
• The Default temporary tablespace is a locally managed tablespace.It cannlt be altered to a permanent tablespace.

• When a tablespace is taken offline with the immediate clause, it need to do media recovery when bringing the tablespace online.

• Locally managed tablespaces :
  

  

O Free extents recorded in bitmap
O Each bit corresponds to a block or group of blocks
O Bit value indicates free or used

O Reduced contention on data dictionary tables

O No undo generated when space allocation or deallocation occurs

O No coalescing required

• Dictionary-managed tablespaces :

• O Default method

  O Free extents recorded in data dictionary tables

  O Extents are managed in the data dictionary

  O Each segment stored in the tablespace can have a different storage clause

  O Coalescing required

  • Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:

  1. Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table.

  2. Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.

  3. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

  4. The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace

  5. Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

  • FOR RECOVERY clause freezes the checkpoint information wherever it is, by not updating it. Because a point-in-time recovery is desired, if a backup had to be taken, he would have updated the checkpoint information in datafile headers and control files by using either NORMAL(default) or TEMPORARY option
  • Dictionary Managed Tablespace cause recursive space management that slows down the systens, however it provides individual STORAGE clause to satisfy user custom needs. It has to be coalesced to fee space.
  • Segments in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient.
  • Add more space in an existing TABLESPACE e

  ALTER DATABASE DATAFILE ‘/u1/d.dbf’ RESIZE 10M

  ALTER TABLESPACE tbs ADD DATAFILE ‘/u1/d2.dbf’ size 10M

  • Tablespace can store multiple datafiles on different disks, but don’t provide multiplexing (store same contents in multiple files)
  • Resizing a Tablespace

  A Tablespace can be resized by :

  O Changing the size of a data file

  - Automatically using AUTOEXTEND

  - Manually using ALTER DATABASE

  O Adding a data file using ALTER TABLESPACE

  • Enabling Automatic Extension of Data Files:

  When a data file is created, the following SQL commands can be used to enable automatic extension of the data file:

  - CREATE DATABASE

  - CREATE TABLESPACE ... DATAFILE

  - ALTER TABLESPACE ... ADD DATAFILE

  Query the DBA_DATA_FILES views to determine whether AUTOEXTEND is enabled.