DBA Queries

DBA Queries

Q1:-How can we get the size of Database ?
Ans :- By below query we can get the size of Database

SQL>select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from dual;

Q2:-How can we get the locked Objects ?
Ans:- By below query we can get the locked object

SQL>SELECT a.sid, a.serial#, a.username, c.os_user_name
 , a.program, a.logon_time, a.machine, a.terminal
 , b.object_id, substr(b.object_name,1,40) object_name
 , DECODE(c.locked_mode,1, 'No Lock',
                        2, 'Row Share',
                        3, 'Row Exclusive',
                        4, 'Shared Table',
                        5, 'Shared Row Exclusive',
                        6, 'Exclusive') locked_mode
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

Q3:-How can we get the SQL Queries those are using high CPU?
Ans:- Below is the query by which we can get the queries which are using high CPU

SQL>select * from (
select p.spid "ospid",(se.SID),ss.serial#,ss.SQL_ID,ss.username,
       substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,
       se.VALUE/100 cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn, v$process p
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

Q3:-How to check database uptime ?

Ans:- 
a) Using v$instance view.

SQL> select instance_name,
to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time
from v$instance;

b) Using v$session view

SQL> SELECT database_name,
TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS')
FROM v$session WHERE program LIKE '%PMON%';

SQL> SELECT database_name,
to_char(logon_time,'DD/MM/YYYY HH24:MI:SS')
FROM v$session WHERE sid=1 ;

Note:- sid=1 refers to PMON

Q4:- How we can get the hourly and daily archive log size ?

Ans :-

a) Daily Archive Log Generation

select trunc(COMPLETION_TIME,'DD') Day, thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

b) Hourly Archive Log Generation

select trunc(COMPLETION_TIME,'HH') Hour,thread# ,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

 Q4:- How we can check the redo log size ?

Ans :-

SQL>SELECT
        a.GROUP#,
        a.THREAD#,
        a.SEQUENCE#,
        a.ARCHIVED,
        a.STATUS,
        b.MEMBER    AS REDOLOG_FILE_NAME,
        (a.BYTES/1024/1024) AS SIZE_MB
    FROM v$log a
    JOIN v$logfile b ON a.Group#=b.Group#
    ORDER BY a.GROUP# ASC;