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:

ACCOUNT_STATUS:

locked, open, expired

LOCK_DATE:

NULL for open a/c.

Otherwise date on which the account locked.

EXPIRY_DATE:

Date on which the a/c will expire.

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.


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google