V$ Views

The following table has the dynamic performance views. You need DBA privileges to access these views.

View Name	Description
V$BGPROCESS	Oracle background processes
V$CONTROLFILE	The names of the control files
V$DATABASE	Information about the database from the control file
V$DATAFILE	Information about the data files from the control file
V$ENABLEDPRIVS	Enabled privileges
V$FILESTAT	I/O activity per data file
V$FIXED_TABLE	Displays all dynamic performance tables, views, and derived tables
V$FIXED_VIEW_DEFINITION	Displays the definitions of all the fixed views
V$INSTANCE	Status about the instance
V$LICENSE	About license limits
V$LOCK	about active locks
V$MYSTAT	Statistics for the current session
V$NLS_PARAMETERS	Current NLS parameter values
V$NLS_VALID_VALUES	All valid values for NLS parameters
V$OPTION	The installed Oracle options
V$PARAMETER	Current initialization parameter values for the session
V$PROCESS	Information about all active processes
V$RESERVED_WORDS	All keywords used by the PL/SQL compiler
V$SESS_IO	I/O statistics for all active sessions
V$SESSION	About all active sessions
V$SESSION_EVENT	Waits for an event by a session
V$SESSION_LONGOPS	Status of operations that run for longer than 6 seconds
V$SESSION_WAIT	Resources or events for which active sessions are waiting
V$SESSTAT	User session statistics
V$SGA	Rhe shared global area
V$SGASTAT	SGA components
V$SPPARAMETER	The contents of the server parameter file
V$SQL	SQL statements in memory
V$SQLAREA	Aggregated information about SQL statements in memory
V$SQL_PLAN	Execution plan information for SQL statements in memory
V$SQL_PLAN_STATISTICS	Execution statistics of SQL statements in memory
V$SQLTEXT	The text of SQL statements in memory
V$SQLTEXT_WITH_NEWLINES	Same as V$SQLTEXT, with improved legibility
V$SYSSTAT	All system statistics
V$SYSTEM_PARAMETER	Information about the initialization parameters in effect
V$TABLESPACE	All tablespaces
V$TIMER	Internal clock, in hundredths of a second
V$TIMEZONE_NAMES	List of names and abbreviations of time zones
V$TRANSACTION	All active transactions
V$VERSION	Version information about installed Oracle products
V$WAITSTAT	Block contention statistics

DBA views

The views listed in the following table have database-wide information and are accessible only to DBA privileges.

View Name	Description
DBA_AUDIT_TRAIL	All entries of the audit trail
DBA_CATALOG	All indexes, tables, views, clusters, synonyms, and sequences
DBA_COL_COMMENTS	All column comments
DBA_COL_PRIVS	All column privileges
DBA_CONSTRAINTS	All constraints
DBA_DATA_FILES	All data files belonging to the database
DBA_DB_LINKS	All database links
DBA_EXTENTS	All extents
DBA_FREE_SPACE	Summary of all free space
DBA_INDEXES	Description of all indexes
DBA_IND_COLUMNS	Description of all index columns
DBA_IND_EXPRESSIONS	Expressions of all function-based indexes
DBA_LOCKS	All locks
DBA_MVIEWS	All materialized views
DBA_NESTED_TABLE_COLS	All columns of nested tables
DBA_NESTED_TABLES	All nested tables
DBA_OBJECTS	All database objects
DBA_RECYCLEBIN	All dropped objects in the recycle bin
DBA_ROLE_PRIVS	All roles, granted to users or other roles
DBA_ROLES	All roles
DBA_SEGMENTS	All segments (e.g., tables and indexes)
DBA_SEQUENCES	All sequences
DBA_SYNONYMS	All synonyms
DBA_SYS_PRIVS	All system privileges granted to users or roles
DBA_TABLES	Description of all tables
DBA_TABLESPACES	All tablespaces
DBA_TAB_COLS	Description of all columns
DBA_TAB_COLUMNS	Description of all columns, without hidden columns
DBA_TAB_COMMENTS	All table comments
DBA_TAB_PRIVS	All table privileges
DBA_TS_QUOTAS	All user quota, per tablespace
DBA_UPDATABLE_COLUMNS	All columns of updatable join views
DBA_USERS	Information about all users
DBA_VIEWS	Description of all views
DBA_WAITERS	All sessions waiting for something (locks)

USER View

Useful Shorthand Names for USER Views

