Temporary/Undo tablespace referance

Temporary tablespace:

>create user mathan identified by mathan temporary tablespace temp1;

 Default temporary tablespace (no drop, no rename, no relocate) its only read only.

>create temporary tablespace temp2 tempfile  ‘/opt…../temp02.dbf’ size 100m;

>select property_name, property_value from database_properties where property_name=’DEFAULT_TEMPORARY_TABLESPACE’;

>alter database default temporary tablespace temp2;

Size check:

>select tablespace_name,(total_block *8)/1024,(used_blocks*8)/1024,current_users from v$sort_segments;

>select *from v$tempfile;

>select * from dba_temp_files;

Size Increases:

>alter tablespace temp add tempfile ‘/opt…./temp01.dbf’ size 100m;

Or

>alter database tempfile ‘/opt/…/tem01.dbf’ resize 200m;

Temporary tablespace group:

>create temporary tablespace temp1 temfile ‘/opt/…./temp1.dbf’ size 100m tablespace group tempgroup;

Add exiting temp to group:

>alter tablespace temp tablespace group tempgroup;

> alter tablespace temp1 tablespace group tempgroup;

>alter database default temporary tablespace tempgroup;

Drop member:

>alter tablespace temp tablespace group ‘’;

>drop tablespace temp;

Undo tablespace:

>create undo tablespace undotbs2 datafile ‘/opt/../undo2.dbf’ size 100m;

>show parameter undo_tablespace;
>alter system set undo_tablespace =undotbs2; 

/Advisor of undo size:

Undo tablespace size=transaction rate *block size *retention time/1024/1024

>slect max(undoblocks)/600 from v$undostat;

>show parameter unto_retention;

/

/10g:

>variable n number;

>begin

:n:=dbms_undo_adv.RBU_migration;

End;

/

>print n;   /