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 '&username') 3new 6: where username …

Select 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 p 12Where s.paddr = p.addr …

Show all the Oracle database used or installed Oracle features using dba_feature_usage_statistics Discover which database features have been actively used, providing insights for license management, optimization, and future planning. Show all the Oracle database installed features for an instance SQL Code 1select name …

Oracle sessions sorted by logon time Track idle time for user sessions to uncover potential resource leaks, optimize database performance, and maintain security best practices. SQL Code 1et lines 100 pages 999 2select username 3, floor(last_call_et / 60) "Minutes" 4, status 5from v$session 6where username is not null …

Display all Oracle users by the time since last user activity Identify idle user sessions and their inactive duration for resource optimization, performance tuning, and security best practices. SQL Code 1set lines 100 pages 999 2select username 3, floor(last_call_et / 60) "Minutes" 4, status 5from v$session 6where …

Show the ten largest oracle objects in the database using dba_segments Pinpoint the segments occupying the most storage space within the database to optimize storage usage, address potential bottlenecks, and enhance performance. SQL Code 1col owner format a15 2col segment_name format a30 3col segment_type format a15 …

Identify if Java is installed in the Oralce database using all_objects Gain visibility into the quantity of Java objects owned by the SYS user, aiding in understanding database usage and potential security considerations. This will return 9000'ish if it is... SQL Code 1select count(*) 2from all_objects 3where …

Oracle SQL Scripts and Database Commands 23c 21c 19c 18c 12c 11g 10g 9i 8i We are in the process of building a list of simple scripts used every day by DBA and others. If you have your own scripts you reuse in a text file on you workstation please send it to us. To look at other scripts see the Oracle Scripts Tag …

Show 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, last_call_et desc 12/ Sample …

Shows the distribution of objects and data across all schemas using dba-segments & dba_objects Gain insights into object distribution across database users and their corresponding storage usage for data management, capacity planning, and optimization. Which schemas are taking up all of the space? SQL Code 1set pages …