View Name	Synonym For
CAT	USER_CATALOG
COLS	USER_TAB_COLUMNS
IND	USER_INDEXES
OBJ	USER_OBJECTS
RECYCLEBIN	USER_RECYCLEBIN
SEQ	USER_SEQUENCES
SYN	USER_SYNONYMS
TABS	USER_TABLES

The following table has USER Data Dictionary Views. Regular database users have access to these views.

View Name	Description
USER_AUDIT_TRAIL	Audit trail entries
USER_CATALOG	Indexes, tables, views, clusters, synonyms, and sequences
USER_COL_COMMENTS	Comments on your columns
USER_COL_PRIVS	Column privileges
USER_COL_PRIVS_MADE	Column privileges you made
USER_COL_PRIVS_RECD	Column privileges you received
USER_CONS_COLUMNS	Columns involved in constraints
USER_CONSTRAINTS	All your constraints
USER_EXTENTS	All extents
USER_FREE_SPACE	Available free space for creating objects
USER_INDEXES	Description of all your indexes
USER_IND_COLUMNS	Description of all your index columns
USER_IND_EXPRESSIONS	Description of all your function-based index expressions
USER_MVIEWS	All your materialized views
USER_NESTED_TABLE_COLS	All columns of your nested tables
USER_NESTED_TABLES	All your nested tables
USER_OBJECTS	All your object descriptions
USER_RECYCLEBIN	Your recycle bin
USER_ROLE_PRIVS	Roles granted to you
USER_SEGMENTS	Storage-related information about your objects
USER_SEQUENCES	All your sequences
USER_SYNONYMS	All your synonym definitions
USER_SYS_PRIVS	System privileges you have
USER_TABLES	Description of your tables
USER_TAB_COLS	Description of your table columns
USER_TAB_COLUMNS	Description of your table columns, without hidden columns
USER_TAB_COMMENTS	Comments on your tables
USER_TAB_PRIVS	Table privileges where you are involved
USER_TAB_PRIVS_MADE	Table privileges you made
USER_TAB_PRIVS_RECD	Table privileges where you received
USER_TABLESPACES	Accessible tablespaces
USER_TS_QUOTAS	Quota for accessible tablespaces
USER_UPDATABLE_COLUMNS	Columns of updatable join views
USER_USERS	Information
USER_VIEWS	Description of all your views

ALL views

The following table lists views that have information about all accessible data. Regular database users have access to these views.

View Name	Description
ALL_CATALOG	All accessible database objects
ALL_COL_COMMENTS	Comments for all accessible columns
ALL_COL_PRIVS	Column privileges where you are involved
ALL_COL_PRIVS_MADE	Column privileges where you are the grantor
ALL_COL_PRIVS_RECD	Column privileges where you are the grantee
ALL_CONS_COLUMNS	All accessible constraint columns
ALL_CONSTRAINTS	All accessible constraint definitions
ALL_INDEXES	Descriptions of all accessible indexes
ALL_IND_COLUMNS	Column descriptions of all accessible indexes
ALL_IND_EXPRESSIONS	Expressions of all accessible function-based indexes
ALL_MVIEWS	Descriptions for all accessible materialized views
ALL_NESTED_TABLE_COLS	All accessible columns of nested tables
ALL_NESTED_TABLES	All accessible nested tables
ALL_OBJECTS	All accessible database objects
ALL_SEQUENCES	All accessible sequences
ALL_SYNONYMS	Synonyms
ALL_TABLES	All accessible tables
ALL_TAB_COLS	Column descriptions for all accessible tables
ALL_TAB_COLUMNS	Column descriptions for all accessible tables without hidden columns
ALL_TAB_COMMENTS	All comments for accessible tables
ALL_TAB_PRIVS	Table privileges
ALL_TAB_PRIVS_MADE	Table privileges where you are object owner or grantor
ALL_TAB_PRIVS_RECD	Table privileges where you are the privilege grantee
ALL_UPDATABLE_COLUMNS	Accessible updatable join view columns
ALL_USERS	Information about all database users
ALL_VIEWS	Information about all accessible views

Creating Roles

A role is a group of privileges. The privileges can be assigned to a role, and then the role can be granted to multiple users and roles. When adding or deleting a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege. A user or role can have multiple roles. A role can have a password. The roles can help you manage multiple privileges assigned to multiple users.

Creating Roles

To create a role, you must have the CREATE ROLE system privilege.

GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;

You create a role using the CREATE ROLE statement. The default is to do both.

CREATE ROLE firstRole;
CREATE ROLE secondRole IDENTIFIED by password;

GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO firstRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO firstRole;
GRANT firstRole TO secondRole;

Synonyms

