Category: Education

Presentation Description

No description available.


Presentation Transcript

Managing Tablespaces and Datafiles : 

Managing Tablespaces and Datafiles

Objectives : 

Objectives After completing this lesson, you should be able to do the following: Define the purpose of tablespaces and datafiles Create tablespaces Manage tablespaces Create and manage tablespaces using Oracle Managed Files (OMF)

Tablespaces and Datafiles : 

Tablespaces and Datafiles Oracle stores data logically in tablespaces and physically in datafiles. Tablespaces: Can belong to only one database at a time Consist of one or more datafiles Are further divided into logical units of storage Datafiles: Can belong to only one tablespace and one database Are a repository for schema object data Database Tablespace Datafiles

Types of Tablespaces : 

Types of Tablespaces 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

Creating Tablespaces : 

Creating Tablespaces CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M; A tablespace is created using the command: CREATE TABLESPACE

Space Management in Tablespaces : 

Space Management in Tablespaces Locally managed tablespace: Free extents 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 Dictionary-managed tablespace: Free extents are managed by the data dictionary Appropriate tables are updated when extents are allocated or deallocated

Locally Managed Tablespaces : 

Locally Managed Tablespaces Reduced contention on data dictionary tables No undo generated when space allocation or deallocation occurs No coalescing required CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Dictionary-Managed Tablespaces : 

Dictionary-Managed Tablespaces Extents are managed in the data dictionary Each segment stored in the tablespace can have a different storage clause Coalescing required CREATE TABLESPACE userdata DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

Undo Tablespace : 

Undo Tablespace Used to store undo segments Cannot contain any other objects Extents are locally managed Can only use the DATAFILE and EXTENT MANAGEMENT clauses CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;

Temporary Tablespaces : 

Temporary Tablespaces Used for sort operations Cannot contain any permanent objects Locally managed extents recommended CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Default Temporary Tablespace : 

Default Temporary Tablespace Specifies a database-wide default temporary tablespace Eliminates using SYSTEM tablespace for storing temporary data Can be created by using: CREATE DATABASE Locally managed ALTER DATABASE ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Creating a Default Temporary Tablespace : 


Creating a Default Temporary Tablespace : 

Creating a Default Temporary Tablespace After database creation: To find the default temporary tablespace for the database query DATABASE_PROPERTIES ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp2; SELECT * FROM DATABASE_PROPERTIES;

Restrictions on Default Temporary Tablespace : 

Restrictions on Default Temporary Tablespace Default temporary tablespaces cannot be: Dropped until after a new default is made available Taken offline Altered to a permanent tablespace

Read Only Tablespaces : 

Read Only Tablespaces Use the following command to place a tablespace in read only mode Causes a checkpoint Data available only for read operations Objects can be dropped from tablespace ALTER TABLESPACE userdata READ ONLY;

Taking a Tablespace Offline : 

Taking a Tablespace Offline 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: To bring a tablespace online: ALTER TABLESPACE userdata OFFLINE; ALTER TABLESPACE userdata ONLINE;

Changing Storage Settings : 

Changing Storage Settings Using ALTER TABLESPACE command to change storage settings: Storage settings for locally managed tablespaces cannot be altered. ALTER TABLESPACE userdata MINIMUM EXTENT 2M; ALTER TABLESPACE userdata DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

Resizing a Tablespace : 

Resizing a Tablespace A tablespace can be resized by: Changing the size of a datafile: Automatically using AUTOEXTEND Manually using ALTER TABLESPACE Adding a datafile using ALTER TABLESPACE

Enabling Automatic Extension of Datafiles : 

Enabling Automatic Extension of Datafiles Can be resized automatically with the following commands: CREATE DATABASE CREATE TABLESPACE ALTER TABLESPACE … ADD DATAFILE Example: Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled. CREATE TABLESPACE user_data DATAFILE '/u01/oradata/userdata01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Manually Resizing a Datafile : 

Manually Resizing a Datafile Manually increase or decrease a datafile size using ALTER DATABASE Resizing a datafile adds more space without adding more datafiles Manual resizing of a datafile reclaims unused space in database Example: ALTER DATABASE DATAFILE '/u03/oradata/userdata02.dbf' RESIZE 200M;

Adding Datafiles to a Tablespace : 

Adding Datafiles to a Tablespace Increases the space allocated to a tablespace by adding additional datafiles ADD DATAFILE clause is used to add a datafile Example: ALTER TABLESPACE user_data ADD DATAFILE '/u01/oradata/userdata03.dbf' SIZE 200M;

Methods for Moving Datafiles : 

Methods for Moving Datafiles ALTER TABLESPACE Tablespace must be offline Target datafiles must exist Steps to rename a datafile: Take the tablespace offline. Use an OS command to move or copy the files. Execute the ALTER TABLESPACE RENAME DATAFILE command. Bring the tablespace online. Use an OS command to delete the file if necessary. ALTER TABLESPACE userdata RENAME DATAFILE '/u01/oradata/userdata01.dbf' TO '/u02/oradata/userdata01.dbf';

Methods for Moving Datafiles : 

Methods for Moving Datafiles ALTER DATABASE Database must be mounted Target datafile must exist ALTER DATABASE RENAME FILE '/u01/oradata/system01.dbf' TO '/u03/oradata/system01.dbf';

Dropping Tablespaces : 

Dropping Tablespaces Cannot drop a tablespace if it: Is the SYSTEM tablespace Has active segments INCLUDING CONTENTS drops the segments INCLUDING CONTENTS AND DATAFILES deletes datafiles CASCADE CONSTRAINTS drops all referential integrity constraints DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;

Managing Tablespaces Using OMF : 

Managing Tablespaces Using OMF Define the DB_CREATE_FILE_DEST parameter in one of the following ways: Initialization parameter file Set dynamically using ALTER SYSTEM command When creating the tablespace: Datafile is automatically created and located in DB_CREATE_FILE_DEST Default size is 100 MB AUTOEXTEND is set to UNLIMITED ALTER SYSTEM SET db_create_file_dest = '/u01/oradata/dba01';

Managing Tablespaces with OMF : 

Managing Tablespaces with OMF Creating an OMF tablespace: Adding an OMF datafile to an existing tablespace: Dynamically changing default file location: Dropping a tablespace includes deleting OS files: CREATE TABLESPACE text_data DATAFILE SIZE 20M; ALTER TABLESPACE text_data ADD DATAFILE; ALTER SYSTEM SET db_create_file_dest = '/u01/oradata/dba01';

Obtaining Tablespace Information : 

Obtaining Tablespace Information Obtaining tablespace and datafile information can be obtained by querying the following: Tablespaces: DBA_TABLESPACES V$TABLESPACE Datafile information: DBA_DATA_FILES V$DATAFILE Temp file information: DBA_TEMP_FILES V$TEMPFILE

Summary : 

Summary In this lesson, you should have learned how to: Use tablespaces to separate data Create various types of tablespaces Manage tablespaces Manage tablespaces using OMF

Practice 8 Overview : 

Practice 8 Overview This practice covers the following topics: Creating tablespaces Modifying tablespaces Configuring for and creating a tablespace using OMF

authorStream Live Help