Oracle8i SQL Reference
Release 8.1.5
A67779-01

Library
Product
Contents
Index

Prev Next

7
SQL Statements (continued)

RENAME

Syntax


Purpose

To rename a table, view, sequence, or private synonym for a table, view, or sequence.

Do not use this statement to rename public synonyms. Instead, drop the public synonym and then create another public synonym with the new name. See "DROP SYNONYM" and "CREATE SYNONYM".

Prerequisites

The object must be in your own schema.

Keywords and Parameters

old  

is the name of an existing table, view, sequence, or private synonym.  

new  

is the new name to be given to the existing object. The new name must not already be used by another schema object in the same namespace and must follow the rules for naming schema objects defined in the section "Schema Object Naming Rules".  

Example

To change the name of table DEPT to EMP_DEPT, issue the following statement: 

RENAME dept TO emp_dept;

You cannot use this statement directly to rename columns. However, you can rename a column using this statement together with the CREATE TABLE statement with AS subquery. The following statements re-create the table STATIC, renaming a column from OLDNAME to NEWNAME: 

CREATE TABLE temporary (newname, col2, col3) 
    AS SELECT oldname, col2, col3 FROM static; 

DROP TABLE static; 

RENAME temporary TO static;

REVOKE system_privileges_and_roles

Syntax


Purpose

To revoke system privileges and roles from users and roles. To revoke object privileges from users and roles, refer to "REVOKE schema_object_privileges". For information on granting system privileges and roles, see "GRANT system_privileges_and_roles".

Prerequisites

You must have been granted the system privilege or role with the ADMIN OPTION. Also, you can revoke any role if you have the GRANT ANY ROLE system privilege.

The REVOKE statement can revoke only privileges and roles that were previously granted directly with a GRANT statement. You cannot use this statement to revoke:

Keywords and Parameters

system_priv  

is a system privilege to be revoked. For a list of the system privileges, see Table 7-5.

  • If you revoke a privilege from a user, Oracle removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.

 

 
  • If you revoke a privilege from a role, Oracle removes the privilege from the role's privilege domain. Effective immediately, users with the role enabled cannot exercise the privilege. Also, other users who have been granted the role and subsequently enable the role cannot exercise the privilege.

  • If you revoke a privilege from PUBLIC, Oracle removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, such users can no longer exercise the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles.

Restriction: A system privilege cannot appear more than once in the list of privileges to be revoked.  

role  

is a role to be revoked. For a list of the roles predefined by Oracle, see "GRANT system_privileges_and_roles".

  • If you revoke a role from a user, Oracle makes the role unavailable to the user. If the role is currently enabled for the user, the user can continue to exercise the privileges in the role's privilege domain as long as it remains enabled. However, the user cannot subsequently enable the role.

  • If you revoke a role from another role, Oracle removes the revoked role's privilege domain from the revokee role's privilege domain. Users who have been granted and have enabled the revokee role can continue to exercise the privileges in the revoked role's privilege domain as long as the revokee role remains enabled. However, other users who have been granted the revokee role and subsequently enable it cannot exercise the privileges in the privilege domain of the revoked role.

  • If you revoke a role from PUBLIC, Oracle makes the role unavailable to all users who have been granted the role through PUBLIC. Any user who has enabled the role can continue to exercise the privileges in its privilege domain as long as it remains enabled. However, users cannot subsequently enable the role. The role is not revoked from users who have been granted the role directly or through other roles.

Restriction: A system role cannot appear more than once in the list of roles to be revoked.  

FROM  

identifies users and roles from which the system privileges or roles are to be revoked.

Restriction: A user, a role, or PUBLIC cannot appear more than once in the FROM clause.  

PUBLIC  

revokes the system privilege or role from all users.  

Examples

The following statement revokes the DROP ANY TABLE system privilege from the users BILL and MARY: 

REVOKE DROP ANY TABLE 
    FROM bill, mary;

BILL and MARY can no longer drop tables in schemas other than their own.

The following statement revokes the role CONTROLLER from the user HANSON: 

REVOKE controller 
    FROM hanson;

HANSON can no longer enable the CONTROLLER role.

The following statement revokes the CREATE TABLESPACE system privilege from the CONTROLLER role: 

REVOKE CREATE TABLESPACE 
   FROM controller;

Enabling the CONTROLLER role no longer allows users to create tablespaces.

To revoke the role VP from the role CEO, issue the following statement: 

REVOKE vp
  FROM ceo;

VP is no longer granted to CEO.

To revoke the CREATE ANY DIRECTORY system privilege from user SCOTT, issue the following statement: 

REVOKE CREATE ANY DIRECTORY FROM scott;

REVOKE schema_object_privileges

Syntax


Purpose

To revoke object privileges for a particular object from users and roles.

For information on granting schema object privileges, see "GRANT object_privileges". To revoke system privileges or roles, refer to "REVOKE system_privileges_and_roles".

Each object privilege authorizes some operation on an object. By revoking an object privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same object privilege to the same user, role, or PUBLIC. To remove the privilege from the grantee's privilege domain, all grantors must revoke the privilege. If even one grantor does not revoke the privilege, the grantee can still exercise the privilege by virtue of that grant.

