Explains how to set the default tablespace for Oracle databases.

DBAs often encounter such a headache: they do not know who created a user in Oracle and did not specify the default tablespace for this user, therefore, this user will use the default tablespace-system tablespace. As a result, the system tablespace is quickly occupied by user data until it goes down.

In 10 Gb, DBAs can avoid this problem-specify the system default tablespace online:

Alter database default tablespace;

You can set the system's default tablespace by executing the preceding command. In this way, if you do not specify the default tablespace when creating a new user, the system default tablespace specified above will be used as the default tablespace used.

SQL> conn/as sysdba

SQL> create user test1 identified by test1 default tablespace ringidx;

The user has been created.

SQL> alter database default tablespace ring;

The database has been changed.

SQL> create user test identified by test;

The user has been created.

SQL> select username, default_tablespace defspace from dba_users where

Username = 'test ';

USERNAME DEFSPACE

-----------

TEST RING

However, once the system's default tablespace is modified, the default tablespace of all common users will be specified as this tablespace. In the preceding example, test1, the default tablespace is ringidx when it is created. After 'alter database default tablespace ring' is executed, its default tablespace is changed to ring.

SQL> select username, default_tablespace defspace from dba_users where

Username = 'test1 ′;

USERNAME DEFSPACE

-----------

TEST1 RING

Specify a special default tablespace for non-core system users

When creating an Oracle instance, in addition to creating systems such as sys and system, non-core users such as dbsnmp, odm, and perfstat will also be created. These users use system as their default tablespace in 9i. Once used, these users will also generate a large amount of data to occupy the system tablespace.

In Oracle10G, a new tablespace SYSAUX is used as the default tablespace for these users. This tablespace is created when an instance is created. Except for its data file name, it cannot be modified.

This change in Oracle enables full database recovery when the system tablespace is corrupted. Objects in sysaux can be restored to common objects, while databases can maintain normal operation.

What if DBA wants to transfer users in the sysaux tablespace to other tablespaces. In 10 Gb, a view V $ SYSAUX_OCCUPANTS is provided specifically to describe how to migrate the tablespaces 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

As shown above, if you want to change the table space of the ODM, you can use the Stored Procedure MOVE_ODM, which currently occupies a table space of 5568kb.

Rename a tablespace

This Oracle10G tablespace enhancement is an exciting change.

This feature allows you to change the names of any tablespace in the database except system and sysaux.

Alter tablespace rename;

With this feature, many things will become very simple.

Some DBAs may worry that once the name of a tablespace changes and it has been used for a long time, will it cause system confusion? You don't have to worry about this. After the preceding statement is executed, Oracle will update all the relevant data dictionary content in the system:

SQL> alter tablespace ring rename to ring1;

The tablespace has been changed.

SQL> select username, default_tablespace defspace from dba_users where username = 'test ';

USERNAME DEFSPACE

-----------

TEST RING1