my notes...

Database -> Tablespace(s) -> Datafile(s)

• An Oracle database stores all its data in logical storage units called tablespaces.

• Each tablespace consists of one or more datafiles which are physical structures conforming with the OS on which Oracle is running.

SYSTEM Tablespace:

• Created with the database and is a required part of the database.

• Contains data dictionary, including stored program units & SYSTEM undo segment.

• Should not contain user data, even though it is allowed.

Non-SYSTEM Tablespace:

Purpose:

• Allows more flexibility in database administration.

• Separate undo, temporary, application data, & application index segments.

• Separate data by backup requirements

• Separate dynamic & static data

• Control space allocated to the user's objects.

Creating TABLESPACE:

CREATE TABLESPACE tablespace [DATAFILE clause] [MINIMUM EXTENT integer [K|M]] [LOGGING | NOLOGGING] [DEFAULT storage_clause]* [ONLINE | OFFLINE] [PERMANENT | TEMPORARY] [extent_management_clause]** [segment_management_clause]***	* Only for dictionary managed tablespace **LOCAL DICTIONARY : : AUTOALLOCATE | UNIFORM SIZE Uses the DEFAULT storage_clause ***Only for permanent local tablespace.

	Examples : CREATE TABLESPACE fin_data DATAFILE 'D:/Oracle/Ora90/findata01.dbf' size 100M; CREATE TABLESPACE sktab DATAFILE 'D:/Oracle/Ora90/sktab01.dbf' SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO; CREATE TABLESPACE fin_data DATAFILE 'D:/Oracle/Ora90/sktab01.dbf' SIZE 100M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

tablespace	:	Name of the tablespace to be created
DATAFILE	:	Datafile(s) which make up the tablespace filename [SIZE integer [K|M]] [REUSE] [autoextend_clause] filename : Name of a datafile in the tablespace SIZE : Size of the file. K : Kilobytes, M: Megabytes REUSE : Reuse an existing file autoextend_clause : Enables or disables autoextention of the datafile. [AUTOEXTEND {OFF | ON [NEXT integer [K|M]] [MAXSIZE UNLIMITED | integer [K|M]] } ] NEXT : The size in bytes of the next increment of disk space to be allocated automatically when more extents are required. MAXSIZE : Maximum disk space allowed for automatic extension of a datafile UNLIMITED : The disk space that can be allocated to the datafile or tempfile is not limited.
MINIMUM EXTENT	:	This ensures that every used extent size in the tablespace is a multiple of this integer in Kilobyte or Megabyte.
LOGGING	:	Any changes to tables, indexes & partition within the tablespace will be written to the redo. This is the default mode.
NOLOGGING	:	Opposite of LOGGING. NOLOGGING effects only some DML & DDL commands ex. direct loads.
DEFAULT	:	Default storage parameters for all objects created in the tablespace. DEFAULT STORAGE (INITIAL integer [K|M] NEXT integer [K|M] MAXEXTENTS integer) Note : Only meant for Dictionary managed tablespace. This is the old way of doing things. It is a default setting to be used for objects creations that don't have a storage parameters defined for them.
OFFLINE	:	Makes the tablespace unavailable immediately after creation.
PERMANENT	:	The tablespace can be used to hold permanent objects.
TEMPORARY	:	The tablespace can be used to hold only temporary objects. Can't specify EXTENT MANAGEMENT LOCAL or BLOCKSIZE clause.
extent_management_clause	:	Specifies how the extents of the tablespace is to be managed. [ENTENT MANAGEMENT [DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM [SIZE integer [K | M]]]] DICTIONARY : Tablespace is managed using dictionary tables. LOCAL : Tablespace is managed locally using bitmaps. In this case can't specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY. AUTOALLOCATE : The tablespace is system managed. Can't specify extent size. This is the default setting. UNIFORM : The tablespace is managed using uniform extents of SIZE bytes. Default size is 1M.
segment_management_clause	:	Relevant only for PERMANENT, LOCAL managed tablespaces. Free lists or bitmaps to track free & used space in the segment. Eg: [SEGMENT SPACE MANAGEMENT AUTO ]

