|
| Saturday, April 4, 2009 |
| Tablespace Management |
Tablespace Management
Tablespace management gets a boost thanks to a sparser SYSTEM, support for defining a default tablespace for users, new SYSAUX,
and even renaming
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 . After creation, you can make a tablespace the default by issuing
ALTER DATABASE DEFAULT TABLESPACE ;
All users created without the DEFAULT TABLESPACE clause will have 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 , 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 RENAME TO ;
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
*************************************************************************************Labels: Tablespace Management |
posted by Srinivasan .R @ 2:09 PM  |
|
|
|
|
|
