How to set current value of a sequence without dropping/recreating

Oracle does not let you change directly the value of a sequence. If you need to change its value, you should re-create the sequence.
On the other hand, we can use a trick to change the value of a sequence without dropping/recreating it.

Let’s say we have a sequence and its value is 1000, and we want to set it to 500.

SQL> CREATE SEQUENCE test_seq

            INCREMENT BY 1

            START WITH 1000

            MINVALUE 1

            MAXVALUE 99999999;

SQL> SELECT last_number

       FROM user_sequences

      WHERE sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

1000

SQL> ALTER SEQUENCE test_seq INCREMENT BY -500;

SQL> SELECT test_seq.NEXTVAL FROM dual;

SQL> ALTER SEQUENCE test_seq INCREMENT BY 1;

SQL> SELECT last_number

       FROM   user_sequences

      WHERE  sequence_name = 'TEST_SEQ';

LAST_NUMBER

-----------

500

By this way, we can set the new value of the given sequence.

Just for fun and for easy use of that logic in the future, let’s create a procedure for it.

CREATE OR REPLACE PROCEDURE Set_Sequence_value

              ( pi_owner         IN VARCHAR2,

                pi_seq_name      IN VARCHAR2,

                pi_new_seq_value IN NUMBER)

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

    l_last_number            NUMBER;

    l_incr_by                NUMBER;

    CURSOR c_get_seq_param IS

    SELECT increment_by

      FROM all_sequences

     WHERE sequence_owner = UPPER (pi_owner)

       AND sequence_name = UPPER (pi_seq_name);

BEGIN

    OPEN c_get_seq_param;

    FETCH c_get_seq_param INTO l_incr_by;

    CLOSE c_get_seq_param;

    IF l_incr_by IS NOT NULL

    THEN

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.CURRVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY '||(pi_new_seq_value - l_last_number);

        EXECUTE IMMEDIATE 'SELECT '||pi_owner||'.'||pi_seq_name||'.NEXTVAL FROM DUAL' INTO l_last_number;

        EXECUTE IMMEDIATE 'ALTER SEQUENCE '||pi_owner||'.'||pi_seq_name||' INCREMENT BY ' || l_incr_by;

    END IF;

END Set_Sequence_value;

Note: In order to not affect the transaction control, the procedure is created under AUTONOMOUS_TRANSACTION.

Have a fun and thank you for your time to read this article.

How to Fix "ORA-12505, TNS: listener does not currently know of SID given in connect descriptor"

Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

To properly resolve this error and connect to the appropriate Oracle database, we’ll need to expound a bit on how Oracle behaves and, therefore, what is causing this issue in the first place.

SIDs vs SERVICE_NAMES
It is important to understand the (slight) difference between what Oracle defines as a SID compared to a SERVICE_NAME, as we’ll use this information to create a proper connection string later on.

In Oracle, the system identifier (or SID) is a local identifier of up to eight characters in length that is used to identify a particular database and differentiate it from other databases on the system.

Often the SID is the prefix word or DB_UNIQUE_NAME that precedes the DB_DOMAIN.

SERVICE_NAMES, on the other hand, represent the names by which database instances can be connected to. A SERVICE_NAME will typically follow the format of the SID followed by the database domain, like so: DB_UNIQUE_NAME.DB_DOMAIN

The TNS Listener
When a client is attempting to connect to an Oracle database, rather than connecting to the database directly, there is a broker service that intervenes and handles the connection request for the client.

This broker application is known as the listener and it performs the task of listening for incoming client requests. When a request is received, the listener processes and forwards that request onto the appropriate Oracle database server using a service handler, which just acts as the connection between the listener and the database server.

TNS Names and Configuration
When connecting to an Oracle database, typically your database server will have tnsnames.ora, which is a configuration file that informs the server about NET_SERVICE_NAMES which are valid database connections. By default, this file is located at ORACLE_HOME/network/admin.

For example, a NET_SERVICE_NAME descriptor in tnsnames.ora may be formatted like this:

myDatabaseNetService =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)(QUEUESIZE = 100))
    (CONNECT_DATA =
      (SERVICE_NAME = bookstore.company.com)
    )
  )