Space management in Tablespaces:

Tablespaces allocate space in extents. There are two ways to keep track of free & used space:

Locally managed tablespace:

• Free extents managed in the tablespace using bitmap. Locally managed is the default beginning with Oracle 9i.

• Each bit corresponds to a block or group of blocks

• bit value indicates whether free or used.

• For permanent tablespaces other than SYSTEM, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TABLESPACE command

• For temporary tablespaces you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command

• Example:

  CREATE TABLESPACE fin_data

  DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

• Advantages:

  • Avoids recursive space management operations

  • reduced contention on data dictionary tables

  • eliminates need to coalesce free space, as adjacent free space gets automatically tracked

  • the sizes of extents that are locally managed can be determined automatically by the system

  • changes to extent bitmap don't generate undo information because they don't update data dictionary tables, except in special cases such as tablespace quota information

• Note :
  

  • Allow 64K for bitmap while calculating for DATAFILE size and UNIFORM extent size. If DATAFILE SIZE is 10M & UNIFORM EXTENT SIZE is 5M, than the space left after the allocation of 1^st extent will not be sufficient to make a 2^nd 5M extent and is therefore wasted.

Dictionary managed tablespace:

• Free extents managed by data dictionary

• Appropriate tables are updated when extents are allocated or deallocated.

• Each segment stored in the tablespace can have a different storage clause. This storage is more flexible than locally managed tablespaces but much less efficient.

• coalescing required

• Example:

  CREATE TABLESPACE fin_data

DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

UNDO Tablespace:

• Used to store undo segments

• Can't contain any other objects

• Extents are locally managed

• Can only use the DATAFILE and EXTENT MANAGEMENT clauses.

	Syntax	Example
	CREATE UNDO TABLESPACE tablespace [DATAFILE clause];	CREATE UNDO TABLESPACE undo1 DATAFILE 'D:/Oracle/Ora90/undo01.dbf' SIZE 50M;

Temporary Tablespace:

• Used for sort operations

• Can't contain any permanent objects

• Locally managed EXTENTS recommended.

• Temporary tablespace provides performance improvement when there are multiple sort operations that are too large to fit into the memory.

Syntax CREATE TEMPORARY TABLESPACE temp1 TEMPFILE 'D:/Oracle/Ora90/temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

• To optimize performance of a sort in a temporary tablespace set UNIFORM SIZE to a multiple of SORT_AREA_SIZE.

• TEMPFILEs are similar to DATAFILES except that:

• Tempfiles are always set to NOLOGGING mode

• Can't make tempfiles read only

• Can't rename a temp file

• Can't create a tempfile with ALTER DATABASE command.

• Media recovery does not recover tempfiles.

Default Temporary Tablespace:

• Specifies a system wide default temporary tablespace

• Prevents SYSTEM tablespace from being used to store temporary data.

• If no such table is created during CREATE DATABASE, then SYSTEM tablespace will be used as default temporary tablespace. A warning to this effect is placed in alert_sid.log

• Once defined, any user not explicitly assigned a temporary tablespace are assigned to default temporary tablespace.

• Default temporary tablespace can be changed anytime by using:

  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

  In this case all users assigned the default temporary tablespace are reassigned to the new default.

• Creating default temporary tablespace:

	During database creation: CREATE DATABASE chik ...... .... .... DEFAULT TEMPORARY TABLESPACE temp TEMPFILE 'D:/Oracle/Ora90/temp.dbf' SIZE 100M .... >>>>When default Temporary Tablespace is created with the CREATE DATABASE, it is of locally managed type<<<<
	After database creation: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1; To find the temporary tablespace for the database (View : DATABASE_PROPERTIES): SELECT * FROM DATABASE_PROPERTIES;

