http://www.oracle-class.com/
rmanbackup.com
orafaq.com
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTOC44/Default.aspx
How to trace :
================
1)
set serveroutput on size 1000000 for wra
declare
paramname varchar2(256);
integerval binary_integer;
stringval varchar2(256);
paramtype binary_integer;
begin
paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
if paramtype=1 then
dbms_output.put_line(stringval);
else
dbms_output.put_line(integerval);
end if;
end;
/
2)
select name,value
from v$parameter
where name='user_dump_dest';
3)
set lines 1000 pages 1000
select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;
SYSTEM : SID: 61 SERIAL : 325
4)
exec dbms_system.set_sql_trace_in_session(61,325,true);
exec dbms_system.set_sql_trace_in_session(61,325,false);
select * from v$session_longops where time_remaining>0 order by time_remaining desc
select event,seconds_in_wait,status from v$session where program like '%DW%';
The followng trace files are created :
-rw-r----- 1 oracle oinstall 1018 Jan 24 17:00 RAC2_j001_12799.trc
-rw-r----- 1 oracle oinstall 1946 Jan 24 17:03 RAC2_lmd0_4111.trc
-rw-r----- 1 oracle oinstall 3053 Jan 24 17:04 RAC2_pz97_12966.trc
-rw-r----- 1 oracle oinstall 4801 Jan 24 17:07 RAC2_dw00_12972.trc
-rw-r----- 1 oracle oinstall 12187 Jan 24 17:07 RAC2_pz98_12958.trc
-rw-r----- 1 oracle oinstall 5049 Jan 24 17:07 RAC2_dm00_12893.trc
-rw-r----- 1 oracle oinstall 81029 Jan 24 17:07 RAC2_pz99_12750.trc
-rw-r----- 1 oracle oinstall 649892 Jan 24 17:07 RAC2_ora_12789.trc
But in above files, only "RAC2_ora_12789.trc" is increasing , so that I update this file only .
DECLARE
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.log',
directory => 'DMPDIR',
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.dmp',
directory => 'DMPDIR',
filetype => 1);
-- Dbms_DataPump.Set_Parameter(handle => hand,
-- name => 'ESTIMATE',
-- value => 'STATISTICS');
Dbms_DataPump.Start_Job(hand);
END;
/
By DBMS_MONITOR :
=================
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN 67,
serial_num IN 413,
waits IN TRUE,
binds IN FALSE,
plan_stat IN NULL);
EXECUTE dbms_monitor.session_trace_enable (2827, 64);
EXECUTE dbms_monitor.session_trace_disable (2827, 64);
Can one monitor how fast a table is imported?
==============================================
If you need to monitor how fast rows are imported from a running import job, try one of the following methods:
Method 1:
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;
For this to work one needs to be on Oracle 7.3 or higher (7.2 might also be OK).
If the import has more than one table, this statement will only show information about the current
table being imported.
Sunday, February 12, 2012
Sunday, January 22, 2012
All Oracle Command !
mystat :
========
select distinct * from v$sysstat order by class
select * from v$mystat
All Alerts :
=============
>dba_outstanding_alerts : DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be
outstanding.
>dba_alert_history : DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer
outstanding.
>v$alert_types : V$ALERT_TYPES displays information about server alert types.
>user_advisor_findings
dba_advisor_objects
dba_advisor_actions
dba_advisor_rationale
----------------------------------------------------------------------
select username,sid,serial#,logon_time,status from v$session where sid=375
select * from dba_outstanding_alerts order by time_suggested desc
select * from dba_alert_history order by time_suggested desc
select * from user_advisor_findings
select * from dba_advisor_objects where owner='ABABIL'
select * from dba_advisor_actions where owner='ABABIL'
select * from dba_advisor_rationale where owner='ABABIL'
-------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
|session_wait shows what is each session waiting at at the instance you issue select * from v$session_wait
|
|session_event would be the sum of session_wait in each session and each event
|
|system_event would be the sum of all waits in session_event
|
|normally I use session_wait to check for enqueue or those transactions that is taking long time (and see what they are doing/waiting)
|
|I query session_event when I am tracing an user and see how many waits, wait time etc his session has been facing
|------------------------------------------------------------------------------------------------------------
select event,count(*) from v$session_wait group by event
select event,count(*) from v$session_event group by event
select sid,p1,p2,p3 from v$session_wait where event='latch: cache buffers chains'
select * from v$latchname where latch#=122
select 'kill -9 '||spid from v$process where addr in(
select paddr--sid,username,program,sql_address,logon_time,status,machine
from v$session where event='latch: cache buffers chains' and program like '%java%'
and username is not null and logon_time
select sql_address,count(*) from v$session where event='latch: cache buffers chains' group by sql_address
select sql_fulltext,address,sql_id from v$sqlarea where sql_id ='1grbab9syxsx9'
---------------------------------------------------------------------------------
SELECT /*+ FIRST_rows */ * FROM VW_DAILY_VOUCHER
WHERE GLOBALTXNNO IN (
SELECT DISTINCT GLOBALTXNNO FROM VW_DAILY_VOUCHER WHERE TXNDATE = : P_TXNDATE AND GLOBALTXNNO <> 0
AND NVL ( BATCHNO , '%' ) LIKE NVL ( : P_BATCHNO , '%' ) AND NVL ( GLCODE , '%' ) LIKE NVL ( : P_GLCODE , '%' )
AND NVL ( TO_CHAR ( GLOBALTXNNO ) , '%' ) LIKE NVL ( : P_GLOBALTXNNO , '%' )
AND NVL ( ENTRYUSER , '%' ) LIKE NVL ( : P_ENTRYUSER , '%' ) AND NVL ( POSTINGUSER , '%' ) LIKE NVL ( : P_VERIFYUSER , '%' )
AND NVL ( OWNERBRANCHID , '%' ) LIKE NVL ( : P_OWNERBRANCH , '%' )
AND NVL ( INITIATORBRANCHID , '%' ) LIKE NVL ( : P_INITBRANCH , '%' ) )
ORDER BY 3 ASC,19 ASC,20 ASC,16 ASC,17 ASC,1 ASC,2 ASC,18 ASC , to_number ( TRACERNO ) , 17 , 3 , 4
select sum(pga_alloc_mem) from v$process
SELECT vv.TXNDATE, vv.VALUEDATE, vv.GLOBALTXNNO, vv.TRACERN
O, vv.GLCODE, vv.GLTITTLE, vv.PARTICULARS, --vv.GLDT
CLIENTACCNO, vv.GLCURRENCY, --vv.BATCHNO, vv.LCYDEBIT,
vv.LCYCREDIT, vv.OWNERBRANCHID, vv.ENTRYUSER FROM VW_
select * from user_views where view_name like 'VV%'
select sid,username,program,sql_address,logon_time,event from v$session where status='ACTIVE' order by logon_time desc
select sql_text from v$sqlarea where address='070000058D49EE90'
select sql_address,prev_sql_addr from v$session where program like '%toad%'
select * from user_objects order by last_ddl_time desc
select event,count(*) from v$session_wait group by event
--CPU TIme :
===========
SELECT
SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL
AND S.SQL_ID = T.SQL_ID
AND SST.SID = S.SID
AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
ORDER BY 1 DESC, S.SID,T.PIECE;
==CPU TIME 3:
==============
a) ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
b) SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address
==CPU TIME 3:
===============
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
order by cpu_time desc
--Longes Time Query :
========================
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'TOAD%' order by elapsed_time desc
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'frmweb%' order by elapsed_time desc
select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
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
--Session Query :
===============
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
Oracle with OS user :
=====================
select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
and status='ACTIVE'
order by 6 asc, 3 desc, 4 desc
Lock Object :
===============
--Find Database Lock:
====================
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
--Details :
===============
SELECT
DISTINCT BLOCKER,
SID_SERIAL,
COUNT(*)
FROM
(
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial_V
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2
)
GROUP BY BLOCKER, SID_SERIAL
ORDER BY COUNT(*) DESC
select sql_hash_value from v$session where sid = &sid
select sql_Text from v$sql where hash_value = &sql_hash_value
SELECT * FROM V$OPEN_CURSOR WHERE SID = 767
--How to Get Detail of Locks with Object Locked:
==============================================
SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
--Find Last SQL query :
========================
select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/
--cpu load :
==========
select * from v$osstat
select * from v$sysmetric_history
--io/cpu usage query :
========================
select
sid,
username, terminal,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username, b.terminal terminal,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name) , b.terminal),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
4 desc
select * from v$filestat
--Find SPID:
=============
select a.username,a.sid,b.spid from v$session a, v$process b where b.spid = '1638634'
select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#
select s.username,p.spid from v$session s, v$process p
where s.paddr=p.addr AND p.background is null;
Tablespace Free Space :
=======================
SELECT a.tablespace_name "TableSpace",
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name
UNION
SELECT 'XXXTOTAL:',
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
-----To check imp0rt performence
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;
logical/physical read :
=======================
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('session logical reads','physical reads',
'physical reads direct','physical reads direct (lob)');
To check import performence :
==============================
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;
To find Longops :
=================
select * from v$session_longops where time_remaining>0 order by time_remaining desc
select username,sid,serial#,logon_time,status,machine,sql_id,prev_sql_id from v$session
select * from v$sqlarea where sql_id ='8wk7fbjqgf1ap'
FRA space :
============
SELECT space_limit/1024/1024/1024 "TOTAL_SPACE_GB",space_used/1024/1024/1024 "SPACE_USED_GB" FROM V$RECOVERY_FILE_DEST;
select * from v$recovery_file_dest
select * from v$flash_recovery_area_usage
Archive History :
=================
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
count(*) Total
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON') order by 1 desc
Logminer :
==========
select * from V$LOGMNR_CONTENTS
Analyze Table :
===============
select * from table(dbms_xplan.display_cursor('4u1s5ns3nuwbz',0))
From Banaful :
==============
select * from v$session_wait
select sid,p1,p2,p3,p2text from v$session_wait where event like 'SQL*Net%'
select segment_name from dba_extents where file_id=1 and (:p2 value) between block_id and block_id+1
select count(*) from obj
select count(*) from sys.obj$
PGA MEMORY USED :
=================
The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:
SQL> select sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
Which process is requesting too much memory? :
===============================================
Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases,
the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to
find out where and why the memory is allocated. You can use the following query to find out oracle's idea of
PGA and UGA size for the oracle processes.
select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc; This query will show the most hungry process last in the list.
select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = 1024 ;
Resource Plan :
================
alter system set RESOURCE_MANAGER_PLAN='system_plan'
To check : select * from V$RSRC_PLAN
To set null : alter system set RESOURCE_MANAGER_PLAN=''
OS SPID with SQL_TEXT :
========================
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid in (1323346)
AND b.addr = a.paddr
AND a.sql_address = c.address (+)
ORDER BY a.sid, c.piece;
[oracle@apps1 ~]$ rwdiag.sh -findall
Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021
(1) Name = rep_svr4 : Type = server : Host = apps1.aibl.com
(2) Name = rep_svr3 : Type = server : Host = apps1.aibl.com
(3) Name = rep_svr2 : Type = server : Host = apps1.aibl.com
(4) Name = rep_svr1 : Type = server : Host = apps1.aibl.com
(5) Name = rep_apps1_as_middle_home : Type = server : Host = apps1.aibl.com
[oracle@apps1 ~]$
opmnctl startproc ias-component=LogLoader
========
select distinct * from v$sysstat order by class
select * from v$mystat
All Alerts :
=============
>dba_outstanding_alerts : DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be
outstanding.
>dba_alert_history : DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer
outstanding.
>v$alert_types : V$ALERT_TYPES displays information about server alert types.
>user_advisor_findings
dba_advisor_objects
dba_advisor_actions
dba_advisor_rationale
----------------------------------------------------------------------
select username,sid,serial#,logon_time,status from v$session where sid=375
select * from dba_outstanding_alerts order by time_suggested desc
select * from dba_alert_history order by time_suggested desc
select * from user_advisor_findings
select * from dba_advisor_objects where owner='ABABIL'
select * from dba_advisor_actions where owner='ABABIL'
select * from dba_advisor_rationale where owner='ABABIL'
-------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
|session_wait shows what is each session waiting at at the instance you issue select * from v$session_wait
|
|session_event would be the sum of session_wait in each session and each event
|
|system_event would be the sum of all waits in session_event
|
|normally I use session_wait to check for enqueue or those transactions that is taking long time (and see what they are doing/waiting)
|
|I query session_event when I am tracing an user and see how many waits, wait time etc his session has been facing
|------------------------------------------------------------------------------------------------------------
select event,count(*) from v$session_wait group by event
select event,count(*) from v$session_event group by event
select sid,p1,p2,p3 from v$session_wait where event='latch: cache buffers chains'
select * from v$latchname where latch#=122
select 'kill -9 '||spid from v$process where addr in(
select paddr--sid,username,program,sql_address,logon_time,status,machine
from v$session where event='latch: cache buffers chains' and program like '%java%'
and username is not null and logon_time
select sql_address,count(*) from v$session where event='latch: cache buffers chains' group by sql_address
select sql_fulltext,address,sql_id from v$sqlarea where sql_id ='1grbab9syxsx9'
---------------------------------------------------------------------------------
SELECT /*+ FIRST_rows */ * FROM VW_DAILY_VOUCHER
WHERE GLOBALTXNNO IN (
SELECT DISTINCT GLOBALTXNNO FROM VW_DAILY_VOUCHER WHERE TXNDATE = : P_TXNDATE AND GLOBALTXNNO <> 0
AND NVL ( BATCHNO , '%' ) LIKE NVL ( : P_BATCHNO , '%' ) AND NVL ( GLCODE , '%' ) LIKE NVL ( : P_GLCODE , '%' )
AND NVL ( TO_CHAR ( GLOBALTXNNO ) , '%' ) LIKE NVL ( : P_GLOBALTXNNO , '%' )
AND NVL ( ENTRYUSER , '%' ) LIKE NVL ( : P_ENTRYUSER , '%' ) AND NVL ( POSTINGUSER , '%' ) LIKE NVL ( : P_VERIFYUSER , '%' )
AND NVL ( OWNERBRANCHID , '%' ) LIKE NVL ( : P_OWNERBRANCH , '%' )
AND NVL ( INITIATORBRANCHID , '%' ) LIKE NVL ( : P_INITBRANCH , '%' ) )
ORDER BY 3 ASC,19 ASC,20 ASC,16 ASC,17 ASC,1 ASC,2 ASC,18 ASC , to_number ( TRACERNO ) , 17 , 3 , 4
select sum(pga_alloc_mem) from v$process
SELECT vv.TXNDATE, vv.VALUEDATE, vv.GLOBALTXNNO, vv.TRACERN
O, vv.GLCODE, vv.GLTITTLE, vv.PARTICULARS, --vv.GLDT
CLIENTACCNO, vv.GLCURRENCY, --vv.BATCHNO, vv.LCYDEBIT,
vv.LCYCREDIT, vv.OWNERBRANCHID, vv.ENTRYUSER FROM VW_
select * from user_views where view_name like 'VV%'
select sid,username,program,sql_address,logon_time,event from v$session where status='ACTIVE' order by logon_time desc
select sql_text from v$sqlarea where address='070000058D49EE90'
select sql_address,prev_sql_addr from v$session where program like '%toad%'
select * from user_objects order by last_ddl_time desc
select event,count(*) from v$session_wait group by event
--CPU TIme :
===========
SELECT
SST.VALUE CPU, S.USERNAME, S.SID, S.SERIAL#, SQL_TEXT
FROM
V$SESSION S,
V$SQLTEXT_WITH_NEWLINES T,
V$SESSTAT SST
WHERE S.SQL_ID IS NOT NULL
AND S.SQL_ID = T.SQL_ID
AND SST.SID = S.SID
AND SST.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME SN WHERE SN.NAME = 'CPU used by this session')
ORDER BY 1 DESC, S.SID,T.PIECE;
==CPU TIME 3:
==============
a) ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
b) SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address
==CPU TIME 3:
===============
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
order by cpu_time desc
--Longes Time Query :
========================
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'TOAD%' order by elapsed_time desc
select sql_text,elapsed_time,sql_id,hash_value,plan_hash_value,program_id,module,action
from v$sql where module like 'frmweb%' order by elapsed_time desc
select sesion.sid,
sesion.serial#,
sesion.username,
sesion.sql_id,
sesion.sql_child_number,
optimizer_mode,
hash_value,
address,
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
--Session Query :
===============
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
SELECT p.spid,
s.process,
s.status,
s.machine,
s.username,
TO_CHAR (s.logon_time, 'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et / 60 TIME_IN_MIN,
s.action,
s.module,
s.program program,
s.sid sid,
s.serial# serial,
s.osuser,
'alter system kill session '''
|| s.sid
|| ','
|| s.serial#
|| ''''
sql_string
FROM V$SESSION s,V$PROCESS p
WHERE s.paddr = p.addr
AND s.username IS NOT NULL
AND UPPER(s.OSUSER) = 'ORACLE'
AND (UPPER(s.PROGRAM) LIKE 'FRMWEB%' OR UPPER(s.PROGRAM) LIKE 'JAVA%'
)
AND s.status = 'INACTIVE'
--and (Last_Call_ET/60)>(select TO_NUMBER(APCVALUE) from APPCONFIGURATIONwhere TRIM(UPPER(APCITEM))='APP_IDLE_TIME_IN_MINUTES')
ORDER BY p.spid
Oracle with OS user :
=====================
select s.username "Oracle User",s.osuser "OS User",i.consistent_gets "Consistent Gets",
i.physical_reads "Physical Reads",s.status "Status",s.sid "SID",s.serial# "Serial#",
s.machine "Machine",s.program "Program",to_char(logon_time, 'DD/MM/YYYY HH24:MI:SS') "Logon Time",
w.seconds_in_wait "Idle Time", P.SPID "PROC",
name "Stat CPU", value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and 'SQL*Net message from client' = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like '%cpu%'
and t.SID = s.sid
and status='ACTIVE'
order by 6 asc, 3 desc, 4 desc
Lock Object :
===============
--Find Database Lock:
====================
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
--Details :
===============
SELECT
DISTINCT BLOCKER,
SID_SERIAL,
COUNT(*)
FROM
(
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' || (select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' || (select serial# from v$session where sid=b.sid) sid_serial_V
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2
)
GROUP BY BLOCKER, SID_SERIAL
ORDER BY COUNT(*) DESC
select sql_hash_value from v$session where sid = &sid
select sql_Text from v$sql where hash_value = &sql_hash_value
SELECT * FROM V$OPEN_CURSOR WHERE SID = 767
--How to Get Detail of Locks with Object Locked:
==============================================
SELECT VLO.OS_USER_NAME "OS USERNAME", VLO.ORACLE_USERNAME "DB USER",
VP.SPID "SPID", AO.OWNER "OWNER", AO.OBJECT_NAME "OBJECT LOCKED",AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, 'NO LOCK',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCL',
6, 'EXCLUSIVE',
NULL
) "MODE OF LOCK",
VS.STATUS "CURRENT STATUS"
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> 'KILLED'
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
--Find Last SQL query :
========================
select S.USERNAME||'('||s.sid||')-'||s.osuser UNAM
-- ,s.program||'-'||s.terminal||'('||s.machine||')' PROG
,s.sid||'/'||s.serial# sid
,s.status "Status",p.spid
,sql_text sqltext
from v$sqltext_with_newlines t,V$SESSION s , v$process p
where t.address =s.sql_address
and p.addr=s.paddr(+)
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/
--cpu load :
==========
select * from v$osstat
select * from v$sysmetric_history
--io/cpu usage query :
========================
select
sid,
username, terminal,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username, b.terminal terminal,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name) , b.terminal),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
4 desc
select * from v$filestat
--Find SPID:
=============
select a.username,a.sid,b.spid from v$session a, v$process b where b.spid = '1638634'
select a.username,a.sid,b.spid from v$session a, v$process b where a.serial# = b.serial#
select s.username,p.spid from v$session s, v$process p
where s.paddr=p.addr AND p.background is null;
Tablespace Free Space :
=======================
SELECT a.tablespace_name "TableSpace",
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name
UNION
SELECT 'XXXTOTAL:',
ROUND(SUM(a.Ebytes),3) "Ext. Space(GB)",
ROUND(SUM(a.bytes),3) "Alo. Space(GB)",
(ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3)) "Used Space(GB)",
ROUND(SUM(a.Ebytes),3) - ((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "Free Space(GB)",
ROUND(SUM(a.bytes),3)-((ROUND(SUM(a.bytes),3) - ROUND(SUM(c.bytes),3))) "ABs Free Space(GB)"
FROM (SELECT tablespace_name tablespace_name,SUM(DECODE(maxbytes,0,bytes,maxbytes)/1024/1024/1024) Ebytes,
SUM((bytes)/1024/1024/1024) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name ) a,
(SELECT tablespace_name tablespace_name, SUM(bytes/1024/1024/1024) bytes
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) c
WHERE a.tablespace_name = c.tablespace_name
-----To check imp0rt performence
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;
logical/physical read :
=======================
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('session logical reads','physical reads',
'physical reads direct','physical reads direct (lob)');
To check import performence :
==============================
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;
To find Longops :
=================
select * from v$session_longops where time_remaining>0 order by time_remaining desc
select username,sid,serial#,logon_time,status,machine,sql_id,prev_sql_id from v$session
select * from v$sqlarea where sql_id ='8wk7fbjqgf1ap'
FRA space :
============
SELECT space_limit/1024/1024/1024 "TOTAL_SPACE_GB",space_used/1024/1024/1024 "SPACE_USED_GB" FROM V$RECOVERY_FILE_DEST;
select * from v$recovery_file_dest
select * from v$flash_recovery_area_usage
Archive History :
=================
select to_char(first_time,'DD/MON') day,
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'000')"01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'000')"02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'000')"03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'000')"04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'000')"05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'000')"06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'000')"07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'000')"08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'000')"09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'000')"10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'000')"11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'000')"12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'000')"13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'000')"14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'000')"15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'000')"16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'000')"17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'000')"18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'000')"19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'000')"20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'000')"21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'000')"22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'000')"23",
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'000') "00",
count(*) Total
from v$log_history
WHERE TRUNC(FIRST_TIME) > TRUNC(SYSDATE) - 7
group by to_char(first_time,'DD/MON') order by 1 desc
Logminer :
==========
select * from V$LOGMNR_CONTENTS
Analyze Table :
===============
select * from table(dbms_xplan.display_cursor('4u1s5ns3nuwbz',0))
From Banaful :
==============
select * from v$session_wait
select sid,p1,p2,p3,p2text from v$session_wait where event like 'SQL*Net%'
select segment_name from dba_extents where file_id=1 and (:p2 value) between block_id and block_id+1
select count(*) from obj
select count(*) from sys.obj$
PGA MEMORY USED :
=================
The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:
SQL> select sum(value)/1024/1024 Mb
from
v$sesstat s, v$statname n
where
n.STATISTIC# = s.STATISTIC# and
name = 'session pga memory';
Which process is requesting too much memory? :
===============================================
Some operations will require lots of process memory like huge PL/SQL tables or big sort operations. In these cases,
the processes will run for a certain period of time before getting the ora-4030 error, hopefully allowing us to
find out where and why the memory is allocated. You can use the following query to find out oracle's idea of
PGA and UGA size for the oracle processes.
select
sid,name,value
from
v$statname n,v$sesstat s
where
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 asc; This query will show the most hungry process last in the list.
select sql_text from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = 1024 ;
Resource Plan :
================
alter system set RESOURCE_MANAGER_PLAN='system_plan'
To check : select * from V$RSRC_PLAN
To set null : alter system set RESOURCE_MANAGER_PLAN=''
OS SPID with SQL_TEXT :
========================
SELECT a.username,
a.machine,
a.program,
a.sid,
a.serial#,
a.status,
c.piece,
c.sql_text
FROM v$session a,
v$process b,
v$sqltext c
WHERE b.spid in (1323346)
AND b.addr = a.paddr
AND a.sql_address = c.address (+)
ORDER BY a.sid, c.piece;
[oracle@apps1 ~]$ rwdiag.sh -findall
Broadcast mechanism used to locate servers
------------------------------------------
Channel address = 228.5.6.7
Channel port = 14021
(1) Name = rep_svr4 : Type = server : Host = apps1.aibl.com
(2) Name = rep_svr3 : Type = server : Host = apps1.aibl.com
(3) Name = rep_svr2 : Type = server : Host = apps1.aibl.com
(4) Name = rep_svr1 : Type = server : Host = apps1.aibl.com
(5) Name = rep_apps1_as_middle_home : Type = server : Host = apps1.aibl.com
[oracle@apps1 ~]$
opmnctl startproc ias-component=LogLoader
Friday, January 20, 2012
Special Query
How to implement Sleep Function in PL/SQL ?
DECLARE
Found BOOLEAN := FALSE;
Count NUMBER := 0;
BEGIN
WHILE (NOT Found AND NOT (Count > 10)) LOOP
BEGIN
-- Try to get free slot, if OK, set Found = TRUE
-- else EXCEPTION will automatically fire.
(Insert Code here)
Found := TRUE;
EXCEPTION
WHEN OTHERS THEN
Found := FALSE;
DBMS_LOCK.SLEEP(10);
Count := Count + 1;
END;
END LOOP;
END;
/
Viewing The SQL Statements of the CPU-Intensive Oracle Processes
First get the top 10 CPU-intensive Oracle processes on the operating system with
the first column giving the %CPU used,
the second column unix PID, the third column USER , the fourth column TERMINAL,
and the last column Unix PROCESS (works only for UNIX).
ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
Now you can specify the found PID in the following SQL-Statement:
column username format a9
column sql_text format a70
SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address
/
Tracking the progress of a long running statement
Sometimes you run an INSERT or DELETE statement that takes a long time to complete. You have wondered how many rows
have already been inserted or deleted so that you can decide whether or not to abort the statement. Is there a way
to display how many rows have been deleted while the statement is occurring ?
You can query the V$SESSION_LONGOPS table to track the progress of the statement.
Example: Starting the following long running INSERT
INSERT INTO bigemp SELECT * FROM bigemp;
Check the progress:
SELECT sofar,totalwork,time_remaining FROM v$session_longops WHERE sid = 55 and time_remaining > 0;
SOFAR TOTALWORK TIME_REMAINING
---------- ---------- --------------
8448 11057 20
8832 11057 17
9024 11057 16
9184 11057 14
9536 11057 12
9646 11057 11
9920 11057 9
10421 11057 5
10529 11057 4
10814 11057 2
Script to see redundant indexes, where all leading columns are duplicated
select a.owner, b.owner, a.column_name, a.table_name, a.index_name, a.column_position
from
all_ind_columns a, all_ind_columns b
where
a.index_owner = b.index_owner and
a.column_name = b.column_name and
a.table_name = b.table_name and
a.column_position = b.column_position and
a.index_name <> b.index_name
ORDER BY a.index_name,a.column_position
/
How to create a read-only table ?
CREATE TRIGGER tab_readonly_emp BEFORE DELETE OR INSERT OR UPDATE
ON employees
BEGIN
RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.');
END;
RUN SQL command from UNIX shell
The following example shows how to run SQL statement in a single UNIX command
$ echo "select count(*) from tab;" | sqlplus -s scott/tiger
Another method is to store the SQL command is a file
$ sqlplus -s scott/tiger < get_count.sql
COUNT(*)
----------
36
$
DECLARE
Found BOOLEAN := FALSE;
Count NUMBER := 0;
BEGIN
WHILE (NOT Found AND NOT (Count > 10)) LOOP
BEGIN
-- Try to get free slot, if OK, set Found = TRUE
-- else EXCEPTION will automatically fire.
(Insert Code here)
Found := TRUE;
EXCEPTION
WHEN OTHERS THEN
Found := FALSE;
DBMS_LOCK.SLEEP(10);
Count := Count + 1;
END;
END LOOP;
END;
/
Viewing The SQL Statements of the CPU-Intensive Oracle Processes
First get the top 10 CPU-intensive Oracle processes on the operating system with
the first column giving the %CPU used,
the second column unix PID, the third column USER , the fourth column TERMINAL,
and the last column Unix PROCESS (works only for UNIX).
ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | sort -r | head -20
Now you can specify the found PID in the following SQL-Statement:
column username format a9
column sql_text format a70
SELECT a.username, b.sql_text
FROM v$session a, v$sqlarea b, v$process c
WHERE (c.spid = '&PID' OR a.process = '&PID')
AND a.paddr = c.addr
AND a.sql_address = b.address
/
Tracking the progress of a long running statement
Sometimes you run an INSERT or DELETE statement that takes a long time to complete. You have wondered how many rows
have already been inserted or deleted so that you can decide whether or not to abort the statement. Is there a way
to display how many rows have been deleted while the statement is occurring ?
You can query the V$SESSION_LONGOPS table to track the progress of the statement.
Example: Starting the following long running INSERT
INSERT INTO bigemp SELECT * FROM bigemp;
Check the progress:
SELECT sofar,totalwork,time_remaining FROM v$session_longops WHERE sid = 55 and time_remaining > 0;
SOFAR TOTALWORK TIME_REMAINING
---------- ---------- --------------
8448 11057 20
8832 11057 17
9024 11057 16
9184 11057 14
9536 11057 12
9646 11057 11
9920 11057 9
10421 11057 5
10529 11057 4
10814 11057 2
Script to see redundant indexes, where all leading columns are duplicated
select a.owner, b.owner, a.column_name, a.table_name, a.index_name, a.column_position
from
all_ind_columns a, all_ind_columns b
where
a.index_owner = b.index_owner and
a.column_name = b.column_name and
a.table_name = b.table_name and
a.column_position = b.column_position and
a.index_name <> b.index_name
ORDER BY a.index_name,a.column_position
/
How to create a read-only table ?
CREATE TRIGGER tab_readonly_emp BEFORE DELETE OR INSERT OR UPDATE
ON employees
BEGIN
RAISE_APPLICATION_ERROR(-20201, 'Table Status: READ ONLY.');
END;
RUN SQL command from UNIX shell
The following example shows how to run SQL statement in a single UNIX command
$ echo "select count(*) from tab;" | sqlplus -s scott/tiger
Another method is to store the SQL command is a file
$ sqlplus -s scott/tiger < get_count.sql
COUNT(*)
----------
36
$
Tuesday, March 29, 2011
Database recovery when datafile lost from physical HDD !
Prob : Datafile is lost physically but the related tablespace is not drop from database
In oracle database 10g , I created a test database name 'support' . I created a tablespace 'support_ts' here .
In my absent mind , I deleted the datafile which is associated to "support_ts" physically .Logically the tablespace information is in database but physically the datafile is deleted .
Lets , 'SUPPORT' database has no backup (export and rman ) ,also the database is in noarchivelog mode .In this moment , is it possible to recover it or can i focely remove the tablespace information from database ? so that , in next startup database don't search the "support_ts" tablespace information ?
Solution :
Recover the datafile is not possible without backup of datafile.
You can recreate the control file and startup the database.
Steps to recrete the control file.
1. Alter database backup controlfile to trace as '';
2. Open the control file and remove the support tablespace and datafile entry in noreset logs part.
3. Connect sys as sysdba
4. Shutdown immediate/ shut abort
5. startup nomount
6. paste the controlfile content of noreset logs
7. after created the controlfile mount and open the database.
startup nomount
Run the below script , without last "comma" :
CREATE CONTROLFILE REUSE DATABASE "SUPPORT" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/dbfs/oradata/support/redo01.log' SIZE 100M,
GROUP 2 '/dbfs/oradata/support/redo02.log' SIZE 100M,
GROUP 3 '/dbfs/oradata/support/redo03.log' SIZE 100M,
GROUP 4 '/orafs/oradata/support/redo04.log' SIZE 100M,
GROUP 5 '/orafs/oradata/support/redo05.log' SIZE 100M,
GROUP 6 '/orafs/oradata/support/redo06.log' SIZE 500M,
GROUP 7 '/orafs/oradata/support/redo07.log' SIZE 500M
DATAFILE
'/dbfs/oradata/support/system01.dbf',
'/dbfs/oradata/support/undotbs01.dbf',
'/dbfs/oradata/support/sysaux01.dbf',
'/dbfs/oradata/support/users01.dbf',
'/dbfs/oradata/support/ababil_ts01.dbf',
'/dbfs/oradata/support/ababil_index01.dbf',
'/dbfs/oradata/support/ababil_ts02.dbf',
'/orafs/support/datafile/ababil_ts03.dbf'
CHARACTER SET WE8ISO8859P1
;
recover databsae ;
alter database open ;
In oracle database 10g , I created a test database name 'support' . I created a tablespace 'support_ts' here .
In my absent mind , I deleted the datafile which is associated to "support_ts" physically .Logically the tablespace information is in database but physically the datafile is deleted .
Lets , 'SUPPORT' database has no backup (export and rman ) ,also the database is in noarchivelog mode .In this moment , is it possible to recover it or can i focely remove the tablespace information from database ? so that , in next startup database don't search the "support_ts" tablespace information ?
Solution :
Recover the datafile is not possible without backup of datafile.
You can recreate the control file and startup the database.
Steps to recrete the control file.
1. Alter database backup controlfile to trace as '
2. Open the control file and remove the support tablespace and datafile entry in noreset logs part.
3. Connect sys as sysdba
4. Shutdown immediate/ shut abort
5. startup nomount
6. paste the controlfile content of noreset logs
7. after created the controlfile mount and open the database.
startup nomount
Run the below script , without last "comma" :
CREATE CONTROLFILE REUSE DATABASE "SUPPORT" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/dbfs/oradata/support/redo01.log' SIZE 100M,
GROUP 2 '/dbfs/oradata/support/redo02.log' SIZE 100M,
GROUP 3 '/dbfs/oradata/support/redo03.log' SIZE 100M,
GROUP 4 '/orafs/oradata/support/redo04.log' SIZE 100M,
GROUP 5 '/orafs/oradata/support/redo05.log' SIZE 100M,
GROUP 6 '/orafs/oradata/support/redo06.log' SIZE 500M,
GROUP 7 '/orafs/oradata/support/redo07.log' SIZE 500M
DATAFILE
'/dbfs/oradata/support/system01.dbf',
'/dbfs/oradata/support/undotbs01.dbf',
'/dbfs/oradata/support/sysaux01.dbf',
'/dbfs/oradata/support/users01.dbf',
'/dbfs/oradata/support/ababil_ts01.dbf',
'/dbfs/oradata/support/ababil_index01.dbf',
'/dbfs/oradata/support/ababil_ts02.dbf',
'/orafs/support/datafile/ababil_ts03.dbf'
CHARACTER SET WE8ISO8859P1
;
recover databsae ;
alter database open ;
Command to get SGA Size Advice and Usage !
The Following Query helps to find out the SGA Size Advice :
select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from v$sga_target_advice order by sga_size_factor;
SGA usage :
-----------
select sum(value)/1024/1024 "Megabytes" from v$sga;
select component, current_size from v$sga_dynamic_components;
select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from v$sga_target_advice order by sga_size_factor;
SGA usage :
-----------
select sum(value)/1024/1024 "Megabytes" from v$sga;
select component, current_size from v$sga_dynamic_components;
How to Identify OS or Oracle 64 bit or 32 bit on Unix !
How to Identify OS or Oracle 64 bit or 32 bit on Unix
---------------------------------------------------------------
It is very common question which version of oracle I need to install. 32 bit or 64 bit? As a 64-bit operating system can support either a 32-bit database or a 64-bit database. A 32-bit operating system cannot support a 64-bit database. So, version identification of the OS is necessary prior to install oracle. The following procedure will hopefully help you.
Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,
SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules
SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu
This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.
On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,
On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
On linux,
$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
If the output is x86_64 then 64-bit and i686 or similar for 32-bit.
On windows,
Start>All Programs>accessories> System Tools>System Information>look for under System summary.
Or start>run>dixdiag>Then check for WHQL digital signature.
Determine of whether Oracle Software is 32 bit or 64 bit.
--------------------------------------------------------------------------------
Method 1:
---------
Go to $ORACLE_HOME/bin and see.
# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped
Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.
If you had 32 bit oracle software installed then output will be like,
oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped
Method 2:
---------
Log on to SQL*plus and see the banner.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Method 3:Query from v$version.
------------------------------
sys@THERAP> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Method 4: Check for lib, lib32
------------------------------
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
Related Documents:
------------------------
http://arjudba.blogspot.com/2008/05/memory-usage-of-solaris-operating.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-host.html
---------------------------------------------------------------
It is very common question which version of oracle I need to install. 32 bit or 64 bit? As a 64-bit operating system can support either a 32-bit database or a 64-bit database. A 32-bit operating system cannot support a 64-bit database. So, version identification of the OS is necessary prior to install oracle. The following procedure will hopefully help you.
Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,
SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules
SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu
This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.
On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,
On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
On linux,
$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
If the output is x86_64 then 64-bit and i686 or similar for 32-bit.
On windows,
Start>All Programs>accessories> System Tools>System Information>look for under System summary.
Or start>run>dixdiag>Then check for WHQL digital signature.
Determine of whether Oracle Software is 32 bit or 64 bit.
--------------------------------------------------------------------------------
Method 1:
---------
Go to $ORACLE_HOME/bin and see.
# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped
Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.
If you had 32 bit oracle software installed then output will be like,
oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped
Method 2:
---------
Log on to SQL*plus and see the banner.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Method 3:Query from v$version.
------------------------------
sys@THERAP> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Method 4: Check for lib, lib32
------------------------------
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
Related Documents:
------------------------
http://arjudba.blogspot.com/2008/05/memory-usage-of-solaris-operating.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-host.html
About DB Time !
What is DB Time :
==================
DB Time : Amount of elapsed time (in microseconds) spent performing Database user-level calls.This does not include the time spent on instance background processes such as PMON.
DB Time = (cpu time+Wait Time); How many time spent against a user .
DB CPU : Amount of CPU time (in microseconds) spent on database user-level calls. This does not
include the CPU time spent on instance background processes such as PMON.
DB Time is the cumulative time spent by the database server in processing user requests .It includes wait time and cpu time of all non-idle user sessions . DB Time is displayed in the v$sess_time_model and v$sys_time_model
background cpu time : Amount of CPU time (in microseconds) consumed by database background processes
The follwing query helps to find out the DB time of Sessions :
select a.username,a.sid,b.stat_id,b.stat_name,b.value from v$session a, v$sess_time_model b where a.sid=b.sid and stat_name like 'DB time' order by value desc;
Here ,
STAT_ID : Statistic identifier for the time statistic
STAT_NAME: Name of the statistic
VALUE : Amount of time (in microseconds) that the system has spent in this operation
Note :
------
1 sec = 1000000 micro seconds
899413577 ms = 899 sec = 15 min
We can use the below query also :
select * from v$sys_time_model
select * from v$sess_time_model
==================
DB Time : Amount of elapsed time (in microseconds) spent performing Database user-level calls.This does not include the time spent on instance background processes such as PMON.
DB Time = (cpu time+Wait Time); How many time spent against a user .
DB CPU : Amount of CPU time (in microseconds) spent on database user-level calls. This does not
include the CPU time spent on instance background processes such as PMON.
DB Time is the cumulative time spent by the database server in processing user requests .It includes wait time and cpu time of all non-idle user sessions . DB Time is displayed in the v$sess_time_model and v$sys_time_model
background cpu time : Amount of CPU time (in microseconds) consumed by database background processes
The follwing query helps to find out the DB time of Sessions :
select a.username,a.sid,b.stat_id,b.stat_name,b.value from v$session a, v$sess_time_model b where a.sid=b.sid and stat_name like 'DB time' order by value desc;
Here ,
STAT_ID : Statistic identifier for the time statistic
STAT_NAME: Name of the statistic
VALUE : Amount of time (in microseconds) that the system has spent in this operation
Note :
------
1 sec = 1000000 micro seconds
899413577 ms = 899 sec = 15 min
We can use the below query also :
select * from v$sys_time_model
select * from v$sess_time_model
Subscribe to:
Posts (Atom)