How many times you have pulled your hair out in frustration over users creating segments other than SYS and SYSTEM in the SYSTEM tablespace?

Prior to Oracle9i Database, if the DEFAULT TABLESPACE was not specified when the user was created, it would default to the SYSTEM tablespace. If the user did not specify a tablespace explicitly while creating a segment, it was created in SYSTEM—provided the user had quota there, either explicitly granted or through the system privilege UNLIMITED TABLESPACE. Oracle9i alleviated this problem by allowing the DBA to specify a default, temporary tablespace for all users created without an explicit temporary tablespace clause.

In Oracle Database 10g, you can similarly specify a default tablespace for users. During database creation, the CREATE DATABASE command can contain the clause DEFAULT TABLESPACE <tsname>. After creation, you can make a tablespace the default by issuing

ALTER DATABASE DEFAULT TABLESPACE <tsname>;

All users created without the DEFAULT TABLESPACE clause will have <tsname> as their default. You can change the default tablespace at any time through this ALTER command, which allows you to specify different tablespaces as default at different points.

Important note: the default tablespaces of all users with the old tablespace are changed to <tsname>, even if something else was specified explicitly for some users. For instance, assume the default tablespaces for users USER1 and USER2 are TS1 and TS2 respectively, specified explicitly during user creation. The current default tablespace for the database is TS2, but later, the database default tablespace is changed to TS1. Even though USER2's default tablespace was explicitly specified as TS2, it will be changed to TS1. Beware this side effect!

If the default tablespace is not specified during the database creation, it defaults to SYSTEM. But how do you know which tablespace is default for the existing database? Issue the following query:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

The DATABASE_PROPERTIES view shows some very important information, in addition to the default tablespace—such as the default temporary tablespace, global database name, time zone, and much more.

Default Tablespace for Nonessential Schemas

Several schemas such as the intelligent agent user DBSNMP, data mining user ODM are not directly related to user operations, but are important to database integrity nonetheless. Some of these schemas used to have SYSTEM as their default tablespace — another reason for the proliferation of objects inside that special tablespace.

Oracle Database 10g introduces a new tablespace called SYSAUX that holds the objects of these schemas. This tablespace is created automatically during database creation and is locally managed. The only change allowed is the name of the datafile.

This approach supports recovery when the corruption of SYSTEM requires a full database recovery. Objects in SYSAUX can be recovered as any normal user object while the database itself remains operational.

But what if you want to move some of these schemas in SYSAUX to a different tablespace? Take, for instance, the objects used by LogMiner, which often grow in size to eventually fill up the tablespace. For manageability reasons, you may consider moving them to their own tablespace. But what is the best way to do that?

As a DBA it's important for you to know the correct procedure for moving these special objects. Fortunately, Oracle Database 10g provides a new view to take the guesswork out. This view, V$SYSAUX_OCCUPANTS, lists the names of the schemas in the tablespace SYSAUX, their description, the space currently used, and how to move them. (See Table 1).

Note how LogMiner is shown as clearly occupying 7,488 KB. It's owned by the schema SYSTEM, and to move the objects, you would execute the packaged procedure SYS.DBMS_LOGMNR_D.SET_TABLESPACE. For STATSPACK objects, however, the view recommends the export/import approach, and for Streams, there is no move procedure—thus, you can't easily move them from the SYSAUX tablespace. The column MOVE_PROCEDURE shows correct moving procedures for almost all tools resident in the SYSAUX by default. The move procedures can also be used in the reverse direction to get objects back into the SYSAUX tablespace.

Renaming a Tablespace

It is common in data warehouse environments, typically for data mart architectures, to transport tablespaces between databases. But the source and target databases must not have tablespaces with the same names. If there are two tablespaces with the same name, the segments in the target tablespace must be moved to a different one and the tablespace recreated—a task easier said than done.

Oracle Database 10g offers a convenient solution: you can simply rename an existing tablespace (SYSTEM and SYSAUX excepted), whether permanent or temporary, using the following command:

ALTER TABLESPACE <oldname> RENAME TO <newname>;

This capability can also come in handy during the archiving process. Assume you have a range-partitioned table for recording sales history, and a partition for each month lives in a tablespace named after the month—for example, the partition for January is named JAN and resides in a tablespace named JAN. You have a 12-month retention policy. In January 2004, you will be able to archive the January 2003 data. A rough course of action will be something similar to the following:

1. Create a stand-alone table JAN03 from the partition JAN using ALTER TABLE EXCHANGE PARTITION.
2. Rename the tablespace to JAN03.
3. Create a transportable tablespace set for the tablespace JAN03
4. Rename tablespace JAN03 back to JAN.
5. Exchange the empty partition back to the table.

Steps 1, 2, 4 and 5 are straightforward and do not overly consume resources such as redo and undo space. Step 3 is merely copying the file and exporting only the data dictionary information for JAN03, which is also a very easy process. Should you need to reinstate the partition archived earlier, the procedure is as simple as reversing the same process.

Oracle Database 10g is quite intelligent in the way it handles these renames. If you rename the tablespace used as the UNDO or the default temporary one, it could cause confusion. But the database automatically adjusts the necessary records to reflect the change. For instance, changing the name of the default tablespace from USERS to USER_DATA automatically changes the view DATABASE_PROPERTIES. Before the change, the query:

select property_value from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

returns USERS. After the following statement is run

alter tablespace users rename to user_data;

The above query returns USER_DATA, as all the references to USERS have been changed to USER_DATA.

Changing the default temporary tablespace does the same thing. Even changing the UNDO tablespace name triggers the change in the SPFILE as shown below.

SQL> select value from v$spparameter where name = 'undo_tablespace';

VALUE
--------
UNDOTBS1

SQL> alter tablespace undotbs1 rename to undotbs;

Tablespace altered.

SQL> select value from v$spparameter where name = 'undo_tablespace';

VALUE
--------
UNDOTBS

Conclusion

Object handling has steadily improved over the course of several recent Oracle versions. Oracle8i introduced the table move from one tablespace to another, Oracle 9i Database R2 introduced the column renaming, and now—the last frontier—the renaming of a tablespace itself is possible. These enhancements significantly ease DBA tasks, especially in data warehouse or mart environments.