Tuesday, May 3, 2016

Oracle database size sql query

How to find an oracle database size allocated and how much is occupied by the actual data..
my own query ..:) not copied from any where... :)

set serveroutput on
set linesize 99
col DATABASE_NAME format a20
select * from
(SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL)m ,
(select name "DATABASE_NAME" from v$database) f,
(select a.data_size+b.temp_size+c.redo_size "ALLOCATED_SIZE_IN_GB"
from ( select sum(bytes) / 1024 / 1024 / 1024 data_size
         from dba_data_files ) a,
     ( select nvl(sum(bytes) / 1024 / 1024 / 1024,0) temp_size
         from dba_temp_files ) b,
     ( select sum(bytes) / 1024 / 1024 / 1024 redo_size
         from sys.v$log ) c ) t,
(select sum(bytes) /1024 / 1024 /1024 "ACTUAL_DATA_OCCUPIED_IN_GB" from dba_segments ) z;

Output would be similar as below ...


NOW   DATABASE_NAME   ALLOCATED_SIZE_IN_GB ACTUAL_DATA_OCCUPIED_IN_GB
------------------- -------------------- -------------------- --------------------------
05-03-2016 15:18:39 RDTEST                        60.9462891                 2.46868896




Thanks 

Rajesh R D

How to Trouble shoot Logfile_sync wait event

   First  Identify and break down LGWR wait events. Query wait events for LGWR. In this instance LGWR sid is 3 (and usually it is). select s...