Display 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 MoreShow all the Oracle users connected to the database SQL Code 1set lines 100 pages 999 2col ID format a15 3col USERNAME format a20 4select username 5, sid || ',' || serial# "ID" 6, status 7, last_call_et "Last Activity" 8from v$session 9where username is not null 10order by status desc 11, …
Read MoreShows the distribution of objects and data across all schemas. Which schemas are taking up all of the space? SQL Code 1set pages 999 2col "size MB" format 999,999,999 3col "Objects" format 999,999,999 4select obj.owner "Owner" 5, obj_cnt "Objects" 6, decode(seg_size, NULL, 0, seg_size) …
Read MoreOracle Startup Time: Identify the Oralce Startup Time using SQL Script SQL Code 1select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" 2from v$instance 3/ Sample Oracle Output: 1 1 select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" 2 2* from v$instance 3SQL> / …
Read More