• Restrictions on default temporary tablespace:

  Can't be:

• Dropped until a new default is made available

• Taken offline

• Altered to permanent tablespace.

Managing Tablespaces:

• Read Only:

Features:

• Causes a checkpoint on the datafiles of the tablespace.

• Data available for read only operations

• objects such as tables & indexes can be dropped from the tablespace since these commands only effect the data dictionary. For locally managed tablespaces, the dropped segments are changed to temporary segments to prevent the bitmap being updated.

ALTER TABLESPACE userdata READ ONLY;

Execution of command : This command puts the table in a transitional mode during which no more write operations can occur on the tablespace except for the roll back of the existing transactions which previously modified blocks in the tablespace. After all existing transactions have either been committed or rolled back, the command completes & the tablespace is placed in the read only mode.

Advantages:

• The datafiles can reside on read only medias such as CD-ROMs.

• Eliminates the need to backup large static portions of the database.

• To make a read only tablespace writable, all the datafiles of the tablespace must be online .

• Steps to create read only write-once read only (WORM) device:

ALTER TABLESPACE userdata READ ONLY;

Move the datafiles of the tablespace to the read only device.

ALTER TABLESPACE .... RENAME DATAFILE...

• Taking a tablespace offline:

  Tablespaces that can't be taken offline:

  • SYSTEM tablespace

  • tablespaces with active undo segments

  • default temporary tablespace

  Syntax:

• ALTER TABLESPACE tablespace
  {ONLINE | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE| FOR RECOVERY ] };

  	NORMAL	:	Default option. Flushes all blocks in all datafiles in the tablespace out of the SGA. Media recovery not needed to bring this tablespace back online.
  	TEMPORARY	:	Performs a checkpoint on all datafiles even if some files could not be written to. Any offline file may require media recovery.
  	IMMEDIATE	:	Does not ensure that tablespace files are available and does not perform a checkpoint. Media recovery must before bringing the tablespace back online.
  	FOR RECOVERY	:	Takes tablespace offline for point-in-time recovery

  ALTER TABLESPACE userdata OFFLINE;
  ALTER TABLESPACE userdata ONLINE;

Note: Oracle automatically switches a tablespace from online to offline when certain errors are encountered ( like when DBWn fails in several attempts to write to a datafile of the database.

Purpose:

• make a portion of the database unavailable while allowing normal access to rest of the database.

• Perform an offline tablespace backup (although it can also be backed while online & in use)

• recover a tablespace or datafile while the database is open

• move datafile while the database is open

Status of offline Tablespace:

• Oracle does not permit any SQL statements to reference any objects contained in the offline tablespace. Users trying to access such objects receive an error.

• The event of tablespace going online or offline is recorded in the data dictionary & control files.

• When a tablespace is taken offline, Oracle server takes the all associated datafiles offline.

• A tablespace which is offline while database shuts down remains offline & is not checked when database is subsequently mounted & reopened.

• Changing storage settings:

  Syntax:

ALTER TABLESPACE tablespace
MINIMUM EXTENT integer [K|M]
| DEFAULT storage_clause];

Examples:

ALTER TABLESPACE itemdata MINIMUM EXTENT 4m;
ALTER TABLESPACE itemdata DEFAULT STORAGE(INITIAL 4M NEXT 4M MAXEXTENTS 500);

Note: Storage settings of locally managed tablespaces can not be altered.

• Resizing a tablespace:

• Enabling automatic extension of Datafiles:

  Syntax:

autoextend_clause for datafiles: [AUTOEXTEND {ON|OFF[NEXT integer[K|M]] [MAXSIZE UNLIMITED|integer[K|M]] } ];

CREATE TABLESPACE tablespace DATAFILE 'D:/Oracle/Ora90/sk01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

ALTER DATABASE [database] DATAFILE filename [, filename] ... autoextend_clause;

