System DBA views - User Management and Administration

Who are you?

SQL> SELECT *
  2  FROM USER_USERS;

OUTPUT:

USERNAME     USER_ID   DEFAULT_TABLESPACE      TEMPORARY TABLESPACE  CREATED
----------   ------    --------------------    --------------------  --------

USER_USERS view allows you to view how your Oracle ID was set up, when it
was set up, and it also shows other user-specific, vital statistics. 

How to view all users that exist in the database - Use ALL_USERS view 

SQL> SELECT *
  2  FROM ALL_USERS;

USERNAME        USER_ID         CREATED
--------------  -------    ------------

What Are Your Privileges?

Use the following views 

USER_SYS_PRIVS - examine your system privileges

USER_ROLE_PRIVS  - view information about roles you have been granted within the database

What Do You Have Access To?

USER_CATALOG -  is simply a catalog of the tables, views, synonyms, and sequences owned by the current user 

ALL_CATALOG - enables you to see tables owned by other individuals.

USER_OBJECTS - view to select general information about a user's
owned objects, such as the name, type, date created, date modified, and the status
of the object.

USER_TABLES - view the information in detail

ALL_TABLES - allows you to see all the tables to which you have access,
instead of just the tables you own 

As a database user, you can monitor the growth of tables and indexes in your catalog
by querying the USER_SEGMENTS view. As the name suggests.

USER_SEGMENTS gives you information about each segment, such as storage information and extents taken. A
segment may consist of a table, index, cluster rollback, temporary, or cache.

ALL_TAB_PRIVS view lists all privileges that you have as a database user on each
table available to you. 

read-only access = SELECT privileges

When you create objects, you usually need to know where to place them in the database unless you allow your target destination to take the default. An Oracle database is broken up into tablespaces, each of which are capable of storing objects. Each tablespace is allocated a certain amount of disk space, according to what is available on the system. Disk space is usually acquired through the system administrator (SA).

USER_TABLESPACES - will list the tablespaces that you have access to, the default initial and next sizes of objects created within them, and their status.

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         INITIAL_EXTENT,
  3         NEXT_EXTENT,
  4         PCT_INCREASE,
  5         STATUS
  6  FROM USER_TABLESPACES;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE STATUS
------------------------------ -------------- ----------- ------------ ------

This type of query is very useful when you are creating objects, such as tables and indexes, which will require storage. When a table or index is created, if the initial and next storage parameters are not specified in the DDL, the table or index will take the tablespace's default values. The same concept applies to PCT INCREASE, which is an Oracle parameter specifying the percentage of allocated space an object should take when it grows. If a value for PCT INCREASE is not specified when the table or index is created, the database server will allocate the default value that is specified for the corresponding tablespace. Seeing the default values enables you to determine whether you need to use a storage clause in the CREATE statement. 

 Sometimes, however, you need to know more than which tablespaces you may access, that is, build tables under. For example, you might need to know what your limits are within the tablespaces so that you can better manage the creation and sizing of your objects. The USER_TS_QUOTAS view provides the necessary information. The next query displays a user's space limits for creating objects in the database. 

 

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         BYTES, MAX_BYTES
  3  FROM USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------ ---------- ----------

BYTES identifies the total number of bytes in that tablespace that are associated with the user. MAX BYTES identifies the maximum bytes allotted to the user, or the user's quota, on the tablespace. The first two values in this column are self-explanatory. The -1 in the third row means quota unlimited--that is, no limits are placed on the user for that tablespace.  

 

System DBA Views

DBA_USERS - you are able to see the vital information on each user.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users.

DBA_ROLE_PRIVS - provides information about database roles that have been granted to users. The first column is the grantee, or user. The second column displays the granted role. Notice that every role granted to the user corresponds to a record in the table. ADM identifies whether the role was granted with the Admin option, meaning that the user is able to grant the matching role to other users. The last column is DEFAULT, stating whether the matching role is a default role for the user.

 

SQL> SELECT *
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'RJENNINGS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
AHASTINGS                      CREATE SESSION                           NO
VROY                           UNLIMITED TABLESPACE                     NO

DBA_SYS_PRIVS view lists all system-level privileges that have been granted to the user. This view is similar to DBA_ROLE_PRIVS

DBA_CATALOG is the same thing as the USER_CATALOG, only the owner of the table is included. DBA_CATALOG is a view that the DBA can use to take a quick look at all tables.

DBA_TABLES view gives specific information about database tables, mostly concerning storage

DBA_SYNONYMS view provides a list of all synonyms that exist in the database

DBA_IND - listing all indexes that belong to a schema and matching them up with their corresponding table.

DBA_TABLESPACES to see a list of all tablespaces.

DBA_SEGMENTS provides information about each segment, or object in the database such as storage allocation, space used, and extents

SQL> SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  2         SUBSTR(SEGMENT_TYPE,1,12) SEGMENT_TYPE,
  3         BYTES,
  4         EXTENTS,
  5  FROM SYS.DBA_SEGMENTS

SEGMENT_NAME                   SEGMENT_TYPE      BYTES    EXTENTS
------------------------------ ------------ ---------- ----------

DBA_EXTENTS provides information about each extent of a segment.

SQL> SELECT SUBSTR(OWNER,1,10) OWNER,
  2         SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  3         EXTENT_ID,
  4         BYTES
  5  FROM SYS.DBA_EXTENTS

OWNER      SEGMENT_NAME                    EXTENT_ID     BYTES
---------- ------------------------------ ---------- --------

Oracle allocates space to the database by using "data files." Space logically exists within a tablespace, but data files are the physical entities of tablespaces    

SQL> SELECT SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,
  2         SUBSTR(FILE_NAME,1,40) FILE_NAME,
  3         BYTES
  4  FROM SYS.DBA_DATA_FILES;

TABLESPACE_NAME           FILE_NAME                                     BYTES
------------------------- ---------------------------------------- ----------