For a summary of the object privileges for each type of object, see Table 7-7.

Prerequisites

You must have previously granted the object privileges to each user and role.

You can use the REVOKE statement only to revoke object privileges that you previously granted directly to the revokee. You cannot use this statement to revoke:

Keywords and Parameters

object_priv  

is an object privilege to be revoked. You can substitute any of the following values: ALTER, DELETE, EXECUTE, INDEX, INSERT, READ, REFERENCES, SELECT, UPDATE.  

 
  • If you revoke a privilege from a user, Oracle removes the privilege from the user's privilege domain. Effective immediately, the user cannot exercise the privilege.

    - If that user has granted that privilege to other users or roles, Oracle also revokes the privilege from those other users or roles.

    - If that user's schema contains a procedure, function, or package that contains SQL statements that exercise the privilege, the procedure, function, or package can no longer be executed.

    - If that user's schema contains a view on that object, Oracle invalidates the view.

    - If you revoke the REFERENCES privilege from a user who has exercised the privilege to define referential integrity constraints, you must specify the CASCADE CONSTRAINTS clause.

  • If you revoke a privilege from a role, Oracle removes the privilege from the role's privilege domain. Effective immediately, users with the role enabled cannot exercise the privilege. Other users who have been granted the role cannot exercise the privilege after enabling the role.

  • If you revoke a privilege from PUBLIC, Oracle removes the privilege from the privilege domain of each user who has been granted the privilege through PUBLIC. Effective immediately, all such users are restricted from exercising the privilege. However, the privilege is not revoked from users who have been granted the privilege directly or through roles.

Restriction: A privilege cannot appear more than once in the list of privileges to be revoked. A user, a role, or PUBLIC cannot appear more than once in the FROM clause.  

ALL PRIVILEGES  

revokes all object privileges that you have granted to the revokee.  

 

Note: If no privileges have been granted on the object, Oracle takes no action and does not return an error.  

ON DIRECTORY directory_object  

identifies a directory object on which privileges are revoked. You cannot qualify directory_object with schema when using the ON DIRECTORY clause. The object must be a directory. See "CREATE DIRECTORY".  

ON object  

identifies the object on which the object privileges are revoked. This object can be

  • A table, view, sequence, procedure, stored function, or package, materialized view/snapshot,

  • A synonym for a table, view, sequence, procedure, stored function, package, or materialized view/snapshot

  • A library, indextype, or user-defined operator.

If you do not qualify object with schema, Oracle assumes the object is in your own schema.  

 
  • If you revoke the SELECT object privilege (with or without the GRANT OPTION) on the containing table or snapshot of a materialized view, the materialized view will be invalidated.

  • If you revoke the SELECT object privilege (with or without the GRANT OPTION) on any of the master tables of a materialized view, both the view and its containing table or materialized view will be invalidated.

 

FROM  

identifies users and roles from which the object privileges are revoked.  

 

PUBLIC  

revokes object privileges from all users.  

CASCADE CONSTRAINTS  

This clause is relevant only if you revoke the REFERENCES privilege or ALL [PRIVILEGES]. It drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege (which might have been granted either explicitly or implicitly through a grant of ALL [PRIVILEGES]).  

FORCE  

revokes EXECUTE object privilege on user-defined type objects with table or type dependencies. You must use the FORCE clause to revoke the EXECUTE object privilege on user-defined type objects with table dependencies.

If you specify FORCE, all privileges will be revoked, but all dependent objects are marked INVALID, data in dependent tables becomes inaccessible, and all dependent function-based indexes are marked UNUSABLE. (Regranting the necessary type privilege will revalidate the table.) For detailed information about type dependencies and user-defined object privileges, see Oracle8i Concepts.  

Examples

Basic Example

You can grant DELETE, INSERT, SELECT, and UPDATE privileges on the table BONUS to the user PEDRO with the following statement: 

GRANT ALL 
    ON bonus TO pedro;

To revoke the DELETE privilege on BONUS from PEDRO, issue the following statement: 

REVOKE DELETE 
    ON bonus FROM pedro;

To revoke the remaining privileges on BONUS that you granted to PEDRO, issue the following statement: 

REVOKE ALL 
    ON bonus FROM pedro;
PUBLIC Example

You can grant SELECT and UPDATE privileges on the view REPORTS to all users by granting the privileges to the role PUBLIC: 

GRANT SELECT, UPDATE 
    ON reports TO public;

The following statement revokes UPDATE privilege on REPORTS from all users: 

REVOKE UPDATE 
    ON reports FROM public;

Users can no longer update the REPORTS view, although users can still query it. However, if you have also granted UPDATE privilege on REPORTS to any users, either directly or through roles, these users retain the privilege.

Schema Example

You can grant the user BLAKE the SELECT privilege on the ESEQ sequence in the schema ELLY with the following statement: 

GRANT SELECT 
    ON elly.eseq TO blake;

To revoke the SELECT privilege on ESEQ from BLAKE, issue the following statement: 

REVOKE SELECT 
    ON elly.eseq FROM blake;

