[showhide type=”script1″ more_text=”How to find current running sqls in oracle” less_text=”How to find current running sqls in oracle—–>”]
select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;
[/showhide]
[showhide type=”script2″ more_text=”How to find active sessions in oracle database” less_text=”How to find active sessions in oracle database”]
set echo off set linesize 95 set head on set feedback on col sid head "Sid" form 9999 trunc col serial# form 99999 trunc head "Ser#" col username form a8 trunc col osuser form a7 trunc col machine form a20 trunc head "Client|Machine" col program form a15 trunc head "Client|Program" col login form a11 col "last call" form 9999999 trunc head "Last Call|In Secs" col status form a6 trunc select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser, substr(program||module,1,15) program,substr(machine,1,22) machine, to_char(logon_time,'ddMon hh24:mi') login, last_call_et "last call",status from v$session where status='ACTIVE' order by 1 /
[/showhide]
[showhide type=”script3″ more_text=”How to find sql text from a sid” less_text=”How to find sql text from a sid”]
col sql_text form a80 set lines 120 select sql_text from gv$sqltext where hash_value= (select sql_hash_value from gv$session where sid=&SID) order by piece /
[/showhide]
[showhide type=”script4″ more_text=”Realtime monitoring of sql query using v$sql_plan_monitor” less_text=”Realtime monitoring of sql query using v$sql_plan_monitor”]
SELECT sid, sql_id, status, plan_line_id, plan_operation || ' ' || plan_options operation, output_rows FROM v$sql_plan_monitor WHERE status not like '%DONE%' ORDER BY 1,4;
Output:
SID SQL_ID STATUS LINE OPERATION ROWS
---------- ------------- ---------- ----- ------------------------- ----------
82 00tr6c6tngp4x EXECUTING 0 INSERT STATEMENT 0
1 LOAD TABLE CONVENTIONAL 0
2 HASH UNIQUE 0
3 HASH JOIN ANTI 0
4 NESTED LOOPS ANTI 0
5 STATISTICS COLLECTOR 4293890583
6 HASH JOIN 4293890583
7 NESTED LOOPS 1
8 STATISTICS COLLECTOR 1565871
9 MERGE JOIN CARTESIAN 1565871
10 INDEX RANGE SCAN 5381
11 BUFFER SORT 1565871
12 INDEX RANGE SCAN 291
13 INDEX RANGE SCAN 0
14 INDEX FAST FULL SCAN 1860196
15 INDEX RANGE SCAN 69
16 INDEX RANGE SCAN 0
17 rows selected.
[/showhide]
[showhide type=”script5″ more_text=”How to find the wait events present in database” less_text=”How to find the wait events present in database”]
select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
a.event,a.p1,b.sql_hash_value
from v$session_wait a,V$session b
where b.sid=a.sid
and a.event not in('SQL*Net message from client','SQL*Net message to client',
'smon timer','pmon timer')
and username is not null
order by 6
/
[/showhide]
[showhide type=”script6″ more_text=”How to find the session using lot of undo” less_text=”How to find the session using lot of undo”]
select a.sid, a.serial#, a.username, b.used_urec used_undo_record, b.used_ublk used_undo_blocks from v$session a, v$transaction b where a.saddr=b.ses_addr ;
[/showhide]
[showhide type=”script7″ more_text=”How to find the locks present in oracle database” less_text=”How to find the locks present in oracle database”]
col session_id head 'Sid' form 9999 col object_name head "Table|Locked" form a30 col oracle_username head "Oracle|Username" form a10 truncate col os_user_name head "OS|Username" form a10 truncate col process head "Client|Process|ID" form 99999999 col mode_held form a15 select lo.session_id,lo.oracle_username,lo.os_user_name, lo.process,do.object_name, decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)', 3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive', to_char(lo.locked_mode)) mode_held from v$locked_object lo, dba_objects do where lo.object_id = do.object_id order by 1,5 /
[/showhide]
[showhide type=”script8″ more_text=”Find the sessions using lot of temp” less_text=”Find the sessions using lot of temp”]
SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, a.inst_id as Instance, a.sid||','||a.serial# AS sid_serial, NVL(a.username, '(oracle)') AS username, a.program, a.status, a.sql_id FROM gv$session a, gv$sort_usage b, gv$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr AND a.inst_id=b.inst_id AND a.inst_id=p.inst_id ORDER BY temp_size desc /
[/showhide]
[showhide type=”script9″ more_text=”Find the sessions generating lot of logs” less_text=”Find the sessions generating lot of logs”]
set lines 2000 set pages 1000 col sid for 99999 col name for a09 col username for a14 col PROGRAM for a21 col MODULE for a25 select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id from v$sesstat s join v$statname n on n.statistic# = s.statistic# join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc;
[/showhide]
[showhide type=”script10″ more_text=”How to get the size of an oracle database” less_text=”How to get the size of an oracle database”]
col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p /
[/showhide]
[showhide type=”script11″ more_text=”Find the archive lag between primary and standby database” less_text=”Find the archive lag between primary and standby database”]
select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
[/showhide]
[showhide type=”script12″ more_text=”check the status of different standby processes” less_text=”check the status of different standby processes”]
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
output:
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 ARCH CONNECTED 0 0 0 0 MRP0 WAIT_FOR_LOG 1 53056 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 53056 10935 2 RFS IDLE 0 0 0 0 9 rows selected.
[/showhide]
[showhide type=”script13″ more_text=”find the last sequence received and applied at standby database” less_text=”find the last sequence received and applied at standby database”]
SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
[/showhide]
[showhide type=”script14″ more_text=”Find the number of archives generated per hour” less_text=”Find the number of archives generated per hour”]
set lines 2000 SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM" FROM V$LOG_HISTORY GROUP BY TRUNC(FIRST_TIME) ORDER BY TRUNC(FIRST_TIME) DESC /
[/showhide]
[showhide type=”script15″ more_text=”query to check tablespace usage” less_text=”query to check tablespace usage”]
set feedback off
set pagesize 70;
set linesize 2000
set head on
COLUMN Tablespace format a25 heading 'Tablespace Name'
COLUMN autoextensible format a11 heading 'AutoExtend'
COLUMN files_in_tablespace format 999 heading 'Files'
COLUMN total_tablespace_space format 99999999 heading 'TotalSpace'
COLUMN total_used_space format 99999999 heading 'UsedSpace'
COLUMN total_tablespace_free_space format 99999999 heading 'FreeSpace'
COLUMN total_used_pct format 9999 heading '%Used'
COLUMN total_free_pct format 9999 heading '%Free'
COLUMN max_size_of_tablespace format 99999999 heading 'ExtendUpto'
COLUM total_auto_used_pct format 999.99 heading 'Max%Used'
COLUMN total_auto_free_pct format 999.99 heading 'Max%Free'
WITH tbs_auto AS
(SELECT DISTINCT tablespace_name, autoextensible
FROM dba_data_files
WHERE autoextensible = 'YES'),
files AS
(SELECT tablespace_name, COUNT (*) tbs_files,
SUM (BYTES/1024/1024) total_tbs_bytes
FROM dba_data_files
GROUP BY tablespace_name),
fragments AS
(SELECT tablespace_name, COUNT (*) tbs_fragments,
SUM (BYTES)/1024/1024 total_tbs_free_bytes,
MAX (BYTES)/1024/1024 max_free_chunk_bytes
FROM dba_free_space
GROUP BY tablespace_name),
AUTOEXTEND AS
(SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
FROM (SELECT tablespace_name, SUM (maxbytes)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION
SELECT tablespace_name, SUM (BYTES)/1024/1024 size_to_grow
FROM dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name)
SELECT c.instance_name,a.tablespace_name Tablespace,
CASE tbs_auto.autoextensible
WHEN 'YES'
THEN 'YES'
ELSE 'NO'
END AS autoextensible,
files.tbs_files files_in_tablespace,
files.total_tbs_bytes total_tablespace_space,
(files.total_tbs_bytes - fragments.total_tbs_free_bytes
) total_used_space,
fragments.total_tbs_free_bytes total_tablespace_free_space,
round(( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
/ files.total_tbs_bytes
)
* 100
)) total_used_pct,
round(((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
)) total_free_pct
FROM dba_tablespaces a,v$instance c , files, fragments, AUTOEXTEND, tbs_auto
WHERE a.tablespace_name = files.tablespace_name
AND a.tablespace_name = fragments.tablespace_name
AND a.tablespace_name = AUTOEXTEND.tablespace_name
AND a.tablespace_name = tbs_auto.tablespace_name(+)
order by total_free_pct;
[/showhide]
[showhide type=”script16″ more_text=”query to find undo tablespace usage” less_text=”query to find undo tablespace usage”]
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name from dba_data_files a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.contents = 'UNDO' group by b.tablespace_name) a, (select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB from DBA_UNDO_EXTENTS c where status <> 'EXPIRED' group by c.tablespace_name) b where a.tablespace_name = b.tablespace_name;
[/showhide]
[showhide type=”script17″ more_text=”query to find temp tablespace usage ” less_text=”query to find temp tablespace usage”]
select a.tablespace_name tablespace,
d.TEMP_TOTAL_MB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_MB;
[/showhide]
[showhide type=”script18″ more_text=”get DDL of all tablespaces in database ” less_text=”get DDL of all tablespaces in database”]
SQL>set heading off;
SQL>set echo off;
SQL>Set pages 999;
SQL>set long 90000;
SQL>spool ddl_list.sql
SQL>select dbms_metadata.get_ddl('TABLESPACE',tb.tablespace_name) from dba_tablespaces tb;
SQL>spool off
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
[/showhide]
[showhide type=”script20″ more_text=”get the DDL of privileges granted to a user ” less_text=”get the DDL of privileges granted to a user”]
set feedback off pages 0 long 900000 lines 20000 pagesize 20000 serveroutput on
accept USERNAME prompt "Enter username :"
--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username='&username'
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee)as script from DBA_SYS_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee)as script from DBA_ROLE_PRIVS where grantee='&username' and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee)as script from DBA_TAB_PRIVS where grantee='&username' and rownum=1;
[/showhide]
[showhide type=”script21″ more_text=”Find the blocking sessions in database” less_text=”Find the blocking sessions in database”]
SELECT s.inst_id, s.blocking_session, s.sid, s.serial#, s.seconds_in_wait FROM gv$session s WHERE blocking_session IS NOT NULL;
[/showhide]
[showhide type=”script22″ more_text=”Find redo log group details” less_text=”Find redo log group details”]
set echo off set feedback off set linesize 120 set pagesize 35 set trim on set trims on set lines 120 col group# format 999 col thread# format 999 col member format a70 wrap col status format a10 col archived format a10 col fsize format 999 heading "Size (MB)" select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize from v$log l, v$logfile f where f.group# = l.group# order by 1,2 /
[/showhide]
[showhide type=”script23″ more_text=”Find the asm disk details” less_text=”Find the asm disk details”]
set pagesize 2000 set lines 2000 set long 999 col path for a54 select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk;
[/showhide]
[showhide type=”script24″ more_text=”Find the asm diskgroup details” less_text=”Find the asm diskgroup details”]
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage
FROM v$asm_diskgroup;
[/showhide]
[showhide type=”script25″ more_text=”Get the bind values of a sql_id” less_text=”Get the bind values of a sql_id”]
SELECT sql_id, b. LAST_CAPTURED, t.sql_text sql_text, b.HASH_VALUE, b.name bind_name, b.value_string bind_value FROM gv$sql t JOIN gv$sql_bind_capture b using (sql_id) WHERE b.value_string is not null AND sql_id='&sqlid' /
[/showhide]
[showhide type=”script26″ more_text=”Find hidden parameters set in the database” less_text=”Find hidden parameters set in the database”]
Set lines 2000 col NAME for a45 col DESCRIPTION for a100 SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
[/showhide]
[showhide type=”script27″ more_text=”script to check oracle database character set ” less_text=”script to check oracle database character set “]
set lines 2000 select * from v$Nls_parameters;
[/showhide]
[showhide type=”script28″ more_text=”monitor rollback of transactions in database ” less_text=”monitor rollback of transactions in database “]
set lines 200 select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL,UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from v$fast_start_transactions;
[/showhide]
[showhide type=”script29″ more_text=”View ACLS details in database ” less_text=”View ACLS details in database”]
set lines 200 COL ACL_OWNER FOR A12 COL ACL FOR A67 COL HOST FOR A34 col PRINCIPAL for a20 col PRIVILEGE for a13 select ACL_OWNER,ACL,HOST,LOWER_PORT,UPPER_PORT FROM dba_network_acls; select ACL_OWNER,ACL,PRINCIPAL,PRIVILEGE from dba_network_acl_privileges;
[/showhide]
[showhide type=”script30″ more_text=”monitor parallel queries in oracle ” less_text=”monitor parallel queries in oracle — > “]
col username for a12 col "QC SID" for A6 col "SID" for A6 col "QC/Slave" for A8 col "Req. DOP" for 9999 col "Actual DOP" for 9999 col "Slaveset" for A8 col "Slave INST" for A9 col "QC INST" for A6 set pages 300 lines 300 col wait_event format a30 select decode(px.qcinst_id,NULL,username, ' - '||lower(substr(pp.SERVER_NAME, length(pp.SERVER_NAME)-4,4) ) )"Username", decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" , to_char( px.server_set) "SlaveSet", to_char(s.sid) "SID", to_char(px.inst_id) "Slave INST", decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE, case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event , decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID", to_char(px.qcinst_id) "QC INST", px.req_degree "Req. DOP", px.degree "Actual DOP" from gv$px_session px, gv$session s , gv$px_process pp, gv$session_wait sw where px.sid=s.sid (+) and px.serial#=s.serial#(+) and px.inst_id = s.inst_id(+) and px.sid = pp.sid (+) and px.serial#=pp.serial#(+) and sw.sid = s.sid and sw.inst_id = s.inst_id order by decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID, decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID /
[/showhide]
