Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






  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


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.


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.

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.