CREATING LOCALLY MANAGED TEMPORARY TABLESPACE
SQL>create temporary tablespace <tsname> TEMPFILE ‘file path’ size
<size>m EXTENT MANAGENT LOCAL;
SQL>create temporary tablespace temptbs tempfile '/u01/app/oracle/oradata/orc/temp01.ora' size 20m Extent management local;
ADDING TEMPFILE TO TEMPORARY TABLESPACE
SQL>alter tablespace <tbname> add tempfile ‘ file path ’ size <size>m;
SQL>alter tablespace temptbs add tempfile
'/u01/app/oracle/oradata/orcl/temp02.ora' size 8m;
RESIZING A TEMPORARY FILE
SQL>alter database TEMPFILE ‘file path’ resize <size>m;
SQL>alter database tempfile
'/u01/app/oracle/oradata/orcl/temp02.ora' resize 15m;
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
TEMPFILES AND ASSOCIATED TEMPORARY TABLESPACE
CHECKING LOCAL MANAGED TEMPFILES AND STATUS
MAKING TEMPORARY TABLESPACE TO OFFLINE
SQL>alter tablespace <tsname> TEMPFILE OFFLINE;
SQL>alter tablespace TEMPTBS TEMPFILE OFFLINE ;
MANAGING TABLESPACES IN ORACLE 10g
ORA-12905
The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line The DEFAULT TEMPORARY TABLESPACE cannot be dropped until create another one. We cannot change a default temporary tablespace into a permanent tablespace. Oracle10g introduced new feature which we will create
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
ALTERING TEMPORARY TABLESPACE TO ONLINE
SQL>alter tablespace <tsname> TEMPFILE ONLINE;
SQL>alter tablespace tmpbs tempfile online;
CREATING TEMPORARY TABLESPACE AND GROUP
SQL> create temporary tablespace <tsname> tempfile ‘filepath' size <size>m tablespace GROUP <group_name>;
SQL> create temporary tablespace temtbs tempfile '/u01/app/oracle/oradata/orcl/temp01.ora' size 2m tablespace GROUP T1;
ORA-10918:
TABLESPACE GROUP name cannot be the same as tablespace name.
CREATING GROUP FOR EXISTING TEMPORARY TABLESPACE
SQL> alter tablespace <tsname> tablespace GROUP <group_name>;
SQL> alter tablespace temp tablespace group t2;
MANAGING TABLESPACES IN ORACLE 10g
Here tmpts1 is created by oracle. If NO group exists oracle will create it.
TEMPORARY TABLESPACE WITH ASSOCIATED GROUP
SQL>select * from DBA_TABLESPACE_GROUPS;
DROPPING TEMPORARY TABLESPACE
SQL> drop tablespace <tsname> including contents and datafiles;
SQL>drop tablespace tmpts including contents and datafiles;
REMOVE A TABLESPACE FROM A GROUP
SQL> alter tablespace <tsname> tablespace group '';
SQL> alter tablespace tmpts tablespace group '';
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu SQL>select property_value from database_properties where
property_name = 'DEFAULT_TEMPORARY_TABLESPACE';
PROPERTY_VALUE TEMP
V$SORT_SEGMENT
It contains information about every sort segment in TEMP TABLESPACE. This view is only updated when the tablespace is of the temporary type. We can check total_extents , extent_size, total_blocks, free_extents, free_blocks from v$sort_segment view.
SQL> select * from V$SORT_SEGMENT;
MANAGING TABLESPACES IN ORACLE 10g
DISPLAY FREE TEMP SPACE
V$TEMP_SPACE_HEADER
SQL> select sum(bytes_used), sum(bytes_free)
from v$temp_space_header group by tablespace_name;
MAKE USER USE TEMP GROUP
SQL> alter user <user_name> temporary tablespace <group name>;
SQL> alter user sam temporary tablespace t3;
The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu UNDO TABLESPACES
Undo tablespaces are used to store original data after it has been changed.
We can create more than one undo tablespace, but one of them can be active at any one time. Oracle provides a fully automated mechanism, referred
to as automatic undo management, for managing undo information and space.
AUM (AUTOMATIC UNDO MANAGEMENT)
CREATING UNDO TABLESPACE
SQL> create UNDO tablespace <tsname> datafile ‘filepath' size <size>m;
RESIZING UNDO TABLESPACE
SQL> alter database datafile ‘pathfile’ resize <size>m;
MANAGING TABLESPACES IN ORACLE 10g
ADDING DATAFILE TO UNDO TABLESPACE
SQL> alter tablespace <tsname> add ‘file path’ size <size> m;
TO FIND CURRENTLY USED UNDO TABLESPACE
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
TO FIND ALL UNDO TABLESPACES
SQL> select tablespace_name , status from DBA_TABLESPACES Where tablespace_name like '%UNDO%';
Only one undo tablespace can be active at a time. Here UNDOTBS1. We cannot drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.
MANAGING TABLESPACES IN ORACLE 10g
SWITCHING UNDO TABLESPACE
SQL>alter system set undo_tablespace=’undo_tsname’;
SQL> alter system set undo_tablespace='UNDOTBS';
UNASSIGN CURRENT UNDO TABLESPACE
SQL>alter system set undo_tablespace=' ';
SQL>alter system set undo_tablespace=' '
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
DROP UNDO TABLESPACE
SQL> drop tablespace <tsname> including contens and datfiles;
FIND ALLOCATED/FREE SIZE OF THE UNDO DATAFILE
Now I have removed UNDOTBS1&UNDOTEST, my default undo tablespace is UNDOTBS. Let’s perform small test with UNDOTBS.
MANAGING TABLESPACES IN ORACLE 10g
POINTS TO NOTE
Space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than by DBA’S so Oracle takes care undo tablespace.
undo_extent_stat.sql
DATABASE_PROPERTIES
The current settings for the default tablespaces can be viewed using the
DATABASE_PROPERTIES view.
SEGMENT_NAME ( ROLL BACK SEGMENT NAME )
MANAGING TABLESPACES IN ORACLE 10g
Exploring the Oracle DBA Technology by Gunasekaran ,Thiyagu
TO GET TABLESPACE METADATA
SQL> set serveroutput on ; SQL> declare c clob;
begin
for t in( select tablespace_name from dba_tablespaces) loop
select dbms_metadata.get_ddl('TABLESPACE', t.tablespace_name) into c from dual;
SOME IMPORTANT PARAMS ABOUT TABLESPACES
COLUMNS VALUES
CONTENTS PERMANENT , TEMPORARY , UNDO
STATUS ONLINE , OFFLINE , READ ONLY
EXTENT_MANAGEMENT DICTIONARY , LOCAL
ALLOCATION_TYPE SYSTEM ( autoallocate) , UNIFORM (manual ) SEGMENT_SPACE_MANAGEMENT MANUAL , AUTO