Shulou(Shulou.com)06/01 Report--

Query the sql statement being executed by Oracle and the user who executed it:

SELECT b.sid oracleID

B.username login Oracle user name

B.serial#

Spid operating system ID

Paddr

SQL being executed by sql_text

B.machine computer name

FROM v$process a, v$session b, v$sqlarea c

WHERE a.addr = b.paddr

AND b.sql_hash_value = c.hash_value

View the distribution program of the initiator who is executing sql:

Login identity of SELECT OSUSER computer

The program that PROGRAM initiates the request

User name of the USERNAME login system

SCHEMANAME

B.Cpu_Time spent the time of cpu

STATUS

Sql executed by B.SQL_TEXT

FROM V$SESSION A

LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS

AND A.SQL_HASH_VALUE = B.HASH_VALUE

ORDER BY b.cpu_time DESC

Find out the current locked object of oracle:

SELECT l.session_id sid

S.serial#

L.locked_mode lock mode

L.oracle_username login user

L.os_user_name login machine user name

S.machine machine name

S.terminal terminal user name

O.object_name locked object name

S.logon_time login time to the database

FROM v$locked_object l, all_objects o, v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid

ORDER BY sid, s.serial#

Kill drops the current lock object:

Alter system kill session 'sid, s. Serializations'

Query the currently executing transaction:

SELECT s.sid

S.serial#

S.event

A.sql_text

A.sql_fulltext

S.username

S.status

S.machine

S.terminal

S.program

A.executions

S.sql_id

P.spid

A.direct_writes

FROM (SELECT * FROM v$session WHERE status = 'ACTIVE') s

LEFT JOIN v$sqlarea a

ON s.sql_id = a.sql_id

INNER JOIN v$process p

ON s.paddr = p.addr

View long transactions in the Oracle database:

Set linesize 200

Set pagesize 5000

Col transaction_duration format a45

With transaction_details as

(select inst_id

, ses_addr

, sysdate-start_date as diff

From gv$transaction

)

Select s.username

, to_char (trunc (t.diff))

| | 'days,' |

| to_char (trunc (mod (t.diff * 24) |

| | 'hours,' |

| to_char (trunc (mod (t.diff * 24 * 60Jet 24) |

| | 'minutes,' |

| to_char (trunc (mod (t.diff * 24 * 60 * 60) |

| | 'seconds' as transaction_duration |

, s.program

, s.terminal

, s.status

, s.sid

, s.serial#

From gv$session s

, transaction_details t

Where s.inst_id = t.inst_id

And s.saddr = t.ses_addr

Order by t.diff desc

/

Query long transaction SQL:

The following SQL queries the database for information about transactions that are executing for more than N seconds:

With ltr as (

Select to_char (sysdate,'YYYYMMDDHH24MISS') TM

S.sid

S.sql_id

S.sql_child_number

S.prev_sql_id

Xid

To_char (t.startdatedthatyyyymMDDHH24MISS') start_time

E.TYPE,e.block

E.ctime

Decode (e.CTIME, 0, (sysdate-t.start_date) * 3600mm 24, e.ctime) el_second

-- q.sql_text

From v$transaction t, v$session sdome vicious transactionist queue e

Where t.start_date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.