This would define a NET_SERVICE_NAME using the SERVICE_NAME we discussed earlier and connecting to localhost through port 1521.

Connection String
With a bit more knowledge about how Oracle actually connects to databases, we can now look at how connection strings are formatted.


Connect via TNS NAME
When connecting through a NET_SERVICE_NAME as specified in your tnsnames.ora config file, you must use the username, password, and then append the NET_SERVICE_NAME with the @ symbol, like so:

username/password@NET_SERVICE_NAME

Connect via NET_SERVICE_NAME
Thus, for our previous NET_SERVICE_NAME descriptor above, the actual NET_SERVICE_NAME we defined was myTestDatabaseNetService, so our connection string might look something like this:

john/Pass123@myTestDatabaseNetService

Connect via SERVICE_NAME
When connecting through a SERVICE_NAME, you’ll also need to add the host and port, along with the / symbol preceding the SERVICE_NAME itself:

username/password@host:port/SERVICE_NAME

Connect via SID
Finally, if connecting without a configured NET_SERVICE_NAME or even SERVICE_NAME, you can do so directly through the SID by using the : symbol instead of the / symbol as with the SERVICE_NAME connection string:

username/password@host:port:SID

Change a user's password in Oracle

Question: How do I change the password for a user in Oracle?

Answer: To change a user's password in Oracle, you need to execute the alter user command.

Syntax:
The syntax for changing a password in Oracle is:
ALTER USER user_name IDENTIFIED BY new_password;

Parameters or Arguments:
user_name - The user whose password you wish to change.
new_password - The new password to assign.

Example
Let's look at an example of how to change a password for a user in Oracle/PLSQL.

ALTER USER smith IDENTIFIED BY Pass05_NM;

This example would change the password for the user named smith and set the new password to Pass05_NM.

How to force a trace file to be created when an ORA error occurs?

There was an issue we ran into where one of the users ran out temporary tablespace (TEMP) we had a message in the database alert log that there oracle couldn’t extend temporary tablespace and we couldn’t find the cause and user who ran into the issue. So turning on an event for the error oracle will create a trace file which will contain user, machine, os, SQL which will allow DBA to find additional information.

Example:

Oracle docs note this about ORA-01652:
Error: RA-01652: unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


To turn trace on you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK LEVEL 3’;

It will write to the alert.log

Tue Jul 21 11:04:45 2009
Errors in file /u01/admin/TESTDB/udump/testdb_ora_17682588.trc:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

The trace file would contain the following information including the SQL statement:
….
oct: 2, prv: 0, sql: 7000000593f3dc8, psql: 700000057c3ec30, user: 420/TEST
O/S info: user: ajaffer, term: MYCOMPUTER, ospid: 3684:2444, machine: MYCOMPUTERNAME

To turn trace off you would run the following SQL:
ALTER system SET EVENTS ‘1652 TRACE NAME ERRORSTACK OFF’;

If one would like to set an event in the spfile, you can set by executing the DDL below. If there are multiple events then it can be set by they have to be “:” separated. In the example below event is set for ORA-01653 and ORA-01652
ALTER system SET event=’1653 TRACE NAME ERRORSTACK LEVEL 3:1652 TRACE NAME ERRORSTACK LEVEL 3′ scope=SPFILE;

To clear the event in the spfile, you can execute the following SQL
ALTER SYSTEM SET EVENT=” SCOPE=spfile;

One can similar turn trace on for various ora errors but be aware that some of them by caused internally from within Oracle example ORA-604 which you may not want to turn on an event for.

Thank you for reading and support.

Recompile the whole DB using Oracle's scripts utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. 

They are typically run after major database changes such as upgrade or downgrade or patches installation. 

They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper for the UTL_RECOMP package. UTL_RECOMP provides a more general recompilation interface, including options to recompile objects in a single schema. Please see the documentation for package UTL_RECOMP for more details. 

The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". 

The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

     0 - The level of parallelism is derived based on the CPU_COUNT parameter.

     1 - The recompilation is run serially, one object at a time.

     N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Thank you for the reading and support.

