Default Temporary Tablespaces:
In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:
- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
To see the default temporary tablespace for a database, execute the following query:
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.
Other Considerations:
Some performance considerations for temporary tablespaces:
- Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
- Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (Use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up sorting operations (user processes can read/write to it directly).
Create new TEMP tablespace and drop the old "invisible" TEMP Tablespace
SQL> select * from v$tempfile;
no rows selected
SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/dbs/ORCL/temp.dbf' SIZE 100M autoextend on next 10m maxsize 500M;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/dbs/ORCL/temp.dbf' SIZE 100M autoextend on next 10m maxsize 500M
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists
SQL> select * from dba_temp_files;
no rows selected
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
*
ERROR at line 1:
ORA-12907: tablespace TEMP is already the default temporary tablespace
SQL> select * from V$TEMPFILE;
no rows selected
SQL> DROP TABLESPACE TEMP including contents and datafiles;
DROP TABLESPACE TEMP including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
4 USERS YES NO YES
5 UNDOTBS1 YES NO YES
3 TEMP NO NO YES
5 rows selected.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE SYSTEM;
Database altered.
SQL> DROP TABLESPACE TEMP including contents and datafiles;
Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/dbs/ORCL/temp.dbf' SIZE 100M autoextend on next 10m maxsize 500M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
Database altered.
SQL> select * from V$TEMPFILE;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
------- ---------------- --------- ------- ------- ------- ---------- ------- ------- ------------ ----------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 335797 03-DEC-15 3 1 ONLINE READ WRITE 1.0E+08 12800 1.0E+08 8192
/u01/app/oracle/dbs/ORCL/temp.dbf
1 row selected.
Monitoring Temporary Tablespaces and Sorting:
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
SQL> select TABLESPACE_NAME, ROUND(BYTES_USED/1024/1024) "USED (MB)",ROUND(BYTES_FREE/1024/1024) "FREE (MB)" from V$TEMP_SPACE_HEADER;
TABLESPACE_NAME USED (MB) FREE (MB)
------------------------------ --------- ---------
TEMP 3 97
1 row selected.
database default tablespaces
select * from database_properties where property_name like 'DEFAULT%TABLESPACE';
CREATE TABLESPACE
create tablespace USERS datafile '/u01/app/oracle/dbs/MIRTH/datafile/mirth_users.dbf' size 50M autoextend on extent management local maxsize 100M;
In this example, the "create tablespace" command creates a tablespace that will auto extend in increments of 1 megabytes (default if no value is set) until it reaches a maximum size of 100 megabytes. We recommend you use auto extend on all tablespaces for any production database. Also see: Oracle tablespace uniform size extents.
When you run a CREATE TABLESPACE command, you have several important tablespace options:
•Automatic segment space management: A tablespace that is created with ASMM is a ?bitmap freelist? approach that replaces the one-way linked list freelists for all objects that reside in the tablespace. You create a tablespace with ?segment space management auto? options. See here for warnings on using ASSM.
•Autoextend on: Creating a tablespace with autoextend on alleviates the potential problem of a database outage if the tablespace reaches maximum extents. You create a tablespace using autoextend with the autoextend on option. Using "create tablespace . . . autoextend on" will push the point of failure to the OS filesystem, which is good because you never want you database to hang because a tablespace cannot grow. With autoextend on, you only need to monitor the free space i the filesystem directory.
•Local management (default): All but the system tablespaces should be created as locally managed. You create a locally-managed tablespace with the extent management local syntax.
https://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm
set the default tablespace
ALTER DATABASE DEFAULT TABLESPACE USERS;
create undo tablepsace
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/u01/app/oracle/dbs/DBSNAME/datafile/ts_undo.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE 3G;
activate new created undo tablespace
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;
Autoextend ON/OFF
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND ON/OFF;
To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
COLUMN FILE_NAME FORMAT A70
SET PAGES 999
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024) MB, AUTOEXTENSIBLE FROM DBA_DATA_FILES;
RESIZE a datafile
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/datafile/datafile_prod01.dbf' RESIZE 12GM;
The following example adds another datafile to undo tablespace undotbs_01:
ALTER TABLESPACE undotbs_01 ADD DATAFILE '/u01/oracle/rbdb1/undo0102.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
https://oraclespin.wordpress.com/2008/10/03/how-to-shrink-undo-tablespace/
How to shrink UNDO tablespace?
Posted by Amin Jaffer on October 3, 2008
The datafile for UNDO tablespace can’t be shrunk as we had issue where the datafile was set to unlimited and it kept on growing to fix the issue one can do the following steps.
— UNDO_RBS1 is new undo tablespace name
SQL> create undo tablespace UNDO_RBS1 datafile ‘/u03/oradata/TEST/undorbs1.dbf’ size 1000m;
— make the new tablespace to be the undo tablespace
SQL> alter system set undo_tablespace=undo_rbs1;
— get the filename of the old undo tablespace which will be dropped so you can remove the file
SQL> SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘UNDO_RBS0’;
FILE_NAME
——————————————————————————–
/u03/oradata/TEST/undotbs0_rbs1.dbf
— drop the undo tablespace which has the unlimited datafile, if there is an active transaction in the undo tablespace then it will not be able to drop the tablespace so one can check and monitor for active transactions that are running
SQL> drop tablespace undo_rbs0;
— once the tablespace is dropped the file can be then be deleted
SQL> !rm /u03/oradata/TEST/undotbs0_rbs1.dbf
No Comments