Saturday, August 12, 2006
Views : Users/Quota/Profile/Resource cost/Resource Limit/Privileges
| DBA_USERS | Username, Oracle generated ID, encrypted password, default & temporary tablespace info, user profile. Also it has following information:
| ||||||
| DBA_OBJECTS | Specific information about every object in the database. Use OWNER column to find which object belongs to whom. | ||||||
| DBA_SEGMENTS | Info abt various segments (tables, indexes etc) created by users, where they reside, and their space allocation information. | ||||||
| DBA_TS_QUOTAS | Names all users and any tablespace quotas created for them. MAX_BYTES or MAX_BLOCKS=-1 implies unlimited space quota. |
| DBA_PROFILES | Resource usage parameters specified in conjunction with each profile. |
| RESOURCE_COST | Identifies all resources in the database and their corresponding cost, as defined by the DBA. Cost determines a resources relative importance of use. |
| USER_RESOURCE_LIMITS | Identifies the system resource limits for individual users as determined by the profile assigned to the users. |
| DBA_SYS_PRIVS | Shows all system privileges associated with this user. |
| DBA_TAB_PRIVS | Shows all object privileges associated with this user. |
| SESSION_PRIVS | Shows all privileges available in this session for this user. |
Examples :
| DBA_USERS |
SQL > select * from DBA_USERS where username='SYS';
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
-------------------------------------------------------------------------
SYS 0 D4C5016086B2DC6A
OPEN
SYSTEM TEMP 02-DEC-05
| DBA_TS_QUOTAS |
SQL> desc dba_ts_quotas
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
USERNAME NOT NULL VARCHAR2(30)
BYTES NUMBER
MAX_BYTES NUMBER
BLOCKS NOT NULL NUMBER
MAX_BLOCKS NUMBER
SQL> select substr(tablespace_name,1,10), substr(username,1,15),bytes, max_bytes
,blocks,max_blocks from dba_ts_quotas ;
SUBSTR(TAB SUBSTR(USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
---------- --------------- ---------- ---------- ---------- ----------
EXAMPLE QS_WS 1216512 -1 198 -1
CWMLITE OLAPSYS 6420480 -1 1045 -1
EXAMPLE HR 1689600 -1 275 -1
EXAMPLE OE 6893568 -1 1122 -1
EXAMPLE PM 3176448 -1 517 -1
EXAMPLE SH 128477184 -1 20911 -1
EXAMPLE QS_ADM 0 -1 0 -1
EXAMPLE QS 1622016 -1 264 -1
EXAMPLE QS_ES 1216512 -1 198 -1
EXAMPLE QS_OS 1216512 -1 198 -1
EXAMPLE QS_CBADM 878592 -1 143 -1
EXAMPLE QS_CB 0 -1 0 -1
EXAMPLE QS_CS 675840 -1 110 -1
13 rows selected.
| DBA_PROFILES |
SQL> DESC DBA_PROFILES
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(8)
LIMIT VARCHAR2(40)
SQL> SELECT SUBSTR(PROFILE,1,15) "Profile", RESOURCE_NAME,RESOURCE_TYPE, SUBSTR(LIMIT,1,10) "Limit" FROM DBA_PROFILES;
Profile RESOURCE_NAME RESOURCE Limit
--------------- -------------------------------- -------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
16 rows selected.
| RESOURCE_COST |
SQL> DESC RESOURCE_COST;
Name Null? Type
----------------------------------------- -------- ------------
RESOURCE_NAME NOT NULL VARCHAR2(32)
UNIT_COST NOT NULL NUMBER
SQL> SELECT * FROM RESOURCE_COST;
RESOURCE_NAME UNIT_COST
-------------------------------- ----------
CPU_PER_SESSION 0
LOGICAL_READS_PER_SESSION 0
CONNECT_TIME 0
PRIVATE_SGA 0
| USER_RESOURCE_LIMITS |
SQL> DESC USER_RESOURCE_LIMITS;
Name Null? Type
----------------------------------------- -------- ------------
RESOURCE_NAME NOT NULL VARCHAR2(32)
LIMIT VARCHAR2(40)
SQL> SELECT * FROM USER_RESOURCE_LIMITS;
RESOURCE_NAME LIMIT
-------------------------------- -------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
9 rows selected.
