All you need to know about Tablespaces in Oracle?

Default Tablespace, Default TEMP Tablespace and Default UNDO Tablespace:

What are default Tablespace in Oracle?

How to validate Default Tablespaces in Oracle Database?

Find Default Tablespace? 

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

--------------------

USERS

Find Default TEMP Tablespace?

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------

TEMP

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_TEMP_TABLESPACE TEMP

Find Default UNDO Tablespace?

SQL> show parameter undo_tablespace

NAME TYPE VALUE

--------------- ------- ----------

undo_tablespace string UNDOTBS1

How to Change Default Tablespace, Default TEMP Tablespace and Default UNDO Tablespace 

ALTER DATABASE DEFAULT TABLESPACE USERS02;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

How to Create more than 1 Default Tablespaces?

--- Its just like creation of normal tablespace but at any given point of time there will be only 1 Default Tablespace in my database.

CREATE TABLESPACE USERS02;

CREATE TABLESPACE USERS02 DATAFILE '/oradata/users02.dbf' SIZE 1000M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TABLESPACE USERS02;

How to Create more than 1 Default TEMP Tablespaces?

Its just like creation of normal TEMP tablepace but at any given point of time there will be only 1 Default Tablespace in my database.

CREATE TEMPORARY TABLESPACE TEMP02;

CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE '/oradata/temp02.dbf' SIZE 1000M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

How to Create more than 1 Default UNDO Tablespaces?

CREATE UNDO TABLESPACE UNDOTBS02;

CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oradata/undo02.dbf' SIZE 1000M AUTOEXTEND ON;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS02 SCOPE=BOTH;

Set of Questions:

1. Can I have more than 1 UNDO Tablespaces?

2. Can I have more than 1 Default UNDO Tablespaces?

3. Can I have more than 1 TEMP Tablespaces?

4. Can I have more than 1 Default TEMP Tablespaces?

3. Can I have more than 1 Default Tablespaces?

Next super set of Questions?

1. Can I assign Multiple TABLESPACE to a single user?

2. Can a single user have multiple TEMP TABLESPACE?

3. Can a single user have multiple UNDO TABLESPACE?

4. Can multiple user have different TABLESPACE, TEMP and UNDO TABLESPACE?

Regards,

Mallik