Examples of Oracle database settings default Tablespace issues

Examples of Oracle database settings default tablespace problems explain Oracle database settings default tablespace problems, read examples of Oracle database settings default tablespace issues, DBAs often encounter a headache: do not know who created a user on Oracle , when it was created, the user was not given a default tablespace, so the user would use the default Tablespace--system table empty

DBAs often encounter a headache: they don't know who created a user on Oracle, and when they do, they don't specify a default tablespace for that user, so the user uses the default tablespace--system table space. Causes the system table space to be quickly occupied by user data until it is down.

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

ALTER DATABASE DEFAULT tablespace;

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

Sql>conn/as SYSDBA

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

The user has created.

sql> ALTER DATABASE default tablespace ring;

The database has changed.

Sql> create user test identified by test;

The user has created.

Sql> Select Username, default_tablespace defspace from Dba_users where

Username= ' TEST ';

USERNAME Defspace

————– —————-

TEST RING

However, it is important to note that once the system default tablespace is modified, the default tablespace for all normal users will be specified as the table space, as in the example above, Test1, when it was created, specified his default tablespace as Ringidx, executed the ' ALTER DATABASE default Tablespace ring ', his default tablespace was also 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 you create an Oracle instance, you create non-core users, such as DBSNMP, ODM, Perfstat, and so on, in addition to creating users such as SYS, system cores, and so on. These users are using system as their default tablespace in 9i. These users, once used, also generate a large amount of data to occupy the system table space.

In oracle10g, a new tablespace sysaux is used as the default table space for these users. This table space is created in the instance creation, except that his data file name can be modified, others are not allowed to be modified.

This change in Oracle enables the database to be fully restored when the system table space is corrupted. Objects in the Sysaux can be restored to normal objects, while the database remains in normal operation.

What to do if the DBA wants to move users in the Sysaux table space to other tablespaces. In 10G, a view v$sysaux_occupants is specifically provided to describe how to transfer the tablespace for 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 above, if you want to change the table space of the ODM, you can use the stored procedure MOVE_ODM, which currently occupies 5568kb of table space.

Renaming a table space

This oracle10g a compelling change in the table space enhancement.

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

ALTER tablespace RENAME to;

With this feature, it will make a lot of things very simple.

A DBA might worry that once the name of a tablespace has changed and it has been used for a long time, will it cause confusion in the system? Do not worry, after executing the above statement, Oracle will update the contents of all relevant data dictionaries in the system:

Sql> alter tablespace ring rename to ring1;

Table space has changed.

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

USERNAME Defspace

————– —————-

TEST RING1