Oracle DBA Useful Queries Part-1 – “Basic DBA Queries”

Dba scripts helps for easy database administration . Here are some dba scripts which will help in Oracle Database Administration

How to check oracle database up-time ?

set lines 3000 pages 3000
col STARTUP_TIME for a20
col HOST_NAME for a20
col INSTANCE_NAME for a15

select to_char(sysdate, ‘dd-mon-yy hh24:mi:ss’) “Current date” from dual;
select instance_name,to_char(STARTUP_TIME, ‘dd-mon-yy hh24:mi:ss’) as “STARTUP_TIME”,host_name from gv$instance;

select name,open_mode,database_role from gv$database;
show parameter db_name
show parameter db_uniq


How to check blocking sessions in oracle database ? (Works in RAC database also)

set pagesize linesize 400
column blocker format a11;
column blockee format a10;
column sid format 99999;

select a.inst_id, (select username from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker,a.sid,
(select module from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker_module ,’ is blocking ‘ “IS BLOCKING”,b.inst_id, (select username from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee,b.sid ,(select module from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee_module from gv$lock a, gv$lock b where a.block <>0 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2 and a.sid <> b.sid order by 1, 2;

 

How to check invalids in oracle database ?

set lines 300  pages 3000 trimspool on
col object_name for a30
col object_type for a15
col owner for a10
select object_name,object_type,owner from dba_objects where status like ‘INVALID’ order by owner;

To list count:
select count(*) from dba_objects where status like ‘INVALID’;
select owner,object_type, count(*) from dba_objects where status =’INVALID’ group by owner,object_type;
select object_name,object_type,owner from dba_objects where status like ‘INVALID’ order by owner;

To recompile:
@$ORACLE_HOME/rdbms/admin/utlrp.sql


How to check find sql text of sid in oracle database ?

select s.sid, q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid like &1 order by piece;

How to check find sql text of sid in oracle RAC database ?

select s.sid, q.sql_text from gv$sqltext q, gv$session s where q.address = s.sql_address and s.sid like &1  and s.inst_id=&2 order by piece;

How to kill session in oracle RAC database ?

ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

How to kill all sessions in  oracle RAC database (auto script generation) ?

select ‘alter system kill session ”’||sid||’,’||serial#||’,@’||inst_id||”’;’from gv$session where sid=&1;
select ‘alter system kill session ”’||sid||’,’||serial#||’,@’||inst_id||”’ immediate;’ from gv$session where sid=&1 ;

How to check sids of all background process in oracle database ?

SELECT C.NAME,B.SPID,A.SID FROM V$SESSION A,V$PROCESS B, V$BGPROCESS C  WHERE C.PADDR <> ’00’ AND C.PADDR = B.ADDR AND B.ADDR = A.PADDR;


How to find sid,serial# for given SPID?

col “SID/SERIAL” format a10
col “SID/SERIAL” format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ‘,’ || s.serial# “SID/SERIAL”, s.username, s.osuser, p.spid “OS PID”, s.programfrom v$session s, v$process p Where     s.paddr = p.addr and  p.spid in (&spid) order by to_number(p.spid);

How to check alert log location from 11g on wards ?

SQL > select * from v$diag_info ;

$ adrci
adrci> show home
ardci> show alert

 

 

Leave a comment

Your email address will not be published. Required fields are marked *