ORACLE/PLSQL: UNIQUE CONSTRAINTS

This Oracle tutorial explains how to create, drop, disable, and enable unique constraints in Oracle with syntax and examples.

WHAT IS A UNIQUE CONSTRAINT IN ORACLE?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

NOTE

In Oracle, a unique constraint can not contain more than 32 columns.

A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

WHAT IS THE DIFFERENCE BETWEEN A UNIQUE CONSTRAINT AND A PRIMARY KEY?

Primary Key	Unique Constraint
None of the fields that are part of the primary key can contain a null value.	Some of the fields that are part of the unique constraint can contain null values as long as the combination of values is unique.

Oracle does not permit you to create both a primary key and unique constraint with the same columns.

CREATE UNIQUE CONTRAINT - USING A CREATE TABLE STATEMENT

The syntax for creating a unique constraint using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);

Example

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_unique UNIQUE (supplier_id)
);

In this example, we've created a unique constraint on the supplier table called supplier_unique. It consists of only one field - the supplier_id field.

We could also create a unique constraint with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);

CREATE UNIQUE CONTRAINT - USING AN ALTER TABLE STATEMENT

The syntax for creating a unique constraint in an ALTER TABLE statement is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Example

ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

In this example, we've created a unique constraint on the existing supplier table called supplier_unique. It consists of the field called supplier_id.

We could also create a unique constraint with more than one field as in the example below:

ALTER TABLE supplier
ADD CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name);

DROP UNIQUE CONSTRAINT

The syntax for dropping a unique constraint is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE supplier
DROP CONSTRAINT supplier_unique;

In this example, we're dropping a unique constraint on the supplier table called supplier_unique.

DISABLE UNIQUE CONSTRAINT

The syntax for disabling a unique constraint is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE supplier
DISABLE CONSTRAINT supplier_unique;

In this example, we're disabling a unique constraint on the supplier table called supplier_unique.

ENABLE UNIQUE CONSTRAINT

The syntax for enabling a unique constraint is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE supplier
ENABLE CONSTRAINT supplier_unique;

In this example, we're enabling a unique constraint on the supplier table called supplier_unique.

ORACLE/PLSQL: CHECK CONSTRAINTS

This Oracle tutorial explains how to use the check constraints in Oracle with syntax and examples.

WHAT IS A CHECK CONSTRAINT IN ORACLE?

A check constraint allows you to specify a condition on each row in a table.

NOTE

A check constraint can NOT be defined on a SQL View.
The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
A check constraint can NOT include a SQL Subquery.

A check constraint can be defined in either a SQL CREATE TABLE statement or a SQL ALTER TABLE statement.

USING A CREATE TABLE STATEMENT

The syntax for creating a check constraint using a CREATE TABLE statement in Oracle is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,

  ...

  CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]

);

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

Example

CREATE TABLE suppliers
(
  supplier_id numeric(4),
  supplier_name varchar2(50),
  CONSTRAINT check_supplier_id
  CHECK (supplier_id BETWEEN 100 and 9999)
);

In this first example, we've created a check constraint on the suppliers table called check_supplier_id. This constraint ensures that the supplier_id field contains values between 100 and 9999.

CREATE TABLE suppliers
(
  supplier_id numeric(4),
  supplier_name varchar2(50),
  CONSTRAINT check_supplier_name
  CHECK (supplier_name = upper(supplier_name))
);

In this second example, we've created a check constraint called check_supplier_name. This constraint ensures that the supplier_name column always contains uppercase characters.

USING AN ALTER TABLE STATEMENT

The syntax for creating a check constraint in an ALTER TABLE statement in Oracle is:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE];

The DISABLE keyword is optional. If you create a check constraint using the DISABLE keyword, the constraint will be created, but the condition will not be enforced.

Example

ALTER TABLE suppliers
ADD CONSTRAINT check_supplier_name
  CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));

In this example, we've created a check constraint on the existing suppliers table called check_supplier_name. It ensures that the supplier_name field only contains the following values: IBM, Microsoft, or NVIDIA.

