You can create a default permanent tablespace for newly created users to avoid them having the SYSTEM tablespace as the default, which is never recommended.  Both the CREATE DATABASE and ALTER DATABASE commands support the DEFAULT TABLESPACE clause.  Command that sets the default tablespace to ‘USERS’ tablespace for all users is:

 

Example:

 

ALTER DATABASE DEFAULT TABLESPACE users;

 

SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

SQL>

 

If you are not sure what the default tablespace is for newly created users, you can view the  DATABASE_PROPERTIES to shows the database default tablespace with a property_name of DEFAULT_TEMP_TABLESPACE and DEFAULT_PERMANENT_TABLESPACE.

 

Example:

 

SELECT property_name, property_value FROM database_properties where property_name like ‘%TABLESPACE’;

 

SQL> SELECT property_name, property_value FROM database_properties where property_name like ‘%TABLESPACE’;

PROPERTY_NAME

——————————————————————————–

PROPERTY_VALUE

——————————————————————————–

DEFAULT_TEMP_TABLESPACE

TEMP

DEFAULT_PERMANENT_TABLESPACE

USERS

SQL>

 

Once a default permanent tablespace is defined, all objects for users will be created there unless they have their own default tablespace defined or the create statement defines a different tablespace.

 

 

Larry Catt

OCP