ORA-22950 cannot ORDER objects without MAP or ORDER method

The following is the cause of this error:

The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared.
But an object type, such as a some_type, which can have multiple attributes of various data types, has no predefined axis of comparison.
To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
The action you can take to resolve this issue: Define a MAP or ORDER method for the object type.

For more information, you can see https://docs.oracle.com/cd/B19306_01/appdev.102/b14260/adobjbas.htm#sthref211

ORA-02030 error when try to grant permissions to a v$ tables

Face with an error ORA-02030 when you try to give permission on a v$ view

SQL> grant select on v$session to hr;
grant select on v$session to hr
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Why it couldn’t give select privileges to a v$ view. If you tried to give permission to other v$views you will face same the error.

The problem is caused because of trying to give select privileges on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym.
If you want to give permission to a V$ view you must grant permissions like below:

SQL> grant select on v_$session to hr;
Grant succeeded.

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR is actually a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000).

RAISE_APPLICATION_ERROR can be used in either (or both) the executable section and the exception section of a PL/SQL program. The error number and message is displayed to the user.

Syntax:
RAISE_APPLICATION_ERROR ({error_number}, {error_message});

{error_number} - The Error number must be between -20000 and -20999

{error_message} - The Error message is the message you want to display when the error occurs


Using in Execution section

BEGIN
...
RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
...
END;


Using in  Exception section:

BEGIN
...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR (-20003, 'This is another user error message');
END;


Examples:

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
l_number = 2

Input:
DECLARE 
    l_number    NUMBER;
BEGIN
    SELECT 2 INTO l_number FROM DUAL;
    RAISE_APPLICATION_ERROR (-20001,'This is a user error message');
    dbms_output.put_line('l_number = '||l_number);
END;

Output: 
There is no output due to our ERROR
line 19: ORA-20001: This is a user error message

ORA-06512: at line 5

Oracle File extensions

SQL*Plus files

.sql - SQL script

.lst - spool file

PL/SQL files

.pls - PL/SQL source

.plb - PL/SQL binary

.pks - Package source or package specification

.pkb - Package binary or package body

.pck - Combined package specification plus body

Oracle database files

.dbf - database file

.log - Online Redo Log

.rdo - Online Redo Log

.arc - Archive log

SQL*Loader files

.ctl - Control file

.dat - Data file

.bad - Bad file

.dsc - Discard file

SQL*Net files

.ora - tnsnames.ora, sqlnet.ora, etc.

Forms files

.fmb - Forms binary

.fmt - Forms text

.fmx - Forms executable

Reports files

.rdf - contains a single report definition in binary format. 

        .rdf files are used to both run and edit reports.

.rep - contains a single report definition in binary format. 

          .rep files are used solely to run reports; you cannot edit a .rep file

.rex - contains a single report definition in text format. .REX files are portable.

Switching to a Different Schema

The following statement sets the schema of the current session to the schema name specified in the statement.

ALTER SESSION SET CURRENT_SCHEMA = <schema name>;

In subsequent SQL statements, Oracle Database uses this schema name as the schema qualifier when the qualifier is omitted. In addition, the database uses the temporary tablespace of the specified schema for sorts, joins, and storage of temporary database objects. The session retains its original privileges and does not acquire any extra privileges by the preceding ALTER SESSION statement.

You can use this command above to avoid the use of public synonyms.  By setting the CURRENT_SCHEMA attribute to the schema owner name it is not necessary to create public synonyms for production table names. Without the current_schema syntax, you would have to assign a public synonym for every table in your production database.

If you want to check the current schema of the session you can use the following statement:

SELECT sys_context( 'userenv', 'current_schema' ) from dual; 

Let's look in the following example:

CONNECT user1

ALTER SESSION SET CURRENT_SCHEMA = schema_2;

SELECT * FROM empoyers;

Because user1 is not schema-qualified, the table name is resolved under schema schema_2. But if user1 does not have select privilege on table schema_2.empoyers, then user1 cannot execute the SELECT statement even switching the current session schema.

ROWID vs. UROWID in Oracle DB

