USER정보 확인하기

[정민이]

Oracle USER에 대한 개략적인 정보를 확인 하는 SQL들 입니다.

참고하세요~


DEFINE B='&Enter_Username'
DEFINE obj_type='&Enter_Object_type_4_User'
Prompt -------------------------------------- USER INFORMATION --------------------------------------

SELECT
' User '||username||'''S Account Information',
' Account Status => '||Account_status,
' Default Tablespace => '||default_tablespace,
' Temp Tablespace => '||temporary_tablespace
from dba_users where username=upper('&&B')
/

prompt -------------------------------------- OBJECTS OWNED BY USER --------------------------------

select a.username LoginId,
'User '||nvl(b.owner,'Does Not Own any objects..') ||' Owns ' ||nvl(b.cou,0)|| ' Nos Of ' ||nvl(b.object_type,'Objects')
FROM
dba_users a,
(SELECT OWNER,OBJECT_TYPE,COUNT(OBJECT_NAME) cou FROM DBA_OBJECTS GROUP BY OBJECT_TYPE,OWNER ORDER BY OWNER,OBJECT_TYPE) b
where a.username=b.owner (+) and a.username=upper('! &&B') and b.object_type(+)=upper('&&obj_type')
/

Prompt -------------------------------------- SYSTEM PRIVIELGES GRANTED TO THE USER ----------------

SELECT
nvl(b.grantee,'No_System_Privileges_Granted'),nvl(b.privilege,'null')
from
dba_users a,dba_sys_privs b
where a.username =b.grantee(+) and grantee=upper('&&B')
/

Prompt ------------------------------------ ROLES GRANTED TO THE USER ------------------------------

SELECT
nvl(b.grantee,'No_Roles_Granted'),nvl(b.granted_role,'null')
from
dba_users a,dba_role_privs b
where a.username =b.grantee(+) and grantee=upper('&&B')
/