How to Improve table space management in Oracle databases

Before Oracle9i Database, if the default tablespace is not specified during user creation, it will be the SYSTEM tablespace by default. If you do not explicitly specify a tablespace when creating a segment, this segment will be created in SYSTEM-provided that you have a quota in the SYSTEM tablespace or are explicitly granted, you can also grant the system permission unlimited tablespace ). Oracle9i allows the DBA to specify a default temporary tablespace for all users created without the explicit temporary tablespace clause, thus reducing this problem.

In Oracle Database 10 Gb, you can specify a default tablespace for users. During DATABASE creation, the create database Command can include the sub-statement default tablespace. After creation, you can run the following command to change a tablespace to the default tablespace:

ALTER DATABASE DEFAULT TABLESPACE ;

All users created without the default tablespace clause will use it as their default tablespace. You can use this ALTER command to change the default tablespace at any time, so that you can specify different tablespaces as the default tablespace on different nodes.

Important: the default tablespaces of all users with old tablespaces are changed to, even if some tablespaces are explicitly specified by some users. For example, assume that the tablespaces of USER1 and USER2 are TS1 and ts2-they are explicitly specified During User Creation. The current default tablespace of the database is TS2, but then the default tablespace of the database is TS1. Even if the default tablespace of USER2 is explicitly specified as TS2, it will change to TS1. Be careful with this boundary effect!

If the default tablespace is not specified during database creation, it is SYSTEM by default. But how do you know the default tablespace of an existing database? Issue the following query:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

Besides the default tablespace, The DATABASE_PROPERTIES view also displays important information, such as the default temporary tablespace, global database name, and time zone.

Default tablespace in non-essential mode

Several modes, such as smart proxy user DBSNMP and Data Mining user ODM, are not directly related to user operations, but are still important to database integrity. Some of these patterns use SYSTEM as their default tablespace-another reason for object proliferation in the SYSTEM tablespace.

Oracle Database 10 Gb introduces a new table space called SYSAUX, which is used to save objects in these modes. This tablespace is automatically created during database creation and managed locally. The only data file name that can be modified is the data file name.

This method provides support for recovery when SYSTEM corruption requires complete database recovery. Objects in SYSAUX can be restored to any normal user object while the database itself remains running.

But what if you want to transfer some of these modes in SYSAUX to a different tablespace? For example, considering the objects used by LogMiner, the size of these objects often increases until the tablespace is finally filled. For manageability reasons, you may consider transferring them to their own tablespace. But what is the best way to achieve this?

As a database administrator, it is important for you to understand the correct process of transferring these special objects. Fortunately, Oracle Database 10 Gb provides a new view that visualizes the work to be done with guesses. In this view, V $ SYSAUX_OCCUPANTS lists the schema names in the tablespace SYSAUX, their descriptions, the space currently in use, and how to transfer them.

Note how LogMiner is clearly displayed as occupying 7,488 KB of space. It belongs to the mode SYSTEM. To transfer objects, You need to execute the packaging process SYS. DBMS_LOGMNR_D.SET_TABLESPACE. However, for STATSPACK objects, the import/export method is recommended for this view. For streams, there is no transfer process-so you cannot easily move them out of the SYSAUX tablespace. By default, the column MOVE_PROCEDURE displays the correct Transfer Process for almost all tools in SYSAUX. You can also reverse the transfer process to bring the object back to the SYSAUX tablespace.

Rename a tablespace

In a typical data warehouse environment, for the data center architecture), it is very common to transmit tablespaces between databases. However, the source database and target database must not have tablespaces with the same name. If there are two tablespaces with the same name, the segments in the target tablespace must be transferred to a different tablespace, and then the tablespace is re-created-this task is easy to do.

Oracle Database 10 Gb provides a convenient solution: You can use the following command to rename an existing tablespace SYSTEM and SYSAUX)-whether it is a permanent tablespace or a temporary tablespace:

ALTER TABLESPACE RENAME TO ;

This feature will also be applied during the archiving process. Assume that you have a table partitioned by range to record sales history data. The partition of each month is located in a tablespace named by this month-for example, the partition in February is named JAN, it is located in a tablespace named JAN. In this way, you have a 12-month retention policy. You will be able to archive data for January 1. The general procedure is similar to the following:

◆ Use alter table exchange partition to create an independent TABLE JAN03 from the jan partition.
◆ Rename the tablespace to JAN03.
◆ Create a convertible tablespace set for tablespace JAN03.
◆ Rename the tablespace JAN03 as JAN.
◆ Swap empty partitions back to the table.

Steps 1st, 2, 4, and 5 are simple and do not overconsume resources such as redo and undo space ). Step 2 only Copies files and Outputs Data Dictionary information for JAN03, which is also a very easy process. If you need to restore the previously archived partitions, this process is also very simple, you just need to reverse the same process.

Oracle Database 10 Gb can intelligently process these renaming methods. If you rename a tablespace as an UNDO or default temporary tablespace, this may cause confusion. However, the database automatically adjusts the necessary records to reflect this change. For example, changing the name of the default tablespace from USERS to USER_DATA will automatically modify the view DATABASE_PROPERTIES. Before modification, query:

select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

Return USERS. After running the following statement:

alter tablespace users rename to user_data;

The preceding query returns USER_DATA because all references to USERS are modified to USER_DATA. Modify the default temporary tablespace. Even modifying the name of the UNDO tablespace triggers changes 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

In the recent evolution of several Oracle versions, object processing has been steadily enhanced. Oracle8i introduces the transfer of a table from one tablespace to another, and Oracle 9i Database R2 introduces column rename. Now-in the latest version-Renaming the tablespace itself becomes possible. These enhancements significantly reduce the tasks of database administrators, especially in data warehouses or data center environments.