DROP A CHECK CONSTRAINT

The syntax for dropping a check constraint is:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
DROP CONSTRAINT check_supplier_id;

In this example, we're dropping a check constraint on the suppliers table called check_supplier_id.

ENABLE A CHECK CONSTRAINT

The syntax for enabling a check constraint in Oracle is:

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
ENABLE CONSTRAINT check_supplier_id;

In this example, we're enabling a check constraint on the suppliers table called check_supplier_id.

DISABLE A CHECK CONSTRAINT

The syntax for disabling a check constraint in Oracle is:

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

Example

ALTER TABLE suppliers
DISABLE CONSTRAINT check_supplier_id;

In this example, we're disabling a check constraint on the suppliers table called check_supplier_id.

ORACLE/PLSQL: INDEXES

This Oracle tutorial explains how to create, rename and drop indexes in Oracle with syntax and examples.

WHAT IS AN INDEX IN ORACLE?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

CREATE AN INDEX

Syntax

The syntax for creating a index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n)
  [ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

index_name is the name to assign to the index.

table_name is the name of the table in which to create the index.

column1, column2, ... column_n are the columns to use in the index.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example

Let's look at an example of how to create an index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;

CREATE A FUNCTION-BASED INDEX

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

Syntax

The syntax for creating a function-based index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, . function_n)
  [ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

index_name is the name to assign to the index.

table_name is the name of the table in which to create the index.

function1, function2, ... function_n are the functions to use in the index.

Example

Let's look at an example of how to create a function-based index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

RENAME AN INDEX

Syntax

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name
  RENAME TO new_index_name;

index_name is the name of the index that you wish to rename.

new_index_name is the new name to assign to the index.

Example

Let's look at an example of how to rename an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

COLLECT STATISTICS ON AN INDEX

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

Syntax

The syntax for collecting statistics on an index in Oracle/PLSQL is:

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

index_name is the index in which to collect statistics.

Example

Let's look at an example of how to collect statistics for an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  REBUILD COMPUTE STATISTICS;

In this example, we're collecting statistics for the index called supplier_idx.

DROP AN INDEX

Syntax

The syntax for dropping an index in Oracle/PLSQL is:

DROP INDEX index_name;

index_name is the name of the index to drop.

Example

Let's look at an example of how to drop an index in Oracle/PLSQL.

For example:

DROP INDEX supplier_idx;

In this example, we're dropping an index called supplier_idx.

ORACLE/PLSQL: GRANT/REVOKE PRIVILEGES

This Oracle tutorial explains how to grant and revoke privileges in Oracle with syntax and examples.

DESCRIPTION

You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.

GRANT PRIVILEGES ON TABLE

You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for granting privileges on a table in Oracle is:

GRANT privileges ON object TO user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object is the name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.

user is the name of the user that will be granted these privileges.

Example

Let's look at some examples of how to grant privileges on tables in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named smithj. For example:

GRANT ALL ON suppliers TO smithj;

If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:

GRANT SELECT ON suppliers TO public;

REVOKE PRIVILEGES ON TABLE

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for revoking privileges on a table in Oracle is:

REVOKE privileges ON object FROM user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object is the name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user is the name of the user that will have these privileges revoked.

Example

Let's look at some examples of how to revoke privileges on tables in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a user named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON suppliers FROM anderson;

If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:

REVOKE ALL ON suppliers FROM anderson;

If you had granted ALL privileges to public (all users) on the suppliers table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE ALL ON suppliers FROM public;

GRANT PRIVILEGES ON FUNCTIONS/PROCEDURES

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

EXECUTE means the following:

Privilege	Description
EXECUTE	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

object is the name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be granted the EXECUTE privileges.

Example

Let's look at some examples of how to grant EXECUTE privileges on a function or procedure in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the user named smithj, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO smithj;

If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO public;

REVOKE PRIVILEGES ON FUNCTIONS/PROCEDURES

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.

Syntax

The syntax for the revoking privileges on a function or procedure in Oracle is:

REVOKE EXECUTE ON object FROM user;

EXECUTE means the following:

Privilege	Description
EXECUTE	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

object is the name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be revoked the EXECUTE privileges.

Example

Let's look at some examples of how to revoke EXECUTE privileges on a function or procedure in Oracle.

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a user named anderson, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM anderson;

If you had granted EXECUTE privileges to public (all users) on the function called Find_Value and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON Find_Value FROM public;

ORACLE/PLSQL: SYNONYMS

This Oracle tutorial explains how to create and drop synonyms in Oracle with syntax and examples.

DESCRIPTION

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

CREATE SYNONYM (OR REPLACE)

You may wish to create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query.

Syntax

The syntax to create a synonym in Oracle is:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name
  FOR [schema .] object_name [@ dblink];

OR REPLACE allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

PUBLIC means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

schema is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

object_name is the name of the object for which you are creating the synonym. It can be one of the following:

table
view
sequence
stored procedure
function
package
materialized view
java class schema object
user-defined object
synonym

Example

Let's look at an example of how to create a synonym in Oracle.

For example:

CREATE PUBLIC SYNONYM suppliers
FOR app.suppliers;

This first CREATE SYNONYM example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app. For example:

SELECT *
FROM suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the OR REPLACE phrase as follows:

CREATE OR REPLACE PUBLIC SYNONYM suppliers
FOR app.suppliers;

DROP SYNONYM

Once a synonym has been created in Oracle, you might at some point need to drop the synonym.

Syntax

The syntax to drop a synonym in Oracle is:

DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];

