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 Concepts - Administering Tablespaces

Oracle Tips by Burleson Consulting

Administering Tablespaces

We have already discussed tablespaces, so perhaps we should take some time to actually tell you the details. A tablespace is where the physical Oracle meets the logical Oracle. A tablespace is a bit like a file system for your database.  It is a logical entity (this means the tablespace only exists within the Oracle database itself, not outside of it) that provides storage space so that users can create things like tables and indexes. Its characteristics include a name, and one or more datafiles that you assign to it on creation time. Hence, the total space available in a tablespace is the total size of the pre-allocated datafiles assigned to the tablespace. One datafile can only be assigned to one tablespace, so there is no co-habitation of datafiles amongst tablespaces.

Tablespaces come in two flavors, dictionary managed and locally managed. You should always use locally managed tablespaces in Oracle Database 10g, and this is the default tablespace type. In this book, we will only discuss locally managed tablespaces.

In this section we will first look at how to create and drop tablespaces. Then we will look at how to alter tablespaces and finally we will look at data dictionary views that you can use to manage tablespaces and their associated datafiles.

Creating Tablespaces

You create tablespaces with the create tablespace command. Before you create the tablespace you should decide:

1. How big you wish the tablespace to be.

2. Where you want to put the datafile or datafiles that will be associated with that tablespace.

3. What you want to call the tablespace and the datafiles.

We recommend that you include the following in the datafile name:

1. The name of the database

2. The name of the tablespace

3. A number that makes the datafile unique

So, for a tablespace called USERS assigned to a database called BOOKTST we would have a datafile called booktst_users_01.dbf. Here is an example of creating such a tablespace using the create tablespace command:

Create tablespace users
Datafile ?/ora01/oracle/oradata/booktst_users_01.dbf? size 50m;

Note that we used 50m to indicate that we wanted to create the tablespace  50 Megabytes in size. You can also use the K symbol for Kilobytes, and the G symbol for Gigabytes. If you want to try to use the P symbol for a 50 Petabyte tablespace, we want to talk to you!

By default, Oracle tablespaces will not grow if it runs out of space. If you use up all your space, you are just out of luck unless you use the autoextend keyword to indicate that the tablespace may grow, on it?s own, dynamically. Here is an example of creating a tablespace that is set to autoextend:

create tablespace
   ?/ora01/oracle/oradata/booktst_users_01.dbf? size 50m
autoextend on
next 10m
maxsize 100m;

In this example, the tablespace will auto extend in increments of 10 megabytes until it reaches a maximum size of 100 megabytes. We recommend you use auto extend on all tablespaces for any production database.

Dropping Tablespaces

Sometimes you want to get rid of tablespaces. The drop tablespace command is used for such an operation. In this example, we are going to drop the USELESS tablespace from our database: 

Drop tablespace useless;

Oracle is pretty smart, and it will generate an error if there is anything in a tablespace when we try to drop the tablespace. If this is the case, you can tell Oracle to remove all objects in the tablespace with the including contents keyword as seen here:

Drop tablespace useless including contents;

By default, Oracle does not clean up after itself (what do you expect from a bunch of database developers anyway?), but they give you an option to force it to clean up after itself with the including contents and datafiles keywords as seen in this example:

Drop tablespace useless including contents and datafiles;

Using this command, we can drop the tablespace, any and all objects within it, and all associated data files on the hard disk.

BE CAREFUL.  If you drop a tablespace, it is very difficult to bring the tables back.  A high quality backup plan must be in place to get all your data back, and even then you are guaranteed to lose data unless you take extraordinary measures during recovery.

Altering Tablespaces

The alter tablespace command allows you to modify tablespace characteristics. You can use the alter tablespace command to add datafiles, indicate the beginning and ending of online backups and other operations as required. Here is an example of the use of the alter tablespace command to rename the BADNAME tablespace name to GOODNAME:


If you would like to add a datafile to a tablespace, you can do so with the following syntax:

ALTER TABLESPACE users ADD DATAFILE ?/ora01/oracle/oradata/booktst_users_02.dbf? size 100m

However, to resize a datafile, we must use the ?alter database? command once again, as seen here:

ALTER DATABASE DATAFILE /ora01/oracle/oradata/booktst_users_02.dbf? resize 150M


This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational