ITPub博客

Oracle Flashback Query lets you view and repair historical data. You can performqueries on the database as of a certain wall clock time or user-specified system changenumber (SCN).Flashback Query uses Oracle＇s multiversion read-consistency capabilities to restoredata by applying undo as needed. Oracle Database 10g automatically tunes aparameter called the undo retention period. The undo retention period indicates theamount of time that must pass before old undo information—that is, undo inform

With Oracle Lock Management services, an application developer can includestatements in PL/SQL blocks that:■ Request a lock of a specific type■ Give the lock a unique name recognizable in another procedure in the same or inanother instance■ Change the lock type■ Release the lockBecause a reserved user lock is the same as an Oracle lock, it has all the Oracle lockfunctionality including deadlock detection. User locks never conflict with Oracle locks,because they are identified with the prefix

Oracle always performs locking automatically to ensure data concurrency, dataintegrity, and statement-level read consistency. However, you can override the Oracledefault locking mechanisms. Overriding the default locking is useful in situations suchas these:■ Applications require transaction-level read consistency or repeatable reads. In other words, queries in them must produce consistent data for the duration of the transaction, not reflecting changes by other transactions. You can achieve

These locks protect tablespaces and rollbacksegments. For example, all instances accessing a database must agree on whether atablespace is online or offline. Rollback segments are locked so that only one instancecan write to a segment.表空间和回滚段锁1. 保护表空间和回滚段 .一个表空间处于联机还是脱机状态对访问同一数据库的所有实例应该是一致的。回滚段上的锁保证只有一个实例能够对其执行写操作[@more@]

These locks protect various files. For example, one lockprotects the control file so that only one process at a time can change it. Another lockcoordinates the use and archiving of the redo log files. Datafiles are locked to ensurethat multiple instances mount a database in shared mode or that one instance mountsit in exclusive mode. Because file and log locks indicate the status of files, these locksare necessarily held for a long time.文件和日志管理锁1. 这些锁用户保护各种文件.如保护控制文件

These locks are of very short duration and are held on entries indictionary caches while the entries are being modified or used. They guarantee thatstatements being parsed do not see inconsistent object definitions.Dictionary cache locks can be shared or exclusive. Shared locks are released when theparse is complete. Exclusive locks are released when the DDL operation is complete.字典缓存锁1. 当用户更新或使用时数据字典缓存内的条目时，需要获取条目上的字典缓存锁.保证被解析的语句不会看到不一致的

Internal locks are higher-level, more complex mechanisms than latches and serve avariety of purposes.内部锁与闩锁相比 , 内部锁是一种更高层且更复杂的锁机制[@more@]

Latches are simple, low-level serialization mechanisms to protect shared datastructures in the system global area (SGA). For example, latches protect the list of userscurrently accessing the database and protect the data structures describing the blocksin the buffer cache. A server or background process acquires a latch for a very shorttime while manipulating or looking at one of these structures. The implementation oflatches is operating system dependent, particularly in regard to whether and h

Latches and internal locks protect internal database and memory structures. Both areinaccessible to users, because users have no need to control over their occurrence orduration. The following section helps to interpret the Enterprise Manager LOCKS andLATCHES monitors.闩锁和内部锁1. 用来保护数据库内部和内存结构2. 对用户不可访问[@more@]

A DDL operation on a cluster acquires exclusive DDL locks on the cluster and on alltables and materialized views in the cluster. A DDL operation on a table ormaterialized view in a cluster acquires a share lock on the cluster, in addition to ashare or exclusive DDL lock on the table or materialized view. The share DDL lock onthe cluster prevents another operation from dropping the cluster while the firstoperation proceeds.DDL锁和簇1. 在一个簇上的DDL操作需要获取在簇上的所有表和物化视图的DDL锁

The duration of a DDL lock depends on its type. Exclusive and share DDL locks last for the duration of DDL statement execution and automatic commit. A parse lock persists as long as the associated SQL statement remains in the shared pool. DDL锁的持续时间 1. 共享和排他DDL锁从DDL语句执行开始, 到自动提交结束 2. 只要相关的SQL语句还在共享池中 , 解析锁就存在[@more@]

A SQL statement (or PL/SQL program unit) in the shared pool holds a parse lock for each schema object it references. Parse locks are acquired so that the associated shared SQL area can be invalidated if a referenced object is altered or dropped. A parse lock does not disallow any DDL operation and can be broken to allow conflicting DDL operations, hence the name breakable parse lock. A parse lock is acquired during the parse phase of SQL statement execution and held as long as the shared

Some DDL operations require share DDL locks for a resource to prevent destructive interference with conflicting DDL operations, but allow data concurrency for similar DDL operations. For example, when a CREATE PROCEDURE statement is run, the containing transaction acquires share DDL locks for all referenced tables. Other transactions can concurrently create procedures that reference the same tables and therefore acquire concurrent share DDL locks on the same tables, but no transaction can

Most DDL operations, except for those listed in the section, "Share DDL Locks" require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same schema object. For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa. During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the schema object by anot

A data dictionary lock (DDL) protects the definition of a schema object while thatobject is acted upon or referred to by an ongoing DDL operation. Recall that a DDLstatement implicitly commits its transaction. For example, assume that a user creates aprocedure. On behalf of the user’s single-statement transaction, Oracle automaticallyacquires DDL locks for all schema objects referenced in the procedure definition. TheDDL locks prevent objects referenced in the procedure from being altered or dr

The locking characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATEstatements are as follows:■ The transaction that contains a DML statement acquires exclusive row locks on therows modified by the statement. Other transactions cannot update or delete thelocked rows until the locking transaction either commits or rolls back.■ The transaction that contains a DML statement does not need to acquire row lockson any rows selected by a subquery or an implicit query, such as a query in aW

Queries are the SQL statements least likely to interfere withother SQL statements because they only read data. INSERT, UPDATE, and DELETEstatements can have implicit queries as part of the statement. Queries include thefollowing kinds of statements:SELECTINSERT ... SELECT ... ;UPDATE ... ;DELETE ... ;They do not include the following statement:SELECT ... FOR UPDATE OF ... ;The following characteristics are true of all queries that do not use the FOR UPDATEclause:■ A query acquires no data locks

The previous sections explained the different types of data locks, the modes in whichthey can be held, when they can be obtained, when they are obtained, and what theyprohibit. The following sections summarize how Oracle automatically locks data onbehalf of different DML operations.执行DML语句时自动获得的DML锁, 获取的行锁和表锁见下表Locks Obtained By DML StatementsDML Statement Row Locks? Mode of Table LockSELECT ... FROM tableINSERT INTO table ...

An exclusive table lock is the most restrictive mode of tablelock, allowing the transaction that holds the lock exclusive write access to the table. Anexclusive table lock is acquired for a table as follows:LOCK TABLE table IN EXCLUSIVE MODE;Permitted Operations: Only one transaction can obtain an exclusive table lock for a table.An exclusive table lock permits other transactions only to query the table.Prohibited Operations: An exclusive table lock held by a transaction prohibits othertransacti

A share row exclusive table lock (also sometimescalled a share-subexclusive table lock, SSX) is more restrictive than a share table lock.A share row exclusive table lock is acquired for a table as follows:LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;Permitted Operations: Only one transaction at a time can acquire a share row exclusivetable lock on a given table. A share row exclusive table lock held by a transactionallows other transactions to query or lock specific rows using SELECT with the FO