ITPub博客

In some cases, a site assigns one or more security officers to a database. A securityofficer enrolls users, controls and monitors user access to the database, and maintainssystem security. As a DBA, you might not be responsible for these duties if your sitehas a separate security officer. Please refer to Oracle Database Security Guide forinformation about the duties of security officers.Oracle数据库用户之 : 安全管理员1. 创建用户 , 管理监控用户 , 维护系统安全[@more@]

Each database requires at least one database administrator (DBA). An Oracle Databasesystem can be large and can have many users. Therefore, database administration issometimes not a one-person job, but a job for a group of DBAs who shareresponsibility.A database administrator＇s responsibilities can include the following tasks:■ Installing and upgrading the Oracle Database server and application tools■ Allocating system storage and planning future storage requirements for thedatabase system■

■ Flashback Query does not undo anything. It is only a querymechanism. You can take the output from a Flashback Query andperform an undo yourself in many circumstances.■ Flashback Query does not tell you what changed. LogMiner doesthat.■ Flashback Query can undo changes and can be very efficient ifyou know the rows that need to be moved back in time. You canuse it to move a full table back in time, but this is very expensive ifthe table is large since it involves a full table copy.■ Flashbac

This section lists some ways to use Flashback Query.■ Self-Service RepairPerhaps you accidentally deleted some important rows from a table and wanted torecover the deleted rows. To do the repair, you can move backward in time and seethe missing rows and re-insert the deleted row into the current table.■ E-mail or Voice Mail ApplicationsYou might have deleted mail in the past. Using Flashback Query, you can restorethe deleted mail by moving back in time and re-inserting the deleted message into

This section lists some of the benefits of using Flashback Query.■ Application TransparencyPackaged applications, like report generation tools that only do queries, can run inFlashback Query mode by using logon triggers. Applications can runtransparently without requiring changes to code. All the constraints that theapplication needs to be satisfied are guaranteed to hold good, because there is aconsistent version of the database as of the Flashback Query time.■ Application PerformanceIf an ap

You set the date and time you want to view. Then, any SQL query you run operates ondata as it existed at that time. If you are an authorized user, then you can correcterrors and back out the restored data without needing the intervention of anadministrator.With the AS OF SQL clause, you can choose different snapshots for each table in thequery. Associating a snapshot with a table is known as table decoration. If you do notdecorate a table with a snapshot, then a default snapshot is used for it.

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