List all open oracle cursors by user or username SQL Code 1set pages 999 lines 300 2col username format a40 3select sess.username as username 4, sess.sid as sid 5, sess.serial# as serial 6, stat.value cursors 7from v$sesstat stat 8, v$statname sn 9, v$session sess 10where sess.username is not null 11and sess.sid = …
Read MoreReset or unset a Oracle parameter in the spfile Setting a parameter to =' ' often isn't enough. Do this instead.. The sid='' bit is always necessary, even in non RAC database. SQL Code 1alter system reset <parameter> scope=spfile sid='' 2/ Sample Oracle Output: 1 2no rows selected 3SQL> Purpose: To …
Read MoreDisplay Session status associated with the specified os process id SQL Code 1select s.username 2, s.sid 3, s.serial# 4, p.spid 5, last_call_et 6, status 7from V$SESSION s 8, V$PROCESS p 9where s.PADDR = p.ADDR 10and p.spid='&pid' 11/ Sample Oracle Output: 1Enter value for pid: 9999 2old 10: and …
Read MoreDisplay the users current Session SQL SQL Code 1Select sql_text 2from v$sqlarea 3where (address, hash_value) in 4(select sql_address, sql_hash_value 5 from v$session 6 where username like '&username') 7/ Sample Oracle Output: 1Enter value for username: sys 2old 6: where username like …
Read MoreSelect Oracle user info including os pid SQL Code 1col "SID/SERIAL" format a10 2col username format a20 3col osuser format a15 4col program format a50 5select s.sid || ',' || s.serial# "SID/SERIAL" 6, s.username 7, s.osuser 8, p.spid "OS PID" 9, s.program 10from v$session s 11, v$process …
Read MoreShow all the Oracle database used or installed Oracle features Show all the Oracle database installed features for an instance SQL Code 1select name 2, detected_usages 3from dba_feature_usage_statistics 4where detected_usages > 0 5/ Sample Oracle Output: 1 2SQL> Purpose: To identify and display database features …
Read MoreOracle sessions sorted by logon time SQL Code 1set lines 100 pages 999 2select username 3, floor(last_call_et / 60) "Minutes" 4, status 5from v$session 6where username is not null 7order by last_call_et 8/ Sample Oracle Output: 1USERNAME OSUSER ID STATUS LOGIN_TIME LAST_CALL_ET 2-------------------- …
Read MoreDisplay all Oracle users by the time since last user activity SQL Code 1set lines 100 pages 999 2select username 3, floor(last_call_et / 60) "Minutes" 4, status 5from v$session 6where username is not null 7order by last_call_et 8/ Sample Oracle Output: 1USERNAME Minutes STATUS 2-------------------- ---------- …
Read MoreShow the ten largest oracle objects in the database SQL Code 1col owner format a15 2col segment_name format a30 3col segment_type format a15 4col mb format 999,999,999 5select owner 6, segment_name 7, segment_type 8, mb 9from ( 10 select owner 11 , segment_name 12 , segment_type 13 , bytes / 1024 / 1024 "MB" 14 …
Read MoreIdentify if Java is installed in the Oralce database This will return 9000'ish if it is... SQL Code 1select count(*) 2from all_objects 3where object_type like '%JAVA%' 4and owner = 'SYS' 5/ Sample Oracle Output: 1 2SQL> ** Purpose:** To count the number of Java-related objects (such as classes, …
Read More