PUBLIC allows you to drop a public synonym. If you have specified PUBLIC, then you don't specify a schema.

force will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use force as it can cause invalidation of Oracle objects.

Example

Let's look at an example of how to drop a synonym in Oracle.

For example:

DROP PUBLIC SYNONYM suppliers;

This DROP statement would drop the synonym called suppliers that we defined earlier.

ORACLE/PLSQL: ROLES

This Oracle tutorial explains how to create roles, grant/revoke privileges to roles, enable/disable roles, set roles as the default, and drop roles in Oracle with syntax and examples.

DESCRIPTION

A role is a set or group of privileges that can be granted to users or another role. This is a great way for database administrators to save time and effort.

CREATE ROLE

You may wish to create a role so that you can logically group the users' permissions. Please note that to create a role, you must have CREATE ROLE system privileges.

Syntax

The syntax for creating a role in Oracle is:

CREATE ROLE role_name
[ NOT IDENTIFIED | 
IDENTIFIED {BY password | USING [schema.] package | EXTERNALLY | GLOBALLY } ;

role_name is the name of the new role that you are creating. This is how you will refer to the grouping of privileges.

NOT IDENTIFIED means that the role is immediately enabled. No password is required to enable the role.

IDENTIFIED means that a user must be authorized by a specified method before the role is enabled.

BY password means that a user must supply a password to enable the role.

USING package means that you are creating an application role - a role that is enabled only by applications using an authorized package.

EXTERNALLY means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

GLOBALLY means that a user must be authorized by the enterprise directory service to enable the role.

Note

If both NOT IDENTIFIED and IDENTIFIED are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

Example

Let's look at an example of how to create a role in Oracle.

For example:

CREATE ROLE test_role;

This first example creates a role called test_role.

CREATE ROLE test_role
IDENTIFIED BY test123;

This second example creates the same role called test_role, but now it is password protected with the password of test123.

GRANT TABLE PRIVILEGES TO ROLE

Once you have created the role in Oracle, your next step is to grant privileges to that role.

Just as you granted privileges to users, you can grant privileges to a role. Let's start with granting table privileges to a role. Table privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for granting table privileges to a role in Oracle is:

GRANT privileges ON object TO role_name

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object is the name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.

role_name is the name of the role that will be granted these privileges.

Example

Let's look at some examples of how to grant table privileges to a role in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a role named test_role, you would run the following GRANT statement:

GRANT select, insert, update, delete ON suppliers TO test_role;

You can also use the ALL keyword to indicate that you wish all permissions to be granted. For example:

GRANT all ON suppliers TO test_role;

REVOKE TABLE PRIVILEGES FROM ROLE

Once you have granted table privileges to a role, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.

Syntax

The syntax for revoking table privileges from a role in Oracle is:

REVOKE privileges ON object FROM role_name;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

object is the name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

role_name is the name of the role that will have these privileges revoked.

Example

Let's look at some examples of how to revoke table privileges from a role in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a role named test_role, you would run the following REVOKE statement:

REVOKE delete ON suppliers FROM test_role;

If you wanted to revoke ALL privileges on the table called suppliers from a role named test_role, you could use the ALL keyword. For example:

REVOKE all ON suppliers FROM test_role;

GRANT FUNCTION/PROCEDURE PRIVILEGES TO ROLE

When dealing with functions and procedures, you can grant a role the ability to EXECUTE these functions and procedures.

Syntax

The syntax for granting EXECUTE privileges on a function/procedure to a role in Oracle is:

GRANT EXECUTE ON object TO role_name;

EXECUTE means the following:

Privilege	Description
EXECUTE	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

role_name is the name of the role that will be granted the EXECUTE privileges.

Example

Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a role in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the role named test_role, you would run the following GRANT statement:

GRANT execute ON Find_Value TO test_role;

REVOKE FUNCTION/PROCEDURE PRIVILEGES FROM ROLE

Once you have granted EXECUTE privileges on a function or procedure to a role, you may need to revoke these privileges from that role. To do this, you can execute a REVOKE command.

Syntax

The syntax for the revoking privileges on a function or procedure from a role in Oracle is:

REVOKE execute ON object FROM role_name;

EXECUTE means the following:

Privilege	Description
EXECUTE	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

role_name is the name of the role that will have the EXECUTE privileges revoked.

Example

Let's look at an example of how to grant EXECUTE privileges on a function or procedure to a role in Oracle.

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a role named test_role, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM test_role;

GRANT ROLE TO USER

Now, that you've created the role and assigned the privileges to the role, you'll need to grant the role to specific users.

Syntax

The syntax to grant a role to a user in Oracle is:

GRANT role_name TO user_name;

role_name is the name of the role that you wish to grant.

user_name is the name of the user that will be granted the role.

Example

Let's look at an example of how to grant a role to a user in Oracle:

GRANT test_role TO smithj;

This example would grant the role called test_role to the user named smithj.

ENABLE/DISABLE ROLE (SET ROLE STATEMENT)

To enable or disable a role for a current session, you can use the SET ROLE statement.

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

Syntax

The syntax for the SET ROLE statement in Oracle is:

SET ROLE
( role_name [ IDENTIFIED BY password ] | ALL [EXCEPT role1, role2, ... ] | NONE );

role_name is the name of the role that you wish to enable.

IDENTIFIED BY password is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.

ALL means that all roles should be enabled for this current session, except those listed in EXCEPT.

NONE disables all roles for the current session (including all default roles).

Example

Let's look at an example of how to enable a role in Oracle.

For example:

SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

SET ROLE AS DEFAULT ROLE

A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT ROLE, you need to issue the ALTER USER statement.

Syntax

The syntax for setting a role as a DEFAULT ROLE in Oracle is:

ALTER USER user_name
DEFAULT ROLE
( role_name | ALL [EXCEPT role1, role2, ... ] | NONE );

user_name is the name of the user whose role you are setting as DEFAULT.

role_name is the name of the role that you wish to set as DEFAULT.

ALL means that all roles should be enabled as DEFAULT, except those listed in EXCEPT.

NONE disables all roles as DEFAULT.

Example

Let's look at an example of how to set a role as a DEFAULT ROLE in Oracle.

For example:

ALTER USER smithj
DEFAULT ROLE
test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

ALTER USER smithj
DEFAULT ROLE
ALL;

This example would set all roles assigned to smithj as DEFAULT.

ALTER USER smithj
DEFAULT ROLE
ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.

DROP ROLE

Once a role has been created in Oracle, you might at some point need to drop the role.

Syntax

The syntax to drop a role in Oracle is:

DROP ROLE role_name;

role_name is the name of the role that is to be dropped.

Example

Let's look at an example of how to drop a role in Oracle.

For example:

DROP ROLE test_role;

This DROP statement would drop the role called test_role that we defined earlier.

ORACLE/PLSQL: CREATE USER STATEMENT

This Oracle tutorial explains how to use the Oracle CREATE USER statement with syntax and examples.

DESCRIPTION

The CREATE USER statement creates a database account that allows you to log into the Oracle database.

SYNTAX

The syntax for the CREATE USER statement in Oracle/PLSQL is:

CREATE USER user_name 
  IDENTIFIED { BY password
             | EXTERNALLY [ AS 'certificate_DN' ]
             | GLOBALLY [ AS '[ directory_DN ]' ]
             }
  [ DEFAULT TABLESPACE tablespace
  | TEMPORARY TABLESPACE
       { tablespace | tablespace_group }
  | QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
        ON tablespace
    [ QUOTA integer [ K | M | G | T | P | E ]
        | UNLIMITED }
            ON tablespace
    ]
  | PROFILE profile_name
  | PASSWORD EXPIRE
  | ACCOUNT { LOCK | UNLOCK }
     [ DEFAULT TABLESPACE tablespace
     | TEMPORARY TABLESPACE
         { tablespace | tablespace_group }
     | QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
       [ QUOTA integer [ K | M | G | T | P | E ]
           | UNLIMITED }
           ON tablespace
        ]
     | PROFILE profile
     | PASSWORD EXPIRE
     | ACCOUNT { LOCK | UNLOCK } ]
     ] ;

