learn-oracle
This SQL Article discusses savepoint to update. Learn more about Oracle dba.






SQL - SAVEPOINT to UPDATE


Previous Chapter

SAVEPOINT
Purpose

Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

Keywords and Parameters

savepoint

Specify 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 Banda's and Greene's salary in the demo table hr.employees, check that the total department salary does not exceed 314,000, then reenter Greene's salary, enter:

UPDATE employees
SET salary = 7000
WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees
SET salary = 12000
WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;

UPDATE employees
SET salary = 11000
WHERE last_name = 'Greene';
COMMIT;



SET CONSTRAINT[S]
Purpose

Use the SET CONSTRAINTS statement to specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.

Prerequisites

To specify when a deferrable constraint is checked, you must have SELECT privilege on the table to which the constraint is applied unless the table is in your schema.

Keywords and Parameters

constraint

Specify the name of one or more integrity constraints.

ALL

Specify ALL to set all deferrable constraints for this transaction.

IMMEDIATE

Specify IMMEDIATE to indicate that the conditions specified by the deferrable constraint are checked immediately after each DML statement.

DEFERRED

Specify DEFERRED to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.

Examples

Setting Constraints Examples

The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:

SET CONSTRAINTS ALL IMMEDIATE;

The following statement checks three deferred constraints when the transaction is committed:

SET CONSTRAINTS emp_job_nn, emp_salary_min,
hr.emp_job_fk@houston DEFERRED;

SET ROLE
Purpose

Use the SET ROLE statement to enable and disable roles for your current session.

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

Prerequisites

You must already have been granted the roles that you name in the SET ROLE statement.

Keywords and Parameters

role

Specify a role to be enabled for the current session. Any roles not listed and not already enabled are disabled for the current session.

In the IDENTIFIED BY password clause, specify the password for a role. If the role has a password, you must specify the password to enable the role.

Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.

ALL

Specify ALL to enable all roles granted to you for the current session except those optionally listed in the EXCEPT clause.

Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.

If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.

Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.

NONE

Specify NONE to disable all roles for the current session, including the DEFAULT role.

Examples

Setting Roles Examples

To enable the role gardener identified by the password marigolds for your current session, issue the following statement:

SET ROLE gardener IDENTIFIED BY marigolds;

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL;

To enable all roles granted to you except dw_manager, issue the following statement:

SET ROLE ALL EXCEPT dw_manager;

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE;

SET TRANSACTION
Purpose

Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.

The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement.

Prerequisites

If you use a SET TRANSACTION statement, it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.

Keywords and Parameters

READ ONLY

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.

All subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.

Restriction: Only the following statements are permitted in a read-only transaction:

* Subqueries (that is, SELECT statements without the for_update_clause)

* LOCK TABLE

* SET ROLE

* ALTER SESSION

* ALTER SYSTEM

READ WRITE

Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes statement-level read consistency, which is the default.

Restriction: You cannot toggle between transaction-level and statement-level read consistency in the same transaction.

ISOLATION LEVEL Clause

Use the ISOLATION LEVEL clause to specify how transactions containing database modifications are handled.

* The SERIALIAZBLE setting specifies serializable transaction isolation mode as defined in the SQL92 standard. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails.

* The READ COMMITTED setting is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released.

USE ROLLBACK SEGMENT Clause

Specify USE ROLLBACK SEGMENT to assign the current transaction to the specified rollback segment. This clause also implicitly establishes the transaction as a read/write transaction.

This clause lets you to assign transactions of different types to rollback segments of different sizes. For example:

* If no long-running queries are concurrently reading the same tables, you can assign small transactions to small rollback segments, which are more likely to remain in memory.

* You can assign transactions that modify tables that are concurrently being read by long-running queries to large rollback segments, so that the rollback information needed for the read-consistent queries is not overwritten.

* You can assign transactions that insert, update, or delete large amounts of data to rollback segments large enough to hold the rollback information for the transaction.

You cannot use the READ ONLY clause and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.

NAME Clause

Use the NAME clause to assign a name to the current transaction. This clause is especially useful in distributed database environments when you must identify and resolve in-doubt transactions. The text string is limited to 255 bytes.

If you specify a name for a distributed transaction, when the transaction commits, the name becomes the commit comment, overriding any comment specified explicitly in the COMMIT statement.

Examples

The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.

COMMIT;
SET TRANSACTION READ ONLY NAME 'Toronto';
SELECT product_id, quantity_on_hand FROM inventories
WHERE warehouse_id = 5;
COMMIT;

The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.

The following statement assigns your current transaction to the rollback segment rs_1:

SET TRANSACTION USE ROLLBACK SEGMENT rs_1;

TRUNCATE


Caution:

You cannot roll back a TRUNCATE statement.

Purpose

Use the TRUNCATE statement to remove all rows from a table or cluster and reset the STORAGE parameters to the values when the table or cluster was created.

