Oracle Database Tips by Donald Burleson

Keywords and Parameters

tablespace. The name of the tablespace to be altered.

LOGGING, NOLOGGING. Specifies that the creation and logging attributes of all tables, indexes, and partitions within the tablespace are logged in the redo log file. LOGGING is the default. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.  

When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new logging attribute; the logging attributes of existing objects are not changed. 

If the database is run in ARCHIVELOG mode, media recovery from a backup will re-create the table (and any indexes required because of constraints). When running in NOARCHIVELOG mode, all operations that can execute without logging will not generate log entries even if LOGGING is specified.  

ADD DATAFILE|TEMPFILE. Adds the datafile specified by filespec to the tablespace. See the syntax description of FILESPEC, next. You can add a datafile while the tablespace is online or offline. Be sure that the datafile is not already in use by another database.  

FILESPEC. Specifes the filename (full path), size, and identifies any existing file that should be reused; for example:

'c:\oracle1\ortest1\data\system01.dbf' SIZE 100M REUSE

AUTO EXTEND clause:

See Code Depot

AUTOEXTEND. Enables or disables the AUTOEXTENDing of the size of the datafile in the tablespace.

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.

ON. Enables AUTOEXTEND.

NEXT. The size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.

MAXSIZE. Maximum disk space allowed for automatic extension of the datafile.

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

RENAME DATAFILE. Renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system. This clause associates the tablespace only with the new file, rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system. TEMPFILEs cannot be renamed.

COALESCE. For each datafile in the tablespace, coalesce all contiguous free extents into larger contiguous extents.

www.dba-oracle.com/oracle_scripts.htm