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

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
$

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 ;

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;

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

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

sysdba vs sysoper !

SYSDBA can do more than start/stop the database. It has a lot more functionality that sysoper (Operator mode) which is normally used for basic database functions such as start/stop.

SYSOPER privilege allows operations such as:
Instance startup, mount & database open ;
Instance shutdown, dismount & database close ;
Alter database BACKUP, ARCHIVE LOG, and RECOVER.
This privilege allows the user to perform basic operational tasks
without the ability to look at user data.


SYSDBA privilege includes all SYSOPER privileges plus full system privileges
(with the ADMIN option), plus 'CREATE DATABASE' etc..
This is effectively the same set of privileges available when
previously connected INTERNAL.