Oracle Tips by Burleson

Tablespace Administration

Carrying through with the analogy that Oracle is an operating system, we can say that tablespaces take the place of disks. But with this “disk,” you, the Remote DBA, can specify its size and how it will create and store data (via the DEFAULT STORAGE clause) in its files (tables).

3.1   Tablespace Creation

See Code Depot

Keywords and ParametersOracle9i creates a system-managed UNDO (rollback, for us old-timers) tablespace (this feature is new with this version). If a database is created in UNDO automatic management mode, and no UNDO tablespace is specified in the CREATE DATABASE command, the SYSTEM tablespace will be used. AN UNDO tablespace uses AUTOALLOCATE LOCAL extent management. The database manages an UNDO tablespace, and no other objects can be assigned to it. You can only include the DATAFILE and EXTENT MANAGMEMENT LOCAL clauses for an UNDO tablespace. All UNDO tablespaces are permanent, read/write, and are in logging mode; and the values for MINIMUM EXTENT and DEFAULT STORAGE are system-generated.

tablespace. The name of the tablespace to be created.

DATAFILE. Specifies the datafile or files to comprise the tablespace.

TEMPFILE. IF tablespace is TEMPORARY, must use TEMPFILE; specifies the tempfiles to be used in the TEMPORARY tablespace.

MINIMUM EXTENT integer. Controls freespace fragmentation in the tablespace by ensuring that every in-use and/or free extent size in a tablespace is at least as large as, and is a multiple of, integer.

AUTOEXTEND. Enables or disables the automatic extension of datafile.

OFF. Disables AUTOEXTEND if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in later ALTER TABLESPACE AUTOEXTEND commands if OFF is specified; they are not persistent values.

ON. Enables AUTOEXTEND.

NEXT. Disk space to allocate to the datafile when more extents are required.

MAXSIZE. Maximum disk space allowed for allocation to the datafile.

UNLIMITED. Set no limit on allocating disk space to the datafile.

LOGGING, NOLOGGING. Specifies the default logging attributes of all tables, index, and partitions within the tablespace. LOGGING is the default. If NOLOGGING is specified, no undo and redo logs are generated for operations that support the NOLOGGING option on the tables, index, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

www.oracle-script.com