Archive for the ‘Uncategorized’ Category

DBA Troubleshoot Queries

+2 Top Ten most CPU TIME Queries SELECT sql_text,ROUND (CPU_TIME/1000000) "CPU_TIME in Seconds" FROM (SELECT  sql_text, CPU_TIME,rank() over ( order by CPU_TIME DESC) rank_desc FROM v$sql) WHERE rank_desc>10;  

ORA-16789: missing standby redo logs

+5 DGMGRL> EDIT DATABASE nsndb SET PROPERTY ‘LogXptMode’=’SYNC’; Property “LogXptMode” updated DGMGRL> EDIT DATABASE nsnstd SET PROPERTY ‘LogXptMode’=’SYNC’; Error: ORA-16789: missing standby redo logs

Query to get uncommitted statements in the current session

+5 Query to get uncommitted statements(INSERT, UPDATE or DELETE statements) in the current session: SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1;   COUNT(*) ———- 0   IF: count>0 SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr […]

Query to get uncommitted statements

+5 Query to get uncommitted statements(INSERT, UPDATE or DELETE statements) in the current session: SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr AND s.sid = m.sid AND ROWNUM = 1; COUNT(*) ———- 0 IF: count>0 SQL> SELECT COUNT(*) FROM v$transaction t, v$session s, v$mystat m WHERE t.ses_addr = s.saddr […]

ORA-01555: snapshot too old

+6 ORA-01555: snapshot too old