Skip to main content

Oracle database check db_recovery_file_dest_size usage


db_recovery_file_dest_size usage


The following scripts will check space utilization for db_recovery_file_dest_size:
col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999


select
   name,
   ceil( space_limit / 1024 / 1024) size_mb,
   ceil( space_used / 1024 / 1024) used_mb,
   decode( nvl( space_used, 0),0, 0,
   ceil ( ( space_used / space_limit) * 100) ) pct_used
from
    v$recovery_file_dest
order by
   name desc;


*********************************



set lines 100
col name format a60

select
   name,
   floor(space_limit / 1024 / 1024) "Size MB",
   ceil(space_used / 1024 / 1024)   "Used MB",
from
   v$recovery_file_dest
order by
   name;

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database How much redo generated per hour

Track the Amount of Redo Generated per Hour / Day  Amount of Redo Generated per Hour SELECT  Start_Date,   Start_Time,   Num_Logs, Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date, To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs FROM V$log_History Vlh GROUP BY To_Char(Vlh.First_Time,  'YYYY-MM-DD'), To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist, V$log Vl ,  V$database Vdb WHERE Vl.Group# = 1 ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time; Redo generated Per day: select trunc(completion_time) rundate ,count(*)  logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1;
3 comments
Read more

Oracle Database Check blocking sessions

Check blocking sessions Let's create a scenario /* Create a blocking lock To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table: */ SQL> create table tstlock (foo varchar2(1), bar varchar2(1)); --Table created. SQL> insert into tstlock values (1,'a'); --1 row created. SQL> insert into tstlock values (2, 'b'); --1 row created. SQL> select * from tstlock ; FOO BAR --- --- 1   a 2   b --2 rows selected. SQL> commit ; --Commit complete. --Now grab a lock on the whole table, still in Session 1: SQL> select * from tstlock for update ; --And in Session 2, try to update a row: SQL> update tstlock set bar= 2  'a' where bar='a' ; --This statement will hang, blocked by the lock that Session 1 is holding on the entire table. /*Identify the blocking session Orac...
Post a Comment
Read more