Each row in the database has an address. The following describe the two forms of row address in an Oracle Database - rowid and urowid.

ROWID Data Type
The rows in heap-organized tables that are native to Oracle Database have row addresses called rowids. You can examine a rowid row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. Oracle Database does not guarantee that the values of such columns are valid rowids. Refer to Chapter 2, "Pseudocolumns" for more information on the ROWID pseudocolumn.

Rowids contain the following information:

PGA usage by sessions

Summary 

Each user session contains its own PGA taken from the operating system free memory pool. As sessions have the need to use PGA memory, the Oracle database will allocate the session memory from the PGA until the total allocation of memory is equal to the pga_aggregate_target value set within the database. As sessions require additional memory from the PGA, Oracle will pull the memory from other sessions that no longer need the memory.

SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') 
       || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'),'HH24:MI:SS') LOGON,
       SID,
       v$session.SERIAL#,
       v$process.SPID,
       ROUND(v$process.pga_used_mem / (1024 * 1024), 2) PGA_MB_USED,
       v$session.USERNAME,
       STATUS,
       OSUSER,
       MACHINE,
       v$session.PROGRAM,
       MODULE
  FROM v$session, v$process
 WHERE v$session.paddr = v$process.addr
--AND status = 'ACTIVE' 
--AND module = 'ECR'
--AND v$session.sid = 452
--AND v$session.username = 'MR54' 
--AND v$process.spid = 5698
 ORDER BY pga_used_mem DESC;

To find the total PGA memory used by processes
SELECT ROUND(SUM(pga_used_mem) / (1024 * 1024), 2) PGA_USED_MB
  FROM v$process;

To find PGA usage for a specific session
SELECT SID, b.NAME, ROUND(a.VALUE / (1024 * 1024), 2) MB
  FROM v$sesstat a, v$statname b
 WHERE (NAME LIKE '%session uga memory%' OR
       NAME LIKE '%session pga memory%')
   AND a.statistic# = b.statistic#
   AND SID = 596;


To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:
SELECT &MAX_CONNECTED_SESSIONS * (2048576 + P1.VALUE + P2.VALUE) /
       (1024 * 1024) YOU_NEED_PGA_MB
  FROM V$PARAMETER P1, V$PARAMETER P2
 WHERE P1.NAME = 'sort_area_size'
   AND P2.NAME = 'hash_area_size';

V$PGASTAT provides PGA memory usage statistics as well as statistics about the automatic PGA memory manager when it is enabled (that is, when PGA_AGGREGATE_TARGET is set). Cumulative values in V$PGASTAT are accumulated since instance startup.

To query the contents of the current PGA settings within Oracle 11g, issue a request against the v$pgastat dynamic performance view as shown:
SELECT *  FROM V$PGASTAT; 

NAME                                   VALUE               UNIT
---------------------------------------------------------------- 
aggregate PGA target parameter         163577856           bytes
aggregate PGA auto target              80363520            bytes
global memory bound                    32714752            bytes
total PGA inusen                       74283008            bytes
total PGA allocated                    156244992           bytes
maximum PGA allocated                  240105472           bytes
total freeable PGA memory              9240576             bytes
process count                          30
max processes count                    41
PGA memory freed back to OS            273874944           bytes
total PGA used for auto workareas      0                   bytes
maximum PGA used for auto workareas    7929856             bytes
total PGA used for manual workareas    0                   bytes
maximum PGA used for manual workareas  270336              bytes
over allocation count                  0       
bytes processed                        581040128           bytes
extra bytes read/written               0                   bytes
cache hit percentage                   100 percent
recompute count (total)                717


19 rows selected.


To change PGA memory parameter
ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;

How to Grant/Revoke privileges

Data control language (DCL)

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (Authorization). In particular, it is a component of Structured Query Language (SQL).

Examples of DCL commands include:

GRANT    to allow specified users to perform specified tasks.
REVOKE  to cancel previously granted or denied permissions.

The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML), and may include CONNECT, SELECT, INSERT, UPDATE, DELETE, EXECUTE, and USAGE.

