- 07 Jun, 2019
- 0 Comments
- 43 Secs Read
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;