Oracle SQL Summary 2: Set operators and DML statements

Oracle SQL Summary 2: Set operator and DML Statement 3. Set operator 3.1 The set operator can only appear before the order by clause. Www.2cto.com 4, DML statement 4.1 SAVEPOINT from ORACLE SQL references Savepoint names must be distinct within a given transaction. if you create a second savepoint with the same identifier as an earlier savepoint, then 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. the translation is as follows:: In a given transaction, the Savepoint name must be unique. If you generate a savepoint with the same name as earlier, the one earlier than the Savepoint name will be cleared. After a savepoint is generated, you can continue the operation, commit your work, roll back the entire transaction, or roll back to a savepoint. Note that all savepoints will be cleared after COMMIT, for example: 01SQL> update employees02 2 set salary = 700003 3 where last_name = 'banda '; 04 www.2cto.com 051 row updated.06 07SQL> savepoint banda_sal; 08 09 Savepoint created.10 11SQL> update employees12 2 set salary = 700013 3 where last_name = 'greene '; 14 151 row updated.16 17SQL> select last_name, salary from employees where last_name = 'banda 'or last_name18 = 'greene'; 19 20LAST_NAME SALA RY21 route ---------- 22 Banda 700023 Greene 700024 25SQL> savepoint greene_sal; 26 27 Savepoint created.28 29SQL> rollback to savepoint banda_sal; 30 www.2cto.com 31 Rollback complete.32 33SQL> select last_name, salary from employees where last_name = 'banda 'or last_name34 = 'greene'; 35 36LAST_NAME SALARY37 --------------------------------------------------------- --- 38 Banda 700039 Greene 950040 41SQL> commit; 42 43 Commit complete.44 45SQL> rollback to savepoint greene_sal; 46 rollback to savepoint greene_sal47 * 48 ERROR at line 1: 49ORA-01086: savepoint 'greene _ SAL 'never established: oracle SQL references provides a detailed explanation of this issue. The excerpt is as follows: use the COMMIT statement to end your current transaction and make permanent all changes stored med in the transaction. A transaction is a se Quence of SQL statements that Oracle Database treats as a single unit. this statement also erases all savepoints in the transaction and releases transaction locks. using COMMIT can terminate your current transaction and permanently change the data (written to a log file) in the database ). A transaction is an ordered combination of a group of SQL statements. The ORACLE Database processes it (transactions) as a single unit. COMMIT also clears all savepoints and releases the transaction lock. Until you commit a transaction :. you can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. after you commit the transaction, the changes are visible to other users 'statements that execute after the commit .. you can roll back (undo) any changes made during the transaction with the ROLLBACK statement. www.2cto.com on your COMMIT 1 Before a transaction: In the current transaction, You can see any data you have changed, but other users cannot see the changes. They won't be able to see it until you COMMIT the transaction. In the current transaction, you can roll back any changes. Oracle Database issues an implicit COMMIT under the following circumstances:-Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error-After any data definition language (DDL) statement that completes without an error ORACLE Database implicitly executes COMMIT in the following situations: -The execution result of this statement is incorrect even before any DDL statement syntax verification. -After execution of any DDL statements is completed without errors. 4.2 ROLLBACK: Why is DBA_2PC_PENDING empty? The FORCE Clause in ROLLBACK is FORCE Clause www.2cto.com Specify FORCE to manually roll back an in-doubt distributed transaction. the transaction is identified by the string containing its local or global transaction ID. to find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. a rollback statement with a FORCE clause rolls back only the specified transaction. such a statement does Not affect your current transaction.01SQL> desc dba_2pc_pending; 02 Name Null? Type03 bytes -------- ---------------- 04 LOCAL_TRAN_ID not null VARCHAR2 (22) 05 GLOBAL_TRAN_ID VARCHAR2 (169) 06 state not null VARCHAR2 (16) 07 MIXED VARCHAR2 (3) 08 ADVICE VARCHAR2 (1) 09 TRAN_COMMENT VARCHAR2 (255) 10 FAIL_TIME not null DATE11 FORCE_TIME DATE12 RETRY_TIME not null DATE13 www.2cto.com OS _USER VARCHAR2 (64) 14 OS _TERMINAL VARCHAR2 (255) 15 HOST VARCHAR2 (128) 16 DB_USER VARCHAR2 (30) 17 COMMIT # VARCHAR2 (16) 18 19SQL> select * from dba_2pc_pending where db_user = 'oe '; 20 21no rows selected author yeyelei