In the Oracle database, executing a DCL command issues an implicit commit. Hence you cannot roll back the command.

Privileges

When multiple users can access database objects, authorization can be controlled to these objects with privileges. Every object has an owner. Privileges control if a user can modify an object owned by another user. Privileges are granted or revoked either by the instance administrator, a user with the ADMIN privilege or, for privileges to a certain object, by the owner of the object.

There are two type of Privileges: System Privileges and Object privileges


System privileges
A system privilege is the right to perform a particular action or to perform an action on any object of a particular type. Objects include tables, views, materialized views, synonyms, indexes, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages. Only the instance administrator or a user with ADMIN privilege can grant or revoke system privileges.
System Privileges are normally granted by a DBA to users.


System privileges are

Privilege	Description
ADMIN	Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion.
ALTER ANY CACHE GROUP	Enables a user to alter any cache group in the database.
ALTER ANY INDEX	Enables a user to alter any index in the database.
	Note: There is no ALTER INDEX statement.
ALTER ANY MATERIALIZED VIEW	Enables a user to alter any materialized view in the database.
	Note: There is no ALTER MATERIALIZED VIEW statement.
ALTER ANY PROCEDURE	Enables a user to alter any PL/SQL procedure, function or package in the database.
ALTER ANY SEQUENCE	Enables a user to alter any sequence in the database.
	Note: There is no ALTER SEQUENCE statement.
ALTER ANY TABLE	Enables a user to alter any table in the database.
ALTER ANY VIEW	Enables a user to alter any view in the database.
	Note: There is no ALTER VIEW statement.
CACHE_MANAGER	Enables a user to perform operations related to cache groups.
CREATE ANY CACHE GROUP	Enables a user to create a cache group owned by any user in the database.
CREATE ANY INDEX	Enables a user to create an index on any table or materialized view in the database.
CREATE ANY MATERIALIZED VIEW	Enables a user to create a materialized view owned by any user in the database.
CREATE ANY PROCEDURE	Enables a user to create a PL/SQL procedure, function or package owned by any user in the database.
CREATE ANY SEQUENCE	Enables a user to create a sequence owned by any user in the database.
CREATE ANY SYNONYM	Enables a user to create a private synonym owned by any user in the database.
CREATE ANY TABLE	Enables a user to create a table owned by any user in the database.
CREATE ANY VIEW	Enables a user to create a view owned by any user in the database.
CREATE CACHE GROUP	Enables a user to create a cache group owned by that user.
CREATE MATERIALIZED VIEW	Enables a user to create a materialized view owned by that user.
CREATE PROCEDURE	Enables a user to create a PL/SQL procedure, function or package owned by that user.
CREATE PUBLIC SYNONYM	Enables a user to create a public synonym.
CREATE SEQUENCE	Enables a user to create a sequence owned by that user.
CREATE SESSION	Enables a user to create a connection to the database.
CREATE SYNONYM	Enables a user to create a private synonym.
CREATE TABLE	Enables a user to create a table owned by that user.
CREATE VIEW	Enables a user to create a view owned by that user.
DELETE ANY TABLE	Enables a user to delete from any table in the database.
DROP ANY CACHE GROUP	Enables a user to drop any cache group in the database.
DROP ANY INDEX	Enables a user to drop any index in the database.
DROP ANY MATERIALIZED VIEW	Enables a user to drop any materialized view in the database.
DROP ANY PROCEDURE	Enables a user to drop any PL/SQL procedure, function or package in the database.
DROP ANY SEQUENCE	Enables a user to drop any sequence in the database.
DROP ANY SYNONYM	Enables a user to drop a synonym owned by any user in the database.
DROP ANY TABLE	Enables a user to drop any table in the database.
DROP ANY VIEW	Enables a user to drop any view in the database.
DROP PUBLIC SYNONYM	Enables a user to drop a public synonym.
EXECUTE ANY PROCEDURE	Enables a user to execute any PL/SQL procedure, function or package in the database.
FLUSH ANY CACHE GROUP	Enables a user to flush any cache group in the database.
INSERT ANY TABLE	Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table.
LOAD ANY CACHE GROUP	Enables a user to load any cache group in the database.
REFRESH ANY CACHE GROUP	Enables a user to flush any cache group in the database.
SELECT ANY SEQUENCE	Enables a user to select from any sequence or synonym on a sequence in the database.
SELECT ANY TABLE	Enables a user to select from any table, view, materialized view, or synonym in the database.
UNLOAD ANY CACHE GROUP	Enables a user to unload any cache group in the database.
UPDATE ANY TABLE	Enables a user to update any table or synonym in the database.
XLA	Enables a user to connect to a database as an XLA reader.