However, if the user ELLY has also granted SELECT privilege on ESEQ to BLAKE, BLAKE can still use ESEQ by virtue of ELLY's grant.

CASCADE CONSTRAINTS Example

You can grant BLAKE the privileges REFERENCES and UPDATE on the EMP table in the schema SCOTT with the following statement: 

GRANT REFERENCES, UPDATE 
    ON scott.emp TO blake;

BLAKE can exercise the REFERENCES privilege to define a constraint in his own DEPENDENT table that refers to the EMP table in the schema SCOTT: 

CREATE TABLE dependent 
(dependno NUMBER, 
 dependname VARCHAR2(10), 
 employee NUMBER                   
    CONSTRAINT in_emp REFERENCES scott.emp(ename) );

You can revoke the REFERENCES privilege on SCOTT.EMP from BLAKE, by issuing the following statement that contains the CASCADE CONSTRAINTS clause: 

REVOKE REFERENCES 
    ON scott.emp 
    FROM blake 
    CASCADE CONSTRAINTS;

Revoking BLAKE's REFERENCES privilege on SCOTT.EMP causes Oracle to drop the IN_EMP constraint, because BLAKE required the privilege to define the constraint.

However, if BLAKE has also been granted the REFERENCES privilege on SCOTT.EMP by a user other than you, Oracle does not drop the constraint. BLAKE still has the privilege necessary for the constraint by virtue of the other user's grant.

Directory Example

You can revoke READ privilege on directory BFILE_DIR1 from SUE, by issuing the following statement: 

REVOKE READ ON DIRECTORY bfile_dir1 FROM sue;

ROLLBACK

Syntax


Purpose

To undo work done in the current transaction, or to manually undo the work done by an in-doubt distributed transaction. For information on transactions, see Oracle8i Concepts. For information on setting characteristics of the current transaction, see "SET TRANSACTION".

Note:

Oracle recommends that you explicitly end transactions in application programs using either a COMMIT or ROLLBACK statement. If you do not explicitly commit the transaction and the program terminates abnormally, Oracle rolls back the last uncommitted transaction. See also "COMMIT".  

See also "SAVEPOINT".

Prerequisites

To roll back your current transaction, no privileges are necessary.

To manually roll back an in-doubt distributed transaction that you originally committed, you must have the FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have the FORCE ANY TRANSACTION system privilege.

Keywords and Parameters

WORK  

is optional and is provided for ANSI compatibility.  

TO SAVEPOINT savepoint  

rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction. See also "SAVEPOINT".  

 

Using ROLLBACK without the TO SAVEPOINT clause performs the following operations:

  • Ends the transaction

  • Undoes all changes in the current transaction

  • Erases all savepoints in the transaction

  • Releases the transaction's locks

 

 

Using ROLLBACK with the TO SAVEPOINT clause performs the following operations:

  • Rolls back just the portion of the transaction after the savepoint.

  • Erases all savepoints created after that savepoint. The named savepoint is retained, so you can roll back to the same savepoint multiple times. Prior savepoints are also retained.

  • Releases all table and row locks acquired since the savepoint. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the rows immediately.

 

 

Restriction: You cannot manually roll back an in-doubt transaction to a savepoint.  

FORCE  

manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. For more information on distributed transactions and rolling back in-doubt transactions, see Oracle8i Distributed Database Systems.  

 

A ROLLBACK statement with a FORCE clause rolls back only the specified transaction. Such a statement does not affect your current transaction.

Restriction: ROLLBACK statements with the FORCE clause are not supported in PL/SQL.  

Examples

The following statement rolls back your entire current transaction: 

ROLLBACK;

The following statement rolls back your current transaction to savepoint SP5: 

ROLLBACK TO SAVEPOINT sp5;

The following statement manually rolls back an in-doubt distributed transaction: 

ROLLBACK WORK 
    FORCE '25.32.87';

SAVEPOINT

Syntax


Purpose

To identify a point in a transaction to which you can later roll back.

For information on savepoints, see Oracle8i Concepts. For information on rolling back transactions, see "ROLLBACK". For information on setting characteristics of the current transaction, see "SET TRANSACTION".

Prerequisites

None.

Keywords and Parameters

savepoint  

is the name of the savepoint to be created.

Savepoint names must be distinct within a given transaction. If you create a second savepoint with the same identifier as an earlier savepoint, the earlier savepoint is erased. After a savepoint has been created, you can either continue processing, commit your work, roll back the entire transaction, or roll back to the savepoint.  

Example

To update BLAKE's and CLARK's salary, check that the total company salary does not exceed 27,000, then reenter CLARK's salary, enter: 

UPDATE emp 
    SET sal = 2000 
    WHERE ename = 'BLAKE';
SAVEPOINT blake_sal;

UPDATE emp 
    SET sal = 1500 
    WHERE ename = 'CLARK';
SAVEPOINT clark_sal;

SELECT SUM(sal) FROM emp;

ROLLBACK TO SAVEPOINT blake_sal;
 
UPDATE emp 
    SET sal = 1200 
    WHERE ename = 'CLARK';
 
COMMIT;
Click the "Next" button to see the next statement in this chapter.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index