Parameters or Arguments

user_name is the name of the database account that you wish to create.

PROFILE profile_name is optional. It is the name of the profile that you wish to assign to the user account to limit the amount of database resources assigned to the user account. If you omit this option, the DEFAULT profile is assigned to the user.

PASSWORD EXPIRE is optional. If this option is set, then the password must be reset before the user can log into the Oracle database.

ACCOUNT LOCK or UNLOCK is optional. ACCOUNT LOCK disables access to the user account. ACCOUNT UNLOCK enables access to the user account.

EXAMPLE

If you wanted to execute a simple CREATE USER statement that creates a new user and assigns a password, you could do the following:

For example:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

This CREATE USER statement would create a new user called smithj in the Oracle database whose password ispwd4smithj, the default tablespace would be tbs_perm_01 with a quota of 20MB, and the temporary tablespace would betbs_temp_01.

If you wanted to make sure that the user changed their password before logging into the database, you could add the PASSWORD EXPIRE option as follows:

CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01
  PASSWORD EXPIRE;

External Database User

To create an External Database user, you could execute the following CREATE USER statement:

CREATE USER external_user1
  IDENTIFIED EXTERNALLY
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 5M on tbs_perm_01
  PROFILE external_user_profile;

This CREATE USER statement would create an External Database user called external_user1 that has a default tablespace of tbs_perm_01 with a quote of 5MB, and is limited by the database resources assigned to external_user_profile.

To create an External Database user that is only accessible by an operating system account, you could run the following CREATE USER statement:

CREATE USER ops$external_user1
  IDENTIFIED EXTERNALLY
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 5M on tbs_perm_01
  PROFILE external_user_profile;

Note that the only difference between this CREATE USER statement and the previous is the ops$ in front of the user_name.

Global Database User

To create a Global Database user, you could execute the following CREATE USER statement:

CREATE USER global_user1
  IDENTIFIED GLOBALLY AS 'CN=manager, OU=division, O=oracle, C=US'
  DEFAULT TABLESPACE tbs_perm_01
  QUOTA 10M on tbs_perm_01;

This CREATE USER statement would create a Global Database user called global_user1 that has a default tablespace oftbs_perm_01 with a quote of 10M.

ORACLE/PLSQL: 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 is the user whose password you wish to change.

new_password is the new password to assign.

EXAMPLE

If you wanted to reset the password for a user named smithj, and you wanted to set the new password to autumn, you would run the following command:

ALTER USER smithj IDENTIFIED BY autumn;

ORACLE/PLSQL: DROP USER STATEMENT

This Oracle tutorial explains how to use the Oracle DROP USER statement with syntax and examples.

DESCRIPTION

The DROP USER statement is used to remove a user from the Oracle database and remove all objects owned by that user.