You can access tables in another schema by specifying the schema name followed by the table. For employee table in the store schema, store.employee is used. You can avoid referencing schema name by creating a synonym for that table. A Synonym is created by CREATE SYNONYM command.

GRANT CREATE SYNONYM TO steve;
CREATE SYNONYM employee FOR store.employee;

Object Privileges

An object privilege allows a user to perform certain actions on database objects. For example, a user can execute DML statements on tables.

Object Privilege	Allows a User to...
SELECT	Perform a select.
INSERT	Perform an insert.
UPDATE	Perform an update.
DELETE	Perform a delete.
EXECUTE	Execute a stored procedure.

Granting Object Privileges to a User

GRANT command grants an object privilege to a user.

GRANT SELECT, INSERT, UPDATE ON store.products TO tom;
GRANT SELECT ON store.employees TO tom;

The next example grants the UPDATE privilege on the last_name and sal columns to tom:

GRANT UPDATE (last_name, sal) ON store.employees TO tom;

The following example uses the GRANT option to enable a user to grant a privilege to another user.

GRANT SELECT ON store.employee TO tom WITH GRANT OPTION;

GRANT OPTION grants an object privilege to another user, and ADMIN OPTION grants a system privilege to another user.

The SELECT ON store.employee privilege can then be granted to another user by tom. The following example connects as tom and grants this privilege to another user:

CONNECT tom/password
GRANT SELECT ON store.employee TO anotherUser;

System Privileges

A system privilege allows a user to perform certain actions within the database, such as executing DDL statements.

The privileges can be grouped together into roles. Two useful roles to grant to a user are CONNECT and RESOURCE. CONNECT allows a user to connect to the database. RESOURCE allows a user to create various database objects like tables.

Commonly Used System Privileges:

System Privilege	Allows You to
CREATE SESSION	Connect to a database.
CREATE SEQUENCE	Create a sequence
CREATE SYNONYM	Create a synonym.
CREATE TABLE	Create a table in the user's schema.
CREATE ANY TABLE	Create a table in any schema.
DROP TABLE	Drop a table from the user's schema.
DROP ANY TABLE	Drop a table from any schema.
CREATE PROCEDURE	Create a stored procedure.
EXECUTE ANY PROCEDURE	Execute a procedure in any schema.
CREATE USER	Create a user.
DROP USER	Drop a user.
CREATE VIEW	Create a view.

Granting System Privileges to a User

You use GRANT to grant a system privilege to a user.

GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO tom;

Creating a User

To create a user in the database, you use the CREATE USER statement. The simplified syntax for the CREATE USER statement:

CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temporary_tablespace];

• user_name is the name of the database user.
• password is the password for the database user.
• default_tablespace is the default tablespace where database objects are stored. If you omit a default tablespace, the default SYSTEM tablespace is used.
• temporary_tablespace is the default tablespace where temporary objects are stored.

Tablespaces are used by the database to store separate objects. The following example connects as system and creates a user named tom with a password of Jerry:

CONNECT system/manager

SQL> CREATE USER tom IDENTIFIED BY Jerry;

User created.

SQL>

The following example creates a user named tom and specifies a default and temporary tablespace:

SQL> CREATE USER tom IDENTIFIED BY yourPassword
  2  DEFAULT TABLESPACE users
  3  TEMPORARY TABLESPACE temp;

User created.

SQL>

To view all table spaces

SELECT tablespace_name FROM dba_tablespaces.

Query to list out the Schemas and the status of the products in Oracle Applications

SELECT   fou.oracle_id                                   "Oracle Id",

         fou.oracle_username                             "Schema Name",

         fa.application_short_name                       "Application Short Name",

         ft.application_name                             "Application Name",

         fa.product_code                                 "Product Code",

         fpi.product_version                             "Product Version",

         DECODE (fpi.status,

                 'I', 'Installed',

                 'N', 'Not Installed',

                 'S', 'Shared Install')                  "Installation Status",

         NVL (fpi.patch_level, '-- Not Available --')    "Patchset",

         TO_CHAR (fpi.last_update_date, 'dd-Mon-RRRR')   "Update Date"

    FROM fnd_oracle_userid fou,

         fnd_application fa,

         fnd_product_installations fpi,

         dba_users du,

         fnd_application_tl ft

   WHERE fpi.application_id = fa.application_id(+)

     AND fpi.oracle_id(+) = fou.oracle_id

     AND du.username(+) = fou.oracle_username

     AND ft.language(+) = 'US'

     AND ft.application_id(+) = fa.application_id

ORDER BY 2