• Manually resizing a Datafile:

	Syntax	Example
	ALTER DATABASE [database] DATAFILE filename [, filename] ... RESIZE integer[K|M];	ALTER DATABASE DATAFILE 'D:/Oracle/Ora90/sk02.dbf' RESIZE 200M;

Note: This command can be used to increase or decrease the size of the database file. If there are datbase objects stored above the specified size, then the database size is decreased only to the last block of the last objects in the datafile.

• Adding Datafiles to a Tablespace:

	Syntax	Example
	ALTER TABLESPACE tablespace ADD DATAFILE filespec [autoextend clause] ;	ALTER TABLESPACE sktab ADD DATAFILE 'D:/Oracle/Ora90/sk03.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M ;

• Moving Datafiles : Methods:

	Syntax 1:	Example
	ALTER TABLESPACE tablespace RENAME DATAFILE filename [, filename] ... TO filename [, filename] ...;	ALTER TABLESPACE sktab RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO SK_TAB03 ;

Note:

• Tablespace must be offline.

• Target datafiles must exist.

• Source filenames must match the names stored in the control file.

• Always provide complete filenames including path names to identify old & new files.

Steps to perform:

• Take Tablespace offline.

• Use OS to copy/move files

• Execute ALTER TABLESPACE command

• Bring Tablespace online.

• Delete the old file using OS if necessary.

	Syntax 2:	Example
	ALTER DATABASE [database] RENAME FILE filename [, filename] ... TO filename [, filename] ...;	ALTER DATABASE RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO 'D:/Oracle/Ora90/SK_TAB03';

Steps to perform:

• Shutdown database

• Use OS to copy/move files

• Mount database

• Execute ALTER DATABASE RENAME command

• Open the database.

• Dropping Tablespaces:

  • Can't drop a SYSTEM tablespace

  • Can't drop a tablespace with active segments.

	Syntax :	Example
	DROP TABLESPACE tablespace [INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]] ;	DROP TABLESPACE sktab INCLUDING CONTENTS AND DATAFILES;

	INCLUDING CONTENTS	:	Drops the segments
	INCLUDING CONTENTS AND DATAFILES	:	Deletes datafiles
	CASCADE CONSTRAINTS	:	Drops all referential integrity constraints

• A tablespace which still contains data can't be dropped without the INCLUDING CONTENTS option.

• When tablespace is dropped only file points in the control file of the associated database is dropped. The datafiles can then be deleted manually. For auto deletion include the AND DATAFILES clause. If the datafiles are OMF then in that case also they are dropped automatically.

• A tablespace in read-only mode can also be dropped with contents.

• It is recommended to take the tablespace offline before dropping it to prevent any any transaction from accessing the segments within.

• Managing Tablespaces using OMF:

Define DB_CREATE_FILE_DEST in either init.ora or set dynamically with ALTER SYSTEM SET db_create_file_dest= 'path/filename';

	Syntax :	Example
	CREATE TABLESPACE tablespace [DATAFILE [filename] [SIZE integer[K|M] ] ];	CREATE TABLESPACE sktab DATAFILES SIZE 50M; ALTER TABLESPACE sktab ADD DATAFILE;

• OMF features:

  • Datafile clause is not required when using OMF. Datafiles are automatically created and located in DB_CREATE_FILE_DEST. Datafile name are generated automatically by Oracle server as (ora_tbs1_2ixfh90q.dbf)

  • Default SIZE is 100M

  • AUTOEXTEND is set to UNLIMITED.

  • Destination can be changed dynamically using ALTER SYSTEM SET db_create_file_dest= 'path/filename'

  • When OMF tablespace is dropped, associated datafiles are deleted automatically at the OS level.

Views:

Tablespaces	:	DBA_TABLESPACES V$TABLESPACE	: :
Datafiles	:	DBA_DATA_FILES V$DATAFILE	: :	Select tablespace_name, file_name, autoextensible from DBA_DATA_FILES;
Temp files	:	DBA_TEMP_FILES V$TEMPFILE	: