Thursday, September 1, 2011

Default Permanent Tablespace


Before 10g default tablespace was SYSTEM which will create issues as users data will get stored in this.

To prevent this,default permanent tablespace concept came into existence in 10g.It can be set at database creation or later.

SQL>ALTER DATABASE DEFAULT TABLESPACE users;

The current settings for the default tablespaces can be viewed using the following query:

SELECT *
FROM   database_properties
WHERE  property_name like '%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

HTH:-)