jump to content

User Information

Here are some scripts related to User Information .

User Objects

USER OBJECT NOTES:

  • Username - Owner of the object(s)
  • Tabs - Table(s)
  • Inds - Index(es)
  • Syns - Synonym(s)
  • Views - Views(s)
  • Seqs - Sequence(s)
  • Procs - Procedure(s)
  • Funcs - Function(s)
  • Pkgs - Packages(s)
  • Trigs - Trigger(s)
  • Deps - Dependencies 

    select 	USERNAME,
	count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,
	count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,
	count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
	count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
	count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
	count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
	count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
	count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
	count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
	count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from 	obj$ o,
	dba_users u
where 	u.USER_ID = o.OWNER# (+)
group	by USERNAME
order	by USERNAME

    Invalid Objects

    INVALID OBJECT NOTES:

  • Owner - Owner of the object
  • Object Type - Type of object
  • Object Name - Name of the object
  • Status - Status of the object 

    select 	OWNER,
	OBJECT_TYPE,
	OBJECT_NAME,
	STATUS
from 	dba_objects
where 	STATUS = 'INVALID'
order 	by OWNER, OBJECT_TYPE, OBJECT_NAME

    Object Modification

    OBJECT MODIFICATION NOTES: (Modified in last 7 days)

  • Owner - Owner of the object
  • Object Name - Name of the object
  • Object Type - Type of the object
  • Last Modified - Last modification date/time
  • Created - Object creation date/time
  • Status - Status of the object 

    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) < 7
order 	by LAST_DDL_TIME DESC

    User Privileges

    USER PRIVILEGES NOTES:

  • Grantee - Grantee name, user or role receiving the grant
  • Granted Role - Granted role name
  • Admin - Grant was with the ADMIN option
  • Default - Role is designated as a DEFAULT ROLE for the user
  • Privilege - System privilege 

    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