Object Privileges

Object privileges mean privileges on tables, views, materialized views, indexes, synonyms, sequences, cache groups, replication schemes and PL/SQL functions, procedures and packages.

An object's owner has all object privileges for that object, and those privileges cannot be revoked. The object's owner can grant object privileges for that object to other database users. A user with ADMIN privilege can grant and revoke object privileges from users who do not own the objects on which the privileges are granted.

Object Privileges are

Privilege	Object type	Description
DELETE	Table	Enables a user to delete from a table.
EXECUTE	PL/SQL package, procedure or function	Enables a user to execute a PL/SQL package, procedure or function directly.
FLUSH	Cache group	Enables a user to flush a cache group.
INDEX	Table or materialized view	Enables a user to create an index on a table or materialized view.
INSERT	Table or synonym	Enables a user to insert into a table or into the table through a synonym.
LOAD	Cache group	Enables a user to load a cache group.
REFERENCES	Table or materialized view	Enables a user to create a foreign key dependency on a table or materialized view. The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table.
REFRESH	Cache group	Enables a user to refresh a cache group.
SELECT	Table, sequence, view, materialized view, or synonym	Enables a user to select from a table, sequence, view, materialized view, or synonym. The SELECT privilege enables a user to perform all operations on a sequence. A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table.
UNLOAD	Cache group	Enables a user to unload a cache group.
UPDATE	Table	Enables a user to update a table.

Dictionary views which show the granted privileges and roles

DBA_SYS_PRIVS - contains information about System privileges
DBA_TAB_PRIVS  - contains information about Object Privileges
DBA_ROLE_PRIVS - contains information about assigned rows;

* You can use user_sys_priv, user_tab_privs and user_role_privs to list the privileges information to the current user.

Examples

To grant the SELECT privilege on table tab1 to the authorization IDs maria and harry, use the following syntax:
GRANT SELECT ON TABLE tab1  TO maria,harry 


To grant the UPDATE and TRIGGER privileges on table tab1  to the authorization IDs anita and zhi, use the following syntax:
GRANT UPDATE, TRIGGER ON TABLE tab1  TO anita,zhi 


To grant the SELECT privilege on table tab1  to all users, use the following syntax:
GRANT SELECT ON TABLE tab1 to PUBLIC


To grant the EXECUTE privilege on procedure proc_1 to the authorization ID george, use the following syntax:
GRANT EXECUTE ON PROCEDURE proc_1 TO george


To grant the role purchases_reader_role to the authorization IDs george and maria, use the following syntax:
GRANT purchases_reader_role TO george,maria


To grant the SELECT privilege on table tab_1 to the role purchases_reader_role, use the following syntax:
GRANT SELECT ON TABLE tab_1 TO purchases_reader_role


To grant the USAGE privilege on the sequence generator order_id to the role sales_role, use the following syntax:
GRANT USAGE ON SEQUENCE order_id TO sales_role;


To grant the USAGE privilege on the user-defined type price to the role finance_role, use the following syntax:
GRANT USAGE ON TYPE price TO finance_role;


The following statement revokes the DROP ANY TABLE system privilege from the users george and oe:
REVOKE DROP ANY TABLE FROM hr, oe;


The following statement revokes the role dw_manager from the user sh:
REVOKE dw_manager FROM sh;


The following statement revokes the CREATE TABLESPACE system privilege from the dw_manager role:
REVOKE CREATE TABLESPACE  FROM dw_manager;


To revoke the DELETE privilege on orders from hr, issue the following statement:
REVOKE DELETE  ON orders FROM hr;