Tablespaces: Can belong to only one database at a time
Consist of one or more data files
Are further divided into logical units of storage
Data files: Can belong to only one tablespace and one database
Is a repository for schema object data
SYSTEM tablespace
1. Created with the database
2. Required in all databases
3. Contains the data dictionary, including the stored program units
4. Contains the SYSTEM undo segment
5. Should not contain user data, although it is allowed.
Non-SYSTEM tablespaces:
1. Enables more flexibility in database administration
2. Separate undo, temporary, application data, and application index segments
3. Separate data by backup requirements
4. separate dynamic and static data
5. Control the amount of space allocated 2 the users.
Creating tablespace:
CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIMUN EXTENT integer [K|M]
[BLOCK SIZE integer [K]]
[LOGGING|NOLOGGING]--- All tables, indexes, and partitions within the tablespace have the changes written to online redo log files.
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management clause]
[segment_management clause]
Locally managed Tablespaces:
Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Extent_management_clause:
[EXTENT MANAGEMENT [DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]]
DICTIONARY: tbspc is managed using data dictionary tables
LOCAL: locally managed via bitmaps. If you specify LOCAL you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.
AUTOALLOCATE: tbspc is system managed. Users cannot specify an extent size.
UNIFORM: tbspc is managed with uniform extents of SIZE bytes. Default size 1 MB.
Advantages of locally Managed Tablespaces:
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.
Dictionary- Managed Tablespaces
Extents are managed in the data dictionary
Each segment stored in the tablespace can have a different storage clause.
Coalescing is required.
E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(Initial 1M NEXT 1M PCTINCREASE 0);
Storage is more flexible than locally managed tablespaces but much less efficient.
Undo Tablespaces
Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses
E.g. CREATE UNDO TABLESPACE undo1
DATAFILE ‘/u01/oradata/undo01.dbf’ SIZE 40M;
An undo tbspc cannot be dropped if it is being used by any instance or contains any undo information to recover transactions.
Temporary Tablespaces
Used for sort operations
Can be shared by multiple users
Cannot contain any permanent objects
Locally managed extents recommended.
E.g. CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘u01/oradata/temp01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
Note: Nonstandard block size cannot be specified when creating temporary tbspc.
Tempfiles:
Always set to the NOLOGGING mode.
Cannot make a tempfile read-only
Cannot rename a tempfile
Cannot create a tempfile with the ALTER DATABASE command
Tempfiles are required for read-only databases
Media recovery does not recover tempfiles
To optimize the performance of a sort area in a temporary tbspc, set the UNIFORM SIZE to be a multiple of the parameter SORT_SIZE_SIZE.
Default Temporary Tablespace
Specifies a database-wide default temporary tablespace
Eliminates using SYSTEM tbspc for storing temporary data
Can be created using:
CRETAE DATABASE DBA01
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/$HOME/ORADATA/u03/temp01.dbf’ SIZE 4M
Or
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE default_temp2;
To find the default temp tbspc for the database query DATABASE_PROPERTIES
SELECT * FROM DATABASE_PROPERTIES;
Restrictions on default temporary tablespace:
Default tbspc cannot be:
Dropped until after a new default is made available
Taken offline
Altered to a permanent tablespace
Read Only Tablespaces
ALTER TABLESPACE userdata READ ONLY;
Causes a checkpoint
Data available only for red operations
Objects can be dropped from tbspc (tables and indexes because these commands affect only the data dictionary
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.
Taking a tablespace offline
Reasons:
Make a portion of the database unavailable, while allowing normal access to the remainder of the database
Perform an offline tablespace backed up (although a tbspc can be backed up while online and in use)
Recover a tablespace or data file while the database is open
Move a data file while the database is open
Offline status of a tablespace
Tbspc offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tbspc
When tbspc goes offline or comes back online, the event is recorded in the data dictionary and in the control file.
Taking Tablespace Offline
ALTER TABLSPACE tablespace
{ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVERY]}
NORMAL: flushes all blocks in all data files in the tbspc out of SGA
TEMPORARY: performs a checkpoint for all online datafiles. Any offline file may require media recovery
IMMEDIATE: does not ensure that the tbspc files are available, does not perform a checkpoint, and must perform media recovery before bringing it back online.
FOR RECOVERY: point-in-time recovery
Changing storage Settings
Using ALTER TABLESPACE command to change storage setting
E.g. ALTER TABLESPACE userdata MINIMUN EXTENT 2M;
ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);
Resizing a Tablespace
1. Enabling Automatic Extension of data files
Can be resized automatically with the following commands:
-CREATE DATABASE
-CREATE TABLESPACE
-ALTER TABLESPACE … ADD DATAFILE
E.g. CREATE TABLESPACE user_data
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.
SELECT tablespace_name, file_name, autoextensible
FROM dba_data_files;
2. Manually Resizing a Data File
Adds more space without adding more data files
Reclaims unused space in database
ALTER DATABASE
DATAFILE ‘u03/oradata/userdata02.dbf’
RESIZE 200M;
3. Adding Data Files to a tablespace
ALTER TABLESPACE user_data
ADD DATAFILE ‘u01/oradata/userdata03.dbf’
SIZE 200M;
Moving Data Files
• ALTER TABLESPACE
[Tbspc must be offline
Target data files must exist]
Steps
Take the tbspc offline
Use an OS command to move or copy the files
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u02/oradata/userdata01.dbf’;
Bring tbspc online
Use OS command to delete the file if necessary
• ALTER DATABASE
[Database must be mounted
Target data file must exist]
Steps
Shut down the database
Use OS command to move the file
Mount the database
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u03/oradata/userdata01.dbf’;
Open the database
Dropping Tablespaces
You cannot drop a tbspc if it
- is the SYSTEM tbspc
- has active segments
INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES deletes data files
CASCADE CONSTRAINTS drops all referential integrity constraints.
Note: tbspc dropped, only file pointers in the control file of the associated database is dropped.
Take tbspc offline before dropping it to ensure that no transactions access any of the segments in the tbspc.
Obtaining Tablespace Information
Tbspc info: DBA_TABLESPACE
V$TABLESPACE
Datafile info: DBA_DATA_FILES
V$DATAFILE
Temp file info: DBA_TEMP_FILES
V$TEMPFILE
No comments:
Post a Comment