SYNTAX

The syntax for the DROP USER statement in Oracle/PLSQL is:

DROP USER user_name [ CASCADE ];

Parameters or Arguments

user_name is the name of the user to remove from the Oracle database.

CASCADE is optional. If user_name owns any objects (ie: tables or views in its schema), you must specify CASCADE to drop all of these objects.

EXAMPLE

Let's look at a simple DROP USER statement.

If the user does not own any objects in its schema, you could execute the following DROP USER statement:

DROP USER smithj;

This would drop the user called smithj. This DROP USER statement will only run if smithj does not own any objects in its schema.

If smithj did own objects in its schema, you would need to run the following DROP USER statement instead:

DROP USER smithj CASCADE;

This DROP USER statement would remove the user smithj, drop all objects (ie: tables and views) owned by smithj, and all referential integrity constraints on smithj's objects would also be dropped.

ORACLE/PLSQL: FIND USERS IN ORACLE/PLSQL

Question:Is there a query to run in Oracle/PLSQL that will return all Users created?

Answer: In Oracle/PLSQL, there is a system table called ALL_USERS that displays all users visible to the current user.

To retrieve all Users that are visible to the current user in Oracle/PLSQL, you can execute the following SQL statement:

SELECT *
FROM ALL_USERS;

The ALL_USERS table contains the following columns:

Column	Explanation
USERNAME	Name of the user
USER_ID	Numeric ID assigned to the user
CREATED	Date that user was created

If you need to find out all users that exist in Oracle or require more information about the user, there is also another system table called DBA_USERS.

To retrieve all Users in the Oracle database, you can execute the following SQL statement:

SELECT *
FROM DBA_USERS;

The DBA_USERS table contains the following columns:

Column	Explanation
USERNAME	Name of the user
USER_ID	Numeric ID assigned to the user
PASSWORD	Deprecated
ACCOUNT_STATUS	Status of the user such as: OPEN EXPIRED EXPIRED(GRACE) EXPIRED(TIMED) LOCKED EXPIRED & LOCKED(TIMED) EXPIRED(GRACE) & LOCKED(TIMED) EXPIRED & LOCKED EXPIRED(GRACE) & LOCKED
LOCK_DATE	Date that User was locked (if applicable)
EXPIRY_DATE	Date that User was expired
DEFAULT_TABLESPACE	Default tablespace for the user
TEMPORARY_TABLESPACE	Temporary tablespace for the user
CREATED	Date that user was created
PROFILE	User resource profile name
INITIAL_RSRC_CONSUMER_GROUP	Initial resource consumer group for the user
EXTERNAL_NAME	External name for the user
PASSWORD_VERSIONS	List of versions of the password hashes
EDITIONS_ENABLED	Y/N indicating whether editions have been enabled for the user
AUTHENTICATION_TYPE	Authentication method for the user
PROXY_ONLY_CONNECT	Y/N indicating whether a user can connect directly or by proxy only
COMMON	YES/NO indicating whether a user is common
LAST_LOGIN	Last login time
ORACLE_MAINTAINED	Y/N indicating whether a user was created and maintained by Oracle-suppllied scripts

ORACLE/PLSQL: FIND USERS LOGGED INTO ORACLE/PLSQL

Question:Is there a query to run that will return all Users that are currently logged into Oracle/PLSQL?

