As a DBA, part of your daily job function is to create database objects. This is especially true for database administrators who manage development and test databases. However, even DBAs working on production systems will find that a good deal of their time is spent exploring the depths of setting up database objects.
In this course, you will cover what you need to know about the underlying storage structures like segments and extents that house database objects like tables and indexes in Oracle. You will also learn about undo segments, which are important data structures used for managing transactions and read-consistency in Oracle.
The contents of this course lay the foundation for management of tables, indexes, and integrity constraints. The information covered comprises about 14 percent of material tested on the Oracle9i DBA Fundamentals I exam.
This course presents information relevant to the following Oracle 1Z1-031: Oracle9i DBA Fundamentals I exam objectives:
Storage Structure and Relationships
- Describe the logical structure of the database
- List segment types and their uses
- List the keywords that control block space usage
- Obtain information about storage structures from the data dictionary
- List criteria for separating segments
- Describe the purpose of undo data
- Implement automatic undo management
- Create and configure undo segments
- Obtain undo segment information from the data dictionary
Lesson 2. Using Segments
Each type of database object has its own storage needs and typical behavior. As the Oracle DBA, your job is to make sure that all objects "play well together."After completing this lesson, you should be able to:
- Describe the basic differences between table, index, undo, and temporary segments and their usage
The storage of database objects in Oracle can often become a cantankerous matter; in fact, the behavior of one type of database object often interferes with the behavior of other objects in the database. To pass the Oracle 1Z1-031 exam and be a DBA, you need to know the different segment types and their uses.
You'll also need to know how to control Oracle's use of extents, the management of space at the block level, where to go for information about your database storage allocation, and how to locate segments by considering fragmentation and lifespan. For purposes of this discussion, make sure you have mastered the basics of the logical structure of a database.
For a quick review, the logical structure of your database consists of the following components: tablespaces, segments, and extents. Tablespaces are logical structures that house Oracle database objects and are comprised of one or more datafiles. Segments are collections of physical data blocks that are used for housing the data in database objects (for example, a table). When Oracle runs out of room in the segment used for housing data in that object, Oracle acquires another set of physical data blocks to house the data being added. This next set of physical data blocks is called an extent.
You know that different types of objects need different types of tablespaces to store them. But at database creation, Oracle usually creates a database with only one tablespace — the SYSTEM tablespace. This tablespace should only be used for housing Oracle data dictionary and system undo segments. Oracle9i permits you to create undo and temporary tablespaces when you create the database. So at a minimum, in addition to the SYSTEM tablespace, you will have separate tablespaces for your tables, indexes, undo segments, and temporary segments.
In order to understand the different types of tablespaces (and why it is a bad idea to ever try to store all your database objects in the SYSTEM tablespace), you must understand the different types of objects that a tablespace may store. Every database object, such as tables or undo segments, ultimately consists of segments and extents. For this reason, our discussion focuses on the different types of segments available on the Oracle database and how they are used.
*Table Segments and Their Usage
The first type of segment is the table segment (which is usually called DATA). Each segment contains data blocks that store the row data for that table. The rate at which the table fills and grows is determined by the type of data that table will support. For example, if a table supports an application component that accepts large volumes of data insertions (sales order entries for a popular brand of wine, for example), the segments that comprise that table will fill at a regular pace and rarely, if ever, reduce in size.
Therefore, the DBA managing the tablespace that stores that segment will want to plan for regular growth. If, however, this table is designed for storing a small amount of validation data, the size requirements of the table may be a bit more static. In this case, the DBA may want to focus more on ensuring that the entire table fits comfortably into one segment, reducing the potential fragmentation that extent allocation could cause.
Still another factor to consider when planning table segments is whether or not you intend to use parallel processing on your Oracle database. Under those circumstances, you would actually want your table divided into several segments and extents, or even to use partitioning. We'll consider this topic in more detail shortly.
*Index Segments and Their Usage
Another type of segment is the index segment. As with table segments, index segment growth is moderated by the type of role the index supports in the database. If the table to which the index is associated is designed for volume transactions (as in the wine example mentioned previously), the index also should be planned for growth. However, the index will almost invariably be smaller than the tables in your database, because it only houses one or a few columns from the table in an easy-to-search format, along with the ROWID information for the associated rows from the table.
What does an index consist of exactly? An index consists of a list of entries for a particular column (the indexed column) that can be easily searched for the values stored in the column. Corresponding to each value is the ROWID for the table row that contains that column value.
The principle behind index growth is the same as the growth of the corresponding table. If an index is associated with a table that rarely changes, the size of the index may be relatively static. However, if the index is associated with a table that experiences high insert activity, then plan the index for growth as well. Again, however, if you plan to use parallel processing in your database, you might actually want your index data stored in a few segments or even to use partitioning. Again, we'll discuss the criteria for separating index segments shortly.
*Undo Segments and Their Usage
Undo segments are different from the table and index segments just discussed. Undo segments store data changes from transactions to provide read consistency and transaction concurrency. The segments used to store data for tables and indexes are generally for ongoing use, meaning that once data is added to a table or index segment, it generally stays there for a while.
Undo segments aren't like that. Instead, once a user process has made its database changes and commits the transaction, the space in the undo segment that held that user's data is released for reuse in support of another user's transaction. Oracle's undo segment architecture is designed to allow the undo segment to reuse that space. Usually, an undo segment has some extents allocated to it at all times to store uncommitted transaction information. As the number of uncommitted transactions rises and falls, so, too, does the amount of space used in the undo segment. Where possible, the undo segment will try to place uncommitted transaction data into an extent that it already has allocated to it.
For example, if an undo segment consists of five extents, and the entire initial extent contains old data from committed transactions, the undo segment will reuse that extent to store data from new or existing uncommitted transactions once it fills the fifth extent. However, if the undo segment fills the fifth extent with data from a long uncommitted transaction, and the first extent still has data from uncommitted transactions in it, the undo segment will need to allocate a new extent.
Various long- and short-running transactions on your Oracle database can cause undo segments to allocate and deallocate dozens of extents over and over again throughout the day, which can adversely affect the growth of other database objects because of tablespace fragmentation. Thus, it is wise to keep undo segments by themselves, in their own undo tablespace.
You can create an undo tablespace (often called UNDOTBS) when you issue the create database command by including the following clause:
undo tablespace name datafile 'filename' size number[K|M]
If you don't create an undo tablespace when you create the database, you should create one later using the create tablespace command.
*Temporary Segments and Their Usage
Next, consider the temporary segment. True to its name, the temporary segment is allocated to store temporary data for a user transaction that cannot all be stored in memory. One popular use for temporary segments in user processes is for sorting data into a requested order. These segments are allocated on-the-fly and dismissed (released back to the tablespace to be used by other segments) when their services are no longer required. Their space utilization is marked by short periods of high storage need followed by periods of no storage need.
Because you have no idea when a temporary segment could come in and use all the available space in a tablespace, you can't make an adequate plan to accommodate the growth of other database objects — you really need to keep temporary segments in their own tablespace as separate from other database objects as possible.
You can create a default temporary tablespace along with your Oracle database using the following clause in the create database command:
default temporary tablespace
If you don't create a temporary tablespace when you issue the create database command, you should create at least one later using the create default temporary tablespace command.
Question 1
Question 2
Question 3
Question 4
In this lesson, you learned about the different types of segments used in Oracle9i databases, along with some analysis techniques you can use for understanding the fragmentation and lifespan of segments.
Lesson 3. Using Advanced Segments
Each type of database object has its own storage needs and typical behavior. As the Oracle DBA, your job is to make sure that all objects "play well together."After completing this lesson, you should be able to:
- Briefly describe the purpose of LOB, cluster, IOT, and TOOLS tablespaces
- Explain the fragmentation potential for different types of tablespaces
You know about data, index, undo, and temporary segments. The final types of segments that may be used in your Oracle database are LOB segments, cluster segments, and IOT segments.
LOB stands for large object, and a large object in Oracle will use a special type of segment to house its data. If your database uses large objects frequently, you may want to create a separate tablespace to hold these objects. Otherwise, don't bother to create the extra tablespace.
You may have heard of clustered tables — a physical grouping of two or more tables in the same segment around a common index. Cluster segments support the use of clusters on the database. The sizing of cluster segments and planning for their growth is complex and should be performed carefully, as each segment will essentially be storing data from two different tables in each block.
Finally, IOT stands for index-organized table, in which essentially the entire table is stored within the index structure. This was historically reserved only for use by indexes. Obviously, these segments have storage needs that are similar to indexes.
However, your use of cluster and IOT segments will probably be so limited that you don't need to worry about any potential conflict between these objects and your other database objects.
*A Note about Database Tools
Database administrative tools like Oracle Enterprise Manager operate based on a set of tables, indexes, and other database objects that collect data about your database. This set of database objects is often called a repository. Although the segments that house repository objects are the same as those segments that house your data, you should create a separate tablespace to store repository objects for several reasons.
One reason is that this will keep a logical division between your organization's data and the tool's data. Another reason is that, although it is not likely, the repository may have a table or other object with the same name as an object in your database, causing a conflict. By using a special TOOLS tablespace to store objects used by your database administrative tools, you will ease your own efforts later.
*Why Separate Segments into Different Tablespaces?
Let's consider the fragmentation potential for the different segments (and thus tablespaces) you may store in your database. This will help you understand why it is so important to store these different types of segments in different tablespaces. First, consider the following question: What makes fragmentation happen? A tablespace gets fragmented when objects stored in the tablespace are truncated or dropped and then re-created (or, for undo segments, when extents the object has acquired are deallocated).
The amount of time a segment or extent will stay allocated to a database object is known as its lifespan. The more frequently an extent is deallocated, the shorter the extent's lifespan. The shorter the lifespan, the more fragmented your tablespace can become. The SMON background process continuously looks for smaller fragments of free space left over by truncate or drop operations, and pieces or coalesces them together to create larger chunks of free space.
*Potential for Fragmentation on Different Types of Tablespaces
Now, consider the potential for tablespace fragmentation on different tablespaces. The SYSTEM tablespace houses the system undo segment and the data dictionary. Oracle manages its SYSTEM tablespace effectively, and extents have a long lifespan, so you are likely to see very little or no fragmentation in this tablespace.
Your TOOLS tablespace will likely have little fragmentation, because you won't (and shouldn't) typically go into your TOOLS tablespace and manage things yourself — your best bet is to let the administrative tool manage the repository itself. Again, extents have a long lifespan.
The next two tablespaces to consider are DATA and INDEX. The amount of fragmentation that may happen with these tablespaces will depend completely on how often you truncate or drop tables. In your production system, you may never, or hardly ever, do this, so extents will have a long lifespan, and fragmentation may be low.
In development, however, you may do this all the time, potentially making extent lifespan very short and fragmentation in the tablespace very high. You are your own best judge for interpreting fragmentation for these tablespaces, which is based on how long or short the extent lifespan is in those systems.
The other two types of tablespaces, UNDOTBS for undo segments and TEMP for temporary segments (you can have more than one tablespace for sorting and temporary segments), will experience high to very high fragmentation.
This is true in the UNDOTBS tablespace because undo segments have potentially a very short lifespan, and Oracle can allocate and deallocate extents as necessitated by long-running transactions. You learn more about undo segment extent allocation and deallocation elsewhere in this course.
Finally, the lifespan of segments and extents in the TEMP tablespace is incredibly short. Temporary segments are used to handle sort operations (a sort might be caused by issuing a select ... order by statement) that manipulate too much data to be stored in memory. Oracle automatically allocates the space when needed. Once the sort operation is finished, Oracle again automatically deallocates the space.
Thus, by definition of usage and lifespan, the TEMP tablespace will have the highest amount of fragmentation of any tablespace on your database.
Thus, although the SYSTEM tablespace can store any database object, it is not recommended that you put objects in it other than the dictionary objects and the system undo segment. To avoid problems with your database, you will need to prepare a few other tablespaces to store types of segments. By placing these objects in other databases designed to fit their storage needs, the DBA prevents a number of potential storage problems.
Tip: Extent lifespan and tablespace fragmentation are inversely proportional — the shorter the lifespan, the higher the potential for tablespace fragmentation.
*Creating Tablespaces for Housing Different Segments
One of your first database activities should be to create separate tablespaces to store tables, indexes, undo segments, temporary segments, and segments associated with database administrative tools such as Oracle Enterprise Manager. The tablespaces necessary for your Oracle database can be created with statements like the ones shown here and on the next page.
CREATE TABLESPACE UNDOTBS datafile
'/u05/oradata/oracledb/undo01.dbf'
SIZE 300M EXTENT MANAGEMENT LOCAL ONLINE;
CREATE TABLESPACE data datafile
'/u06/oradata/oracledb/data01.dbf'
SIZE 300M EXTENT MANAGEMENT LOCAL ONLINE;
CREATE TABLESPACE index datafile
'/u07/oradata/oracledb/index01.dbf'
SIZE 300M EXTENT MANAGEMENT LOCAL ONLINE;
As you will learn, each of these different types of database objects has its own unique behavior, and sometimes the behavior of one type of object conflicts with another.
CREATE TABLESPACE tools datafile
'/u08/oradata/oracledb/tools01.dbf'
SIZE 300M EXTENT MANAGEMENT LOCAL ONLINE;
CREATE temporary TABLESPACE temp tempfile
'/u09/oradata/oracledb/temp01.dbf'
SIZE 300M EXTENT MANAGEMENT LOCAL online;
Question 5
Question 6
Question 7
Question 8
Question 9
Topic 3.1 Exercises
* Exercise 1
Try learning more about tablespace storage in Oracle9i.
| Step | Action |
|---|---|
| 1 | Download information from one of the Oracle online documentation websites, for example: download-west.oracle.com/otndoc/oracle9i/901_doc/index.htm download-east.oracle.com/otndoc/oracle9i/901_doc/index.htm Note: All Web site references are current at the time of writing but are subject to change. |
| 2 | Look up tablespace (Concepts) in the Master Index. |
| 3 | Read the following sections: The SYSTEM Tablespace Undo Tablespaces Default Temporary Tablespace |
In this lesson, you learned more about the various types of segments that exist in Oracle and why it is important to put them in their own tablespaces. When identifying default storage parameters for these tablespaces, you should attempt to set parameters that work well for the type of database object that will be stored in this tablespace, or simply use uniform extent allocation, which is the default allocation type in Oracle9i.
Lesson 4. Extents in Locally Managed Tablespaces
An extent is really just a part of a segment.After completing this lesson, you should be able to:
- Describe how and when extents are acquired
- Identify the datatypes that can be spanned across tablespaces
- Describe what the uniform size clause does and where it is used
- Explain what happens when you override the uniform size clause in the create table or alter table commands
Growth in a data segment is generally handled with extents. If the segment runs out of space to handle new record entries for the object, then the object will acquire an extent from the remaining free space in the tablespace. In general, a logical database object such as a table or index can have many extents, but all those extents (plus the original segment) must be stored in the same tablespace.
There are, however, some exceptions. For example, a table containing a column defined as a large object datatype (BLOB, CLOB, NCLOB) can store its non-LOB data in one tablespace while storing LOB data in a different tablespace. However, generally speaking, you should remember that most database objects will have all their extents stored in the same tablespace.
The fact that all extents for a database object should be stored in the same tablespace shouldn't be taken to mean that all extents for a database object will be stored in the same datafile. For example, a partitioned table may have its partitions or extents spread across datafiles on different disks to improve parallel processing performance. Oracle may also run out of room in one tablespace datafile and thus be forced to allocate an extent in another datafile. This fact is important to remember for the Oracle 1Z1-031 exam.
Recall that since Oracle8i, tablespaces can be either locally managedlocally managed or dictionary-managed. You can specify this in the EXTENT MANAGEMENT clause in the create tablespace command.
When you specify local extent management, you can then specify either autoallocate so that Oracle allocates the extents, or you can specify a size using the uniform keyword. With dictionary-managed tablespaces, you can set very specific default storage settings using the STORAGE clause of the create tablespace command.
Let's take a look at extent allocation in locally managed tablespaces. The default in Oracle9i is locally-managed tablespaces with uniform extent management, also known as uniform space allocation.
*Uniform Space Allocation in Tablespaces
To configure uniform space allocation, you use the following keywords instead of autoallocate when you issue the create tablespace command:
uniform size n[K|M]
where n is the size of the extent allocated in KB or MB. When uniform size is specified, Oracle still manages all extent allocation and sizing automatically. The difference is that n is the size Oracle uses for later extents allocated to the object in the tablespace, regardless of the settings of the other storage settings in the create table statement. Here is an example of creating a tablespace in which all extents will be sized by Oracle as 10KB:
SQL> create tablespace lmtab5 datafile
2 'c:\oracle\oradata\orcl\lmtab501.dbf'
3 size 10M reuse
4 extent management local
5 uniform size 10K online;
Tablespace created.
When uniform extent allocation is used, Oracle calculates the number of bits to use in the storage allocation bitmap as the number of uniformly sized extents that would fit in the tablespace. Otherwise, the number of bits used for the bitmap is the same as the number of blocks that would fit in the tablespace.
*Extent Allocation in Locally-Managed Tablespaces
Oracle always uses the value specified for uniform size when sizing extents in locally-managed tablespaces. However, if you ask for extents to be a different size than what Oracle wants to give you through automatic extent management, you will see conflicting information in your dictionary views regarding objects placed in locally-managed tablespaces. To illustrate, let's create a table in the LMTAB5 tablespace.
This code attempts to override the uniform extent management size in LMTAB5 for table MYTAB01 by specifying a STORAGE clause with an initial extent size of 20KB. Remember, Oracle is supposed to create every object in LMTAB5 with an extent size of 10KB.
SQL> create table mytab01
2 (col1 number)
3 tablespace lmtab5
4 storage (initial 20K) online;
Table created.
Since LMTAB5 is a locally managed tablespace, the storage parameters next, pctincrease, minextents, maxextents, and default storage are not valid. The only storage parameter that is valid is initial.
Oracle ignores your attempted override of uniform storage management, right? Let's take a look at the DBA_TABLES view to see:
SQL> select table_name, initial_extent
2 from dba_tables
3 where table_name = 'MYTAB01';
TABLE_NAME INITIAL_EXTENT
---------- --------------
MYTAB01 20480
Oracle's supposed to make the initial extent 10KB. So, why is DBA_TABLES telling us the initial extent is 20KB? This is strange behavior indeed. What's more, the same thing seems to happen when we use the alter table allocate extent command as well. Take a look:
SQL> alter table mytab01 allocate extent (size 30K);
Table altered.
SQL> select table_name, initial_extent, next_extent
2 from dba_tables
3 where table_name = 'MYTAB01';
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
---------- -------------- -----------
MYTAB01 20480 30720
Does this mean that Oracle's uniform extent management feature can be overridden by use of a STORAGE clause when creating the table or by explicitly allocating extents of a larger size than that permitted by uniform extent management?
No, not really. Take a look at the DBA_EXTENTS view to get a picture of what's really going on here:
SQL> select segment_name, extent_id, bytes
2 from dba_extents
3 where segment_name = 'MYTAB01';
SEGMENT_NA EXTENT_ID BYTES
---------- --------- -------
MYTAB01 0 10240
MYTAB01 1 10240
MYTAB01 2 10240
MYTAB01 3 10240
MYTAB01 4 10240
5 rows selected.
So, in reality, the data dictionary satisfies both your specified space allocation in the STORAGE clause and in the allocate extent clause as well as its own rules about uniform extent sizes by simply allocating more extents of the same size.
SQL> select table_name, initial_extent
2 from dba_tables
3 where table_name = 'MYTAB01';
TABLE_NAME INITIAL_EXTENT
---------- --------------
MYTAB01 20480
What do you think Oracle would do if we asked it to allocate an extent of 15KB in this context? Well, Oracle would simply allocate more space than you asked for by grabbing two more extents of 10KB each.
SQL> select table_name, initial_extent
2 from dba_tables
3 where table_name = 'MYTAB01';
TABLE_NAME INITIAL_EXTENT
---------- --------------
MYTAB01 20480
Question 10
Question 11
Question 12
Question 13
Question 14
Topic 4.1 Exercises
* Exercise 1
Try learning more about blocks and extents in Oracle9i.
| Step | Action |
|---|---|
| 1 | Go to one of the Oracle online documentation websites, for example: download-west.oracle.com/otndoc/oracle9i/901_doc/index.htm download-east.oracle.com/otndoc/oracle9i/901_doc/index.htm |
| 2 | |
| 3 | Scroll down to the book entitled Concepts. |
| 4 | Read Section 3 on Data Blocks, Extents, and Segments. |
In this lesson, you learned that most tables have their extents all in one tablespace, with the exception of LOB type tables, which may span tablespaces. However, extents for tables and tablespaces do span physical datafiles.
You also learned that locally managed tablespaces are the default in Oracle9i and how the extent allocation clause of the create tablespace command works in combination with the STORAGE clause in the create table command.
Lesson 5. Extents in Dictionary-Managed Tablespaces
Though dictionary-managed tablespaces require more processing overhead than locally managed ones, they allow minute control over extent allocation.After completing this lesson, you should be able to:
- Describe the way Oracle uses the initial, next, and pctincrease values of the storage clause to allocate extents in dictionary-managed tablespaces
Unless you specify otherwise, when you create a database object in a dictionary-managed tablespace, Oracle allocates it a single initial segment in the tablespace based on the database object's STORAGE clause parameters (if there are any). If the object creation command lacks a STORAGE clause, then Oracle uses the DEFAULT STORAGE clause settings for the tablespace you place your object in.
Usually, the object is initially created with only one segment of space allocated (called the initial extent). As new rows are added to tables, the space of the segment is used to store that new data. When the segment storing the table data is full and more data must be added to the table, the table must allocate additional extents (called incremental extents) to store that data in.
A new extent will be acquired for a database object only if there is no room in any of the object's current segments or extents for the new data. Once acquired, an extent will only be relinquished if the DBA truncates or drops and re-creates the table.The next page illustrates an extent being acquired on an Oracle database.
This diagram illustrates an extent being acquired on an Oracle database.
*Acquiring Extents in Dictionary-Managed Tablespaces
For dictionary-managed tablespaces, the size of an acquired extent is based on the value specified for the next parameter inside the STORAGE clause provided in the object creation clause. If the object is acquiring its third or greater extent, the size of the extent will equal next multiplied by pctincrease. This concept is a little tricky, so let's take a look at what we mean. Let's say you create a table called EMPLOYEE with the following command:
SQL> create table employee
2 (empid number primary key,
3 name varchar2(20),
4 salary number)
5 storage (initial 100K next 2K pctincrease 25
6 minextents 1 maxextents 100)
7 tablespace DATA;
Table created.
Once you issue this command, Oracle immediately allocates a single segment 100KB in size inside the DATA tablespace. We don't know which datafile Oracle will place the segment into, just that it will be a datafile associated with the DATA tablespace. We know the initial segment will be 100KB in size because that's the value specified for the initial setting inside the STORAGE clause.
Suppose a user adds several employees to the table, filling that initial segment. The initial segment is now full, so the next user who adds an employee to the table will force Oracle to allocate another extent to the EMPLOYEE table. This extent will be allocated inside the DATA tablespace and will be 2KB in size. Again, we know the second extent will be 2KB in size because the next setting in the STORAGE clause tells us so.
SQL> create table employee
2 (empid number primary key,
3 name varchar2(20),
4 salary number)
5 storage (initial 100K next 2K pctincrease 25
6 minextents 1 maxextents 100)
7 tablespace DATA;
Table created.
If you want to specify a different size for that next extent, you should issue the following statement before that next extent gets allocated, where nextval is the size of the next extent (in KB or MB):
alter table tablename storage (next nextval)
Oracle applies the value you specify for this setting to the next extent it acquires.
Suppose we issue the following statement. Now, instead of allocating only 2KB for the next extent, Oracle allocates 100KB, so that the next extent is as big as the first one.
SQL> ALTER TABLE employee STORAGE (NEXT 100K);
Table altered.
Now suppose the second user adds several employees such that the second extent is now full. What happens next? Well, Oracle has to do some calculating to determine how large to make the third extent. This is because a value greater than zero was specified for the pctincrease setting.
Oracle multiplies the value you specified for next in the STORAGE clause by the percentage increase specified. Thus, because we specified 25 for the pctincrease setting and 100KB for next in our STORAGE clause, Oracle will allocate 100KB x 1.25, or 125KB, for the third extent.
Whenever Oracle allocates a new extent for an object where pctincrease is greater than zero, Oracle recalculates the value for next that is stored in the data dictionary record associated with this object. This could be dangerous because the size of subsequent extents allocated will increase exponentially.
To understand what we mean by this, consider what will happen when Oracle allocates the fourth extent for the EMPLOYEE table. The size of the third extent was 125KB, so Oracle bases the size of the fourth extent on the size of the third extent, increased by the value specified for pctincrease. In other words, Oracle allocates 100KBx1.25x1.25, or 156.25KB, of space for the fourth extent.
Setting pctincrease to a value greater than zero is meant to slow down the extent allocation process for growing database objects by allocating progressively more space each time a new extent is required. You should understand the way this works.
However, on a practical basis Oracle recommends setting pctincrease to 0 to minimize fragmentation. This will result in all extents being a uniform size.
Question 15
Question 16
Question 17
Question 18
Question 19
Question 20
Question 21
In this lesson, you learned how the next and pctincrease values work in the STORAGE clause of the create table command.
Lesson 6. Extents and Parallel Processing
Allocation of extents is important to optimizing your database.After completing this lesson, you should be able to:
- Specify when the data for a schema object should be stored contiguously or spread across multiple segments
- List the negative consequences of preallocating too many extents to a tablespace
- List the negative consequences of preallocating too few extents to a tablespace
If you're not using parallel processing, you will get the best performance from a database object, such as a table or index, if all the data for that object is housed in only one segment or in a set of contiguous blocks. This is especially true when you use dictionary-managed tablespaces to house Oracle data and less true when when you use locally managed tablespaces.
However, when you use parallel processing, you'll want your data stored in multiple extents (preferably spread across several disks), so that parallel processes accessing the same table won't contend with each other. If you use partitioned tables and the parallel query option, obviously you'll want to have as many different segments for your table as there are parallel I/O slave processes utilized by Oracle to search that table. Furthermore, certain trade-offs exist in deciding how much space to allocate, particularly for tables you know will grow quickly.
*The Preallocation Balancing Act
In the STORAGE clause for the EMPLOYEE table there are two other settings. They are minextents, the number of extents Oracle allocates to the table when it's first created, and maxextents, the maximum number of extents Oracle will ever allocate for that object. Once Oracle has allocated the maximum number of extents you identified via the maxextents setting, that's it — the table cannot grow any larger (unless you change the value for maxextents, which you can only do for dictionary-managed tablespaces).
Many inexperienced DBAs fall prey to the trap of preallocation. They want to preallocate as much space as possible to avoid problems later with hitting the limit set by maxextents. Sometimes that's a good thing — especially if you believe that users are going to fill the object with data quickly or if you need to keep all the data in one segment.
SQL> create table employee
2 (empid number primary key,
3 name varchar2(20),
4 salary number)
5 storage (initial 100K next 2K pctincrease 25
6 minextents 1 maxextents 100)
7 tablespace DATA;
Table created.
However, there are a couple of drawbacks to be aware of when preallocating extents:
- First, the host machine has limited space.
- Second, and more importantly, your database could suffer from poor backup performance because of the excessive time required to compress a tablespace containing mostly empty segments for storage on tape.
- A third drawback is also evident sometimes. If the tablespace has vast wastelands of empty segment space, then tables are placed far away from one another on disk. This could cause your database to experience the poor performance seen on databases with huge tables, even though your tables are mostly empty.
Oracle recommends specifying maxextents unlimited in order to allow the database object to expand as necessary and minimize fragmentation. However this should not be used for undo segments.
The trick, then, is to balance the following considerations:
- Using locally managed tablespaces instead of dictionary-managed tablespaces
- Leaving a generous percentage of additional space, both in the tablespace and the object's initial segment, to accommodate growth
- Planning disk purchases for the host machine or leaving extra space to add new tablespaces and datafiles in order to alleviate a space crunch
- If possible, setting up a monthly or quarterly maintenance schedule with your users so that you can have some downtime to reorganize your tablespaces to avert those potential sizing issues before they become headaches
Question 22
Question 23
Question 24
Question 25
Question 26
Topic 6.1 Exercises
* Exercise 1
Try learning more about extent management in Oracle9i.
| Step | Action |
|---|---|
| 1 | Go to one of the Oracle online documentation sites, for example: download-west.oracle.com/otndoc/oracle9i/901_doc/index.htm download-east.oracle.com/otndoc/oracle9i/901_doc/index.htm |
| 2 | In the Master Index, look up the following items: EXTENT MANAGEMENT CLAUSE of create tablespace STORAGE CLAUSE of create tablespace STORAGE CLAUSE of create table partitions (Concepts) |
| 3 | Go to the book list and scroll down to the Concepts book. Read the following sections: Section 9, Process Architecture Section 12, Partitioned tables and indexes |
In this lesson, you learned about preallocation of extents, the advantages and disadvantages of preallocation, and the circumstances in which preallocation is necessary. You also learned to specify when data for a schema object should be stored contiguously or spread across multiple segments
Lesson 7. Controlling Block Space Usage
You can control storage in your database at the block level. You can decide how full data blocks can get before Oracle switches to the next block, and decide how empty data blocks can get before Oracle starts refilling them.After completing this lesson, you should be able to:
- Describe the purpose and use of the pctfree and pctused options of the create table statement
- Explain how to set optimal pctfree and pctused values under different circumstances
In addition to overall storage allocation for extents on objects in the database, Oracle enables you to manage how the objects use the space inside each data block they are given. Space usage is determined at the block level with the pctfree and pctused options. By controlling space allocation at the block level, you can manage how a database object utilizes the space allocated to it more effectively according to the type of data change activity the object is likely to be subjected to.
SQL> CREATE TABLE FAMILY
2 ( NAME VARCHAR2(10) primary key,
3 RELATIONSHIP VARCHAR2(10))
4 storage (initial 100K next 100K pctincrease 0
5 minextents 1 maxextents 200)
6 PCTFREE 20 PCTUSED 40;
Table created.
Database objects are subjected to data changes in different ways by different applications: for example, take a database that supports high online transaction-processing (OLTP) activity. In this case, later data changes to tables will expand the size of existing rows by populating columns initially set to NULL when the row was added to the table. Therefore, space must be preallocated at the block level to let the row grow.
In contrast, a decision support system (DSS) such as a data warehouse will likely not see its data changed once rows are loaded for query access. In this case, you'll want to pack as much data into every block allocated to a row as possible.
Why do DBAs manage space allocation at the block level? Because the objects themselves are utilized differently by different application data manipulation language (DML) activities. For example, if a table experiences a high update activity that increases the size of rows in the table, the block space allocation for that database object should allow for additional growth per row.
If data change activity on the object consists of a lot of insert statements entering rows mostly the same size, then the space usage goal within each block should be to place as many rows as possible into each block before allocating another one. This same approach may work if a table's size is static and rows are infrequently added to the table.
*Leaving Extra Space for Row Growth: pctfree
The pctfree clause is specified at the database object level. It tells Oracle how much free space to leave in a block when that block initially gets populated with row data. This leftover space remains free in each block to accommodate the growth of existing rows in the block. For example, if a table has pctfree specified to be 10 percent, Oracle will stop adding new rows to the block when there is 10 percent free space left over in the block. That remaining space is used by existing rows when users set NULL columns to a non-NULL value.
You should use the following general approach when deciding how to set pctfree:
- If rows added to a table will be updated often and each update will add to the size in bytes of the row, then set pctfree to a high value. You'll see some examples of values that are considered high for pctfree in a moment. For now, understand that setting pctfree high prevents performance killers such as row migration (where Oracle moves an entire row to another block because the original block doesn't have the room to store it anymore).
- Conversely, if the rows in the block will not be updated frequently, or if the updates that will occur will not affect the size of each row, set the value for pctfree low on that database object.
A high value for pctfree is about 20 to 25, which means that 20 or 25 percent of space in a block is left free for later updates that might increase the size of an existing row in the block. Conversely, a low value for pctfree would be 4 or 5, which you might use for static or read-only tables.
*Ongoing Management of Free Space in Blocks: pctused
The other option for managing free space in blocks is pctused. It is also defined at the database object level. The pctused option specifies the percent usage threshold by which Oracle will determine if it is acceptable to add new rows to a block. To understand what this means, consider the situation where data is added to a table. As new rows are added, Oracle fills the block with inserted rows until reaching the cutoff set by pctfree.
Later, as data is deleted from the table, that table's space utilization at the block level falls. When the space used in a data block falls below the threshold limit set by pctused, Oracle adds the block to a freelist maintained for that table. A freelist is a list of data blocks that are currently accepting new data rows.
When setting pctused, be mindful that Oracle incurs some performance overhead by marking a block free and adding it to a freelist for that database object. Thus, there is a trade-off inherent in specifying pctused that you should understand for the Oracle 1Z1-031 exam and beyond. You must temper your interest in managing space freed by row removal as efficiently as possible against that overhead incurred by each block. To prevent the block from making its way to the freelist when only one or two rows can even be added to the block, you should set the pctused option relatively low.
Unless you're really concerned about managing free space actively, don't set pctused higher than 40 or 50. A situation where you might be concerned about managing free space actively would be when you have very little free space on the disks in your host system.
*Setting Actual Values for pctfree and pctused
pctfree and pctused are defined at the database object level, and must always be considered in tandem. You can specify a value for each option between 0 and 100. However, when determining values for pctfree and pctused, do not assign values that exceed 100 when added together. In fact, if you set values for these options that approach 90, this causes Oracle to spend more time managing space utilization, although the database will achieve very efficient usage of disk space. The lower the sum of the values, the less efficient the disk space usage. Following are some values for pctfree and pctused that would be considered appropriate:
pctfree 5, pctused 40 Good for static or read-only tables, such as those loaded periodically for data warehouse and/or query-only applications
pctfree 10, pctused 40 Good for all-around OLTP situations especially when existing rows won't be increased by update activity after the row is inserted
pctfree 20, pctused 40 Good for OLTP activity where existing rows will be increased by updates after the row is inserted (20 is a high value for pctfree)
*Setting pctfree and pctused: An Example
You'll learn more about storage options for creating database objects in other courses. For now, to give you the opportunity to see pctfree and pctused in action, the following code block contains a create table statement with pctfree and pctused specified. Notice that these clauses are not defined as part of the STORAGE clause — they are their own clauses in a create table command:
SQL> CREATE TABLE FAMILY
2 ( NAME VARCHAR2(10) primary key,
3 RELATIONSHIP VARCHAR2(10))
4 storage (initial 100K next 100K pctincrease 0
5 minextents 1 maxextents 200)
6 PCTFREE 20 PCTUSED 40;
Table created.
Question 27
Question 28
In this lesson, you learned about controlling storage at the block level. The pctfree and pctused clauses of the create table statement allow you to specify how full blocks can get before Oracle starts filling the next block, and how empty blocks can get before Oracle puts them back onto the freelist to be recycled. You learned how to set optimal pctfree and pctused values under different circumstances.
Lesson 8. Obtaining Information about Storage Structures
There are several data dictionary views associated with tracking information about storage structures in the database.After completing this lesson, you should be able to:
- List and briefly describe the views used to obtain information about storage in the database
You can determine storage information for database objects from many sources in the data dictionary. There are several data dictionary views associated with tracking information about structures for storage in the database, such as tablespaces, extents, and segments. In addition, there are dictionary views for the database objects that offer information about space utilization settings. The names of dictionary views are usually taken from the objects represented by the data in the dictionary view, preceded by classification on the scope of the data. Each segment has its own data dictionary view that displays the storage information.
Assuming that you want to know the storage parameters set for all objects on the database, you may use views on the following pages to determine storage information for the segment types discussed in this course.
DBA_SEGMENTS This summary view contains all types of segments listed by the data dictionary views and their storage parameters.
DBA_TABLESPACES You can use this view to see the default storage settings for the tablespaces in the database.
DBA_TS_QUOTAS You can use this view to identify the tablespace quotas assigned for users to create objects in their default and temporary tablespaces.
V$TABLESPACE This gives a simple listing of the tablespace number and name.
DBA_EXTENTS You use this view to see the segment name, type, owner, name of tablespace storing the extent, ID for the extent, file ID storing the extent, starting block ID of the extent, total bytes, and blocks of the extent. This view is good for determining the total allocated space of a table.
DBA_FREE_SPACE This view identifies the location and amount of free space by tablespace name, file ID, starting block ID, bytes, and blocks.
DBA_FREE_SPACE_COALESCED This view identifies the location of free space in a tablespace that has been coalesced by tablespace name, total extents, extents coalesced, and the percent of extents that are coalesced, as well as other information about the space in the tablespace that SMON has coalesced.
DBA_DATA_FILES This static data dictionary view gives information about datafiles for every tablespace.
V$DATAFILE This dynamic performance view gives information about datafiles for every tablespace.
Coalescing is the act of putting small chunks of free space in a tablespace that are contiguous, and merging them into larger chunks of free space. The SMON process takes care of coalescing the tablespace on a regular basis. With locally managed tablespaces free space is monitored in datafile header bitmaps, so SMON doesn't need to coalesce.
If you want to take care of coalescing the tablespace yourself, issue the alter tablespace tblspc coalesce command.
Question 29
Question 30
Question 31
Question 32
In this lesson, you learned about dictionary and dynamic performance views that display information about storage parameters and about actual storage in your database.
Lesson 9. Using Undo Data
Often, the DBA spends part of any given day "fighting fires" involving undo segments.After completing this lesson, you should be able to:
- Explain the purpose of undo segments
- Describe the process by which undo extents are acquired
- List the conditions that cause the ORA-1555 "Snapshot too old (rollback segment too small)" error
- List solutions for the ORA-1555 error
In Oracle9i, the objects that were formerly called rollback segments are now referred to as undo segments.
Undo segments store the old data value when a process is making changes to the data in a database. The undo segment stores data and block information, such as file and block IDs, for data from a table or index as that block existed before being modified. This copy of the data is available to other users running queries until the user making changes commits his or her transaction. All types of transactions generate undo data.
The undo segment actually stores the the original data even after the commit takes place. However, Oracle will eventually and systematically overwrite the data in an undo segment from committed transactions when it needs room in the undo segment to store data for its uncommitted changes (resulting from new user transactions).
Undo segments serve three purposes:
- They provide transaction-level read consistency of data to all users in the database.
- They permit users to roll back, or discard, changes that have been made in a transaction in favor of the original version of that data.
- They provide transaction recovery in case the instance fails while a user is making a data change.
Undo segments are probably the most useful database objects in data processing, but they can be troublesome for DBAs to maintain. In prior versions of Oracle, you had to master the management of these fussy objects very quickly if you were going to survive as a DBA for long. However, Oracle9i has simplified administrative tasks related to undo segments greatly by automating many aspects of their management.
*Undo Segment Contents
The contents of an undo segment are manifold. Each undo segment contains a table in the segment header. This table lists the transactions and users currently using this undo segment to store data changes for the purpose of read-consistency. Each undo segment also has several extents allocated to it. The extents store original versions of data from tables and indexes. Whenever a user issues an update or delete statement in Oracle, the server process writes the original version of the data being changed or removed to the undo segment.
Later, if the user decides to roll back his or her transaction, Oracle can easily copy the old version of the data in the undo segment back into its proper place. Or, if the user decides to commit his or her transaction, Oracle simply leaves the original data in the undo segment. Later, the space in the undo segment can be reused for storing original data for another transaction that is needed by another user.
A new feature in Oracle9i called Flashback Query uses undo data stored in an undo segment to permit users to view data as it existed in the database at some point in the past.
*ORA-1555 Error
Designing and administering undo segments manually is one of the most challenging tasks for DBAs. If the undo segments are not properly designed, users will experience the dreaded ORA-1555: Snapshot too old (rollback segment too small) error.
Actually, this error message is misleading. This error could be caused not just by a too-small rollback segment but for many other reasons. If this problem is not taken care of, you may face a situation where a batch process running for many hours has to be rerun just because it fails in the middle due to this error.
In order to understand and solve rollback segment problems, one has to understand how Oracle supports read consistency. In a multi-user environment, many transactions are writing or modifying rows. Before a transaction commits the changes it is making, the changed data is visible to all statements within the transaction making the changes but not visible to other statements or transactions.
Once committed, the changes are visible to all subsequent transactions. Statements that began prior to the commit will continue to show the old data because those changes were not present at the start of this transaction. This is called read consistency.
How is read consistency made possible by Oracle? We will consider two transactions — the changing transaction that is making the changes in the table and reading transaction that is reading the data that is being changed by changing transaction.
As discussed before, the changing transaction keeps the before-image of the data being changed in an undo segment. The reading transaction that starts before the changing transaction committed the changes will read the before-image of the data from the undo segment. Once the changing transaction commits, the undo segments containing the before-image are marked free and can be used by any transaction or could be cleaned up due to shrinking of the undo segment.
However, if the reading transaction continues to exist and comes back to read the before-image data from the undo segment and finds it missing, it returns the 'ORA-1555' error message and rolls back all the changes it made since it started. This can be quite a disruption if this transaction was a long running one.
It's important to note that many DBAs have mistakenly thought that they would somehow be able to solve the ORA-01555 problem by increasing the size of their undo segments,. This is often not the case. The problem is really that the transaction making changes to Oracle data was taking too long. Let's take a look at the general process that generates an undo error.
| Step | Action |
|---|---|
| 1 | Because Oracle must support read-consistency for all users, the original data must be available in the undo segment for the duration of a user's transaction. That way, other users querying the data being changed can see the original until the transaction is ended. This keeps a portion of the undo segment's extents active. |
| 2 | If an undo segment or extent is active (meaning that it contains data in support of a transaction), then other user transactions cannot overwrite the data in that extent. Instead, they will have to use the space in another extent. |
| 3 | If all extents are active, then Oracle has to allocate a new extent to the undo segment. If the transactions stay active for a long time, then Oracle may have to allocate more and more extents until finally the available space for the undo segment is exhausted. |
| 4 | At this point, Oracle throws the ORA-1555 error, rolls back the transaction, and forces the user executing the transaction to start again from scratch. |
So what causes the rollback segment too small error to happen? It could be many reasons, including the following:
- An inadequate number of undo segments
- Inadequately sized undo segments
- The data buffer cache being too small
- Large and small transactions using the same undo segments
- An improperly sized optimal parameter (that could be shrinking too often)
- Committing too often in a transaction
Question 33
Question 34
Topic 9.1 Exercises
* Exercise 1
Try learning more about undo data in Oracle9i.
| Step | Action |
|---|---|
| 1 | Go to one of the Oracle online documentation sites, for example: download-west.oracle.com/otndoc/oracle9i/901_doc/index.htm download-east.oracle.com/otndoc/oracle9i/901_doc/index.htm |
| 2 | Look up the follow items in the Master Index: UNDO tablespace clause, create database UNDO tablespace clause, create tablespace undo management, automatic (Concepts) undo space management, described (Admin Guide) |
In this lesson, you learned about undo segments and how they work. You learned about the ORA-1555 "Snapshot too old (rollback segment too small)" error, and how it doesn't necesarily mean that your segments are too small.
Lesson 10. Implementing Automatic Undo Management
Automatic undo management is new to Oracle9i.After completing this lesson, you should be able to:
- Create an undo tablespace that is automatically managed
- Specify how long to retain undo data
- Switch, drop, and alter undo tablespaces
Oracle9i administers undo data in two ways — automatic undo management and manual undo management. For the purpose of passing the Oracle9i DBA Fundamentals I exam, we'll look at how to implement both automatic undo management and manual management.
If you're an experienced DBA, rest assured — Oracle hasn't taken away your ability to configure undo segments manually. Manual configuration is still available in Oracle9i for backward compatibility. The exam may or may not test manual management of undo segments, so we'll cover how to manage undo segments automatically as well as manually.
*Implementing Automatic Undo Management
Implementing automatic undo management is by far the easiest way to configure the management of undo segments in an Oracle database, especially for less-experienced DBAs. It is quick and easy, and it pushes the dirty work back on Oracle.
Even if you're an experienced DBA, automatic undo management could be a good option to reduce the time you spend on mundane administrative tasks. Automatic undo management enables you to focus your attention on more interesting aspects of your work like strategic planning for data growth or architecture of new database systems just coming online. In addition, automatic undo management works well in Oracle systems that aren't transaction intensive, such as development or testing environments.
Configuration and deployment of automatic undo management consists of three important steps:
| Step | Action |
|---|---|
| 1 | Define an undo tablespace that Oracle can use for allocation and deallocation of undo segments. |
| 2 | Instruct Oracle to run in automatic undo management mode. |
| 3 | Instruct Oracle how long it should retain undo information in undo segments. |
*Step 1: Define Undo Tablespace
In order to use automatic undo management in an Oracle9i database, you must first create an undo tablespace. The undo tablespace houses undo segments, and it can be created in two different ways. You can create it when you create the database. The following code block shows an example of how to create an undo tablespace automatically during database creation in a Windows environment:
CREATE DATABASE DB1
CONTROLFILE REUSE
LOGFILE GROUP 1 ('C:\Oracle\oradata\DB1\redo01.log') SIZE 100K,
GROUP 2 ('C:\Oracle\oradata\DB1\redo02.log') SIZE 100K,
GROUP 3 ('C:\Oracle\oradata\DB1\redo03.log') SIZE 100K;
DATAFILE 'C:\Oracle\oradata\DB1\system01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS
DATAFILE 'C:\Oracle\oradata\DB1\undotbs01.dbf'
SIZE 50M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMIITED
NOARCHIVELOG
CHARACTER SET US7ASCII
;
Or, you can create the undo tablespace manually with the create tablespace command, as shown in the following code block:
SQL> CREATE UNDO TABLESPACE undotbs2
2 DATAFILE 'c:\oracle\oradata\DB1\undotbs2.dbf'
SIZE 50M;
Tablespace created.
*Step 2: Run in Automatic Undo Management Mode
The next step is accomplished by setting the UNDO_MANAGEMENT initialization parameter in your init.ora file. This parameter has two settings: MANUAL and AUTO. MANUAL is the default setting when the UNDO_MANAGEMENT parameter isn't present in the init.ora file. This setting permits you to manage undo segments manually, as you would have managed rollback segments in versions of Oracle prior to Oracle9i.
To place the Oracle database into automatic undo management mode, you set UNDO_MANAGEMENT to AUTO in the init.ora file, as shown here.
UNDO_MANAGEMENT = AUTO
When a database is defined to run in automatic undo management mode, you have to have at least one undo tablespace created and online. If you have more than one undo tablespace in the tablespace, Oracle will use only one of them. You can explicitly specify which one to use by specifying it in the init.ora parameter UNDO_TABLESPACE.
This parameter can also be dynamically altered using the alter system command.
UNDO_TABLESPACE = undotbs
ALTER SYSTEM
SET undo_tablespace = UNDOTBS;
Finally, a caveat: If you specify automatic undo management but provide no undo tablespace for Oracle to use, Oracle will not let your database start. So, for example, if you set UNDO_MANAGEMENT to AUTO but set UNDO_TABLESPACE to a tablespace that does not exist, then Oracle will issue the ORA-01092 error when you attempt to start up and open the database. The solution to this issue is to set the UNDO_TABLESPACE parameter to a valid undo tablespace in your database.
Note, however, that if you actually have an undo tablespace available and have set UNDO_TABLESPACE to that tablespace at any point when the database was running before, Oracle will keep track of the setting for that parameter using the SPFILE feature. That way, if you simply omitted the UNDO_TABLESPACE parameter setting in init.ora, Oracle will still run normally. It is only when you incorrectly instruct Oracle to use an invalid tablespace that you will encounter this problem.
If you choose not to use automatic undo management, then you should ensure that you set the ROLLBACK_SEGMENTS parameter to bring named undo segments online when the database starts. Otherwise, Oracle will only have the system undo segment in the SYSTEM tablespace online at database startup.
*Step 3: Set Retention Period
One significant improvement with Oracle9i undo segments is the ability to retain prechange data in the undo segments for a specific period of time. This functionality works in conjunction with a new feature in Oracle9i called Flashback Query, which enables you to view old versions of your data after changes are committed for a specified period of time.
With automatic undo management, you can specify how long you want to retain the committed undo information. The number of seconds to retain this data is specified in the init.ora parameter UNDO_RETENTION. This can also be dynamically changed using the alter system set undo_retention = n command, where n is the number in seconds that Oracle9i will retain the prechange original copy of data in an undo segment for Flashback Query. The default value for this parameter is 900.
#set undo retention
#to 30 minutes
alter system
set undo_retention 1800
*Administering Undo Tablespaces
Even though undo data is a new concept in Oracle9i, management of undo tablespaces is the same as any other tablespace. The undo tablespace can be altered using the alter tablespace command to add datafiles, rename datafiles, bring a datafile online, offline, or in backup mode. Similarly, the alter database command can be used to resize undo datafiles.
An undo tablespace can also be dropped using a drop tablespace command, with one restriction. An undo tablespace cannot be dropped while active transactions are using undo segments in it. This restriction is similar to the restriction on tablespaces containing active undo segments created manually. The following code block illustrates this.
SQL> drop tablespace undotbs2;
drop tablespace undotbs2
*
ERROR at line 1:ORA-30013:
undo tablespace 'UNDOTBS2'
is currently in use
Oracle supports multiple undo tablespaces, but only one is used per instance. However, Oracle does let you switch from one undo tablespace to the other dynamically while the database is online and available to users. The following code block shows how this can be done. In this case, we move from using one undo tablespace to another in order to drop the other undo tablespace.
SQL> ALTER SYSTEM
SET UNDO_TABLESPACE=undotbs;
System altered.
SQL> DROP TABLESPACE undotbs2;
Tablespace dropped.
*Creating the Segments
If you're an experienced DBA, you might be asking yourself the question, "OK, so now I know how to create the undo tablespace, but how do I create the undo segments in Oracle9i?" The answer is, you don't. Oracle creates and manages all undo segments for you automatically as soon as the undo tablespace is created. Thus, when automatic undo management is enabled, you do not need to ensure that the undo segments are created or that they are online the way you had to in earlier versions of Oracle. Be sure you understand this fundamental difference in how undo segments are managed for Oracle9i, especially if you already have hands-on experience with the Oracle database from prior versions.
Question 35
Question 36
Question 37
Question 38
Question 39
In this lesson, you learned how to set up automatic undo management by creating an undo tablespace and setting UNDO_MANAGEMENT to AUTO. You also learned about Flashback Query and how Oracle behaves when different undo parameters are set under automatic undo management.
Lesson 11. Configuring Undo Segments Manually
Although you need to know how to implement automatic configuration for the exam, you can still configure undo segments manually the same way you used to configure rollback segments.After completing this lesson, you should be able to:
- Explain the difference between system and non-system, and private and public undo segments
- Describe the methods and commands used for assigning undo segments
- Outline the undo extent allocation process
- Apply the Rule of Four to estimate undo segment needs
To understand undo segment manual configuration, let's start with a quick refresher on the types of undo segments. These objects can be broken into two categories: the system undo segment and non-system undo segments. As you know, the system undo segment is housed by the SYSTEM tablespace and handles transactions made on objects in the SYSTEM tablespace.
The other type of undo segment, the non-system undo segment, handles transactions made on data in non-system tablespaces in the Oracle database. These non-system undo segments are housed in a non-system tablespace, such as the UNDOTBS tablespace. In order for Oracle to start when the database has one or more non-system tablespaces, there must be at least one non-system undo segment available for the instance to acquire outside the SYSTEM tablespace.
Non-system undo segments come in two flavors: private and public undo segments. A private undo segment is one that is only acquired by an instance explicitly naming the undo segment to be acquired at startup via the ROLLBACK_SEGMENTS parameter in initsid.ora, or via the following statement issued manually by you, the DBA:
alter rollback segment undoseg online
Public undo segments are normally used when Oracle9i Real Application Clusters is running, but can also be used in a single instance. Public undo segments are acquired by Oracle automatically from a pool of undo segments available on the database. These segments are calculated using the TRANSACTIONS and the TRANSACTIONS_PER_ROLLBACK_SEGMENT init.ora parameters.
*How Transactions Use Undo Segments
Transactions occurring on the Oracle database need undo segments to store their uncommitted data changes. Transactions are assigned to undo segments in one of two ways:
- Explicitly with the set transaction use rollback segment undoseg statement.
- If no undo segment is explicitly defined for the transaction, Oracle assigns the transaction to the undo segment that currently has the lightest transaction load, in round-robin fashion.
Note: For manually managed undo, we use the terms "undo segment" and "rollback segment" interchangeably. We'll use the term "rollback segment" primarily when talking about code, since the code still uses the term.
An undo segment usually has several extents allocated to it at any given time, and these extents are used sequentially. After the database is started, the first transaction will be assigned to the first undo segment, and it will store its data changes in extent #1 of the undo segment. As the transaction progresses (a long-running batch process with thousands of update statements, let's say), it places more and more data into undo segment extent #1. An extent containing data from a transaction in progress is called an active extent. More and more transactions are starting on the database, and some of those other transactions may be assigned to this undo segment. Each transaction will fill extent #1 with more and more change data until the transactions commit.
If extent #1 fills with data changes before the transactions commit, the transactions will begin filling extent #2 with data. Transactions with data changes spilling over to a new extent are said to be performing an extend. A special marker called an undo segment head moves from extent #1 to extent #2 to indicate to the extent where new and existing transactions assigned to the undo segment can write their next data change.
As soon as the transaction commits its data changes, the space in extent #1 used to store them is no longer required. If extent #1 is filled with data change information from only committed transactions, extent #1 is considered inactive.
To effectively use undo segment space, the undo segment allocates only a few extents, and those extents are reused often. The ideal operation of an undo segment with five extents is as follows: Transactions assigned to the undo segment should fill extent #5 a little after transactions with data changes in extent #1 commit. Thus, extent #1 becomes inactive just before transactions in extent #5 need to wrap into it. However, this behavior is not always possible. If a transaction goes on for a long time without committing data changes, it may eventually fill all extents in the undo segment.
When this happens, the undo segment acquires extent #6, and wraps data changes from the current transaction into it. The undo segment head moves into extent #6 as well.
Note that if a transaction causes the undo segment to allocate the maximum number of extents for storing the long transaction's data changes — as determined by the maxextents storage option defined when the undo segment is created — the undo segment becomes enormously stretched out of shape.
Oracle has an optimal option available in undo segment storage that permits undo segments to deallocate extents after long-running transactions cause them to acquire more extents than they really need. The optimal clause specifies the ideal size of the undo segment in KB or MB. This value tells Oracle the ideal number of extents the undo segment should maintain. If optimal is specified for an undo segment, that object will deallocate space when the undo segment head moves from one extent to another, if the current size of the undo segment exceeds optimal and if there are contiguous adjoining inactive extents.
Extent deallocation as the result of optimal has nothing to do with transactions committing on the database. The deallocation occurs when the undo segment head moves from one extent to another. Oracle does not deallocate extents currently in use (even if the total size exceeds optimal) and always attempts to deallocate the oldest inactive extents first.
*The Rule of Four
Oracle's recommended strategy for planning the appropriate number of undo segments for most online transaction-processing (OLTP) systems is called the Rule of Four, for easy recollection. Take the total number of transactions that will hit the database at any given time and divide by 4 to decide how many undo segments to create.
Consider this example. You have a database that will be used for a small user rollout of an OLTP application. About 25 concurrent transactions will happen on the database at any given time. By applying the Rule of Four, you determine that about six undo segments are required.
Two exceptions exist to the Rule of Four. The first is, if the quotient is less than 4 + 4, round the result of the Rule of Four up to the nearest multiple of 4 and use that number of undo segments. In this case, the result would be rounded from 6 to 8.
The second exception to the Rule of Four is that Oracle generally doesn't recommend more than 50 undo segments for a database, although that exception has faded somewhat in the face of massive database systems requiring more than 2,000 concurrent users. Thus, if the Rule of Four determines that more than 50 undo segments are needed, the DBA should start by allocating 50 and spend time monitoring the undo segment wait ratio to determine whether more should be added later.
*Batch System Processing vs. OLTP
When planning the number of undo segments required on the batch transaction-processing system, you need to make a small number of large undo segments available to support long-running processes that make several data changes. You should monitor the database to see how many transactions your batch processes execute concurrently and apply the Rule of Four to determine the number of undo segments needed, just as you would with an OLTP application. You learn how to calculate the size of undo segments supporting both OLTP and batch transactions in another lesson.
Question 40
Question 41
Question 42
Question 43
Question 44
Question 45
Question 46
Question 47
In this lesson, you learned about system and non-system, and private and public segments. You learned about managing undo segments manually, including how extents are allocated and deallocated, and how to calculate the number of undo segments needed using the Rule of Four.
Lesson 12. Sizing Undo Segments Manually
When you are not using automatic undo management, you must manually create the actual undo segment and make sure it is brought online.After completing this lesson, you should be able to:
- Identify how much undo space different DML statements use
- Manually create an undo segment
- Bring an undo segment online
- Outline the process by which Oracle brings undo segments online at instance startup
There are two components to determining undo segment size, and the first is the overall size of the undo segment. The size of your undo segments, in turn, depends on two main factors: the type of DML statement used to perform the data change and the volume of data being processed. Different DML statements that change data require different amounts of data storage; the order from the least amount of data change information stored in an undo segment to greatest is as follows:
- insert — stores only new ROWID in undo segment
- update — stores ROWID plus old column values
- delete — stores ROWID and all row/column data
The second component involved in undo segment size is the number of extents that will comprise the undo segment. Bigger is often better in determining the number of extents to have in your undo segment. By using more extents in the initial undo segment allocation — determined by the minextents storage option — you reduce the probability of your undo segment extending. Oracle recommends 20 (or more) extents as part of the initial undo segment allocation.
*Creating Undo Segments with Appropriate Storage Settings
Undo segments are created with the create rollback segment statement. All extents in the undo segments of an Oracle database should be the same size. Commit this fact to memory — it's on the Oracle 1Z1-031 exam in one form or another. To partially enforce this recommendation, Oracle disallows the use of pctincrease in the create rollback segment statement.
CREATE ROLLBACK SEGMENT rollseg01
TABLESPACE orgdbrbs
STORAGE ( INITIAL 10K
NEXT 10K
MINEXTENTS 20
MAXEXTENTS 450
OPTIMAL 300K );
Sizes for undo segments and their included extents are determined by the options in the STORAGE clause. The following options are available for setting up undo segments:
| This Storage Option... | Determines... |
|---|---|
| initial | The size in KB or MB of the initial undo segment extent. |
| next | The size in KB or MB of the next undo segment extent to be allocated. Ensure that all extents are the same size by specifying next equal to initial. |
| minextents | The minimum number of extents on the undo segment. The value for minextents should be 2 or greater. |
| maxextents | The maximum number of extents the undo segment can acquire. Be sure to set this to a number and not to unlimited; this will prevent runaway transactions from using all your available tablespace. This is especially important if your UNDOTBS tablespace has datafiles using the autoextend feature. |
| optimal | The total size in KB or MB of the undo segment, optimally. Assuming initial equals next, the value for optimal cannot be less than initial * minextents. |
The following code block demonstrates the creation of a non-SYSTEM private undo segment in your database, according to the guidelines Oracle recommends. On Oracle 1Z1-031 exam questions in this area, you should base your answers on the Oracle guidelines. Note that this command is designed for use with a dictionary-managed tablespace.
CREATE ROLLBACK SEGMENT rollseg01
TABLESPACE orgdbrbs
STORAGE ( INITIAL 10K
NEXT 10K
MINEXTENTS 20
MAXEXTENTS 450
OPTIMAL 300K );
Notice the public keyword is not used. Undo segments are private unless you create them with the create public rollback segment command.
After creating your undo segment, you must bring it online so it will be available for user transactions. This is accomplished with the following command:
alter rollback segment undoseg online
The number of undo segments that can be brought online can be limited at instance startup by setting the MAX_ROLLBACK_SEGMENTS initsid.ora parameter to 1 plus the number of non-system undo segments you want available in Oracle.
CREATE ROLLBACK SEGMENT rollseg01
TABLESPACE orgdbrbs
STORAGE ( INITIAL 10K
NEXT 10K
MINEXTENTS 20
MAXEXTENTS 450
OPTIMAL 300K );
You can create undo segments using the Storage Manager administrative utility in Oracle Enterprise Manager, as well as from within SQL*Plus.
*Bringing Undo Segments Online at Instance Startup
Once you issue the shutdown command, any undo segments you created or brought online while the database was up are now offline as well. They will only be brought back online in one of two ways:
- Issue the command alter rollback segment undoseg online again for every undo segment you want online.
- Allow Oracle to engage in a multi-step process at instance startup. This process is outlined on the following page.
The steps Oracle uses to find rollback segments at instance startup are as follows:
| Step | Action |
|---|---|
| 1 | Oracle acquires (brings online) any undo segments at instance startup named by you in the initsid.ora file. Rollback segments are specified as ROLLBACK_SEGMENTS = (rollseg01,rollseg02...). |
| 2 | Oracle performs a calculation of the undo segments required for the proper operation of the database, based on values set for the TRANSACTIONS, TRANSACTIONS_PER_ROLLBACK_SEGMENT, and other initsid.ora parameters. The calculation performed is TRANSACTIONS ----------------------- TRANSACTIONS_PER_ROLLBACK_SEGMENT. Thus, if TRANSACTIONS is 146 and TRANSACTIONS_PER_ROLLBACK_SEGMENT is 18, then Oracle knows it needs to acquire eight undo segments. |
| 3 | If enough undo segments were named in init.ora, Oracle brings the private undo segments online. If there weren't enough undo segments named, then Oracle attempts to acquire the difference from the pool of public undo segments available. |
| 4 | If there are enough public undo segments available in the pool, Oracle acquires the difference and brings all its acquired undo segments online. Note that the calculation step is required primarily for public undo segments where Oracle Parallel Server is being used. |
| 5 | If not enough public undo segments are available for Oracle to acquire, the Oracle instance will start, and the database will open anyway, with no errors reported in trace files or the ALERT log. |
Question 48
Question 49
Question 50
Question 51
Question 52
Question 53
In this lesson, you learned more about manually managing your undo segments, including how to create the segments themselves and how to set the storage clause for optimal effect. You also learned about the process by which Oracle acquires undo segments at instance startup.
Lesson 13. Maintaining Undo Segments
Even if you're using automatic undo management, you need to know how to manage undo segments.After completing this lesson, you should be able to:
- Bring undo segments online and offline and change their storage options
- Drop an undo segment
- Use views to obtain information about undo data
Several statements are available in Oracle for maintaining undo segments. The first is the alter rollback segment statement. This statement is used to bring the undo segment online as follows
alter rollback segment undoseg online
You can also bring an undo segment offline in this way:
alter rollback segment undoseg offline
However, you can only bring an undo segment offline if it contains no active extents supporting transactions with uncommitted data changes.
The alter rollback segment statement can also be used to change any option in the STORAGE clause except for the size of the initial extent. However, note that changing the next extent size will alter the size of the next extent the undo segment acquires, not the size of any extents already acquired. Furthermore, rollback segments should always be the same size.
ALTER ROLLBACK SEGMENT rollseg01
STORAGE ( MAXEXTENTS 200
OPTIMAL 310K );
The alter rollback segment statement has one additional clause — shrink to. This clause enables you to manually reduce the storage allocation of your undo segment to a size not less than that specified for optimal (if optimal is specified). The syntax is
alter rollback segment undoseg shrink [to x[K|M]]
As with optimal, Oracle will not reduce the size of the undo segment if extents over the size specified are still active. If no value is specified, Oracle will attempt to shrink the undo segment to the value specified for optimal. Finally, Oracle will ignore the statement if the value specified for x is greater than the current undo segment allocation.
ALTER ROLLBACK SEGMENT rollseg01 SHRINK;
ALTER ROLLBACK SEGMENT rollseg01 SHRINK TO 220K;
*Dropping Rollback Segments
Once brought offline, an undo segment can be dropped if you feel it is no longer needed, or if you need to re-create it with different initial, next, and minextents extent size settings. The statement used for this purpose is drop rollback segment undoseg.
DROP ROLLBACK SEGMENT rollseg01;
If you want to manage undo segments manually while Oracle9i is running in automatic undo management mode, you must change the UNDO_SUPPRESS_ERRORS init.ora parameter to TRUE. This parameter is FALSE by default, meaning that any manual attempt to manage undo segments while Oracle automatically manages them will result in errors.
*Obtaining Information about Undo Data
Oracle has many dictionary and dynamic performance views both old and new to track undo statistics. Both the undo as well as the undo data dictionary views have to be used to obtain full information about the undo data. Following are some views to be aware of:
DBA_ROLLBACK_SEGS
This is an existing view that displays the data about undo or undo segments such as the name of the undo segments, the tablespace they reside in, and their current size and status.
DBA_UNDO_EXTENTS
This data dictionary view contains the information about the commit time for each extent in the undo tablespace.
V$UNDOSTAT
This view displays a histogram of statistical data to show the undo behavior over the past 24 hours. Each row in this view keeps statistics of undo segments in the instance for ten-minute intervals. This view can be used with both automatic and manual undo management to estimate the amount of undo space required for the database.
V$ROLLSTAT
This view displays the dynamic performance information for your undo segments when using automatic undo management. Since it displays segments by number (USN), it must be used in combination with V$ROLLNAME to determine the segment name (SEGMENT_NAME).
V$ROLLNAME
This view displays information about segment numbers assigned to each segment. It should be used with V$ROLLSTAT to determine the segment name that corresponds to a given segment number.
V$WAITSTAT
This view shows statistics for buffer wait times. You can use this view to see if there are any waits happening for undo buffers by selecting for class='undo header'.
SELECT class, count
FROM V$WAITSTAT
WHERE class='undo header';
Question 54
Question 55
Question 56
Question 57
Question 58
Question 59
Question 60
Question 61
Question 62
Question 63
In this lesson, you learned how to alter rollback segments, including how to drop them and shrink them to optimal size. You also learned about the data dictionary and dynamic performance views used to manage undo data.
Lesson 14. Course In Review
This lesson includes questions and material covering the entire course. You can use it to review and study for the exam.Topic 14.1 Review Notes
*Review Notes| 1. | Be sure you can identify the types of segments available for storing database objects. |
| 2. | Know that it is important not to store all the segments in your Oracle database inside the SYSTEM tablespace. Oracle9i offers clauses in the create database statement such as default temporary tablespace and undo tablespace that permit you to create tablespaces specifically for temporary and undo segments when you create your database. This is used to prevent Oracle from dumping these segments into the SYSTEM tablespace, which should be used only to house data dictionary and system undo segments. |
| 3. | You can also create the different tablespaces you will need for separating different types of segments by using the create tablespace command. |
| 4. | Be able to distinguish the following types of segments in an Oracle database and describe why they should be stored in their own tablespaces: data, index, undo, and temporary. Repository data should also be stored in a separate tablespace, even though the segment type is the same as data segments. |
| 5. | Extents are allocated whenever a database object runs out of space for incoming data. |
| 6. | The size of an extent allocated depends on the storage settings defined when the object was created. This information comes either from the STORAGE clause in the object creation statement or from the DEFAULT STORAGE clause present on the tablespace the object is stored in. |
| 7. | Understand the use of uniform extent allocations in Oracle9i. Remember that uniform extent management is the default for locally managed temporary tablespaces, whereas automatic allocation is the default for locally managed permanent tablespaces. |
| 8. | Know what the initial, next, and pctincrease settings mean in the context of segments in dictionary-managed tablespaces. Pay particular attention to the exponential growth factor present whenever pctincrease is set to a value greater than zero. |
| 9. | Understand the balancing act required when considering how large to make your segments and extents. Too much preallocation leads to wasted space, whereas too little leads to too many extents. It's a tightrope that ultimately every DBA walks, and the right answer often depends on the needs of a particular system. For the Oracle9i DBA Fundamentals I exam, understand how to define these parameters, while for real life, you'll need a lot of experience with a particular system (or a lot of meetings with users to figure out how quickly they add data) before the numbers start seeming intuitive. |
| 10. | Understand the use of pctfree and pctused with respect to space management in a table. |
| 11. | The pctfree option tells Oracle how much space should be left over when new rows are added to a block in order to leave room for growth in existing rows. |
| 12. | The pctused option tells Oracle the space usage threshold below which Oracle should place a block on the table's freelist so new rows can be added to that block. |
| 13. | The values for these options are set in tandem when the database object is created, so that they never add up to a value close to 100. |
| 14. | Know the different views in the data dictionary that give you information about storage. |
| 15. | Be sure you can define the threefold purpose of an undo segment in Oracle, including the usage of undo segments for read consistency, transaction recovery, and instance recovery. |
| 16. | Know the causes of the Snapshot too old error in Oracle. |
| 17. | For automatic undo management, you must first create undo tablespaces in Oracle. Undo tablespaces can be created using the create undo tablespace command or by including the undo tablespace clause in the create database command. |
| 18. | Undo tablespaces can be altered using the alter tablespace or alter database commands to add datafiles, rename datafiles, and bring a datafile online, offline, or in backup mode. Undo tablespaces can also be dropped (provided no active transactions are in them) using the drop tablespace command. |
| 19. | To configure Oracle9i to run in automatic undo management mode, you must set the parameter UNDO_MANAGEMENT to AUTO in the init.ora file. |
| 20. | You can have more than one undo tablespace in your database, but only one will be used at a time per instance. If you have more than one undo tablespace, then the one to be used by the instance can be specified in the parameter UNDO_TABLESPACE. If you forget to set this parameter in your init.ora file, it is still possible that Oracle will bring the correct undo tablespace online for you via the SPFILE feature in Oracle9i. |
| 21. | You also specify how long undo data should be kept available for Flashback Query by setting the UNDO_RETENTION initialization parameter. |
| 22. | Once the undo tablespace is created and automatic undo management is enabled, you do not have to actually create the undo segments themselves. Oracle9i creates them for you as soon as the undo tablespace is created. Moreover, Oracle brings the undo segments online automatically when the database starts. |
| 23. | Be able to answer the following questions: What happens to database performance if Oracle has to allocate extents to its undo segments frequently without giving them up? What storage option can be used to minimize this occurrence? |
| 24. | How are minextents and maxextents used in sizing undo segments? What is the Rule of Four? What rules can you apply to sizing undo segments on batch applications? Why is it important to use many extents in your undo segment? |
| 25. | How are extents of an undo segment deallocated? |
| 26. | Identify the options available for the undo segment storage clause, and describe their general use. How does Oracle attempt to enforce equal sizing of all extents in undo segments? |
| 27. | How are undo segments brought online after creation? At instance startup? |
| 28. | What storage option cannot be modified by the alter rollback segment statement? How might you manually make an undo segment unavailable for transaction usage, and what might prevent you from doing so? |
| 29. | What is the shrink to clause of the alter rollback segment statement, and how is it used? When is it appropriate (or possible) to eliminate an undo segment, and what statement is used to do it? |
| 30. | Be sure you can identify the dictionary and dynamic performance views available for managing undo segments in Oracle. |
Question 64
Question 65
Question 66
Question 67
Question 68
Question 69
Question 70
Question 71
Question 72
Question 73
Question 74
Question 75
Topic 14.2 Exam Preparation
*Exam PreparationHere is a bulleted list of fast facts to review, or crib notes for the days leading up to the Oracle 1Z1-031 exam.
- Every database object in Oracle is stored in a segment. Because these segments support data in Oracle that are used for different purposes, you should keep the different segments in different tablespaces.
- Understand the inverse proportional relationship between the lifespan of extents and fragmentation in the tablespace — the shorter the lifespan, the higher the potential for fragmentation in the tablespace.
- Remember that the method by which you can control the allocation of extents by database objects is determined by the storage settings for a database object. These can either be set at the object level or inherited from the default settings for the tablespace. The storage settings are
initial — First segment in the object
next — Next segment allocated (not simply the second one in the object)
pctincrease — Percentage increase of next extent allocated over next value
minextents — Minimum number of extents allocated at object creation
maxextents — Maximum number of extents the object can allocate
pctfree — How much of each block stays free after insert for row update
pctused — Threshold that usage must fall below before a row is added - Understand how Oracle enables the DBA to control space usage at the block level with pctfree and pctused.
- Know what dictionary views are used to find information about storage structures, including
DBA_SEGMENTS
DBA_TABLESPACES
DBA_TS_QUOTAS
V$TABLESPACE
DBA_EXTENTS
DBA_FREE_SPACE
DBA_FREE_SPACE_COALESCED. - Undo segments used to be called rollback segments in prior versions of Oracle.
- Undo segments enable transaction processing to occur by storing the old version of data that has been changed but not committed by the users.
- Oracle9i offers a new feature for undo segments — automatic undo management. To use automatic undo management, you must execute the following steps:
1. Create UNDO tablespaces either when you create the database using the undo tablespace clause or by using the create undo tablespace command.
2. Set the UNDO_MANAGEMENT parameter to AUTO and the UNDO_TABLESPACE parameter to the UNDO tablespace you created.
3. Set the UNDO_RETENTION parameter to the length of time you want to retain undo information in the segments in support of Flashback Query. - You can also manage undo segments manually, as you did in previous versions of Oracle. If you do so, set UNDO_MANAGEMENT to MANUAL. You might also want to set UNDO_SUPPRESS_ERRORS to TRUE in order to avoid the chance that your database running in automatic undo management will return errors if you try to manage undo segments manually.
- If you decide to manage undo segments manually, make sure that undo segments consist of equally sized extents.
- The pctincrease option is not permitted on undo segments if you create them manually.
- Undo segments must be brought online in order to use them. Oracle will take care of this automatically if you use automatic undo management, but if you use manual undo management, you have to bring them online yourself.
- An undo segment cannot be taken offline until all active transactions writing undo entries have completed. This same restriction applies to tablespaces containing active undo segments.
- Entries are associated with transactions in the undo segment via the use of a system change number (SCN).
- Specific private undo segments can be allocated at startup if they are specified in the ROLLBACK_SEGMENTS parameter in initsid.ora when you're using manual undo management.
- Monitor performance in undo segments with V$ROLLSTAT and V$WAITSTAT when using manual undo management.
- Know the new dictionary views in support of automatic undo management.
No comments:
Post a Comment