Release 8.0
A58225-01
Library |
Product |
Contents |
Index |
| Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
Use the ALTER DATABASE command with the RECOVER clause if
you want to write your own specialized media recovery application using
SQL. For other situations, Oracle recommends that you use the Server Manager
RECOVER command rather than the ALTER DATABASE command with the RECOVER
clause to perform media recovery.
For more information on media recovery, see the Oracle8
Backup and Recovery Guide and Oracle8
Administrator's Guide. For illustrations, see "Examples".
The RECOVER clause must appear in an ALTER DATABASE statement.
You must have the privileges necessary to issue this statement. For information
on these privileges, see ALTER DATABASE.
In addition:
parallel_clause:
See the PARALLEL clause.
|
AUTOMATIC |
automatically generates the names of the redo log files to apply during media recovery. If you omit this option, then you must specify the names of redo log files using the ALTER DATABASE ... RECOVER command with the LOGFILE clause. |
|
FROM |
specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST. |
|
STANDBY |
recovers the standby database using the control file and archived redo log files copied from the primary database. For more information, see Oracle8 Administrator's Guide. |
|
DATABASE |
recovers the entire database. This is the default option. You can use this option only when the database is closed. Note: This option will recover only online datafiles. |
|
UNTIL CANCEL |
performs cancel-based recovery. This option recovers the database until you issue the ALTER DATABASE RECOVER command with the CANCEL clause. |
|
UNTIL TIME |
performs time-based recovery. This parameter recovers the database to the time specified by the date. The date must be a character literal in the format 'YYYY-MM-DD:HH24:MI:SS'. |
|
UNTIL CHANGE |
performs change-based recovery. This parameter recovers the database to a transaction-consistent state immediately before the system change number (SCN) specified by integer. |
|
USING BACKUP CONTROLFILE |
specifies that a backup control file is being used instead of the current control file. |
|
TABLESPACE |
recovers only the specified tablespaces. You can use this option if the database is open or closed, provided the tablespaces to be recovered are offline. |
|
DATAFILE |
recovers the specified datafiles. You can use this option when the database is open or closed, provided the datafiles to be recovered are offline. |
|
LOGFILE |
continues media recovery by applying the specified redo log file. |
|
CONTINUE |
continues multi-instance recovery after it has been interrupted to disable a thread. |
|
CONTINUE DEFAULT |
continues recovery by applying the redo log file that Oracle has automatically generated. |
|
CANCEL |
terminates cancel-based recovery. |
|
parallel_clause |
specifies degree of parallelism to use when recovering. See the PARALLEL clause. |
The following statement performs complete recovery of the entire database:
ALTER DATABASE RECOVER AUTOMATIC DATABASE;
Oracle automatically generates the names of redo log files to apply and prompts you with them. The following statement applies a suggested file:
ALTER DATABASE RECOVER CONTINUE DEFAULT;
The following statement explicitly names a redo log file for Oracle to apply:
ALTER DATABASE RECOVER LOGFILE 'diska:arch0006.arc';
The following statement performs time-based recovery of the database:
ALTER DATABASE AUTOMATIC RECOVER UNTIL TIME '1992-10-27:14:00:00';
Oracle recovers the database until 2:00 pm on October 27,
1992.
The following statement recovers the tablespace USER5:
ALTER DATABASE RECOVER TABLESPACE user5;
To rename a table, view, sequence, or private synonym for
a table, view, or sequence. See also "Renaming
Objects".
The object must be in your own schema. See also "Restrictions".
|
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". |
Integrity constraints, indexes, and grants on the old object
are automatically transferred to the new object. Oracle invalidates all
objects that depend on the renamed object, such as views, synonyms, and
stored procedures and functions that refer to a renamed table.
To change the name of table DEPT to EMP_DEPT, issue the following statement:
RENAME dept TO emp_dept;
You cannot use this command to rename public synonyms. To
rename a public synonym, you must first drop it with the DROP SYNONYM command
and then create another public synonym with the new name using the CREATE
SYNONYM command.
You cannot use this command to rename columns. You can rename a column using the CREATE TABLE command with the AS clause. For example, the following statement re-creates 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;
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 illustrations, see "Examples".
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. See also "Limitations".
|
system_priv |
is a system privilege to be revoked. For a list of the system privileges, see Table 4-11. See also "Revoking Privileges". |
|
role |
is a role to be revoked. For a list of the roles predefined by Oracle, see Oracle8 Administrator's Guide.. See also "Revoking Roles". |
|
FROM |
identifies users and roles from which the system privileges or roles are to be revoked. |
|
PUBLIC |
revokes the system privilege or role from all users. |
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.
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. Note that the role is not revoked from users
who have been granted the privilege directly or through other roles.
The REVOKE command can revoke only privileges and roles that were previously granted directly with a GRANT statement. The REVOKE command cannot perform the following operations:
A system privilege or role cannot appear more than once in
the list of privileges and roles to be revoked. A user, a role, or PUBLIC
cannot appear more than once in the FROM clause.
The following statement revokes 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;
To revoke object privileges for a particular object from
users and roles. To revoke system privileges or roles, refer to . REVOKE
(System Privileges and Roles). See also "Revoking Object
Privileges".
Each object privilege authorizes some operation on an object.
By revoking an object privilege, you prevent the revokee from performing
that operation. For a summary of the object privileges for each type of
object, see Table 4-13. For illustrations, see
"Examples".
You must have previously granted the object privileges to
each user and role. See also "Revoking Multiple
Identical Grants".
|
object_priv |
is an object privilege to be revoked. You can substitute any of the following values: UPDATE |
|
|
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 message. |
|
|
ON DIRECTORY directory_object |
identifies a directory object on which privileges are revoked. You cannot qualify directory_object with schema when using the DIRECTORY option. 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; snapshot; synonym for a table, view, sequence, procedure, stored function, package, or snapshot; or library. |
|
|
|
If you do not qualify object with schema, Oracle assumes the object is in your own schema. |
|
|
FROM |
identifies users and roles from which the object privileges are revoked. |
|
|
|
PUBLIC |
revokes object privileges from all users. |
|
CASCADE CONSTRAINTS |
drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege or the ALL PRIVILEGES option if the revokee has exercised the REFERENCES privilege to define a referential integrity constraints. See also "Cascading Revokes". |
|
|
FORCE |
revokes EXECUTE object privileges on user-defined type objects with table dependencies. You must use the FORCE option to revoke the EXECUTE object privilege on user-defined type objects with table dependencies. See also "Using FORCE". For detailed information about type dependencies and user-defined object privileges, see Oracle8 Concepts. |
|
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. 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.
You can use the REVOKE command only to revoke object privileges that you previously granted directly to the revokee. You cannot use the REVOKE command to perform the following operations:
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.
You must use the FORCE option to revoke the EXECUTE object
privilege on user-defined type objects with table dependencies. The FORCE
option causes the data in the dependent tables to become inaccessible.
Regranting the necessary type privilege will revalidate the table. For
detailed information about type dependencies and user-defined object privileges,
see Oracle8 Concepts.
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.
Revoking an object privilege that a user has either granted or exercised to define an object or a referential integrity constraint has the following cascading effects:
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;
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.
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.
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 option:
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.
You can revoke READ privilege on directory BFILE_DIR1 from SUE, by issuing the following statement:
REVOKE READ ON DIRECTORY bfile_dir1 FROM sue;
To undo work done in the current transaction, or to manually
undo the work done by an in-doubt distributed transaction. See also "Rolling
Back Transactions".
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 FORCE TRANSACTION system privilege.
To manually roll back an in-doubt distributed transaction originally committed
by another user, you must have FORCE ANY TRANSACTION system privilege.
|
WORK |
is optional and is provided for ANSI compatibility. |
|
TO |
rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction. |
|
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. See also "Distributed Transactions". |
|
|
ROLLBACK statements with the FORCE clause are not supported in PL/SQL. |
A transaction (or a logical unit of work) is a sequence of
SQL statements that Oracle treats as a single unit. A transaction begins
with the first executable SQL statement after a COMMIT, ROLLBACK, or connection
to the database. A transaction ends with a COMMIT statement, a ROLLBACK
statement, or disconnection (intentional or unintentional) from the database.
|
Note: Oracle issues an implicit COMMIT statement before and after processing any data definition language (DDL) statement. |
Using ROLLBACK without the TO SAVEPOINT clause performs the following operations:
Using ROLLBACK with the TO SAVEPOINT clause performs the following operations:
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.
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;
Oracle's distributed functionality enables you to perform
distributed transactions, or transactions that modify data on multiple
databases. To commit or roll back a distributed transaction, you need only
issue a COMMIT or ROLLBACK statement as you would any other transaction.
If a network failure occurs during the commit process for
a distributed transaction, the state of the transaction may be unknown,
or in doubt. After consultation with the administrators of the other
databases involved in the transaction, you may decide to manually commit
or roll back the transaction on your local database. You can manually roll
back the transaction on your local database by issuing a ROLLBACK statement
with the FORCE clause.
For more information on when to roll back in-doubt transactions,
see Oracle8 Distributed Database
Systems.
You cannot manually roll back an in-doubt transaction to
a savepoint.
A ROLLBACK statement with a FORCE clause only rolls back
the specified transaction. Such a statement does not affect your current
transaction.
The following statement manually rolls back an in-doubt distributed transaction:
ROLLBACK WORK FORCE '25.32.87';
To identify a point in a transaction to which you can later
roll back. See also "Creating Savepoints".
|
savepoint |
is the name of the savepoint to be created. |
Savepoints are used with the ROLLBACK command to roll back
portions of the current transaction. For more information, see "Rolling
Back Transactions".
Savepoints are useful in interactive programs, because you
can create and name intermediate steps of a program. This allows you more
control over longer, more complex programs. For example, you can use savepoints
throughout a long complex series of updates, so that if you make an error,
you need not resubmit every statement.
Savepoints are similarly useful in application programs:
if a program contains several subprograms, you can create a savepoint before
each subprogram begins. If a subprogram fails, you can easily return the
data to its state before the subprogram began and then reexecute the subprogram
with revised parameters or perform a recovery action.
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.
To update BLAKE's and CLARK's salary, check that the total company salary does not exceed 20,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 = 1300 WHERE ename = 'CLARK' COMMIT;
