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:

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:

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:

ALTER TABLESPACE badname RENAME 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.