Third Week: Table Space Management
What is included in the name? : Improved Table Space Management
Table space management has been significantly improved, which can be attributed to a sparser system, define support for users, new Sysaux, even renamed
How many times have you been hurt by the user in the SYS and SYSTEM segment in the SYSTEM table space?
Before Oracle9i Database, if you do not specify the default table space when you create a user, it will default to the System tablespace. If the user does not explicitly specify a table space when creating a segment, this segment will be created in the System - Prerequisites that the user has quotas in the System tablespace (either explicitly grant, either grant by system authority unlimited TableSpace ). Oracle9i allows DBA to specify a default temporary table space for users who are not created with explicit temporary tablespace clauses, thereby reducing this issue.
In Oracle Database 10g, you can similarly specify a default table space for the user. During the database creation, the Create Database command can contain clauses default tablespace.
. After you create, you can make a table space into the default table space by issuing the following command.
Alter Database Default TableSpace
All users who have not created in the DEFAULT TABLESPACE clause will
As their default table space. You can change the default table space at any time, allowing you to specify different tablespaces as the default table space on different nodes.
Important note: The default table space of all users who have the old table space is modified to
Even if some tablespace is explicitly specified for certain users. For example, assuming that the tablespaces of users USER1 and USER2 are TS1 and TS2, respectively, they are explicitly specified during user creation. The current default table space of the database is TS2, but then, the default table space of the database becomes TS1. Even if the default table space of USER2 is explicitly designated as TS2, it will also become TS1. Caute this boundary effect!
If the default table space is not specified during the database creation, it will default to System. But how can you know which one is the default table space of the existing database? Send the following query:
SELECT Property_Value
From Database_Properties
Where property_name = 'default_persManent_TablesPace';
The Database_Properties view displays the default table space, and some very important information is displayed - such as the default temporary table space, the global database name, time zone, etc.
Default table space for non-essential patterns
Several modes (such as intelligent agent users dbsnmp, data mining users ODM) are not directly related to user operation, but it is still important for database integrity. Some of these modes have used System as their default table space - this is another reason for the proliferation of objects within the SYSTEM table.
Oracle Database 10g introduces a new table space called Sysaux, which is used to save these modes. This table space is automatically created during the database creation and manages locally. The only allowed to modify the name of the data file.
This approach is supported when the system is damaged to the recovery when the SYSTEM is required to recover. Objects in Sysaux can be restored to any normal user object, while the database itself remains.
But what if you want to transfer some of these modes in these modes in sysaux to a different table space? For example, consider the object used by Logminer, the size of these objects often grow until the table space is finally filled. For manageability, you may consider transferring them to their own tablespace. But what is the best way to achieve this? As a database administrator, understand the correct process of transferring these special objects is important for you. Fortunately, Oracle Database 10g offers a new view to make the work visualization to do. This view, V $ sysaux_occupants, lists the names of the pattern in the tablespace sysaux, their instructions, the currently used space, and how to transfer them. (See Table 1.)
Note How Logminer is clearly displayed as spaced in 7,488 KB. It hits the mode System All, and to transfer objects, you need to perform packaging process sys.dbms_logmnr_d.set_tablespace. However, for the StatsPack object, this view is recommended to use the import / export method; for streaming, there is no transfer process - so you cannot easily transfer them from the SYSAUX tablespace. Colum Move_Procedure The correct transfer process of almost all tools existing in sysaux is displayed. You can also use the transfer process inversely to enable the object back to the SYSAUX table space.
Rename a table space
In the data warehouse environment (typically, for the data center architecture), it is common between the transport tablespace between the database. But the source database and target database must have a table space with the same name. If there are two tablespaces with the same name, the segments in the target table space must be transferred to a different table space, and then recreate this table space - this task is easy to do.
Oracle Database 10g provides a convenient solution: You can use the following command to simply rename an existing table space (except system and sysaux) - whether permanent table space or temporary table space:
Alter TableSpace
This feature will also be applied to the archive process. Assuming that you have a list of partitioned partitions to record sales history data, this partition each month is located in a table space named in this month - for example, the partition named Jan in January, and is in a name JAN Table space. This way you have a policy that retains information for 12 months. In January 2004, you will be able to archive data from January 2003. The rough operational process is similar to the following:
Create a separate table JAN03 from the partition JAN using ALTER TABLE Exchange Partition. Rename the table space as JAN03. Create a transportable table space set for table space JAN03. Rename the tablespace JAN03 to Jan. Exchange empty partitions back.
Step 1, 2, 4 and 5 are very simple and will not excessively consume resource (eg, redo and undo space). Step 3 is just a copy file and only output data dictionary information for JAN03, which is also a very easy process. This process is also very simple if you need to restore the archive, this process is also very simple, you only need to put the same process in turn.
Oracle Database 10g is quite intelligent in handling these renamed methods. If you rename a table space as undo or default temporary table space, this may be confused. However, the database will automatically adjust the necessary records to reflect this change. For example, modify the name of the default table space from users to user_data will automatically modify the view Database_Properties. Before you modified, inquiry: SELECT Property_Value from Database_Properties
Where property_name = 'default_persManent_TablesPace';
Return Users. After running the following statement
Alter TableSpace Uses rename to user_data;
The above query returns USER_DATA because all references to Users are modified to user_data.
Modify the situation of the default temporary table space. Even the names of the UNDO table space will also trigger 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
in conclusion
The object processing has been stable in the process of the evolution of several Oracle versions. Oracle8i introduced the transfer from a table space to another table space, Oracle 9i Database R2 introduced a column rename, now - in the latest version - Table space itself is possible. These enhancements significantly reduce the task of the database administrator - especially in a data warehouse or data center environment.
