||Oracle Database Tips by Donald Burleson
Keywords and Parameters
tablespace. The name of the tablespace to be
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
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
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
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