Oracle
Week Three: Table space management
What does the name contain? : Improved table space management
Table space management has been significantly improved, which can be attributed to a sparser SYSTEM, support for user-defined default tablespace, new Sysaux, and even renaming
How many times have you been hurt by the user's creation of a non-SYS and system segment in the system table space?
Before Oracle9i Database, if you do not specify a default tablespace when you create a user, it defaults to the SYSTEM table space. If a user does not explicitly specify a table space when creating a segment, the segment is created in system-provided that the user has quotas in the system tablespace (either explicitly or through System Permissions unlimited tablespace). Oracle9i allows DBAs to reduce this problem by specifying a default temporary tablespace for all users who are not created with explicit temporary table space clauses.
In Oracle Database 10g, you can specify a default tablespace for the user in a similar way. During database creation, the CREATE DATABASE command can contain clause DEFAULT tablespace. Once created, you can make a table space a default tablespace by issuing the following command
ALTER DATABASE DEFAULT tablespace <tsname>;
All users that are not created with the default TABLESPACE clause will be used as their default table space. You can change the default table space at any time through this alter command, allowing you to designate different table spaces as the default tablespace on different nodes.
Important NOTE: The default table space for all users with the old tablespace is modified to even if some of the table spaces are explicitly specified for some users. For example, assume that user USER1 and USER2 table spaces are TS1 and ts2-, respectively, and that they are explicitly specified during user creation. The database's current default tablespace is TS2, but then the default tablespace for the database becomes TS1. Even if the default tablespace for USER2 is explicitly specified as TS2, it will also become TS1. Watch out for this boundary effect!
If no default table space is specified during database creation, it defaults to SYSTEM. But how do you know which is the default table space for an existing database? Issue the following query:
SELECT property_valuefrom database_propertieswhere property_name = ' default_permanent_tablespace ';
The Database_properties view displays some very important information outside of the default tablespace, such as the default temp table space, global database name, time zone, and so on.
Default tablespace for Non-essential mode
Several modes, such as intelligent Agent user DBSNMP, data Mining user ODM, are not directly related to user actions, but are still important for database integrity. Some of these patterns used SYSTEM as their default tablespace-another reason for the proliferation of objects in the system table space.
Oracle 10g introduces a new table space called Sysaux, which is used to hold objects of these schemas. This table space is created automatically during the creation of the database and is managed locally. The only allowed modification is the name of the data file.
This approach provides support for recovery in the case of SYSTEM corruption that requires a complete database recovery. The objects in the Sysaux can be restored to any normal user object while the database itself remains running.
But what if you want to move some of these patterns in Sysaux into a different tablespace? For example, consider the objects used by Logminer, which often grow in size until the table space is eventually filled. For reasons of manageability, you might consider moving them into their own tablespaces. 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 10g provides a new view to visualize the work to be done with guesswork. This view, v$sysaux_occupants, lists the names of the patterns in the Tablespace sysaux, their descriptions, the space currently in use, and how to transfer them. (see table 1.) ）
Notice how Logminer is clearly displayed as a space that occupies 7,488 KB. It is owned by mode SYSTEM, and to transfer objects, you need to perform a packaged process SYS. Dbms_logmnr_d.set_tablespace. However, for Statspack objects, this view recommends the use of an import/export method, while for a stream there is no staging process-so you cannot easily move them out of the Sysaux tablespace. The column move_procedure defaults to display the correct transfer process for almost any tool that exists in Sysaux. You can also reverse-use the transfer process to bring the object back into the Sysaux tablespace.
Renaming a table space
In a data warehouse environment (typically, for data center architectures), it is common to transfer tablespaces between databases. However, the source and destination databases must not have a tablespace with the same name. If there are two tablespaces with the same name, the segments in the destination table space must be moved to a different tablespace and recreated in the tablespace-a task that is easier said than done.
Oracle Database 10g provides a convenient solution: You can simply rename an existing tablespace (except SYSTEM and Sysaux) with the following command-whether it is a permanent tablespace or a temporary table space:
ALTER tablespace <oldname> RENAME to <newname>;
This feature will also be applied during the archive process. Suppose you have a table that is partitioned by a range to record sales history data, each month in a tablespace named after this month-for example, the January partition is named after a single, and is in a tablespace called a table space. This way you have a strategy to keep the information for 12 months. In January 2004, you will be able to archive the January 2003 data. The approximate operation process is similar to the following operations:
Use ALTER table EXCHANGE PARTITION to create a separate table JAN03 from a partition. Rename the table space to JAN03. Creates a JAN03 set of table spaces for tablespaces. Rename the tablespace JAN03 to the back of the space. Swap the empty partitions back into the table.
The 1th, 2, 4, and 5 steps are simple and do not unduly consume resources (such as redo and undo space). The 3rd step is simply to copy the file and output the data dictionary information only for JAN03, which is also a very easy process. If you need to restore a previously archived partition, this process is also very simple, and you just need to turn the same process back.
Oracle Database 10g is quite intelligent in how these renames are handled. This can be confusing if you rename the tablespace as the UNDO or default temporary table space. However, the database will automatically adjust the necessary records to reflect this change. For example, modifying the name of the default table space from USERS to user_data automatically modifies the view database_properties. Before you modify, query:
Select Property_value from Database_propertieswhere property_name = ' default_permanent_tablespace ';
Return to USERS. After you run the following statement
Alter tablespace users rename to User_data;
The above query returns USER_DATA because all references to USERS are modified to user_data.
The same is the case for modifying the default temp table space. Even modifying the name of the UNDO table space will trigger changes in the SPFILE, as follows:
Sql> Select value from v$spparameter where name = ' Undo_tablespace '; VALUE--------undotbs1sql> alter tablespace UNDOTBS1 rename to Undotbs; Tablespace altered. Sql> Select value from v$spparameter where name = ' Undo_tablespace '; VALUE--------Undotbs
Conclusion
During the recent evolution of several Oracle versions, object processing has been steadily enhanced. Oracle8i introduced the transfer of tables from one tablespace to another, Oracle 9i Database R2 introduced rename naming, now--in the latest version--The renaming of the tablespace itself became possible. These enhancements significantly reduce the task of the database administrator-especially in a data warehouse or data center environment.
