OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

133
lượt xem 20
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P17

Identifying PL/SQL Objects 731 ,’No grant to PUBLIC allowed for ‘ ||DICTIONARY_OBJ_OWNER||’.’ ||DICTIONARY_OBJ_NAME); END IF; END LOOP; END; In the preceding example, the DDL event is a GRANT statement issued by user engineering. The code examines the grantee list, and if it finds the special user/role PUBLIC, an exception is raised, causing the grant to fail. Table 13.2 shows the DDL trigger events. ta b l e 1 3 . 2 DDL Trigger Events Event When It Fires [BEFORE/AFTER] ALTER When an ALTER statement changes a database object [BEFORE/AFTER] ANALYZE When the database gathers or deletes statistics or validates the structure of an object [BEFORE/AFTER] ASSOCIATE When the database associates a statistic with a database STATISTICS object with an ASSOCIATE STATISTICS statement [BEFORE/AFTER] AUDIT When the database records an audit action (except FGA) [BEFORE/AFTER] COMMENT When a comment on a table or column is modified [BEFORE/AFTER] CREATE When the database object is created [BEFORE/AFTER] DDL In conjunction with any of the following: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE [BEFORE/AFTER] When a database disassociates a statistic type from a database DISASSOCIATE STATISTICS object with a DISASSOCIATE STATISTICS statement [BEFORE/AFTER] DROP When a DROP statement removes an object from the database [BEFORE/AFTER] GRANT When a GRANT statement assigns a privilege [BEFORE/AFTER] NOAUDIT When a NOAUDIT statement changes database auditing [BEFORE/AFTER] RENAME When a RENAME statement changes an object name [BEFORE/AFTER] REVOKE When a REVOKE statement rescinds a privilege [BEFORE/AFTER] TRUNCATE When a TRUNCATE statement purges a table
732 Chapter 13 N Managing Data and Undo Database Trigger Events Database event triggers fire when the specified database-level event occurs. Most of these triggers are available only before or after the database event, but not both. The following example creates an after-server error trigger that sends an email notifica- tion when an ORA-01555 error occurs: CREATE OR REPLACE TRIGGER Email_on_1555_Err AFTER SERVERERROR ON DATABASE DECLARE mail_conn UTL_SMTP.connection; smtp_relay VARCHAR2(32) := ‘mailserver’; recipient_address VARCHAR2(64) := ‘DBA@hotmail.com’; sender_address VARCHAR2(64) := ‘oracle@sybex.com’; mail_port NUMBER := 25; msg VARCHAR2(200); BEGIN IF USER = ‘SYSTEM’ THEN -- Ignore this error NULL; ELSIF IS_SERVERERROR (1555) THEN -- compose the message msg := ‘Subject: ORA-1555 error’; msg := msg||’Snapshot too old err at ‘||systimestamp; -- send email notice mail_conn := UTL_SMTP.open_connection(smtp_relay ,mail_port); UTL_SMTP.HELO(mail_conn, smtp_relay); UTL_SMTP.MAIL(mail_conn, sender_address); UTL_SMTP.RCPT(mail_conn, recipient_address); UTL_SMTP.DATA(mail_conn, msg); UTL_SMTP.QUIT(mail_conn); END IF; END; Be careful when using database triggers. Fully test them in development before deploying them to production. Table 13.3 shows the database trigger events. ta b l e 1 3 . 3 Database Trigger Events Event When It Fires AFTER LOGON When a database session is established—only the AFTER trigger is allowed BEFORE LOGOFF When a database session ends normally—only the BEFORE trigger is allowed
Identifying PL/SQL Objects 733 ta b l e 1 3 . 3 Database Trigger Events (continued) Event When It Fires AFTER STARTUP When the database is opened—only the AFTER trigger is allowed BEFORE SHUTDOWN When the database is closed—only the BEFORE trigger is allowed AFTER SERVERERROR When a database exception is raised—only the AFTER trigger is allowed AFTER SUSPEND When a server error causes a transaction to be suspended—only the AFTER trigger is allowed Enabling and Disabling Triggers The database automatically enables a trigger when you create it. After creating a trigger, you can disable (temporarily prevent it from firing) or reenable it. You can disable and enable triggers by name with an ALTER TRIGGER statement. Here are two examples: ALTER TRIGGER after_ora60 DISABLE; ALTER TRIGGER load_packages ENABLE; Alternatively, you can enable and disable multiple DML triggers with an ALTER TABLE statement, like this: ALTER TABLE employees DISABLE ALL TRIGGERS; ALTER TABLE employees ENABLE ALL TRIGGERS; You can also create a trigger with the ENABLE or DISABLE clause. ENABLE is the default. You can query the STATUS column of the DBA_TRIGGERS view to find out whether a trig- ger is enabled or disabled. Using and Administering PL/SQL Programs Oracle 11g comes bundled with hundreds of built-in packages that give you significant capabilities for administering your database. Many features in the database are imple- mented through one or more of these built-in packages. To use the job scheduler, collect and manage optimizer statistics, implement fine-grained auditing, send email from the database, and use Data Pump or Log Miner, you must engage built-in packages. As you gain experience, you will use these built-in packages more extensively. These are some of the commonly used built-in catalog packages: NÛ DBMS_STATS NÛ DBMS_METADATA NÛ DBMS_MONITOR
734 Chapter 13 N Managing Data and Undo NÛ UTL_FILE NÛ UTL_MAIL To view the names and parameter lists for stored programs (except triggers), use the SQL*Plus DESCRIBE command like this: describe dbms_monitor -- some output is deleted for brevity PROCEDURE SESSION_TRACE_DISABLE Argument Name Type In/Out Default? --------------- ------------------- ------ -------- SESSION_ID BINARY_INTEGER IN DEFAULT SERIAL_NUM BINARY_INTEGER IN DEFAULT PROCEDURE SESSION_TRACE_ENABLE Argument Name Type In/Out Default? --------------- ------------------- ------ -------- SESSION_ID BINARY_INTEGER IN DEFAULT SERIAL_NUM BINARY_INTEGER IN DEFAULT WAITS BOOLEAN IN DEFAULT BINDS BOOLEAN IN DEFAULT PLAN_STAT VARCHAR2 IN DEFAULT You can see in this output from DESCRIBE that the packaged procedure DBMS_MONITOR con- tains several procedures, including SESSION_TRACE_DISABLE and SESSION_TRACE_ENABLE. Furthermore, you can see the names, datatypes, and in/out mode for each parameter (SESSION_ID, SERIAL_NUM, and so on). An extensive list of Oracle built-in PL/SQL packages is available in the manual “Oracle Database PL/SQL Packages and Types Reference 11g Release 1 (11.1) Part Number B28419-03.” Fortunately, you don’t have to know all these programs for the certification exam! A PL/SQL program may be invalidated when a dependent object is changed through the ALTER command. The database automatically recompiles the package body the next time it is called, but you can choose to compile invalid PL/SQL programs yourself and thus elimi- nate the costly recompile during regular system processing. To explicitly compile a named SQL program, use the ALTER…COMPILE statement, like this: ALTER PROCEDURE archive_orders COMPILE; ALTER FUNCTION is_weekend COMPILE; ALTER PACKAGE table_util COMPILE;
Monitoring Locks and Resolving Lock Conflicts 735 ALTER PACKAGE table_util COMPILE BODY; ALTER TRIGGER fire_me COMPILE; Other objects, such as views or types, are similarly compiled. Oracle 11g implements a finer-grained dependency control; hence, if the package speci- fication is not changed, the PL/SQL objects that reference the functions and procedures of the package are not invalidated when only the package body is changed. Monitoring Locks and Resolving Lock Conflicts In any database with many users, you will eventually have to deal with locking conflicts when two or more users try to change the same row in the database. In the following sec- tions, I’ll present an overview of how locking works in the Oracle Database, how users are queued for a particular resource once it is locked, and how Oracle classifies lock types in the database. Then, I’ll show you a number of ways to detect and resolve locking issues; I’ll also cover a special type of lock situation: the deadlock. Understanding Locks and Transactions Locks prevent multiple users from changing the same data at the same time. Before one or more rows in a table can be changed, the user executing the DML statement must obtain a lock on the row or rows; a lock gives the user exclusive control over the data until the user has committed or rolled back the transaction that is changing the data. In Oracle 11g, a transaction can lock one row, multiple rows, or an entire table. Although you can manually lock rows, Oracle can automatically lock the rows needed at the lowest possible level to ensure data integrity and minimize conflicts with other transac- tions that may need to access other rows in the table. In Table 13.4, both updates to the EMPLOYEES table return to the command prompt immediately after the UPDATE because the locks are on different rows in the EMPLOYEES table and neither session is waiting for the other lock to be released. ta b l e 1 3 . 4 Concurrent Transactions on Different Rows of the Same Table Session 1 Time Session 2 update employees set salary = 11:29 update employees set manager = 100 salary * 1.2 where employee_id = where employee_id = 109; 102; commit; 11:30 commit;
736 Chapter 13 N Managing Data and Undo packaged applications and locking The HR department recently purchased a benefits-management package that interfaced well with our existing employee-management tables; however, once HR started using the application, other users who accessed the employee tables started complaining of severe slowdowns in updates to the employee information. Reviewing the CPU and I/O usage of the instance did not reveal any problems, and it wasn’t until we looked at the locking information that we noticed a table lock on the employees table whenever the benefits-management features were being used! The benefits-management application was written to work on a number of database platforms, and the least capable of those platforms did not support row locking. As a result, no one could make changes to the employees table whenever an employee’s benefits were being changed, and every- one had to wait for the benefits changes to complete. Fortunately, the parameter file for the benefits-management package had an option to specify Oracle as the target platform; after setting the specific database version in the package’s parameter file, the package was smart enough to use row locking instead of table locking whenever the employee table needed to be updated. Queries never require a lock. Even if another transaction has locked several rows or an entire table, a query always succeeds, using the prelock image of the data stored in the undo tablespace. If multiple users require a lock on a row or rows in a table, the first user to request the lock obtains it, and the remaining users are enqueued using a first-in, first-out (FIFO) method. At a SQL> command prompt, a DML statement (INSERT, UPDATE, DELETE, or MERGE) that is waiting for a lock on a resource appears to hang, unless the NOWAIT keyword is used in a LOCK statement. The WAIT and NOWAIT keywords are explained in the next section, “Maxi- mizing Data Concurrency.” At the end of a transaction, when either a COMMIT or a ROLLBACK is issued (either explic- itly by the user or implicitly when the session terminates normally or abnormally), all locks are released. Maximizing Data Concurrency Rows of a table are locked either explicitly by the user at the beginning of a transaction or implicitly by Oracle, usually at the row level, depending on the operation. If a table must
Monitoring Locks and Resolving Lock Conflicts 737 be locked for performance reasons (which is rare), you can use the LOCK TABLE command, specifying the level at which the table should be locked. In the following example, you lock the EMPLOYEES and DEPARTMENTS tables at the highest possible level, EXCLUSIVE: SQL> lock table hr.employees, hr.departments in exclusive mode; Table(s) Locked. Until the transaction with the LOCK statement either commits or rolls back, only queries are allowed on the EMPLOYEES or DEPARTMENTS table. In the sections that follow, I will review the lock modes, as well as show you how to avoid the lock enqueue process and terminate the command if the requested resource is already locked. Lock Modes Lock modes provide a way for you to specify how much and what kinds of access other users have on tables that you are using in DML commands. In Table 13.5, you can see the types of locks that can be obtained at the table level. ta b l e 1 3 . 5 Table Lock Modes Table Lock Mode Description ROW SHARE Permits concurrent access to the locked table but prohibits other users from locking the entire table for exclusive access. ROW EXCLUSIVE Same as ROW SHARE but also prohibits locking in SHARE mode. This type of lock is obtained automatically with standard DML com- mands such as UPDATE, INSERT, or DELETE. SHARE Permits concurrent queries but prohibits updates to the table; this mode is required to create an index on a table and is automatically obtained when using the CREATE INDEX statement. SHARE ROW EXCLUSIVE Used to query a whole table and to allow other users to query the table but to prevent other users from locking the table in SHARE mode or updating rows. EXCLUSIVE The most restrictive locking mode; permits queries on the locked table but prohibits DML by any other users. This mode is required to drop the table and is automatically obtained when using the DROP TABLE statement. Manual lock requests wait in the same queue as implicit locks and are satisfied in a FIFO manner as each request releases the lock with an implicit or explicit COMMIT or ROLLBACK.
738 Chapter 13 N Managing Data and Undo You can explicitly obtain locks on individual rows by using the SELECT … FOR UPDATE statement, as you can see in the following example: SQL> select * from hr.employees where manager_id = 100 for update; Not only does this query show the rows that satisfy the query conditions, but it also locks the selected rows and prevents other transactions from locking or updating these rows until a COMMIT or ROLLBACK occurs. NOWAIT Mode Using NOWAIT in a LOCK TABLE statement returns control to the user immediately if any locks already exist on the requested resource, as you can see in the following example: SQL> lock table hr.employees in share row exclusive mode nowait; lock table hr.employees * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> This is especially useful in a PL/SQL application if an alternate execution path can be followed if the requested resource is not yet available. NOWAIT can also be used in the SELECT … FOR UPDATE statement. WAIT Mode You can tell Oracle 11g to wait a specified number of seconds to acquire a DML lock. If you do not specify a NOWAIT or WAIT clause, then the database waits indefinitely if the table is locked by another user. In the following example, Oracle will wait for 60 seconds to acquire the lock. If the lock is not acquired within 60 seconds, an error is returned. SQL> lock table hr.employees in share row exclusive mode wait 60; DDL Lock Waits When DML statements have rows locked in a table or if the table is manually locked by a user, DDL statements on the table fail with the ORA-00054 error. To have the DDL state- ments wait for a specified number of seconds before throwing the ORA-00054 error, you
Monitoring Locks and Resolving Lock Conflicts 739 can set the initialization parameter DDL_LOCK_TIMEOUT. The default value is 0, which means the error is issued immediately. You can specify a value up to 1,000,000 seconds. SQL> alter table hr.employees modify salary number (15,2); alter table hr.employees modify salary number (15,2) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> show parameter ddl_lock NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 SQL> Detecting and Resolving Lock Conflicts Although locks are a common and sometimes unavoidable occurrence in many databases, they are usually resolved by waiting in the queue. In some cases, you may need to resolve the lock problem manually (such as if a user makes an update at 4:59 p.m. and does not perform a COMMIT before leaving for the day). In the next few sections, I will describe in more detail some of the reasons that lock conflicts occur and how to detect lock conflicts, and I’ll discuss a more specific and serious type of lock conflict: a deadlock. Understanding Lock Conflicts In addition to the proverbial user who makes a change at 4:59 p.m. and forgets to perform a COMMIT before leaving for the day, other more typical lock conflicts are caused by long- running transactions that perform hundreds, thousands, or even hundreds of thousands of DML commands in the overnight batch run but are not finished updating the tables when the normal business day starts. The uncommitted transactions from the overnight batch jobs may lock tables that need to be updated by clerical staff during the business day, caus- ing a lock conflict. Another typical cause of lock conflicts is using unnecessarily high locking levels. In the “Packaged Applications and Locking” sidebar earlier in this chapter, we described a third- party application that routinely locked resources at the table level instead of at the row level to be compatible with every SQL-based database on the market. Developers may unneces- sarily code updates to tables with higher locking levels than required by Oracle 11g. Detecting Lock Conflicts Detecting locks in Oracle 11g using EM Database Control makes your job easy; you don’t need to query against V$SESSION, V$TRANSACTION, V$LOCK, and V$LOCKED_OBJECT to see who
740 Chapter 13 N Managing Data and Undo is locking what resource. You can click the Instance Locks link on the Performance tab of EM Grid Control. In Figure 13.1, you can see the tables locked by the user SCOTT after executing the following statement: SQL> lock table hr.employees, hr.departments in exclusive mode; Table(s) Locked. F i g U r e 1 3 .1 The Instance Locks screen in EM Database Control
Monitoring Locks and Resolving Lock Conflicts 741 SCOTT has an EXCLUSIVE lock on both the EMPLOYEES and DEPARTMENTS tables. You can drill down on the locked object by clicking one of the links in the Object Name column; similarly, you can review other information about SCOTT’s session by clicking one of the links in the Session ID column. If the HR user performs the following SQL, HR’s session will wait until the SCOTT user releases the locks: SQL> UPDATE employees SET salary = 0 WHERE salary IS NULL; On the EM Grid Control screen, choose Blocking Locks from the drop-down view, and you can see that user SCOTT is blocking the HR user, as shown in Figure 13.2. F i g U r e 13 . 2 The blocking locks shown in EM Database Control The data dictionary view DBA_LOCK is very handy for the DBA to look for locks and whether any session is blocking other users. A session with value of Blocking in the BLOCKING_OTHERS column may have to be manu- ally terminated using the ALTER SYSTEM KILL SESSION statement. DBA_ WAITERS is another view that shows only the sessions that are waiting on a lock. This view shows the holding session and the waiting session.
742 Chapter 13 N Managing Data and Undo Understanding and Resolving Deadlocks Resolving a lock conflict, the user can either COMMIT or ROLLBACK the current transaction. If you cannot contact the user and it is an emergency, you can select the session holding the lock and click the Kill Session button on the Instance Locks screen of the EM Database Control (refer to Figure 13.1, earlier in this chapter). The next time the user whose session has been killed tries to execute a command, the error message “ORA-00028: Your session has been killed” is returned. Again, this is an option of last resort: you’ll lose all the state- ments executed in the session since the last COMMIT. A more serious type of lock conflict is a deadlock. A deadlock is a special type of lock conflict in which two or more users are waiting for a resource locked by the other users. As a result, neither transaction can complete without some kind of intervention: the session that first detects a deadlock rolls back the statement waiting on the resource with the error message “ORA-00060: Deadlock detected while waiting for resource.” Oracle automati- cally resolves deadlocks without user/DBA intervention. In Table 13.6, two sessions are attempting to update a row locked by the other session. ta b l e 1 3 . 6 Deadlock Scenario Session 1 Time Session 2 update employees set salary = 11:29 update employees set manager = 100 salary * 1.2 where employee_id = where employee_id = 109; 102; update employees set salary = 11:44 salary * 1.2 where employee_id = 109; 11:50 update employees set manager = 100 where employee_id = 102; Prior to 11:44, session 1 and session 2 updated two different rows in the database and did not commit the transaction. At 11:44, session 1 issued an UPDATE statement against the same row locked by session 2. This causes session 1 to hang, waiting for the lock to be released by session 2. The lock held by session 2 will be released only when session 2 performs a commit or rollback. At 11:50, when session 2 is trying to update a row already locked by session 1, you have a deadlock situation: session 1 waiting on session 2 and ses- sion 2 waiting on session 1. When this situation forms, Oracle throws out the ORA-00060 error and fails the statement. Remember, the transaction is not rolled back, because only the statement is in error. In our example, session 2 will get the ORA-00060 error when the update at 11:50 is issued, but session 1 will wait until session 2 commits or rolls back.
Leveraging Undo Management 743 Leveraging Undo Management Whenever a process or a user session changes data in the database, Oracle saves the old value as it existed before it was modified as undo data. This provides a number of benefits to the database user: NÛ It lets the user change their minds and roll back, or undo, the change to the database. NÛ It supports read-consistent queries. Once a query starts, any changes to the query’s underlying tables are not reflected in the query’s results. NÛ It supports flashback query, an Oracle feature introduced in Oracle9i. Flashback query allows a user to see how a table looked at some point in the past. As long as the undo data still exists for the requested point of time, flashback queries are possible. In the following sections, I present all aspects of undo management. First, I will show how transactions are related to undo management and how undo records are stored in an undo tablespace along with some of the features supported by undo records. Next, I will show you how to set up the initialization parameters to specify a target for how much undo is retained in the undo tablespace; in addition, I will show you the commands needed to guarantee that undo space is available for SELECT statements at the expense of DML commands. Monitoring an undo tablespace is not unlike monitoring any other tablespace: you want to make sure you have enough undo space in the tablespace to satisfy all types of user trans- actions but not so much that you’re wasting space that can be used for objects in other tablespaces. Therefore, I will present some methods to accurately calculate the optimal amount of undo space you will need. Finally, I will review the notification methods you can use to proactively alert you to problems with the undo tablespace. Understanding Undo Segments Undo segments, also known as rollback segments, are similar to other segments in the database, such as table or index segments, in that an undo segment consists of extents, which in turn consist of data blocks. Also, an undo segment contains data similar to that stored in a table. However, that is where the similarity ends. Undo segments must be stored in a special type of tablespace called an undo tablespace. Although a database can have more than one undo tablespace, only one undo tablespace can be active at any one time. Undo segments contain undo information about one or many tables involved in a transac- tion. Also, undo segments automatically grow and shrink as needed, acting as a circular buffer—transactions that fill up the extents in an undo segment can wrap around to the beginning of the segment if the first extent is not being used by an active transaction. At the beginning of a transaction—in other words, when the first DML command is issued after a previous COMMIT or a user first connects to the database—the transaction is assigned to an undo segment in the undo tablespace. Any changes to any table in the trans- action are recorded in the assigned undo segment. The names of the current active undo
744 Chapter 13 N Managing Data and Undo segments can be retrieved from the dynamic performance view V$ROLLNAME, as you can see in the following query: SQL> select * from v$rollname; USN NAME ---------- ---------------------- 0 SYSTEM 1 _SYSSMU1_1192467665$ 2 _SYSSMU2_1192467665$ 3 _SYSSMU3_1192467665$ 4 _SYSSMU4_1192467665$ 5 _SYSSMU5_1192467665$ 6 _SYSSMU6_1192467665$ 7 _SYSSMU7_1192467665$ 8 _SYSSMU8_1192467665$ 9 _SYSSMU9_1192467665$ 10 _SYSSMU10_1192467665$ 11 rows selected. The data dictionary view DBA_ROLLBACK_SEGS shows both active (online) and inactive (offline) undo segments in both the SYSTEM and undo tablespaces. The undo segment with an undo segment number (USN) of 0 is an undo segment reserved for exclusive use by system users such as SYS or SYSTEM or if no other undo segments are online and the data being changed resides in the SYSTEM tablespace. In this example, nine other undo segments are available in the undo tablespace for user transactions. The dynamic performance view V$TRANSACTION shows the relationship between a trans- action and the undo segments. In the following query, you begin a transaction and then join V$TRANSACTION to V$ROLLNAME to find out the name of the undo segment assigned to the transaction: SQL> set transaction name ‘Update clerk salaries’; Transaction set. SQL> update hr.employees set salary = salary * 1.25 where job_id like ‘%CLERK’; 44 rows updated.
Leveraging Undo Management 745 SQL> select xid, status, start_time, xidusn seg_num, r.name seg_name from v$transaction t join v$rollname r on t.xidusn = r.usn where t.name = ‘Update clerk salaries’; XID STATUS START_TIME SEG_NUM SEG_NAME ---------------- ------- ----------------- ------- -------------------- 05001100DD020000 ACTIVE 09/25/08 03:03:34 5 _SYSSMU5_1192467665$ 1 row selected. The column XID is the internally assigned, unique transaction number assigned to this transaction, and it is assigned the undo segment _SYSSMU5_1192467665$. The column XIDUSN (aliased as SEG_NUM in the query) is the undo segment number for _SYSSMU5_1192467665$. A transaction can reside in only one undo segment; it cannot be moved to another undo segment. However, many different transactions can use the same undo segment. If an extent in the assigned undo segment fills up and more space is required, the next available extent is used; if all extents in the segment are needed for current transactions, a new extent is allocated for the undo segment. All undo segments are owned by SYS, regardless of who is making changes in a transac- tion. Each segment must have a minimum of two extents; the maximum number of extents in an undo segment is high: for an undo tablespace with a block size of 8KB, the default maximum number of extents per undo segment is 32,765. During a media failure with an undo tablespace, the tablespace can be recovered using archived and online redo log files just as with any other tablespace; however, the instance must be in a MOUNT state to recover an undo tablespace. Tablespace recovery is discussed in Chapter 16, “Recovering the Database.” Using Undo Data Undo data is the old value of data when a process or user changes data in a table or an index. Undo data serves four purposes in an Oracle Database: NÛ User rollback of a transaction NÛ Read consistency of DML operations and queries NÛ Database recovery operations NÛ Flashback functionality
746 Chapter 13 N Managing Data and Undo User Transaction Rollback In Chapter 8, “Introducing Oracle Database 11g Components and Architecture,” you learned about transactions and how they are managed within the database architecture. At the user level, you might have one or hundreds of DML commands (such as DELETE, INSERT, UPDATE, or MERGE) within a particular transaction that need to be undone by a user or a process that is making changes to one or more tables. Undoing the changes within a transaction is called rolling back part or all of the transaction. The undo information needed to roll back the changes is called, appropriately, the rollback information and is stored in a special type of tablespace called an undo tablespace. When an entire transaction is rolled back, Oracle undoes all the changes since the begin- ning of the transactions, using the saved undo information in the undo tablespace, releases any locks on rows involved in the transaction, and ends the transaction. If a failure occurs on the client or a network, abnormally terminating the user’s connec- tion to the database, undo information is used in much the same way as if the user explicitly rolled back the transaction, and Oracle undoes all the changes since the beginning of the transaction, using information saved in the undo tablespace. Read Consistency Undo also provides read consistency for users who are querying rows involved in a DML transaction by another user or session. When one user starts to make changes to a table after another user has already begun a query against the table, the user issuing the query will not see the changes to the table until after the query has completed and the user issues a new query against the table. Undo segments in an undo tablespace are used to reconstruct the data blocks belonging to the table to provide the previous values of the rows for any user issuing SELECT statements against the table before the DML statements’ transaction commits. For example, the user KELSIEJ begins a transaction at 3 p.m. that contains several long- running DML statements against the EMPLOYEES table; the statements aren’t expected to finish until 3:15 p.m. As each DML command is issued, the previous values of each row are saved in the transaction’s undo segment. At 3:05 p.m., the user SARAHCR issues a SELECT against the EMPLOYEES table; none of the changes made so far by KELSIEJ are visible to SARAHCR. The undo tablespace provides the previous values of the EMPLOYEES table to SARAHCR and any other users querying the EMPLOYEES table between 3 p.m. and 3:15 p.m. Even if SARAHCR’s query is still running at 3:20 p.m., the query still appears as it did at 3 p.m. before KELSIEJ started making changes. INSERT statements use little space in an undo segment; only the pointer to the new row is stored in the undo tablespace. To undo an INSERT state- ment, the pointer locates the new row and deletes it from the table if the transaction is rolled back.
Leveraging Undo Management 747 In a few situations, either SARAHCR’s query or KELSIEJ’s DML statements might fail, because the undo tablespace is not sized correctly or because the undo retention period is too short. You can also apply read consistency to an entire transaction instead of just a single SELECT statement by using the SET TRANSACTION statement as follows: SQL> set transaction read only; Transaction set. Until the transaction is either rolled back or committed, all queries in the transaction see only those changes to other tables that were committed before the transaction began. Only the following statements are permitted in a read-only transaction: NÛ SELECT statements without the FOR UPDATE clause NÛ LOCK TABLE NÛ SET ROLE NÛ ALTER SESSION NÛ ALTER SYSTEM In other words, a read-only transaction cannot contain any statement that changes data in a table, regardless of where the table resides. For example, although an ALTER USER com- mand does not change data in the USERS or any other non-SYSTEM tablespace, it does change the data dictionary tables and therefore cannot be used in a read-only transaction. Monitoring, Configuring, and Administering Undo Compared with configuring rollback operations in releases previous to Oracle9i, managing undo in later versions of Oracle requires little intervention. However, two particular situ- ations will trigger intervention: either not enough undo space to handle all active transac- tions or not enough undo space to satisfy long-running queries that need undo information for read consistency. Running out of undo space for transactions generates messages such as “ORA-01650: Unable to extend rollback segment”; long-running queries whose undo entries have been reused by current transactions typically receive the “ORA-01555: Snap- shot too old” message. In the following sections, I will show you how to configure the undo tablespace using two initialization parameters: UNDO_MANAGEMENT and UNDO_TABLESPACE. I will also present the methods available for monitoring the health of the undo tablespace, as well as using EM Database Control’s Undo Advisor to size or resize the undo tablespace. Using the dynamic performance view V$UNDOSTAT, you can calculate an optimal size for the undo tablespace if the Undo Advisor is not available. Finally, I will show you how to guarantee that long-running queries will have undo entries available, even if it means that a DML transaction fails, by using the RETENTION GUARANTEE option.
748 Chapter 13 N Managing Data and Undo Configuring the Undo Tablespace Manual undo management is not recommended, although it is still available in Oracle 11g. Instead, use manual undo management only for compatibility with Oracle8i or ear- lier. Automatic undo management is the default for the Oracle 11g database. To configure automatic undo management, use the initialization parameters UNDO_MANAGEMENT, UNDO_ TABLESPACE, and UNDO_RETENTION. UNDO_MANAGEMENT The parameter UNDO_MANAGEMENT specifies the way in which undo data is managed in the database: either manually using rollback segments or automatically using a single tablespace to hold undo information. The allowed values for UNDO_MANAGEMENT are MANUAL and AUTO. To change the undo- management mode, you must restart the instance. This parameter is not dynamic, as you can see in the following example: SQL> alter system set undo_management = manual; set undo_management = manual * ERROR at line 2: ORA-02095: specified initialization parameter cannot be modified If you are using an spfile, you can change the value of this parameter in the spfile only and then restart the instance for the parameter to take effect, as follows: SQL> alter system set undo_management = manual scope=spfile; System altered. UNDO_TABLESPACE The parameter UNDO_TABLESPACE specifies the name of the undo tablespace to use for read consistency and transaction rollback. You can create an undo tablespace when the database is created; you can resize it later or create a new one later. In any case, only one undo tablespace can be active at any given time, unless the value of UNDO_TABLESPACE is changed while the old undo tablespace still contains active transactions. In this case, the old undo tablespace remains active until the last transaction using the old undo tablespace either commits or rolls back; all new transac- tions use the new undo tablespace. If UNDO_TABLESPACE is not defined but at least one undo tablespace exists in the data- base, the first undo tablespace discovered by the Oracle instance at startup is assigned to
Leveraging Undo Management 749 UNDO_TABLESPACE. You can find out the name of the current undo tablespace with the SHOW PARAMETER command, as in the following example: SQL> show parameter undo_tablespace NAME TYPE VALUE ----------------------- ----------- -------------------- undo_tablespace string UNDOTBS1 For most platforms, if an undo tablespace is not explicitly created in the CREATE DATABASE command, Oracle automatically creates one with the name SYS_UNDOTBS. Here is an example of how you can switch the undo tablespace from UNDOTBS1 to UNDO_BATCH: SQL> show parameter undo_tablespace NAME TYPE VALUE -------------------------- ----------- ------------------- undo_tablespace string UNDOTBS1 SQL> alter system set undo_tablespace=undo_batch; System altered. SQL> show parameter undo_tablespace NAME TYPE VALUE -------------------------- ----------- ------------------- undo_tablespace string UNDO_BATCH UNDO_RETENTION The parameter UNDO_RETENTION specifies, in seconds, how long undo information that has already been committed should be retained until it can be overwritten. This is not a guaran- teed limit: if the number of seconds specified by UNDO_RETENTION has not been reached and if a transaction needs undo space, already committed undo information can be overwritten. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ---------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 To guarantee undo retention, you can use the RETENTION GUARANTEE key- words for the undo tablespace, as you will see later in this chapter in the section “Guaranteeing Undo Retention.”
750 Chapter 13 N Managing Data and Undo Setting UNDO_RETENTION to zero turns on automatic undo retention tuning. Oracle con- tinually adjusts this parameter to retain just enough undo information to satisfy the longest- running query to date. If the undo tablespace is not big enough for the longest-running query, automatic undo retention retains as much as possible without extending the undo tablespace. In any case, automatic undo retention attempts to maintain at least 900 seconds, or 15 min- utes, of undo information. Regardless of how long undo information is retained, it falls into one of three categories: Uncommitted undo information This is undo information that is still supporting an active transaction and is required in the event of a ROLLBACK or a transaction failure. This undo information is never overwritten. Committed undo information Also known as unexpired undo, this is undo information that is no longer needed to support an active transaction but is still needed to satisfy the undo retention interval, as defined by UNDO_RETENTION. This undo can be overwritten, how- ever, if an active transaction needs undo space. Expired undo information This is undo information that is no longer needed to support an active transaction and is overwritten when space is required by an active transaction. Here is an example of how you can change undo retention from its current value to 12 hours: SQL> show parameter undo_retention NAME TYPE VALUE ------------------ ----------- ----------------------- undo_retention integer 600 SQL> alter system set undo_retention = 43200; System altered. SQL> show parameter undo_retention NAME TYPE VALUE ------------------ ----------- ----------------------- undo_retention integer 43200 Unless you use the SCOPE parameter in the ALTER SYSTEM command, the change to UNDO_RETENTION takes effect immediately and stays in effect the next time the instance is restarted. Monitoring the Undo Tablespace Undo tablespaces are monitored just like any other tablespace: if a specific set of space thresh- olds is not defined, the database default values are used; otherwise, a specific set of thresholds