How to avoid creating user objects in a SYSTEM Tablespace on Oracle database Instances

Products

• VMware

Issue/Introduction

This article provides a method of avoiding and creating user objects in a SYSTEM Tablespace on Oracle database Instance.

Symptoms:

• Fragmentation of the tablespace.
• System tables are unable to grow.

Cause

In Oracle database instances, user objects are created in the system tablespace leading to unnecessary fragmentation and preventing system tables from growing.

Resolution

When the CONNECT and RESOURCE roles are granted to a user in the Oracle database instance, the user has UNLIMITED TABLESPACE privilege (RESOURCE role has this inbuilt privilege) on all tablespaces.
The user then has the ability to create a table (ANY object) by specifying the tablespace name in the create command, including SYSTEM tablespace.

To avoid this, you must revoke the UNLIMITED TABLESPACE privilege from the user, then, provide an UNLIMITED quota on the particular tablespaces where the user needs to create objects.

Example:
Lets assume that a user needs to have the following tablespaces:

• System
• data
• indexes
• temp
• UNDO

1. Create the user as follows:

Create user user01 identified by user01

Default tablespace data

Temporary tablespace temp;

2. Next, grant the roles:

Grant connect, resource to user01;

3. Revoke the UNLIMITED TABLESPACE system privilege after login as SYS or SYSTEM:

Revoke Unlimited tablespace from user01;

4. Now, specify the quota in the tablespace where the user01 needs to create his objects:

Alter user user01

Quota unlimited on data

Quota unlimited on indexes;
 

From now on, user01 will not be able to create an object in the system tablespace.