To check default tablespace
COLUMN property_name FORMAT A30COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
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
select TABLESPACE_NAME, BYTES/1024/1024, FILE_NAME from dba_temp_files;
TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
------------------------------ --------------- ---------------------
TEMP 66 +DATA/AGAP/TEMPFILE/temp.269.954836903
To create new temporary tablespace
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'+DATA_GRUP' SIZE 10M AUTOEXTEND ON MAXSIZE 200m
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
To set up as default
alter database default temporary tablespace temp1;
To drop old temporary tablespacedrop tablespace TEMP including contents and datafiles;
To check the result about temporary tablespace
select TABLESPACE_NAME, BYTES/1024/1024, FILE_NAME from dba_temp_files;
TABLESPACE_NAME BYTES/1024/1024 FILE_NAME
------------------------------ --------------- ---------------------
TEMP1 10 +DATA_GRUP/AGAP/TEMPFILE/temp1.264.95530
To check default tablespace
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP1 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace
