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
