Temp Tablespace and UNDO Tablespace management

Check UNDO tablespace usage

SELECT file_name, tablespace_name, bytes/1024/1024 UNDO_SIZE_MB, SUM

(bytes/1024/1024) OVER() TOTAL_UNDO_SIZE_MB

FROM dba_data_files d

WHERE EXISTS (SELECT 1 FROM v$parameter p WHERE LOWER

(p.name)=’undo_tablespace’ AND p.value=d.tablespace_name);

Add datafile to undo tablespace

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE E:\APP\ANKUSH\ORADATA\MYDB\UNDOTBS02.DBF’ size 10M;

Query to see Current Temp Datafiles State

set pages 999

set lines 400

col FILE_NAME format a75

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MBfrom dba_temp_files d, v$tempfile vwhere d.FILE_ID = v.FILE#order by d.TABLESPACE_NAME, d.FILE_NAME;

Add tempfile to temp tablespace

ALTER TABLESPACE TEMP ADD TEMPFILE ‘E:\APP\ANKUSH\ORADATA\MYDB\TEMP02.DBF’ SIZE 10M;

Leave a Reply

Your email address will not be published. Required fields are marked *