Useful Scripts
To check INACTIVE sessions with HIGH DISK IO
select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) “Last SQL”
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;
select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) “Last SQL”
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;
Backup Progress during Incremental Backups with BCT enabled
select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = 'YES'
group by file#
order by file#;
select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = 'YES'
group by file#
order by file#;
Archivelog generation per hour in GB
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_GB
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B;
V$lock - query to find blockers/Lockconflicts - Oracle ######## if dba_blockers is not working ###########
##### Using V$lock ########################
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
####### mix of v$session #############
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
###############
SELECT vh.sid locking_sid,
vs.status status,
vs.program program_holding,
vw.sid waiter_sid,
vsw.program program_waiting
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
##### Using V$lock ########################
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2
####### mix of v$session #############
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
###############
SELECT vh.sid locking_sid,
vs.status status,
vs.program program_holding,
vw.sid waiter_sid,
vsw.program program_waiting
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
Get create user DDL with grants, privileges and roles rem script: user_cr_ddl.sql
rem Purpose: generate create user script with privs
rem
rem Usage: user_cr_ddl
rem
rem Note:
rem If ORA-31608 encountered, it means the user does not
rem have grants in that category. Edit the spooled script
rem as ncessary
rem
rem
SET LINESIZE 200
SET PAGESIZE 0 FEEDBACK off VERIFY off
-- SET TRIMSPOOL on
SET LONG 1000000
-- COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
COLUMN ddl FORMAT A4000
define username=&&1
spool &username._cr_ddl.sql
SELECT DBMS_METADATA.GET_DDL('USER', upper('&username') ) DDL FROM dual;
prompt -- Role
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper('&username')) DDL from dual
where exists ( select 1 from dba_role_privs where grantee=upper('&username') );
prompt -- Sys priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper('&username')) DDL FROM dual
where exists ( select 1 from dba_sys_privs where grantee=upper('&username') );
prompt -- Object priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper('&username')) DDL
FROM dual where exists ( select 1 from dba_tab_privs where grantee=upper('&username') );
prompt -- tablespace quota
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&username')) DDL from dual
where exists ( select 1 from dba_ts_quotas where username=upper('&username') );
spool off
exit
rem Purpose: generate create user script with privs
rem
rem Usage: user_cr_ddl
rem
rem Note:
rem If ORA-31608 encountered, it means the user does not
rem have grants in that category. Edit the spooled script
rem as ncessary
rem
rem
SET LINESIZE 200
SET PAGESIZE 0 FEEDBACK off VERIFY off
-- SET TRIMSPOOL on
SET LONG 1000000
-- COLUMN ddl_string FORMAT A100 WORD_WRAP
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
COLUMN ddl FORMAT A4000
define username=&&1
spool &username._cr_ddl.sql
SELECT DBMS_METADATA.GET_DDL('USER', upper('&username') ) DDL FROM dual;
prompt -- Role
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper('&username')) DDL from dual
where exists ( select 1 from dba_role_privs where grantee=upper('&username') );
prompt -- Sys priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper('&username')) DDL FROM dual
where exists ( select 1 from dba_sys_privs where grantee=upper('&username') );
prompt -- Object priv
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper('&username')) DDL
FROM dual where exists ( select 1 from dba_tab_privs where grantee=upper('&username') );
prompt -- tablespace quota
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA',upper('&username')) DDL from dual
where exists ( select 1 from dba_ts_quotas where username=upper('&username') );
spool off
exit
Query to find pid of lgwr
SQL> select pid, program from v$process where program like '%LGWR%';
----------------------------------------------------------------------------------------
SQL> SELECT p.spid, s.program
2 FROM v$session s, v$process p
3 WHERE s.paddr=p.addr
4 AND s.program LIKE '%LGWR%';
SPID PROGRAM
------------------------ ------------------------------------------------
26803 oracle (LGWR)
ps -ef | grep 26803 | grep -v grep
oracle 26803 1 0 Jan 14 ? 2:11 ora_lgwr_ORCL11GR2
SQL> select pid, program from v$process where program like '%LGWR%';
----------------------------------------------------------------------------------------
SQL> SELECT p.spid, s.program
2 FROM v$session s, v$process p
3 WHERE s.paddr=p.addr
4 AND s.program LIKE '%LGWR%';
SPID PROGRAM
------------------------ ------------------------------------------------
26803 oracle (LGWR)
ps -ef | grep 26803 | grep -v grep
oracle 26803 1 0 Jan 14 ? 2:11 ora_lgwr_ORCL11GR2
Import job Progress query
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
Check long running queries - check time remaining
SELECTopname,target,ROUND((sofar/totalwork),4)*100 Percentage_Complete,start_time,CEIL(time_remaining/60) Max_Time_Remaining_In_Min,FLOOR(elapsed_seconds/60) Time_Spent_In_MinFROM v$session_longopsWHERE sofar != totalwork;
SELECTopname,target,ROUND((sofar/totalwork),4)*100 Percentage_Complete,start_time,CEIL(time_remaining/60) Max_Time_Remaining_In_Min,FLOOR(elapsed_seconds/60) Time_Spent_In_MinFROM v$session_longopsWHERE sofar != totalwork;
To Check the sessions having the DISK I/o’s
prompt SESSIONS PERFORMING HIGH I/O > 50000
select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) "Last SQL"
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 10000
order by t.disk_reads desc;
prompt SESSIONS PERFORMING HIGH I/O > 50000
select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) "Last SQL"
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 10000
order by t.disk_reads desc;
To check INACTIVE sessions with HIGH DISK IO
select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;
select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) "Last SQL"
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;
Redo generated by current sessions
select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = ‘redo size’
and value > 0
and username is not null
order by value
/
select v$session.sid, username, value redo_size
from v$sesstat, v$statname, v$session
where v$sesstat.STATISTIC# = v$statname.STATISTIC#
and v$session.sid = v$sesstat.sid
and name = ‘redo size’
and value > 0
and username is not null
order by value
/
Oracle - Long running SQL
col long_opname head OPNAME for a40
col long_target head TARGET for a40
col long_units head UNITS for a10
prompt Show session long operations from v$session_longops for sid &1
select
sid,
serial#,
opname long_opname,
target long_target,
sofar,
totalwork,
units long_units,
time_remaining,
start_time,
elapsed_seconds
/*, target_desc, last_update_time, username, sql_address, sql_hash_value */
from
v$session_longops
where
sid in (&1)
and sofar != totalwork
/
col long_opname head OPNAME for a40
col long_target head TARGET for a40
col long_units head UNITS for a10
prompt Show session long operations from v$session_longops for sid &1
select
sid,
serial#,
opname long_opname,
target long_target,
sofar,
totalwork,
units long_units,
time_remaining,
start_time,
elapsed_seconds
/*, target_desc, last_update_time, username, sql_address, sql_hash_value */
from
v$session_longops
where
sid in (&1)
and sofar != totalwork
/
Tablespace utilization
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool healthcheck.lst
select tablespace_name, sum(bytes/1024/1024) as "FREE MB" from dba_free_space group by tablespace_name;
select tablespace_name, sum(bytes/1024/1024) as "USED MB" from dba_data_files group by tablespace_name;
spool off
set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool healthcheck.lst
select tablespace_name, sum(bytes/1024/1024) as "FREE MB" from dba_free_space group by tablespace_name;
select tablespace_name, sum(bytes/1024/1024) as "USED MB" from dba_data_files group by tablespace_name;
spool off
Long Running Operations:
SELECT osuser,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining
FROM v$session_longops sl
inner join v$session s ON sl.SID = s.SID AND sl.SERIAL# = s.SERIAL#
WHERE time_remaining > 0
Slow Running SQL statement:
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0
SELECT s.username,
sl.sid,
sq.executions,
sl.last_update_time,
sl.sql_id,
sl.sql_hash_value,
opname,
target,
elapsed_seconds,
time_remaining,
sq.sql_fulltext
FROM v$session_longops sl
INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
WHERE time_remaining > 0
RMAN fullbackup - shellscript #/usr/bin/sh
export ORACLE_SID=db01
export ORACLE_HOME=/home/oracle/product/10.2.0.1
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/product/10.2.0.1/bin:/home/oracle/bin
echo "ORACLE_SID -> ${ORACLE_SID}"
echo "ORACLE_HOME -> ${ORACLE_HOME}"
#-----------------------------------------------
dt=`date +%Y%m%d`
SUBJECT="db01 rmanbackup errors please investigate "
SUBJECTP="db01 backup DONE"
EMAIL_ADDRESS="[email protected]"
BODY="check the log files and space utilization "
thresold=90
verror=""
outofspace=" The disks is out of space "
#----------------------------------------------
$ORACLE_HOME/bin/rman nocatalog log = /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt<connect target /
configure controlfile autobackup on;
configure device type disk parallelism 4;
configure retention policy to redundancy 7;
configure channel 1 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman1_%U.bk';
configure channel 2 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman2_%U.bk';
configure channel 3 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman3_%U.bk';
configure channel 4 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman4_%U.bk';
backup database plus archivelog delete all input;
crosscheck backup;
delete noprompt obsolete;
exit
verror= `cat /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt |grep -i error | wc -l`
# the above should be a log file > in crontab
if [ $verror -gt 0 ]; then
echo "$BODY" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS"
echo $verror
fi
exit;
export ORACLE_SID=db01
export ORACLE_HOME=/home/oracle/product/10.2.0.1
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/home/oracle/product/10.2.0.1/bin:/home/oracle/bin
echo "ORACLE_SID -> ${ORACLE_SID}"
echo "ORACLE_HOME -> ${ORACLE_HOME}"
#-----------------------------------------------
dt=`date +%Y%m%d`
SUBJECT="db01 rmanbackup errors please investigate "
SUBJECTP="db01 backup DONE"
EMAIL_ADDRESS="[email protected]"
BODY="check the log files and space utilization "
thresold=90
verror=""
outofspace=" The disks is out of space "
#----------------------------------------------
$ORACLE_HOME/bin/rman nocatalog log = /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt<connect target /
configure controlfile autobackup on;
configure device type disk parallelism 4;
configure retention policy to redundancy 7;
configure channel 1 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman1_%U.bk';
configure channel 2 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman2_%U.bk';
configure channel 3 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman3_%U.bk';
configure channel 4 device type disk format '/u01/app/oracle/db01/db01/rman_backup_loc/rman4_%U.bk';
backup database plus archivelog delete all input;
crosscheck backup;
delete noprompt obsolete;
exit
verror= `cat /u01/app/oracle/db01/db01/rman_backup_loc/backup_script/db01log$dt |grep -i error | wc -l`
# the above should be a log file > in crontab
if [ $verror -gt 0 ]; then
echo "$BODY" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS"
echo $verror
fi
exit;
Estimate Tablespace Growth
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
Get Database Parameters even Hidden One
You can use this Scripts to check the init.ora in Oracle but not from v$parameter this Time
First Query : (display all init.ora parameter including the hidden parameters):
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;
Second Query (list of parameter which are not default):
SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT * FROM (SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm) WHERE d='F';
You can use this Scripts to check the init.ora in Oracle but not from v$parameter this Time
First Query : (display all init.ora parameter including the hidden parameters):
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;
Second Query (list of parameter which are not default):
SET linesize 235 pagesize 200
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90
SELECT * FROM (SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm) WHERE d='F';
Identify Locks on the Table
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;
List tables with high watermark:
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
and a.table_name like upper('&table_name')||'%'
order by 1,2
/
set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select
a.owner,
a.table_name,
b.blocks alcblks,
a.blocks usdblks,
(b.blocks-a.empty_blocks-1) hgwtr
from
dba_tables a,
dba_segments b
where
a.table_name=b.segment_name
and a.owner=b.owner
and a.owner not in('SYS','SYSTEM')
and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'
and a.table_name like upper('&table_name')||'%'
order by 1,2
/
RMAN Backup completed in last 24 hours:
col type format a4
col handle format a35 trunc
col file# format 9999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/
col type format a4
col handle format a35 trunc
col file# format 9999
col duration format a9
select decode(BACKUP_TYPE, 'L', 'ARCH', 'D', 'DB', 'I', 'INC',
'Unknown type='||BACKUP_TYPE) TYPE,
to_char(a.start_time, 'DDMON HH24:MI') start_time,
to_char(a.elapsed_seconds/60, '99.9')||' Min' DURATION,
substr(handle, -35) handle,
nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks
from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE b,
SYS.V_$BACKUP_DATAFILE d, SYS.V_$BACKUP_REDOLOG l
where a.start_time between sysdate-1 and sysdate
and a.SET_STAMP = b.SET_STAMP
and a.SET_STAMP = d.SET_STAMP(+)
and a.SET_STAMP = l.SET_STAMP(+)
order by start_time, file#
/
To check given query performance for any specific table:-
select a. snap_id, begin_interval_time,c.sql_text,executions_delta,(elapsed_time_delta /executions_delta )/1000000,plan_hash_value,(cpu_time_delta /executions_delta )/1000000,rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000,(fetches_delta/ executions_delta),(disk_reads_delta/ executions_delta),(buffer_gets_delta/ executions_delta)from dba_hist_sqlstat a, dba_hist_snapshot b ,dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc
select a. snap_id, begin_interval_time,c.sql_text,executions_delta,(elapsed_time_delta /executions_delta )/1000000,plan_hash_value,(cpu_time_delta /executions_delta )/1000000,rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000,(fetches_delta/ executions_delta),(disk_reads_delta/ executions_delta),(buffer_gets_delta/ executions_delta)from dba_hist_sqlstat a, dba_hist_snapshot b ,dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc
query to check what query running into specific user at given time SET HEADING ONSET LINESIZE 300SET PAGESIZE 60
COLUMN Sample_Time FOR A12COLUMN username FOR A20COLUMN sql_text FOR A40COLUMN program FOR A25COLUMN module FOR A25
SELECT to_char(sample_time,'DD Mon HH24:MI') as Sample_Time, u.username, h.program, h.module, s.sql_textFROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT sWHERE sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM' AND h.user_id=u.user_id AND h.sql_id = s.sql_iD AND u.username='SCOTT'order by 1;
COLUMN Sample_Time FOR A12COLUMN username FOR A20COLUMN sql_text FOR A40COLUMN program FOR A25COLUMN module FOR A25
SELECT to_char(sample_time,'DD Mon HH24:MI') as Sample_Time, u.username, h.program, h.module, s.sql_textFROM DBA_HIST_ACTIVE_SESS_HISTORY h, DBA_USERS u, DBA_HIST_SQLTEXT sWHERE sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM' AND h.user_id=u.user_id AND h.sql_id = s.sql_iD AND u.username='SCOTT'order by 1;
Top 10 memory consuming processes:
#prstat -s size -n 10 (to determine top 10 memory consuming processes)
#prstat -s size -n 10 (to determine top 10 memory consuming processes)
Top 10 cpu consuming processes:
#prstat -s cpu -n 10 (to determine top 10 cpu consuming processes)
#prstat -s cpu -n 10 (to determine top 10 cpu consuming processes)
Identify database SID based on OS Process ID col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;