? Describes locking mechanisms and how Oracle manages data concurrency processing? Using SQL to manage Data? identify and manage PL/SQL Objects? Describe triggers and trigger events? Monitoring and resolving locking conflicts
- Working with data through SQL
Use basic Data Manipulation language (DML) statements to process data in the database.
? Create one line at a time. Insert multiple rows from another table. Use the basic INSERT statement to create one line at a time. If you use a so-called sub-selection, you can use the Insertcommand to copy several rows from one table to another table. This method is also known as the Insert SELECT statement. such as the following insert command:
INSERT INTO dept_80 (SELECT * FROM Employees where department_id =); In this case, the DEPT_80 table has the exact same structure as the Employees table. If this is not the case,You can name the columns in each table. The individual values selected in the SELECT statement are associated with the individual columns in the table to be inserted. The column values are matched in the order specified in the INSERT and SELECT statements. As long as the data type exactly matches. For example:
INSERT INTO Just_names (first, last) (select First_Name, last_name from Employees); The data type of the only two columns in the Just_names table and the Employees table first_name andThe last_name column has the same data type. Use the Insert SELECT method to load large amounts of data from one or more tables into another table.
Use the update command to change 0 or more rows of a table.the UPDATE command modifies existing rows in the table. The number of rows modified by the UPDATE command depends on the where condition. If the WHERE clause is omitted, all rows are changed. If all rows do not satisfy the where condition, do not modify theany line.
Use the Delete command to remove 0 or more rows from a table. The delete command is used to delete existing rows from the table. The number of rows modified by the DELETE command depends on the where condition. If the WHERE clause is omitted, all rows are deleted. If all rows do not meet the Where condition, then anywhat line. Note that no rows are deleted, and this does not indicate an error; The returned message indicates only the0 rows were deleted.
Use the merge command to perform both insert andUPDATE operation.
MERGE into Jobs J
USING (SELECT * from Jobs_acquisition) aOn
(j.job_id=a.job_id)
When matched then UPDATE SET j.job_title=a.job_title
When isn't matched then INSERT
(j.job_id,j.job_title,j.min_salary,j.max_salary)Use the merge command to execute update and insert in one command. You can merge data from one source intoanother source, so you can choose to insert new rows and update specific columns if the row already exists. Consider the following example. Some of the data in the JOBS table is as follows: job_id job_title min_salary max_salary--------------------------------------------ad_pres President 20000 40000 Fi_account Accountant 4200 9000 St_clerk Stock Clerk It_ PROG Programmer 4000 10000 Below is the contents of the Jobs_acquisition table: job_id job_title min_salary max_salary-------- ------------------------------------ad_presVP20000 40000DBA DB Admin 4200 9000SA Sys AdminUse the merge command to insert all rows with a new job_id into the jobs table, if job_id already exists,the existing jobs row is updated with Job_title. Result "President" position changed to "VP" and addedThe new title "SA" and "DBA".
- COMMIT and Rollback commands
The following command is used to end a transaction:? COMMIT: Make the change a permanent change? ROLLBACK: Undo Change By default, each DML command entered is not committed. Several tools (including I sql*plus) provide someoptions, which can be submitted on a per-command basis, or based on a set of commands. Before a commit or rollback is issued,change in Pending state。 Only users who perform changes can view the morethe data after the change. Other users can select the same data, but only see the data before the change. Other users cannotthe user has changed the data emitted by DML. By default, when one user tries to change the row that another user is changing, the user must wait until thethe changed user submits or rolls back the changes. This is controlled automatically by the locking mechanism of the Oracle database. Because of the lockThe system is built into the row, so the database will never run out of locks.
Oracle's Process language extension (PL/SQL) for the fourth generation programminglanguage (4GL). It provides the following features:? Procedure extensions for SQL? Portability between platforms and products? A higher level of security and data integrity protection? Support for object-oriented programming PL/SQL is an Oracle proprietary fourth generation programming language that provides procedural extensions to SQL. PL/SQL forOracle databases and applications provide a common programming environment for all operating systems or hardware platforms. with PL/SQL, you can work with your data by using it, and you can also use process structures such as If-then,Case and Loop) to control the programming flow. In addition, you can declare constants and variables, define procedures and functions, use setsobject types, and traps to capture run-time errors. Other language can also be called in PL/SQL programs(e.g. C, C + +, and Java). PL/SQL also provides data protection capabilities.Callers do not have to know what data structures to read or manipulate to invoke。 In addition, callers do not have to have permission to access these objects, only having permission to execute a PL/SQL program is sufficientup. You can choose to invoke PL/SQL with the permissions of another mode, in which case the caller must have permission to executeEach statement executed during the invocation of the program. Because PL/SQL code runs inside the database, this code is very effective in performing operations with a large amount of data.and minimizes the amount of network traffic to the application.
The database administrator should be able to:? Find out if there is a problem with PL/SQL objects? recommend the appropriate PL/SQL usage? Load a PL/SQL object into a database? Assist PL/SQL developers in diagnosing failures as DBAs and are generally not responsible for loading PL/SQL code into the database or assisting the developerThe fault is broken. In addition, DBAs are typically not required to write applications using PL/SQL, but as DBAs should notThe same PL/SQL object is sufficient to provide advice to application developers and to identify problemsobject. In the database Control, click the Administration tab in schema, toaccess to PL/SQL objects. When you click an object type, you can view, modify, and create the type of the selected PL/SQL object.
There are several types of PL/SQL database objects: ? Packages: Packages are a collection of logically related procedures and functions. This part of the packagealso known as a description, used to describe the interface of an application, which declares the types, variables, constants,Exception errors, cursors, and subroutines. Package Body: The package body defines cursors and subroutines in its entirety, thus implementing the instructions. The package Body containsimplement detail and private declarations that are not displayed to the caller. ? Type Principal: A type principal is a method (procedure and function) that is associated with a user-defined data typea collection of components. ? Procedure: A procedure is a PL/SQL block used to perform a specific operation. Function: A function is a PL/SQL block that returns a single value using the return PL/SQL command. It is a return valuethe process. ? Trigger: A trigger is a PL/SQL block that executes when a specific event occurs in the database. These events can be based ontable, such as when rows are inserted in a table. It can also be a database event, such as when a user logs on to a database.
The PL/SQL function is commonly used to calculate values. There are many built-in functions, such as Sysdate, SUM, AVG, and To_date. Developers can also create their own functions when writing an application. The code of the PL/SQL function must containRETURN statement. As created with the following SQL command:
CREATE OR REPLACE FUNCTION compute_tax (salary number)
RETURN Number
as
BEGIN
IF salary<5000 Then
RETURN salary*.15;
ELSE
RETURN salary*.33;
END IF;
END;
/
? Used to perform specific actions? Use the parameter list for incoming and outgoing values? You can invoke the following command: The –call command (which belongs to the SQL statement) the –execute command (which belongs to the sql*plus command) PL/SQL procedures are used to perform specific actions. As with functions, the procedure accepts input values, performs If-then,conditional statements such as case and loop.
A package is a collection of functions and procedures. Each package should consist of twoObject Composition:? Package description? The package body package is a combination of functions and procedures. Some functions and procedures are composed of a package, and performance and maintainability areimproved. Each package should consist of two independently compiled database objects:? Package Description: The object type of this object (sometimes referred to as the header) is package, which contains onlyThe definition of Procedures , functions, and variables in a package. ? Package Body: The object type of this object is the package body, which contains the child threads defined in the description of the packagesthe actual code of the sequence. Use dot notation to invoke procedures and functions in a package: package_name.procedure or function name in the package shown in the diagram, you can call the subroutine:sql> SELECT Money.compute_tax as follows ( Salary) from Hr.employeesWHERE employee_id=107;Sql> EXECUTE Money.give_raise_to_all;
- Package Description and Package body
Package body:? is separate from the package description. As a result, the package body code can be changed and recompiled withoutother objects related to package description are marked as invalid. ? The code that contains the subroutine defined in the package description. This is the part responsible for the completion of the work. Package Description Tableshows how to invoke a subroutine in a package, and the package body is a code snippet. ? The package body can be compiled only after the package description has been compiled. Can be created without a package bodyPackage Description, but you cannot create a package body without a package description. ? You can hide code details by wrapping. Packaging is a standalone program that can disrupt PL/SQL source code, so you canthe PL/SQL application is delivered without exposing the source code.
? The Oracle database has more than 350 built-in PL/SQL packages that can be used for: – Management and Maintenance utilities – extended functionality? You can use the describe command to view subroutines. The built-in PL/SQL packages provided with the Oracle database can be used to access extended database functionality, such as advancedqueues, encryption, and file input/output (I/O), which also contains many management and maintenance utilities. Which packages an administrator can use depends on the type of application to which the database serves. Here are some comparisoncommon management and maintenance packages:? Dbms_stats: Used to collect, view, and modify optimizer statistics? Dbms_output: Generate output from PL/SQL? Dbms_session: Access the Alter SESSION and set ROLE statements via PL/SQL? Dbms_random: Generate random numbers? Dbms_utility: Gets the time, CPU time, and version information, computes the hash value, and performs manyOther Functions? Dbms_scheduler: Scheduling functions and procedures that can be called from PL/sql? Dbms_crypto: Encrypt and decrypt database data? Utl_file: Read and write operating system files via PL/SQL
Triggers are PL/SQL code objects stored in a database that automatically run when certain events occur or "touchissued ". The Oracle database allows many operations to act as triggering events, including inserting into tables, user login databases, andattempt to delete a table or change auditing settings. Triggers can call other procedures or functions. The code for the trigger is better short, requiring longer code content to be asplaced in a separate package. DBAs can use triggers to assist in performing value-based audits, forcing complex constraints, and automating many tasks.
There are many events that can be used to trigger triggers that fall into three categories. DML event triggers are triggered when data is modified through a statement. DDL event triggers are triggered when an object is created through a statement or modified in some way. A database event trigger is triggered when a specific event occurs in the database. Most triggers can be specified to be triggered before an event occurs or after an event occurs. For DML events, you can set the triggerdesigned to be triggered once when a statement is executed, or once per row is modified.
? Prevents multiple sessions from changing the same data at the same time? is automatically obtained at the lowest possible level of the specified statement? No upgrade session must lock the data to be modified before the database allows the session to modify the data. When locked, the session hasexclusive control over the data so that no other transaction can modify the locked data until the lock is released. Transactions can lock a single row of data, multiple rows of data, or even an entire table. Oracle DB supports manual locking and self-To lock the actuator. Automatically acquired locks always choose the lowest possible lock level to minimize potential for other transactionsconflict. Note: Oracle instances use many types of locks to maintain internal consistency.
? Advanced Data concurrency Processing: –use row-level locks when inserting, updating, and deleting– Does the query require no locks? Automatic queue management? At the end of the transaction (using commit or rollback operation)will remain locked beforeThe locking mechanism is used to provide the highest possible data concurrency in the database. When a transaction modifies data, it getsrow-level locks, not block-level or table-level locks. Object-level locks are obtained when modifying objects, such as table moves, rather than the entire numberaccording to the vault lock or scheme lock. Data queries do not require locks, and even if someone locks the data, the query can be successful (always showing the original, based onrestore information to reconstruct the value before the lock). If multiple transactions require the same resource to be locked, the transaction for the first request lock gets a lock. Other Affairs Officewait until the first transaction is complete. The queuing mechanism is automated and does not require administrator intervention. When the transaction is complete (that is, commit or rollback), all locks are released. If transaction processing fails,The same background process automatically rolls back all changes made by the failed transaction, and then releases the failed transaction holdof all locks.
- Data concurrency Processing
By default, the locking mechanism uses thefine-grained row-level locking mode。 Different transactions can update different types within the same tableline, each other does not interfere. Although the default mode is row-level locking, Oracle DB allows manual locking of:sql> lock TABLE employees in EXCLUSIVE MODE to be performed at a higher level as needed; Table (s) Locked. When you use the above statement, any other transactions that attempt to update rows in the locked table must wait until the lock is issuedThe requested transaction is complete. The EXCLUSIVE is the most stringent lock mode. Some other lock modes are listed below:? ROW SHARE: Allows concurrent access to locked tables, but prohibits locking the entire table in the session for exclusiveaccess. ? ROW EXCLUSIVE: Same as row SHARE, but disables locking in SHARE mode. UpdateThe row EXCLUSIVE Lock is automatically acquired when data is inserted or deleted. ROW EXCLUSIVE locks Allow multipleThe process executes the read, but only one process is allowed to execute the write. ? SHARE:Concurrent queries are allowed, but locked tables are forbidden to be updated. A share lock is required to create the index of the table.the lock is automatically requested when it is created. However, the operation to create an online index requires a row when the index is builtshare lock. A shared lock allows multiple processes to read, but does not allow writes to be performed. Delete or update rows in a parent table,and the child table has a FOREIGN key constraint on the parent table, the shared lock is also used transparently. ? SHARE ROW EXCLUSIVE:Used to query the entire table, allowing other people to query rows in the table, but notOther people lock the table or update the row in share mode. ? EXCLUSIVE: Allows the query to lock the table and prevents any other activity from being performed on the locked table. Need to haveEXCLUSIVE lock to delete a table. As with any lock request, the manual lock statement waits until a lock has been held (or a previous request was locked)release locks for all sessions. The LOCK command accepts special parameter nowait that are used to control the wait behavior. NOWAIT will give you control right away, even if the specified table is locked by another session:Sql> LOCK TABLE hr.employees in SHARE MODE NOWAIT; LOCK TABLE hr.employees in SHARE MODE nowait*error on line 1:ora-00054:resource busy and acquire with NOWAIT specified usually not The object must be locked manually. The auto-lock mechanism provides the data concurrency processing power required by most applications. Oracle recommends that you try not to use manual locking, especially when developing applications. Use an unnecessarily high lock level, there are often serious performance issues.
Each DML transaction must obtain two locks:? Exclusive row locks for one or more rows that are being updated? Row EXCLUSIVE (RX) mode for tables that contain these rowsthe table lock (TM) under the this can be avoided inwhen a row changes, another session locks the entire table (the table may be deleted or truncated). This pattern is also known as the Sub-row tableLock (SX). When you perform a row EXCLUSIVE lock on a table, the DDL command is suppressed halfway through uncommitted transactionsChange the dictionary metadata. This keeps the dictionary integrity and read consistency within the lifetime of the transaction.
The queue mechanism is used for tracking:? A session waiting for a lock? The requested lock mode? Sequential lock requests for session request locks are automatically queued. The next session in the queue receives the lock whenever a transaction that holds a lock is completed.The queue mechanism tracks the order of request locks and the lock mode of the request. A session that already holds a lock can request a conversion lock without having to queue to the end of the queue. For example, suppose a session holds share on a tablelock. The session can request that the share lock be converted to a exclusive lock. If no other transaction has been done on theIf the table holds a exclusive or share lock, the session holding the share lock will be granted a exclusive lock,instead of having to wait in the queue again. Note: The process waiting to be queued is divided into two categories: a wait process without shared ownership, and a shared ownership, but noSelect the wait process to upgrade the lock level. The second type of wait process is called the conversion process, and the priority of such a process is always higher thanwaits for a process normally, even if its wait time is short.
Lock collisions often occur, but are usually resolved over time through the queue mechanism. There are very few cases where the lockconflicts may require administrator intervention. As shown, transaction 2 acquires a lock on a row at 9:00:00 andforgot to commit, thus leaving a lock. Transaction 1 tries to update the entire table at 9:00:05, so you need to lock allline. However, transaction 2 will block transaction 1 until 16:30:01 transaction 2 commits. In this case, if the user tries to perform transaction 1, it is important that the administrator is contacted for assistance and the DBA mustdetects conflicts and resolves conflicts.
- Possible causes of lock conflicts
? Didn't commit the changes? Long-running transaction processing? The most common cause of an unnecessary high lock-level lock conflict is uncommitted changes, but there are some other possible causes:? Long-running transactions: Many applications use batch processing to perform bulk updates. These batch jobs are usuallyIs scheduled to occur when there is no user activity or user activity, however, in some cases, the batch jobThe period of inactivity is not completed or takes too long to run. Perform both transactional and batch processingusually occur when a lock conflict occurs. ? Unnecessary high lock levels: not all databases support row-level locking (Oracle's release in 1988support for row-level locking is added in version 6). Some databases are still locked at the page level or at the table level. Openwhen writing applications to run on many different databases, the high lock level is artificially used,so that Oracle DB operates in the same way as a less functional database system. If the developer is unfamiliar withOracle also sometimes writes code at a lock level higher than the Oracle DB requirement, which is not necessary.
On the Performance (performance) page, select Blocking Sessions(blocking session) ". Click the session ID link to view information about the locked sessioninformation, including actual SQL statements. Use the Blocking Sessions (blocking sessions) page in enterprise Manager to identify lock conflicts. There is a conflictthe lock request is displayed in a hierarchical layout where the session holding the lock is at the top, and the following is the queue request lockthere is a session. For each session involved in the conflict, the user name, the session ID, and the number of seconds that the conversation has been waiting are displayed. Select Session IDYou can view the actual SQL statement that the session is currently executing or requesting. Automatic Database Diagnostic monitor (ADDM) also automatically detects lock collisions and makes recommendations for inefficient locking trends.
In order to resolve the lock conflict, you should:? Commit or rewind a session holding a lock? Terminate a session holding a lock (in case of an emergency) to resolve a lock conflict, the session holding the lock must release the lock. The best way to let a session release a lock is to contact the user askingThe user completes the transaction process. In case of an emergency, an administrator can terminate a session holding a lock by clicking the Kill Session button. ALTER SYSTEM KILL SESSION ' 130,651 ' IMMEDIATE
Keep in mind that all work in the current transaction will be lost (rolled back) after the session is terminated. User with session terminatedyou must log on again, and then redo all the work that has been done since the last commit of the terminated session. If the user's session is terminated, the next time the user tries to issue the SQL statement, they receive the following error:Ora-03135:connection lost Contact[Email protected]> update emp set sal=sal+100 where empno=7369; Error:ora-03114:not connected to ORACLE update emp set sal=sal+100 where empno=7369*error on line 1:ora-03135:connectio N Lost contactprocess id:24763session id:130 Serial number:651 Note: If the session has an idle timeout, the Pmon session detection program automatically terminates the session, which can use the profileor resource Manager to complete.
- Resolving lock conflicts using SQL
You can use an SQL statement to determine the blocking session and terminate the session. [Email protected]> Select Sid, Serial#, USERNAME from V$session where SID in (select Blocking_session from v$session); SID serial# USERNAME--------------------------------------------------675 SCOTT [Email prote Cted]>
alter system kill session ' 96,675 ' immediate;System altered. As with most other tasks performed in Enterprise Manager, session operations can also be made by issuing SQLsentence to complete. The V$session table contains detailed information about all connected sessions. Blocking_session in thethe value is the session ID of the blocking session. If the query Sid and Serial# (where the SID matches the blocking session ID),You will get the information needed to perform the kill session operation. Note: You can use Database Explorer to automatically unregister idle sessions that block other sessions.
Deadlocks are a special case of lock collisions. When two or more sessions are waiting for data that has been locked by another session,A deadlock can occur. Because each session is waiting for another session to release the lock, no one session can do anythingThe conflict can not be resolved. Oracle DBThe deadlock is automatically detected and the statement that the error occurred is terminated。 The appropriate way to deal with this error is to implement the commitor fallback, doing so frees all other locks in the session so that other sessions can continue to complete their transaction processing. In the example, transaction 1 must be committed or rolled back in order to correct the detected deadlock error. If the implementation of the proposedYou must resubmit the second update to complete the transaction. If a fallback is performed, it must be resubmitted at the same timeThese two statements can complete the transaction.
Source: http://blog.csdn.net/rlhua/article/details/12652569