Answer: In Oracle/PLSQL, there is a system view called V$SESSION which shows the session information for each current session in the database. You can run a query against this system view that returns all of the Users that are currently have a connection running in the Oracle/PLSQL database.

To retrieve all Users logged into Oracle/PLSQL, you can execute the following SQL statement:

SELECT USERNAME FROM V$SESSION;

This SELECT statement will return each username that is logged in.

The V$SESSION view contains the following columns:

Column	Explanation
SADDR	Address for session
SID	Identifier for session
SERIAL#	Serial number for session
AUDSID	Auditing session ID
PADDR	Address of the process that owns the session
USER#	User identifier
USERNAME	User name (ie: root, techonthenet, etc)
COMMAND	Last statement parsed
OWNERID	User identifier who owns the migratable session
TADDR	Address of the transaction state object
LOCKWAIT	Address for lock wait
STATUS	Status of the session. It can be one of the following: ACTIVE, INACTIVE, KILLED, CACHED, or SNIPED.
SERVER	Type of server. It can be one of the following: DEDICATED, SHARED, PSEUDO, or NONE.
SCHEMA#	User identifier for schema
SCHEMANAME	User name for schema
OSUSER	Operation system client user name
PROCESS	Operating system client process ID
MACHINE	Operating system machine name
TERMINAL	Operating system terminal name
PROGRAM	Operating system program name
TYPE	Type of session
SQL_ADDRESS	Identifies the SQL statement currently being executed (used with SQL_HASH_VALUE)
SQL_HASH_VALUE	Identifies the SQL statement currently being executed (used with SQL_ADDRESS)
SQL_ID	SQL identifier for the SQL statement currently being executed
SQL_CHILD_NUMBER	Child number for the SQL statement currently being executed
PREV_SQL_ADDR	Identifies the last SQL statement executed (used with PREV_HASH_VALUE)
PREV_HASH_VALUE	Identifies the last SQL statement executed (used with PREV_SQL_ADDR)
PREV_SQL_ID	SQL identifier for the last SQL statement executed
PREV_CHILD_NUMBER	Child number for the last SQL statement executed
MODULE	Name of the currently executing module (as per DBMS_APPLICATION_INFO.SET_MODULE)
MODULE_HASH	Hash value of the currently executing module
ACTION	Name of the currently executing action (as per DBMS_APPLICATION_INFO.SET_ACTION)
ACTION_HASH	Hash value of the currently executing action
CLIENT_INFO	Client information (as per DBMS_APPLICATION_INFO.SET_CLIENT_INFO)
FIXED_TABLE_SEQUENCE	Sequence number incremented each time there has been an intervening select from a dynamic performance table
ROW_WAIT_OBJ#	Object identifier for table specified by ROW_WAIT_ROW#
ROW_WAIT_FILE#	Identifier for datafile specified in ROW_WAIT_ROW#
ROW_WAIT_BLOCK#	Identifier for block specified in ROW_WAIT_ROW#
ROW_WAIT_ROW#	Row that is currently locked
LOGON_TIME	Time that user logged in
LAST_CALL_ET	If STATUS is ACTIVE, LAST_CALL_ET is the elapsed time (in seconds) since the session became active. If STATUS is INACTIVE, LAST_CALL_ET is the elapsed time (in seconds) since the session became inactive.
PDML_ENABLED	Replaced by PDML_STATUS
FAILOVER_TYPE	What type of transparent application failover is enabled for the session. It can be one of the following: NONE, SESSION, or SELECT.
FAILOVER_METHOD	Method of transparent application failure for the session. It can be one of the following: NONE, BASIC, or PRECONNECT.
FAILED_OVER	YES or NO to indicate whether failover has occurred
RESOURCE_CONSUMER_GROUP	Resource consumer group for the session
PDML_STATUS	ENABLED or DISABLED
PDDL_STATUS	ENABLED or DISABLED
PQ_STATUS	ENABLED or DISABLED
CURRENT_QUEUE_DURATION	Length of time that session has been queued
CLIENT_IDENTIFIER	Client identifier for the session
BLOCKING_SESSION_STATUS	It can be one of the following values: VALID, NO HOLDER, GLOBAL, NOT IN WAIT, or UNKNOWN
BLOCKING_INSTANCE	Instance identifier of blocking session
BLOCK_SESSION	Session identifier of blocking session
SEQ#	Sequence number that is incremented for each wait
EVENT#	Event number
EVENT	Resource that the session is waiting for
P1TEXT	Description of the first additional parameter
P1	First additional parameter
P1RAW	First additional parameter
P2TEXT	Description of the second additional parameter
P2	Second additional parameter
P2RAW	Second additional parameter
P3TEXT	Description of the third additional parameter
P3	Third additional parameter
P3RAW	Third additional parameter
WAIT_CLASS_ID	Identifier of the wait class
WAIT_CLASS#	Number of the wait class
WAIT_CLASS	Name of the wait class
WAIT_TIME	Value of session's last wait time. If 0, then the session is currently waiting
SECONDS_IN_WAIT	If WAIT_TIME > 0, then SECOND_IN_WAIT is the number of seconds since the start of the last wait. If WAIT_TIME = 0, then SECONDS_IN_WAIT is the number of seconds elapsed in the current wait.
STATE	0 means WAITING -2 means WAITED UNKNOWN TIME -1 means WAITED SHORT TIME >0 means WAITED KNOWN TIME
SERVICE_NAME	Service name of the session
SQL_TRACE	ENABLED or DISABLED
SQL_TRACE_WAITS	TRUE or FALSE
SQL_TRACE_BINDS	TRUE or FALSE

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 5

