Find schema/user action(s) in Oracle

Find current running sql query(s) in Oracle

If you are uncertain who work around with Oracle as backend, you would face the sitution what are current sql, which is executed by whom and where.

SELECT S.USERNAME,
         S.program,
         S.machine,
         s.sid,
         s.osuser,
         t.sql_id,
         sql_text,
         s.sql_trace
    FROM v$sqltext_with_newlines t, V$SESSION s
   WHERE     t.address = s.sql_address
         AND t.hash_value = s.sql_hash_value
         AND s.status = 'ACTIVE'
ORDER BY s.sid, t.piece; 

Find invalid user object(s) in Oracle

In some time stored procedure may fail because of invalid objects, that certain we want to know the complete invalid objects lists to compile.

  SELECT OWNER,
         OBJECT_TYPE,
         OBJECT_NAME,
         STATUS
    FROM dba_objects
   WHERE STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;

Find last modified object(s) in Oracle

Here is the sql to find last modified objects list.

  SELECT OWNER,
         OBJECT_NAME,
         OBJECT_TYPE,
         TO_CHAR (LAST_DDL_TIME, 'MM/DD/YYYY HH24:MI:SS') last_modified,
         TO_CHAR (CREATED, 'MM/DD/YYYY HH24:MI:SS') created,
         STATUS
    FROM dba_objects
   WHERE (SYSDATE - LAST_DDL_TIME) < 1
ORDER BY LAST_DDL_TIME DESC;

Find user object notes(s) in Oracle

If you are looking out to find out the user objects counts, we need to use the following script.

SELECT USERNAME,
         COUNT (DECODE (o.TYPE#, 2, o.OBJ#, '')) Tables,
         COUNT (DECODE (o.TYPE#, 1, o.OBJ#, '')) Indexes,
         COUNT (DECODE (o.TYPE#, 5, o.OBJ#, '')) Synonyms,
         COUNT (DECODE (o.TYPE#, 4, o.OBJ#, '')) Views,
         COUNT (DECODE (o.TYPE#, 6, o.OBJ#, '')) Sequences,
         COUNT (DECODE (o.TYPE#, 7, o.OBJ#, '')) Procedures,
         COUNT (DECODE (o.TYPE#, 8, o.OBJ#, '')) Functions,
         COUNT (DECODE (o.TYPE#, 9, o.OBJ#, '')) Packages,
         COUNT (DECODE (o.TYPE#, 12, o.OBJ#, '')) Trigers,
         COUNT (DECODE (o.TYPE#, 10, o.OBJ#, '')) Dependencies
    FROM sys.obj$ o, dba_users u
   WHERE u.USER_ID = o.OWNER#(+)
GROUP BY USERNAME
ORDER BY USERNAME;

Find user privileges note(s) in Oracle

Here is the sql to find user privileges list.

  SELECT rp.GRANTEE,
         GRANTED_ROLE,
         rp.ADMIN_OPTION,
         DEFAULT_ROLE,
         PRIVILEGE
    FROM dba_role_privs rp, dba_sys_privs sp
   WHERE rp.GRANTEE = sp.GRANTEE AND rp.GRANTEE NOT IN ('SYS', 'SYSTEM', 'DBA')
ORDER BY rp.GRANTEE, GRANTED_ROLE, PRIVILEGE;

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode