Oracle Common Query __oracle

Oracle detects deadlock Sql:select SID, DECODE (block, 0, ' no ', ' Yes ') blocker, DECODE (request, 0, ' no ', ' yes ') waiter from V$lock WH ERE Request > 0 OR block > 0 ORDER by block DESC Select Bs.username "Blocking user", bs.username "DB user",
Ws.username "Waiting User", BS. Sid "Sid", WS. SID "Wsid",
bs.serial# "serial#", bs.sql_address "address",
Bs.sql_hash_value "SQL Hash", bs.program "Blocking App",
Ws.program "Waiting App", bs.machine "Blocking machine",
Ws.machine "Waiting Machine", Bs.osuser "Blocking OS User",
Ws.osuser "Waiting OS User", bs.serial# "serial#", ws.serial# "wserial#",
DECODE (wk. TYPE, ' MR ', ' Media Recovery ', ' RT ', ' Redo Thread ', ' UN ', ' USER Name ', ' TX ',
' Transaction ', ' TM ', ' DML ', ' UL ', ' pl/sql USER LOCK ', ' DX ', ' Distributed xaction ',
' CF ', ' Control FILE ', ' are ', ' Instance state ', ' FS ', ' FILE SET ', ' IR ', ' Instance Recovery ',
' ST ', ' Disk space Transaction ', ' TS ', ' Temp Segment ', ' IV ', ' Library Cache invalidation ',
' LS ', ' LOG START OR Switch ', ' RW ', ' ROW wait ', ' SQ ', ' Sequence number ', ' TE ', ' Extend TABLE ',
' TT ', ' Temp TABLE ', wk. TYPE) Lock_type, DECODE (hk.lmode, 0, ' None ', 1, ' NULL ', 2,
' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' SHARE ', 5, ' S/row-x (SSX) ', 6, ' EXCLUSIVE ', To_char (Hk.lmode)) Mode_held,
DECODE (wk.request, 0, ' None ', 1, ' NULL ', 2, ' Row-s (SS) ', 3, ' Row-x ' (SX) ', 4, ' SHARE ', 5, ' s/row-x ' (SSX) ', 6,
' EXCLUSIVE ', To_char (wk.request)) mode_requested, To_char (HK.ID1) lock_id1, To_char (HK.ID2) Lock_id2,
DECODE (HK. block, 0, ' Don't Blocking ',/**//* not Blocking any other processes * * * 1, ' Blocking ',
/**//* This lock blocks the other processes/* 2, ' global ',/**//* this lock is Global, so we can ' t-tell/TO_CHAR (HK. Block)) Blocking_others
From V$lock HK, v$session BS, V$lock wk, v$session ws
WHERE HK. Block = 1
and Hk.lmode!= 0
and Hk.lmode!= 1
and wk.request!= 0
and WK. TYPE (+) = HK. TYPE
and WK.ID1 (+) = Hk.id1
and Wk.id2 (+) = Hk.id2
and HK. SID = BS. SID (+)
and WK. SID = ws. SID (+)
and (Bs.username is not NULL)
and (Bs.username <> ' SYSTEM ')
and (Bs.username <> ' SYS ')
ORDER BY 1/

Check the application system for invalid objects (resulting in invalid object compilation scripts): SELECT ' PROMPT Compile VIEW ' | | object_name| | |        CHR (10) | | ' ALTER VIEW ' | | object_name | | ' COMPILE; ' | | CHR (10) | | Show ERRORS; ' | | CHR (a) "--compile view" from Dba_objects WHERE STATUS <> ' VALID ' and object_type = ' view ' ORDER by Object_nam E SELECT ' PROMPT Compile TRIGGER ' | | object_name| | |        CHR (10) | | ' ALTER TRIGGER ' | | object_name| | ' COMPILE; ' | | CHR (10) | | Show ERRORS; ' | | CHR (a) "--compile TRIGGER" from dba_objects WHERE STATUS <> ' VALID ' and object_type = ' TRIGGER ' ORDER by OBJ Ect_name/
Querying Oracle Hidden parameters: Select X.ksppinm, Y.KSPPSTVL, X.ksppdesc from X$ksppi x, X$KSPPCV y where x.indx = Y.indx and Y.inst_   id = userenv (' Instance ') and x.inst_id = Userenv (' Instance ') and x.ksppinm like ' \_% ' escape '/or: SELECT KSPPINM, KSPPSTVL, Ksppdesc from X$ksppi x, X$KSPPCV y WHERE x.indx = y.indx and ksppinm = ' _serial_direct_read '
/
Query the headroom of the database SCN (refers to the difference between the maximum reasonable SCN and the current database SCN): SELECT
((((
((To_number (To_char (cur_date, ' YYYY '))-1988 *12*31*24*60*60) +
((To_number (To_char (cur_date, ' MM '))-1 *31*24*60*60) +
(((To_number (To_char (cur_date, ' DD ')-1)) *24*60*60) +
(To_number (To_char (cur_date, ' HH24 ')) *60*60) +
(To_number (To_char (cur_date, ' MI ')) *60) +
(To_number (To_char (cur_date, ' SS '))
) * (16*1024))-12821569053984)
/(16*1024*60*60*24)
) Headroom
From (select To_date (' Info 15:09:57 ', ' yyyy-mm-dd hh24:mi:ss ') cur_date from dual)/query database currently the largest possible SCN "maximum reasonable scn": col For 999,999,999,999,999,999 Select
(
(
(
(
(
(
To_char (sysdate, ' YYYY ')-1988
) *12+
To_char (sysdate, ' mm ')-1
) *31+to_char (sysdate, ' DD ')-1
) *24+to_char (sysdate, ' hh24 ')
) *60+to_char (sysdate, ' mi ')
) *60+to_char (sysdate, ' SS ')
) * To_number (' ffff ', ' XXXXXXXX ')/4 SCN
From dual
/based on the SCN query table T more history: Select VERSIONS_XID,VERSIONS_STARTSCN,VERSIONS_ENDSCN,
DECODE (versions_operation, ' I ', ' Insert ', ' U ', ' Update ', ' D ', ' Delete ', ' Original ') "Operation", owner
From T versions between SCN MinValue and MaxValue
/
To view the execution time of an executing SQL statement and the wait event for an SQL statement: Select A.sql_text,b.status,b.last_call_et,b.event
From V$sql a,v$session b
where a.sql_id=b.sql_id and B.sid= 41
/or select Event,p1,p1text,p2,p2text,p3,p3text,wait_time,seconds_in_wait,state from v$session_wait where sid=41/
View the various resource consumption statistics since the session was run: Select A.sid,b.name,a.value from V$sesstat a,v$statname b where a.sid=41 and a.statistic#=b . statistic# and B.name in (' Consistent gets ', ' physical reads ', ' parse count (total) ', ' Parse count (hard) ')/
Fenng provides an SQL to determine the extent of fragmentation as follows: SELECT free_space, Avg_free_size,used_space, Avg_used_size, Request_failures, last_failure_ Size        from v$shared_pool_reserved; if: request_failures > 0 and Last_failure_size < Shared_pool_reserved_min_alloc or   request_failures equals 0 and last _failure_size < Shared_pool_reserved_min_alloc This is the time to adjust;   View total PGA allocations from v$process: Select Spid,program,pga_ Max_mem Max,pga_alloc_mem alloc,pga_used_mem used,pga_freeable_mem free from v$process where spid (select spid) from v$p rocess where addr in                   (select paddr from v$session where SID in                      (select distinct SID from V$mystat))/from V$process_memory attempt, You can further view the size of the PGA area allocations: Select p.program,p.spid,pm.category,pm.allocated,pm.used,pm.max_allocated from V$process p,v$process_memory pm where P.pid=pm.pid    and p.spid in (select spid from v$process where addr in   &nbsp ;                 (Select Paddr From V$session where SID in                         (select distinct SID from V$mystat))/    11g, check tablespace usage: Select Df.tablespace_name "Table space name",        totalspace-freespace "USED_MB",   & nbsp;    totalspace "Total space M",        freespace "remaining space M",         round ((1-freespace/totalspace) * 100, 2) "Usage%"   from (select Tablespace_name, round (su m (bytes)/1024/1024) Totalspace           from Dba_data_files  & nbsp;       GROUP BY Tablespace_name)DF,        (select Tablespace_name, round (sum (bytes)/1024/1024) FreeSpace            from dba_free_space               GROUP by Tablespace_name) fs  where df.tablespace_name = Fs.tablespace_name (+) Order by Rou nd ((1-freespace/totalspace) * 2) DESC/
Table Space Fsfi (free space fragment index) value query, FSFI maximum of 100 is an ideal single file tablespace: Select TABLESPACE_NAME,SQRT (max (blocks)/sum (blocks)) * (100/sqrt ( sqrt (count (blocks))) FSFI
From Dba_free_space
Group BY Tablespace_name ORDER by 1/
Check database table space fragmentation (table space management mode is divided into local and dictionary): select A.tablespace_name, COUNT (1) fragment amount from Dba_free_space a,dba_tablespaces b
where A.tablespace_name =b.tablespace_name
and b.extent_management = ' local '
GROUP BY A.tablespace_name
Having count (1) >20
ORDER BY 2/
Table space defragmentation: ALTER DATABASE users COALESCE;
Query table Space free space: Select a.file_id "Fileno", A.tablespace_name "Tablespace_name", a.bytes "bytes", A.bytes-sum (NVL (B.bytes, 0) "Used", Sum (NVL (b.bytes,0)) "Free", Sum (NVL (b.bytes,0))/a.bytes*100 "%free" from Dba_data_files A, Dba_free_spac E b where a.file_id=b.file_id (+) group by A.tablespace_name, a.file_id,a.bytes ORDER by A.tablespace_name/
Check the Oracle system undo Tablespace Usage (see Undo tablespace usage): Select To_char (MIN (begin_time), ' DD HH24:MI:SS ') ' Start time ',
To_char (MAX (end_time), ' DD HH24:MI:SS ') "End Time",
SUM (undoblks) "Number of undo blocks",
SUM (Txncount) "Number of transaction execution blocks",
MAX (Maxquerylen) "Query for maximum Seconds",
Max (maxconcurrency) "Maximum number of transactions",
SUM (ssolderrcnt) "ORA-01555 Times",
SUM (nospaceerrcnt) "No number of free space"
From V$undostat/
Check the related objects within the Recycle Bin: SELECT * FROM (select a.owner,a.object_name,a.original_name,b.bytes/1024/1024 MB from Dba_recyclebin A,dba_segments b where a.object_name=b.segment_name order by MB Desc) where rownum <11/
Check the physical reading of Oracle system data files (for the current balance of data files): Select sum (Decode (name, ' physical reads ', value,0)) dsk_rds, sum (Decode (name, ' db Block gets ', value,0)) Blk_gts, sum (Decode (name, ' consistent gets ', value,0)) Con_gts, ((() (SUM (Decode (name, ' P Hysical reads ', value,0))/(SUM (Decode (name, ' db block gets ', value,0)) + sum (Decode (name, ' consistent gets ', Val ue,0))) *100) Hit_rate from V$sysstat/
Find the current trace file name in sql: select      d.value          | | '/'          | | LOWER (RTRIM (i.instance, CHR (0))          | | ' _ora_ '          | | P.spid          | | '. TRC '             as "trace_file_name"   from    (select   p.spid             from    V$mystat m, v$session S, v$process p            where & nbsp m.statistic# = 1 and S.sid = M.sid and p.addr = s.paddr) p,          (select    T.instance             from   v$thread T, v$ Parameter v            where   v.name = ' thread '                     and (v.value = 0 OR t.thread# = to_number (v.value)) ) I,          (select   VALUE              from   V$parameter             where   NAME = ' user_dump_dest ') d/or: Select C.value | | '/' || D.instance_name | | ' _ora_ ' | | A.spid | | '. TRC ' trace from v$process a,v$session b,v$parameter c,v$instance D where a.addr=b.paddr   and b.audsid=userenv (' ses Sionid ')   and c.name= ' user_dump_dest '/
View block case (file, blocks, line; bbed) Select
rowID
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) Rel_fno,
Dbms_rowid.rowid_block_number (ROWID) Blockno,
Dbms_rowid.rowid_row_number (ROWID) rowno
From d/or (for large tables): Select Owner,segment_name,header_file,header_block,blocks from dba_segments where owner= ' SYS ' and SE Gment_name= ' D '/