Set Default table Space

DBAs often encounter a problem that is not known who created a user on Oracle, and when created, does not specify a default tablespace for the user, so the user takes the default tablespace--system the table space. Causes system tablespaces to be quickly filled with user data until downtime.

In 10G, DBAs have a way to avoid this problem--Specify the system default Tablespace online:

ALTER DATABASE DEFAULT tablespace <tsname>;

By executing the above command, you can set the default tablespace for the system. In this case, when creating a new user, if you do not specify his default tablespace, the system default tablespace specified above will be used as the default tablespace.

Sql>conn/as SYSDBA

Sql> create user test1 identified by test1 default Tablespace ringidx;

User has created.

sql> ALTER DATABASE default tablespace ring;

The database has changed.

Sql> create user test identified by test;

User has created.

Sql> Select Username, default_tablespace defspace from Dba_users where

Username= ' TEST ';

USERNAME Defspace

--------------                 ----------------

TEST Ring

Note, however, that once the system default table space has been modified, the default tablespace for all normal users will be specified as the tablespace, as in the example above, when test1 specified his default tablespace as RINGIDX and executed ' ALTER DATABASE default Tablespace ring ', his default table space was also changed to ring.

Sql> Select Username, default_tablespace defspace from Dba_users where

Username= ' TEST1 ';

USERNAME Defspace

--------------                 ----------------

TEST1 Ring Specifies a special default table space for non-core system users

When you create an Oracle instance, you create some non-core users, such as DBSNMP, ODM, Perfstat, and so on, in addition to creating a core of systems such as SYS, System, and so on. These users use System as their default table space in 9i. Once these users are used, they also produce a larger amount of data to occupy the system table space.

In oracle10g, a new table space Sysaux is used as the default tablespace for these users. This table space is created when the instance is created, except that his data file name can be modified, and nothing else is allowed to be modified.

This change in Oracle allows for a full library recovery of the database when the system table space is corrupted. Objects in Sysaux can be restored to normal objects, and the database will remain in operation.

What if the DBA wants to move users in the Sysaux tablespace to other tablespaces? In 10G, this is dedicated to providing a view v$sysaux_occupants to describe how to transfer the table spaces of these users.

SELECT * from v$sysaux_occupants where occupant_name = ' ODM '

Occupant_name occupant_desc schema_name move_procedure move_procedure_desc space_usage_kbytes

------------- ------------- ----------- -------------- -------------------   ------------------

ODM Oracle Data Mining dmsys MOVE_ODM move Procedure for Oracle data Mining 5568

1 rows selected

For example, if you want to change the table space of the ODM, you can use the stored procedure MOVE_ODM, which currently occupies 5568kb of tablespace. Renaming a table space

This oracle10g an exciting change in table space enhancements.

This feature allows you to change the name of any table space in the database except system and Sysaux.

ALTER tablespace <oldname> RENAME to <newname>;

With this feature, a lot of things are going to be very simple.

A DBA may worry that once the name of a table space has changed and that it has been in use for a long time, it will cause confusion in the system. Don't worry about it. After executing the above statement, Oracle updates all the relevant data dictionaries in the system:

Sql> alter tablespace ring rename to ring1;

The table space has changed.

Sql> Select Username, default_tablespace defspace from dba_users where username= ' TEST ';

USERNAME Defspace

--------------                 ----------------

TEST RING1