Removing rows with the TRUNCATE statement can be more efficient than dropping and re-creating a table. Dropping and re-creating a table invalidates the table's dependent objects, requires you to regrant object privileges on the table, and requires you to re-create the table's indexes, integrity constraints, and triggers and respecify its storage parameters. Truncating has none of these effects.

Prerequisites

To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.

Keywords and Parameters

TABLE Clause

Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema, Oracle assumes the table is in your own cluster.

* You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.

* Oracle changes the NEXT storage parameter of table to be the size of the last extent deleted from the segment in the process of truncation.

* Oracle also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.

* If table is not empty, Oracle marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table.

* For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.

# If table (whether it is a regular or index-organized table) contains LOB columns, all LOB data and LOB index segments are truncated.

# If table is partitioned, all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.

Restrictions:

* You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

* You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

* If table belongs to a hierarchy, it must be the root of the hierarchy.

* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.

MATERIALIZED VIEW LOG Clause

The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.

Restrictions:

* You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.

* You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. (An exception is that you may truncate the table if the integrity constraint is self-referential.)

* If table belongs to a hierarchy, it must be the root of the hierarchy.

* If a domain index is defined on table, neither the index nor any index partitions can be marked IN_PROGRESS.

MATERIALIZED VIEW LOG Clause

The MATERIALIZED VIEW LOG clause lets you specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. This clause permits materialized view master tables to be reorganized through export/import without affecting the ability of primary-key materialized views defined on the master to be fast refreshed. To support continued fast refresh of primary-key materialized views, the materialized view log must record primary-key information.

CLUSTER Clause

Specify the schema and name of the cluster to be truncated. You can truncate only an indexed cluster, not a hash cluster. If you omit schema, Oracle assumes the cluster is in your own schema.

When you truncate a cluster, Oracle also automatically deletes all data in the indexes of the cluster tables.

STORAGE Clauses

The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.

DROP STORAGE

Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. This is the default. REUSE STORAGE

Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table or cluster. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations.

Examples

Simple TRUNCATE Example

The following statement removes all rows from the employees table and returns the freed space to the tablespace containing employees:

TRUNCATE TABLE employees;

The above statement also removes all data from all indexes on employees and returns the freed space to the tablespaces containing them.

Retaining Free Space After Truncate Example

The following statement removes all rows from all tables in the personnel cluster, but leaves the freed space allocated to the tables:

TRUNCATE CLUSTER personnel REUSE STORAGE

The above statement also removes all data from all indexes on the tables in the personnel cluster.

Preserving Materialized View Logs After Truncate Example

The following statements are examples of truncate statements that preserve materialized view logs:

TRUNCATE TABLE sales PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE orders;

UPDATE
Purpose

Use the UPDATE statement to change existing values in a table or in a view's base table

Prerequisites

For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.

For you to update values in the base table of a view:

* You must have UPDATE privilege on the view, and

* Whoever owns the schema containing the view must have UPDATE privilege on the base table.

If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table whose column values you are referencing (such as the columns in a where_clause) to perform an UPDATE.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.

Examples

Simple Examples

The following statement gives null commissions to all employees with the job sa_clerk:

UPDATE employees
SET commission_pct = NULL
WHERE job = 'SA_CLERK';

The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:

UPDATE employees SET
job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
WHERE first_name||' '||last_name = 'Douglas Grant';

The following statement increases the balance of bank account number 5001 in the accounts table on a remote database accessible through the database link boston:

UPDATE accounts@boston
SET balance = balance + 500
WHERE acc_no = 5001;

PARTITION Example

The following example updates values in a single partition of the sales table:

UPDATE sales PARTITION (sales_q1_1999) s
SET s.promo_id = 494;

Complex Example

The next example shows the following syntactic constructs of the UPDATE statement:

* Both forms of the update_set_clause together in a single statement

* A correlated subquery

* A where_clause to limit the updated rows

UPDATE employees a
SET department_id =
(SELECT department_id
FROM departments
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
WHERE a.department_id = b.department_id)
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE location_id = 2900
OR location_id = 2700);

The above UPDATE statement performs the following operations:

* Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)

* Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)

* Sets each employee's salary to 1.1 times the average salary of their department

* Sets each employee's commission to 1.5 times the average commission of their department

SET VALUE Example

The following statement updates a row of object table table1 by selecting a row from another object table table2:

UPDATE table1 p SET VALUE(p) =
(SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.id = 10;

The subquery uses the value object reference function in its expression.

Correlated Update Example

The following example updates particular rows of the projs nested table corresponding to the department whose department equals 123:

UPDATE TABLE(SELECT projs
FROM dept d WHERE d.dno = 123) p
SET p.budgets = p.budgets + 1
WHERE p.pno IN (123, 456);

RETURNING Clause Example

The following example returns values from the updated row and stores the result in PL/SQL variables bnd1, bnd2, bnd3:

UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;

Previous Chapter

Discuss Savepoint To Update | More Tutorials on Oracle dba | Source : Oracle Documentation




Liked it ? Want to share it ? Social Bookmarking

Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information




Want to share or request more about Savepoint To Update in SQL Tutorial to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info