ORACLE/PLSQL: UNIQUE CONSTRAINTS

WHAT IS A UNIQUE CONSTRAINT IN ORACLE?

NOTE

WHAT IS THE DIFFERENCE BETWEEN A UNIQUE CONSTRAINT AND A PRIMARY KEY?

CREATE UNIQUE CONTRAINT - USING A CREATE TABLE STATEMENT

Example

CREATE UNIQUE CONTRAINT - USING AN ALTER TABLE STATEMENT

Example

DROP UNIQUE CONSTRAINT

Example

DISABLE UNIQUE CONSTRAINT

Example

ENABLE UNIQUE CONSTRAINT

Example

ORACLE/PLSQL: CHECK CONSTRAINTS

WHAT IS A CHECK CONSTRAINT IN ORACLE?

NOTE

USING A CREATE TABLE STATEMENT

Example

USING AN ALTER TABLE STATEMENT

Example

DROP A CHECK CONSTRAINT

Example

ENABLE A CHECK CONSTRAINT

Example

DISABLE A CHECK CONSTRAINT

Example

ORACLE/PLSQL: INDEXES

WHAT IS AN INDEX IN ORACLE?

CREATE AN INDEX

Syntax

Example

CREATE A FUNCTION-BASED INDEX

Syntax

Example

RENAME AN INDEX

Syntax

Example

COLLECT STATISTICS ON AN INDEX

Syntax

Example

DROP AN INDEX

Syntax

Example

ORACLE/PLSQL: GRANT/REVOKE PRIVILEGES

DESCRIPTION

GRANT PRIVILEGES ON TABLE

Syntax

Example

REVOKE PRIVILEGES ON TABLE

Syntax

Example

GRANT PRIVILEGES ON FUNCTIONS/PROCEDURES

Syntax

Example

REVOKE PRIVILEGES ON FUNCTIONS/PROCEDURES

Syntax

Example

ORACLE/PLSQL: SYNONYMS

DESCRIPTION

CREATE SYNONYM (OR REPLACE)

Syntax

Example

DROP SYNONYM

Syntax

Example

ORACLE/PLSQL: ROLES

DESCRIPTION

CREATE ROLE

Syntax

Note

Example

GRANT TABLE PRIVILEGES TO ROLE

Syntax