| Monday, June 22, 2009 |
| User Information |
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, PRIVILEGELabels: User Information |
posted by Srinivasan .R @ 1:20 AM  |
|
|
|
