Wednesday, 14 January 2015

working with tablespace




Notes for B.Sc ty    Oracle 10g DBA             Chapter 2
These notes are taken from the internet
Creating Tablespaces
Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment.
you cannot rename or drop the SYSTEM tablespace or take it offline.
The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it..
To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.
You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement..

CREATE TABLESPACE

Purpose
Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects.
·         A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in datafiles.
·         An undo tablespace is a type 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 tempfiles.
When you create a tablespace, it is initially a read/write tablespace. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add datafiles or tempfiles to it, or make it a read-only tablespace.
You can also drop a tablespace from the database with the DROP TABLESPACE statement.

Syntax
The syntax for the CREATE TABLESPACE statement when creating a permanent tablespace is:


CREATE
  [ SMALLFILE | BIGFILE ]
  TABLESPACE tablespace_name
  { DATAFILE { [ 'filename' | 'ASM_filename' ]
               [ SIZE integer [ K | M | G | T | P | E ] ]
               [ REUSE ]
               [ AUTOEXTEND
                   { OFF
                   | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                   [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                   }
               ]
             | [ 'filename | ASM_filename'
             | ('filename | ASM_filename'
                 [, 'filename | ASM_filename' ] )
             ]
             [ SIZE integer [ K | M | G | T | P | E ] ]
             [ REUSE ]
             }
     { MINIMUM EXTENT integer [ K | M | G | T | P | E ]
     | BLOCKSIZE integer [ K ]
     | { LOGGING | NOLOGGING }
     | DEFAULT [ { COMPRESS | NOCOMPRESS } ]
       storage_clause
     | { ONLINE | OFFLINE }
     | EXTENT MANAGEMENT
        { LOCAL
           [ AUTOALLOCATE
           | UNIFORM
              [ SIZE integer [ K | M | G | T | P | E ] ]
           ]
        | DICTIONARY
        }
     | SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
          }
SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.
  • SMALLFILE is a tablespace that contains 1,022 data or temp files
  • BIGFILE is a tablespace that contains only one data or temp file
  • tablespace_name is the name of the tablespace to create.
  • storage_clause is: STORAGE
   Example - PERMANENT TABLESPACE
The following is a CREATE TABLESPACE statement that creates a simple permanent tablespace:
CREATE TABLESPACE tbs_perm_01
  DATAFILE 'tbs_perm_01.dat'
    SIZE 20M
  ONLINE;
This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_01 that has one data file called tbs_perm_01.dat.
The following is a CREATE TABLESPACE statement that creates a permanent tablespace that will extend when more space is required:
CREATE TABLESPACE tbs_perm_02
  DATAFILE 'tbs_perm_02.dat'
    SIZE 10M
    REUSE
    AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_02 that has one data file called tbs_perm_02.dat. When more space is required, 10M extents will automatically be added until 200MB is reached.
The following is a CREATE TABLESPACE statement that creates a BIGFILE permanent tablespace that will extend when more space is required:
CREATE BIGFILE TABLESPACE tbs_perm_03
  DATAFILE 'tbs_perm_03.dat'
    SIZE 10M
    AUTOEXTEND ON;
This CREATE TABLESPACE statement creates a BIGFILE permanent tablespace called tbs_perm_03 that has one data file called tbs_perm_03.dat.
#2 - TEMPORARY TABLESPACE
A temporary tablespace contains schema objects that are stored in temp files that exist during a session.
Syntax
The syntax for the CREATE TABLESPACE statement when creating a temporary tablespace is:
CREATE
  [ SMALLFILE | BIGFILE ]
  TEMPORARY TABLESPACE tablespace_name
    [ TEMPFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
              
    [ EXTENT MANAGEMENT
       { LOCAL
       | DICTIONARY
       } ]
SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.
  • SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files
  • BIGFILE is a tablespace that contains only one data or temp file
  • tablespace_name is the name of the tablespace to create.
Example - TEMPORARY TABLESPACE
The following is a CREATE TABLESPACE statement that creates a temporary tablespace:
CREATE TEMPORARY TABLESPACE tbs_temp_01
  TEMPFILE 'tbs_temp_01.dbf'
    SIZE 5M
    AUTOEXTEND ON;
This CREATE TABLESPACE statement creates a temporary tablespace called tbs_temp_01 that has one temp file called tbs_temp_01.dbf.
#3 - UNDO TABLESPACE
A undo tablespace is created to manage undo data if the Oracle database is being run in automatic undo management mode.
Syntax
The syntax for the CREATE TABLESPACE statement when creating an undo tablespace is:
CREATE
  [ SMALLFILE | BIGFILE ]
  UNDO TABLESPACE tablespace_name
    [ DATAFILE { [ 'filename' | 'ASM_filename' ]
                 [ SIZE integer [ K | M | G | T | P | E ] ]
                 [ REUSE ]
                 [ AUTOEXTEND
                     { OFF
                     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
                     [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
                     }
                 ]
    [ EXTENT MANAGEMENT
       { LOCAL
       | DICTIONARY
       } ]
SMALLFILE or BIGFILE is optional. It determines the size of the tablespace. If you omit this option, the Oracle database will use the default tablespace type.
  • SMALLFILE is the most common tablespace size to create. It can contain 1,022 data or temp files
  • BIGFILE is a tablespace that contains only one data or temp file
  • tablespace_name is the name of the tablespace to create.
Example - UNDO TABLESPACE
The following is a CREATE TABLESPACE statement that creates an undo tablespace:
CREATE UNDO TABLESPACE tbs_undo_01
  DATAFILE 'tbs_undo_01.f'
    SIZE 5M
    AUTOEXTEND ON
  RETENTION GUARANTEE;
This CREATE TABLESPACE statement creates an undo tablespace called tbs_undo_01 that is 5MB in size and has one data file called tbs_undo_01.f.

ALTER TABLESPACE

Purpose
Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its datafiles or tempfiles.
You cannot use this statement to convert a dictionary-managed tablespace to a locally managed tablespace..
To alter the SYSAUX tablespace, you must have the SYSDBA system privilege.
If you have ALTER TABLESPACE system privilege, then you can perform any ALTER TABLESPACE operation. If you have MANAGE TABLESPACE system privilege, then you can only perform the following operations:
·         Take the tablespace online or offline
·         Begin or end a backup
·         Make the tablespace read only or read write
Before you can make a tablespace read only, the following conditions must be met:
·         The tablespace must be online.
·         The tablespace must not contain any active rollback segments.
·         The tablespace must not be involved in an open backup.
This Oracle tutorial explains how to use the Oracle ALTER TABLESPACE statement with syntax and examples.
Description
The ALTER TABLESPACE statement is used to modify a tablespace or one of its data files or temp files. A tablespace is used to allocate space in the Oracle database where schema objects are stored.
Syntax
The syntax for the ALTER TABLESPACE statement in Oracle/PLSQL is:
ALTER TABLESPACE tablespace_name
  { DEFAULT
     [ { COMPRESS | NOCOMPRESS } ] storage_clause
  | MINIMUM EXTENT integer [ K | M | G | T | P | E ]
  | RESIZE integer [ K | M | G | T | P | E ]
  | COALESCE
  | RENAME TO new_tablespace_name
  | { BEGIN | END } BACKUP
  | { ADD { DATAFILE | TEMPFILE }
       [ file_specification
          [, file_specification ]
       ]
    | DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
    | RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
    | { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
    }
  | { ONLINE
    | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
    }
    | READ { ONLY | WRITE }
    | { PERMANENT | TEMPORARY }
  | AUTOEXTEND
     { OFF
     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
        [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
     }
  } ;
Parameters or Arguments
tablespace_name is the name of the tablespace to remove from the Oracle database.
storage_clause is: STORAGE
file_specification is: { [ 'filename' | 'ASM_filename' ]
  [ SIZE integer [ K | M | G | T | P | E ] ]
  [ REUSE ]
  [ AUTOEXTEND
      { OFF
      | ON [ NEXT integer [ K | M | G | T | P | E ] ]
      [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
      }
  ]
Example - Rename Datafile
Let's look at an ALTER TABLESPACE statement that renames a datafile associated with a tablespace.
For example:
ALTER TABLESPACE tbs_perm_01 OFFLINE NORMAL;

ALTER TABLESPACE tbs_perm_01
  RENAME DATAFILE 'tbs_perm_01.dat'
  TO 'tbs_perm_01_new.dat';

ALTER TABLESPACE tbs_perm_01 ONLINE;
This ALTER TABLESPACE statement would take the tablespace offline, rename the datafile from tbl_perm_01.dat to tbl_perm_01_new.dat, and then bring the tablespace back online again.
Example - Add Datafile
Let's look at an ALTER TABLESPACE statement that adds a datafile to a tablespace.
For example:
ALTER TABLESPACE tbs_perm_02
 ADD DATAFILE 'tbs_perm_02.dat'
   SIZE 20M
   AUTOEXTEND ON;
This ALTER TABLESPACE statement add the datafile called tbs_perm_02.dat to the tbs_perm_02 tablespace.
Example - Drop Datafile
Let's look at an ALTER TABLESPACE statement that drops a datafile from a tablespace.
For example:
ALTER TABLESPACE tbs_perm_03
 DROP DATAFILE 'tbs_perm_03.dat';
This ALTER TABLESPACE statement drops the datafile called tbs_perm_03.dat to the tbs_perm_03 tablespace.
Example - Add Tempfile
Let's look at an ALTER TABLESPACE statement that adds a tempfile to a tablespace.
For example:
ALTER TABLESPACE tbs_temp_04
 ADD TEMPFILE 'tbs_temp_04.dat'
   SIZE 10M
   AUTOEXTEND ON;
This ALTER TABLESPACE statement add the tempfile called tbs_temp_04.dat to the tbs_temp_04 tablespace.
Example - Drop Tempfile
Let's look at an ALTER TABLESPACE statement that drops a tempfile from a tablespace.
For example:
ALTER TABLESPACE tbs_temp_05
 DROP TEMPFILE 'tbs_temp_05.dat';
This ALTER TABLESPACE statement drops the tempfile called tbs_temp_05.dat to the tbs_temp_05 tablespace.

DROP TABLESPACE statement

This Oracle tutorial explains how to use the Oracle DROP TABLESPACE statement with syntax and examples.

Description

The DROP TABLESPACE statement is used to remove a tablespace from the Oracle database. A tablespace is used to allocate space in the Oracle database where schema objects are stored.

Syntax

The syntax for the DROP TABLESPACE statement is:
DROP TABLESPACE tablespace_name
  [ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
    [ CASCADE CONSTRAINTS ] ] ;

Parameters or Arguments

tablespace_name is the name of the tablespace to remove from the Oracle database.
INCLUDING CONTENTS is optional. If you specify INCLUDING CONTENTS, all contents of the tablespace will be dropped. If there are objects in the tablespace, you must specify INCLUDING CONTENT or you will receive an error.
  • AND DATAFILES is optional. It will delete the associated operating system files. When using Oracle-managed files
  • KEEP DATAFILES is optional. It will NOT delete the associated operating system files.
  • CASCADE CONSTRAINTS is optional. If you specify CASCADE CONSTRAINTS, all referential integrity constraints will be dropped that meet the following criteria:

Example

Let's look at a simple DROP TABLESPACE statement.
For example:
DROP TABLESPACE tbs_perm_01
  INCLUDING CONTENTS
    CASCADE CONSTRAINTS;
This would drop tablespace called tbs_perm_01, delete all contents from the tbs_perm_01 tablespace, and drop all referential integrity constraints
Let's look at a another DROP TABLESPACE statement.
For example:
DROP TABLESPACE tbs_perm_02
  INCLUDING CONTENTS AND DATAFILES
    CASCADE CONSTRAINTS;
This would drop tablespace called tbs_perm_02, delete all contents from the tbs_perm_02 tablespace, remove the associated operating system files, and drop all referential integrity constraints
Let's look at a one file DROP TABLESPACE statement.
For example:
DROP TABLESPACE tbs_perm_03
  INCLUDING CONTENTS KEEP DATAFILES;
This would drop tablespace called tbs_perm_03, delete all contents from the tbs_perm_03 tablespace, but keep the associated operating system files.
Types of Tablespace:-
Tablespaces can be either Locally Managed to Dictionary Managed.  Dictionary managed tablespaces have been deprecated (are no longer used--are obsolete) with Oracle 11g; however, you may encounter them when working at a site that is using Oracle 10g.
 When you create a tablespace, if you do not specify extent management, the default is locally managed.
 Locally Managed 
1] Locally Managed Tablespaces
Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
  • Fast, concurrent space operations..
  • Enhanced performance
  • Readable standby databases are allowed
  • User reliance on the data dictionary is reduced
  • Coalescing free extents is unnecessary for locally managed tablespaces.
All tablespaces, including the SYSTEM tablespace, can be locally managed.
The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.
The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.
The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

The extents allocated to a locally managed tablespace are managed through the use of bitmaps. 
·        Each bit corresponds to a block or group of blocks (an extent). 
·        The bitmap value (on or off) corresponds to whether or not an extent is allocated or free for reuse. 
 Description: http://www.siue.edu/%7Edbock/cmis565/module4-tablespaces_and_datafiles_files/image004.jpg

·        Local management is the default for the SYSTEM tablespace beginning with Oracle 10g.
·        When the SYSTEM tablespace is locally managed, the other tablespaces in the database must also be either locally managed or read-only.
·        Local management reduces conflict for the SYSTEM tablespace because space allocation and deallocation operations for other tablespaces do not need to use data dictionary tables.
·        The LOCAL option is the default so it is normally not specified. 
 ·        With the LOCAL option, you cannot specify any DEFAULT STORAGE, MINIMUM EXTENT, or TEMPORARY clauses. 
 Extent Management
·        UNIFORM – a specification of UNIFORM means that the tablespace is managed in uniform extents of the SIZE specified.
o   use UNIFORM to enable exact control over unused space and when you can predict the space that needs to be allocated for an object or objects.
o   Use K, M, G, T, etc  to specify the extent size in kilobytes, megabytes, gigabytes, terabytes, etc.  The default is 1M; however, you can specify the extent size with the SIZE clause of the UNIFORM clause.
o   For our small student databases, a good SIZE clause value is 128K.
o   You must ensure with this setting that each extent has at least 5 database blocks.
·        AUTOALLOCATE – a specification of AUTOALLOCATE instead of UNIFORM, then the tablespace is system managed and you cannot specify extent sizes. 
o   AUTOALLOCATE is the default. 
§  this simplifies disk space allocation because the database automatically selects the appropriate extent size.
§  this does waste some space but simplifies management of tablespace.
o   Tablespaces with AUTOALLOCATE are allocated minimum extent sizes of 64K with a minimum of 5 database blocks per extent.
Example CREATE TABLESPACE command – this creates a locally managed Inventory tablespace with AUTOALLOCATE management of extents.
 CREATE TABLESPACE inventory
    DATAFILE '/u02/student/dbockstd/oradata/USER350invent01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
 Example CREATE TABLESPACE command – this creates a locally managed Inventory tablespace with UNIFORM management of extents with extent sizes of 128K.
 CREATE TABLESPACE inventory
    DATAFILE '/u02/student/dbockstd/oradata/USER350invent01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
 Possible Errors
You cannot specify the following clauses when you explicitly specify EXTENT MANAGEMENT LOCAL:
o   DEFAULT storage clause
o   MINIMUM EXTENT
o   TEMPORARY
 Segment Space Management in Locally Managed Tablespaces
Use the SEGMENT SPACE MANAGEMENT clause to specify how free and used space within a segment is to be managed.  Once established, you cannot alter the segment space management method for a tablespace.

MANUAL:  This setting uses free lists to manage free space within segments.
o   MANUAL is usually NOT a good choice.
AUTO:  This uses bitmaps to manage free space within segments.
o   This is the default.
o   Bitmaps allow Oracle to manage free space automatically.
o   Specify automatic segment-space management only for permanent, locally managed tablespaces.
Example CREATE TABLESPACE command – this creates a locally managed Inventory tablespace with AUTO segment space management.
 CREATE TABLESPACE inventory
    DATAFILE '/u02/student/dbockstd/oradata/USER350invent01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;
 2] Dictionary Managed 
With this approach the data dictionary contains tables that store information that is used to manage extent allocation and deallocation manually. 
Description: http://www.siue.edu/%7Edbock/cmis565/module4-tablespaces_and_datafiles_files/image006.jpg
 The DEFAULT STORAGE clause enables you to customize the allocation of extents.  This provides increased flexibility, but less efficiency than locally managed tablespaces.
 Example – this example creates a tablespace using all DEFAULT STORAGE clauses.
 CREATE TABLESPACE inventory
  DATAFILE '/u02/student/dbockstd/oradata/USER350invent01.dbf' SIZE 50M
  EXTENT MANAGEMENT DICTIONARY
  DEFAULT STORAGE (
    INITIAL 50K
    NEXT 50K
    MINEXTENTS 2
    MAXEXTENTS 50
    PCTINCREASE 0);   

·        The tablespace will be stored in a single, 50M datafile.
·        The EXTENT MANAGEMENT DICTIONARY clause specifies the management.
·        All segments created in the tablespace will inherit the default storage parameters unless their storage parameters are specified explicitly to override the default.
 The storage parameters specify the following:
·        INITIAL – size in bytes of the first extent in a segment.
·        NEXT – size in bytes of second and subsequent segment extents.
·        PCTINCREASE – percent by which each extent after the second extent grows.
·        MINEXTENTS

Oracle-Managed Files

Using Oracle-managed files simplifies the administration of an Oracle Database. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames. The database internally uses standard file system interfaces to create and delete files as needed for the following database structures:
·         Tablespaces
·         Redo log files
·         Control files
·         Archived logs
·         Block change tracking files
·         Flashback logs
·         RMAN backups
Through initialization parameters, you specify the file system directory to be used for a particular type of file. The database then ensures that a unique file, an Oracle-managed file, is created and deleted when no longer needed.

Benefits of Using Oracle-Managed Files

Consider the following benefits of using Oracle-managed files:
·         They make the administration of the database easier.
·         They reduce corruption caused by administrators specifying the wrong file.
·         They reduce wasted disk space consumed by obsolete files.
·         They simplify creation of test and development databases.
·         Oracle-managed files make development of portable third-party tools easier.
Oracle-managed files eliminate the need to put operating system specific file names in SQL scripts

on No comments:
Subscribe to: Posts (Atom)