UNDO Tablespace total and used size in MB or GB

To check the size of each undo tablespace along with total and used sizes.

--Undo TBS size in MB

select

a. tablespace_name,

sum(a.bytes)/(1024*10240) "Allocation in MB",

sum(b.bytes)/(1024*10240) "Used in MB"

from

dba_data_files a,

dba_segments b

where

a.tablespace_name = b.tablespace_name

and

a.tablespace_name in (select tablespace_name from dba_undo_extents) 

group by

a.tablespace_name;

--Undo TBS size in GB

set linesize 200

set pagesize 200  

select 

   tablespace_name, 

   --round(TABLESPACE_SIZE/1024,2) size_MB,

   round(TABLESPACE_SIZE/1024/1024,2) size_GB,

   round(used_space/1024/1024,2) Used_MB,

   --round(used_space/1024/1024,2) Used_GB,

   round(used_percent,2) Percentage_used

from

   dba_tablespace_usage_metrics

 where tablespace_name in (select tablespace_name from dba_undo_extents) 

order by 2;

Resolving Tempfile is failing verification test or the error message ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

Following are the commands and steps to resolve  ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles

-----Tempfile drop and recreate----

 

--Enable restricted session 

ALTER SYSTEM ENABLE RESTRICTED SESSION;

--Create another Temporary Tablespace TEMP1

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE  'D:\app\oracle\oradata\......\TEMP02.DBF' SIZE 2G;

 

--Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

--shutdown database

shutdown immediate;

--startup database

startup;

--Drop the original temp tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

--Create TEMP tablespace

 

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\app\oracle\oradata\.......\TEMP01.DBF' SIZE 2G;

--Make TEMP as default tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

--shutdown database

shutdown immediate;

--startup database

startup;

--Drop temporary for tablespace temp1

 

DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

ALTER SYSTEM DISABLE RESTRICTED SESSION;