Oracle Tablespaces DDL

CREATE Tablespace

This command allows the creation of tablespaces and one or more initial datafiles. Tablespaces can contain multiple datafiles.

• DATAFILE - specifies one or more datafiles.
• MINEXTENTS - controls free space fragmentation ensuring that every used and free extent size is at least as large as and is a multiple of MINEXTENTS.
• AUTOEXTEND - enables or disables auto extension of datafile sizes as required. This can use up large amounts of disk space since when records are deleted from datafiles already allocated extended space that is not reclaimed by the operating system or released by Oracle.
  • OFF - disables AUTOEXTEND. NEXT and MAXSIZE are set to zero.
  • ON - enables AUTOEXEND.
• NEXT - disk space to be allocated to the datafile in the current tablespace when more space is required.
• MAXSIZE - maximum disk space allowed for automatic extension allocation to the datafile within the tablespace.
• UNLIMITED - set no limit when allocating disk space to a datafile.
• LOGGING or NOLOGGING - applies to tables, indexes and partitions within the tablespace.
  • LOGGING - LOGGING is the default setting.
  • NOLOGGING - no undo and redo logs are generated for operations that support the NOLOGGING option on tables, indexes and partitions in the tablespace. The tablespace-level logging attribute can be over-ridden by logging specifications at the table, index and partition levels.
• DEFAULT STORAGE - default storage parameters for all objects created in the database.
• ONLINE or OFFLINE
  • ONLINE - makes the tablespace immediately available to access granted users. ONLINE is the default.
  • OFFLINE - makes the tablespace unavailable immediately after creation.
• PERMANENT or TEMPORARY
  • PERMANENT - tablespace stores permanent (persistent) objects.
  • TEMPORARY - tablespace only used for temporary objects, eg. segments used by implicit sorts for ORDER BY clauses.
• EXTENT MANAGEMENT.
  • DICTIONARY - tablespace managed using dictionary tables as in previous versions of Oracle. The default.
  • LOCAL - a locally managed tablespace manages its own extents and particularly the addition of new extents when the tablespace size is increased. Each datafile contains a bitmap which which tracks datafile block status.
    • AUTOALLOCATE - tablespace extent sizes are managed by Oracle.
    • UNIFORM - tablespace will always contain extents of the same size, default is 1M.

CREATE TEMPORARY Tablespace

Creates a locally managed temporary tablespace. The uniform size parameter should be set to an exact multiple of the SORT_AREA_SIZE parameter size for in-memory sorting in the SGA.

CREATE TEMPORARY TABLESPACE tablespace
	TEMPFILE file specification
	[autoextend clause]
		[AUTOEXTEND OFF | AUTOEXTEND ON [ NEXT n [K | M] ] [MAXSIZE UNLIMITED | MAXSIZE n [K | M] ] ]
	[EXTENT MANAGEMENT LOCAL]
	[UNIFORM [SIZE n [K | M] ]

ALTER Tablespace

Tablespaces can be altered to add datafiles, change online status, recoverability, backup status and tablespace default storage characteristics.

• tablespace_name.
• ADD DATAFILE - adds a datafile to a tablespace. Can be done with tablespace online or offline as long as datafile not in use by another database. Backup the control file after any change to the physical structure of the database.
• AUTOEXTEND - autoextending of datafile size within a tablespace.
  • OFF - disables autoextend if enabled and sets NEXT and MAXSIZE to zero. NEXT and MAXSIZE must be re-specified if later renabled.
  • ON - enables autoextend.
• NEXT - byte size increment allocated to a datafile when more extents required.
• MAXSIZE - maximum disk space allowed for automatic extension of a datafile.
• UNLIMITED - no limit of datafile disk space allocation.
• RENAME DATAFILE - associates a tablespace with a new datafile. Must change physical file name through the operating system. Must be performed with the tablespace offline. Note that moving or renaming datafiles, redo logs or control files is a two-step process. Move the datafiles in the operating and then use the ALTER TABLESPACE RENAME command to alter the pointers to those files in the database.
• COALESCE - coalesces all contiguous free extents into larger contiguous extents for each tablespace datafile. This transaction is committed only after eight ALTER TABLESPACE commands executed. If PCTINCREASE set to a non-zero value then the SMON process will perform the coalescing operation automatically. Dropping or truncating segments performs a limited amount of free space coalescence. Note that COALESCE is only available with the ALTER TABLESPACE command. Tablespace free extents are collections of contiguos blocks, ie. blocks situated physically next to each other on disk. New extent allocation to an existing segment and extent closest in size to the extent required is used. When segments are dropped their extents are sometimes deallocated and marked free. Note that adjacent free extents are not usually combined into larger free extents and thus fragmentation of free extent space can occur.
• DEFAULT STORAGE - see CREATE TABLESPACE.
• ONLINE - attempting to put a tablespace ONLINE when previously not taken OFFLINE cleanly requires media recovery on the tablespace.
• OFFLINE.
  • NORMAL - wait for users to finish. Must be used if database in NOARCHIVELOG mode. Performs checkpoint for all online datafiles. NORMAL is the default. If the tablespace will be offline for an extended period users can be reassigned to other tablespaces using the ALTER USER command.
  • TEMPORARY - do a checkpoint of all datafiles. As with NORMAL but does not ensure all files can be written. May require later recovery.
  • IMMEDIATE - do immediately with no regards to users and checkpointing. No datafile availability check and no checkpointing. Recovery must be performed when bringing back online.
  • FOR REVOER - take tablespaces offline for point-in-time recovery.

• BEGIN BACKUP - online backup is to be performed on tablespace datafiles. Does not prevent user tablespace access. BEGIN BACKUP can not be used on a read-only tablespace and can only be used in ARCHIVELOG mode. The BEGIN BACKUP command suspends updates to the header block of the datafiles for the referenced tablespace and places the redo logs into block mode where entire changed blocks instead of just the transactions are recorded.

  During backup one can not

  • Take the tablespace offline normally.
  • Shut down the instance.
  • Begin another backup of the same tablespace
  
  
• END BACKUP - signify completion of tablespace online backup. Option should be used as soon as possible after backup completion. A tablespace left in BACKUP mode will make the database think it requires recovery on shut down and may become unrecoverable on restart. END BACKUP can not be used on a read-only tablespace and can only be used in ARCHIVELOG mode.
• READ ONLY - no further write operations for the tablespace.
• READ WRITE - both read and write operations allowed in tablespace.
• PERMANENT - permanent database objects in tablespace, persistent store.
• TEMPORARY - non-persistent storage tablespace.

DROP Tablespace

A tablespace which contains active segments cannot be dropped.