Oracle tablespace management is a fascinating concept because it involves mapping plain old files on a disk in your host system to a more abstract concept of space where your Oracle database can store information.
The physical and logical components being mapped together is the basis for understanding what we'll present in this course. You will learn what a tablespace is, how it is managed, and what you must do as an Oracle DBA to keep things flowing smoothly from a storage management perspective.
This course presents information relevant to the following Oracle 1Z1-031 Oracle9i DBA Fundamentals I exam objectives:
Managing Tablespaces and Datafiles
- Describe the logical structure of tablespaces within the database
- Create tablespaces
- Change the size of tablespaces
- Allocate space for temporary segments
- Change the status of tablespaces
- Change the storage settings tablespaces
- Implement Oracle Managed Files
Lesson 2. Oracle's Logical Structure
Oracle databases have a logical structure consisting of tablespaces, segments, and extents.After completing this lesson, you should be able to:
- Describe the logical structure of Oracle databases
- Describe the underlying physical disk storage layout
Meet three players in the world of logical Oracle disk resources: tablespaces, segments, and extents.
A tablespace is a logical database structure that is designed to store other logical database structures. Oracle sees a tablespace as a large area of space into which Oracle can place new objects. Space in tablespaces is allocated in segments.
A segment is an allocation of space used to store the data of a table, index, undo segment, or temporary object.
When the database object runs out of space in its segment and needs to add more data, Oracle lets it allocate more space in the form of an extent.
An extent is similar to a segment in that the extent stores information corresponding to a table, index, undo segment, or temporary object.
More about segments and extents is covered in another lesson on storage structures and relationships, so for now, we will focus on tablespaces. When you are logged into Oracle and manipulate storage factors, you are doing so with the logical perspective of tablespaces.
*Data Blocks
The other perspective you will have on your Oracle database is that provided by the operating system of the host machine. Underlying the logical storage in Oracle is the physical method your host system uses to store data, the cornerstone of which is the block.
Segments and extents are composed of data blocks, and in turn, the blocks are taken together to comprise a datafile. Recall that you specified a value in bytes for an initialization parameter called DB_BLOCK_SIZE. This parameter determined the standard size of each Oracle block. Block size is typically specified as a multiple of operating system block size. Oracle blocks are usually 2KB, 4KB, 8KB, and sometimes 16KB.
Prior versions of Oracle released before Oracle9i required that the entire database use only one block size and that once the block size for the database was defined, you couldn't change it later.
In Oracle9i, Oracle enables you to specify up to five nonstandard block sizes for your database, providing a great deal more flexibility in terms of using one database to fill multiple data management roles in your organization. More information about standard and nonstandard block sizes is discussed in another course.
A tablespace may consist of one or many datafiles, and the objects in a tablespace can be stored by Oracle anywhere within the one or multiple datafiles comprising the tablespace.
Although a tablespace may have many datafiles, each datafile can belong to only one tablespace.
Here you see the logical and physical disk storage layout used in your Oracle database.
Question 1
Question 2
Question 3
In this lesson, you learned the basic elements of the logical structure of an Oracle database — tablespaces, segments, and extents.
You also learned how to relate the logical structure to the physical methods the host system uses to store data. You learned that segments and extents are composed of data blocks, and that the blocks, taken together, make up a datafile.
Lesson 3. Understanding Tablespaces
Tablespaces are large areas of space into which Oracle can place new objects.After completing this lesson, you should be able to:
- Describe how Oracle uses tablespaces to manage free space
- Explain the difference between dictionary-managed tablespaces and locally managed tablespaces
- Describe the two types of segments stored in Oracle tablespaces (temporary and permanent)
Free space management is an important task for Oracle because without it, Oracle would not know where to put things like tables or indexes when you wanted to create and modify them.
Prior to Oracle8i, all tablespaces were created as dictionary-managed tablespaces. Dictionary-managed tablespaces rely on Oracle populating data dictionary tables housed in the SYSTEM tablespace to track free space utilization.
With Oracle8i and later, there is a new type of tablespace called the locally managed tablespace.
Locally managed tablespaces use bitmaps stored within the header of the datafiles comprising a tablespace to track the space utilization of the tablespaces. This bitmap represents every block in the datafile, and each bit in the map represents whether that block is free or not.
Within tablespaces, Oracle manages free space by coalescing it into contiguous segments. The system monitor (or SMON) background process handles this coalescing activity automatically.
When new database objects are created, Oracle will acquire the requested amount of contiguous storage space in the form of a segment for the new object. The amount of space SMON will use is based either on the object's own storage clause, the default storage clause for that tablespace, or on the uniform extent allocation configured for the tablespace.
For the Oracle9i DBA Fundamentals I exam, remember that SMON is the process that handles this coalescing of free space into contiguous chunks on an ongoing basis while Oracle is running.
As SMON coalesces free space, it either updates the dictionary tables in the SYSTEM tablespace to notify Oracle where the free space is in dictionary-managed tablespaces, or it maintains the bitmap in the datafiles of the tablespace when locally managed tablespaces are used.
The SYSTEM tablespace is always a dictionary-managed tablespace.
Why? Because the dictionary files are in the SYSTEM tablespace anyway, so you don't really get much performance gain with locally managed SYSTEM tablespaces if the data dictionary is already local to the tablespace!
Why might we want to use locally managed tablespaces rather than dictionary-managed tablespaces?
Locally managed tablespaces offer better performance because Oracle stores the storage information in file headers and bitmap blocks, eliminating the recursive operations that are required with dictionary-managed space allocation.
Beginning in Oracle9i, the default space management for tablespaces other than the SYSTEM tablespace is locally managed. However, you can explicitly specify that you want to create a dictionary-managed tablespace.
Tip: The syntax of creating locally managed and dictionary-managed tablespaces is discussed in another lesson.
*Permanent vs. Temporary Segments
Regardless of how you configure Oracle to handle space management in your tablespace, the tablespace itself will generally be configured to store two types of segments: permanent and temporary.
A permanent segment is one designed to store data for a table or other object that's going to house data for a long time in your database.
For example, if you're using Oracle to manage employee information for a large corporation, chances are the employee data is going to stay in the database for many months or years. Thus, you would use permanent segments to house that data in your database.
Along that same line of thinking, you would want to house your permanent segments inside a permanent tablespace. In fact, Oracle requires that permanent segments be housed in permanent tablespaces.
In contrast, some forms of data in Oracle are stored only temporarily for the duration of a particular database operation. An example of such an operation is a disk sort, such as when a user queries Oracle for a very large set of records that must be returned sorted in alphabetical order to the user.
A sort operation can require a lot of memory temporarily. Sometimes the sort operation may require more memory than Oracle has available to use. To cope with the lack of memory, Oracle starts writing records to disk in a temporary segment. When Oracle is finished with the sort, Oracle no longer needs the data housed in the temporary segment, so Oracle deletes the temporary segment automatically behind the scenes from both the user and the DBA.
You should understand that permanent tablespaces in Oracle are designed to house temporary segments for backward compatibility purposes.
However, the space allocation and deallocation required to handle disk sorts take place within a matter of seconds and could be very disruptive to other permanent objects housed in a tablespace, so Oracle also offers a special type of tablespace called a temporary tablespace. The temporary tablespace is only used for housing temporary objects. No permanent database objects are allowed.
*Default Temporary Tablespaces
Because it is possible for any user in Oracle to issue a long-running query to return data in sorted order, every user in Oracle must be assigned a tablespace where Oracle can write temporary segments in case Oracle must perform a disk sort on behalf of that user.
Prior versions of Oracle handled this assignment when the user was created using the optional but highly recommended temporary tablespace tablespacename clause in the create user command.
This approach has one tragic flaw. If you didn't specify a temporary tablespace clause in your create user command, Oracle assigned the user to the SYSTEM tablespace for temporary segment storage needs.
This is very bad, because temporary segment storage allocations can be very disruptive to permanent segments stored in the database.
In Oracle9i, you can create a default temporary tablespace when creating a database by including the default temporary tablespace clause in your create database command.
If you do not create one, SYSTEM will become the default temporary storage location. However, you will be warned in the alert log file about the missing default temporary tablespace.
Let's take a quick look at the create database command from another course. However, this time we've revised it to include a default temporary tablespace.
CREATE DATABASE orgdb01
CONTROLFILE REUSE
LOGFILE
GROUP 1 ('/u01/oradata/oracle/redo1a.log',
'/u02/oradata/oracle/redo1b.log') SIZE 5M,
GROUP 2 ('/u02/oradata/oracle/redo2a.log',
'/u01/oradata/oracle/redo2b.log') SIZE 5M
MAXLOGFILES 40
DATAFILE '/u03/oradata/oracle/sys01.dbf'
SIZE 50M AUTOEXTEND ON NEXT 30M MAXSIZE 150M
MAXDATAFILES 240
CHARACTERSET WE8IS08859P1
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u04/oradata/oracle/temp01.dbf' SIZE 100M;
The default temporary tablespace can also be allocated after the database has been created using the create default temporary tablespace command.
Question 4
Question 5
Question 6
Question 7
Question 8
In this lesson, you learned how Oracle uses tablespaces to manage free space in a database.
You learned two different methods Oracle uses to track space utilization in tablespaces. Dictionary-managed tablespaces use the SYSTEM tablespace, while locally managed tablespaces use bitmaps within the datafiles comprising a tablespace.
Finally, you learned how Oracle uses both temporary and permanent segments to store data in tablespaces.
Lesson 4. Creating Tablespaces
In a typical Oracle database, you will want to segregate different types of data into different tablespaces.After completing this lesson, you should be able to:
- Describe the common types of Oracle tablespaces
- Create tablespaces to house permanent segments
- Create tablespaces to house temporary segments
- Apply storage options to newly created database objects
Historically in Oracle, the SYSTEM tablespace was the only tablespace you could create during the creation of the database. This is done by explicitly specifying the location of the SYSTEM tablespace datafile in the create database command.
You can still do things this way, but starting with Oracle9i, you can also create two other types of tablespaces at the same time.
One example is where we create a default temporary tablespace in the create database command. The other example is the undo segment tablespace.
For those of you approaching Oracle9i with experience as an Oracle DBA on prior versions, you should make a mental note that undo is the same as rollback. You can create your SYSTEM, temporary, and UNDOTBS tablespaces when you create the Oracle database in Oracle9i.
*Common Tablespaces
On a typical database, you'll want to segregate different types of data into different tablespaces.
At a minimum, you will typically want to create the following tablespaces:
| Tablespace | Function |
|---|---|
| SYSTEM | Every database must have a SYSTEM tablespace. This tablespace is created when you create the database. |
| DATA | A DATA tablespace is used to house table data. |
| INDEX | An INDEX tablespace is used to house indexes separate from other types of objects. |
| UNDOTBS | An UNDOTBS tablespace houses undo segments (called rollback segments in prior versions of Oracle). These need to be kept separate from other types of objects due to volatility in allocating extents. |
| TEMP | A TEMP tablespace houses temporary segments. These also need to be kept separate from other types of objects due to volatility in allocating extents. |
| TOOLS | A TOOLS tablespace houses objects that support administrative or other tools you might use in conjunction with your database, such as Oracle Enterprise Manager. |
We'll focus on the creation of a tablespace to house object data for the rest of this course.
Keep in mind that the principles you learn apply to the manual creation of the other types of tablespaces as well.
*Using create tablespace
With the exception of the three tablespaces you can create while creating the database, all other tablespaces must be created using the create tablespace command.
Let's look at an example where we create a locally managed tablespace for a database hosted in a Windows environment that we will probably use to hold table data in our database.
create tablespace
How do we know this tablespace will hold table data? Because of the nomenclature — notice that the name of the tablespace is DATA. This typically indicates that the tablespace will be used for housing table data.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
The creation of a tablespace can take a long time, depending on how large you specified your tablespace datafiles to be. This is because Oracle has to physically allocate itself a file of whatever size you specify on the host system. You should be patient during this operation, particularly if you've specified your tablespace to be quite large.
As you can see, there are several components to the create tablespace statement.
First, you specify the datafiles your tablespace will own, using absolute pathnames. (If you are using an operating system like UNIX to host your Oracle database, be sure the pathnames you specify for your datafiles are ones Oracle has permission to write.)
Notice that one of our datafiles has an autoextend clause defined for it. This feature enables the datafile to grow past its originally defined size automatically in order to accommodate data growth.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
The next step is to specify the default storage clause to set options that will be applied to database object creation if the object created does not have storage parameters defined for it.
If an object placed in this tablespace has its own storage clause defined, then the object's storage clause settings will override the tablespace's default storage clause settings, with one exception.
There is one storage option that when defined in a tablespace cannot be overridden. That option is minimum extent, which ensures that every extent size used in the tablespace is a multiple of the specified integer value.
The other details of the default storage parameters will be explained shortly.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
Permanent tablespaces like the one created here can house both permanent segments and temporary segments. This functionality is provided by Oracle for backward compatibility purposes. However, you should put temporary segments in temporary tablespaces.
After that, notice that we define this tablespace to be locally managed using the extent management local clause.
This is the default space management setting for tablespaces in Oracle9i and later releases, so we didn't need to specify the clause in order to achieve local extent management. If we wanted to specify dictionary-managed tablespaces, we would have used the extent management dictionary clause instead.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
You can specify that the tablespace houses permanent database objects with the permanent keyword. However, Oracle assumes that the tablespace is a permanent tablespace if the permanent keyword is omitted.
Finally, we instruct Oracle to bring the tablespace online after creating it using the online keyword. This is the default availability status of your tablespace after you create it: online. If you omit the online keyword from your create tablespace statement, it will still be online. You can also ensure that it is online later by issuing alter tablespace name online.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
If no specification is made in a create tablespace command whether it is locally managed or dictionary-managed, Oracle9i will create it as a locally managed tablespace.
*Creating Tablespaces for Temporary Segments
Most of your tablespaces on the database will house permanent objects that will stick around in your database for a long time.
However, remember that you will also want a special tablespace for housing temporary segments.
If you have not created a default temporary tablespace during the database creation, then you can create a tablespace to store temporary objects.
You should do so for two reasons. First, to take advantage of Oracle's more efficient usage of sort segments compared to temporary segments. And second, to prevent anyone from creating a permanent database object, such as a table or index with data, in the tablespace used for temporary segments.
There are some important keywords you should be aware of that are different for creating temporary tablespaces.
Take a moment to review this code block, paying special attention to the keywords in bold.
SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '/u06/oradata/oracle/temp01.dbf' SIZE 300M
3 extent management local;
Tablespace created.
Another way to create a temporary tablespace is to include the temporary keyword at the end of the create tablespace command.
Note that we use the datafile keyword rather than tempfile when we create a temporary tablespace in this manner, as illustrated in the following code block:
SQL> CREATE TABLESPACE temp
2 DATAFILE '/u06/oradata/oracle/temp01.dbf' SIZE 300M
3 extent management local TEMPORARY;
Tablespace created.
Like permanent tablespaces, your temporary tablespace will be brought online automatically after Oracle creates it.
You can view the status of your temporary tablespaces in the data dictionary view DBA_TEMP_FILES. You can also view whether a tablespace is temporary or not by viewing the CONTENTS column of the DBA_TABLESPACES data dictionary view.
Tip: All tablespaces in the database will use the standard block size defined for the database. A standard block size is the size of the Oracle block that is defined by the parameter DB_BLOCK_SIZE. However, you may also create a tablespace with a nonstandard block size. This could be useful when transporting tablespaces between databases of different block sizes. To use nonstandard block sizes, you must configure subcaches within the buffer cache of System Global Area (SGA) for all the nonstandard block sizes that you intend to use. There are platform-specific restrictions and some platforms may not support it.
*Default Storage Options
The default storage clause defines storage options that will be applied to newly created database objects if the create statement does not have storage parameters defined for it.
SQL> create tablespace test01 datafile 'D:\ORACLE\test01.dat'
2 size 1M default storage ( initial 200K
3 next 200K pctincrease 50
4 minextents 1 maxextents 5 ) temporary;
Tablespace created.
The initial and next options specify the size of the object's initial segment and next allocated extent, respectively.
If minimum extent is defined for the tablespace you put your object in, and the value specified for next on your database object is less than minimum extent, Oracle rounds up to the next highest multiple for minimum extent and creates the initial or next extent as that size. This feature can reduce the amount of fragmentation in a tablespace.
minimum extent
This is really the old way of doing things. You'll want to use locally managed uniform extent allocation in order to take advantage of the new technology. However, you should be familiar with the old way to do this as well in case there's a question on the Oracle9i DBA Fundamentals I exam.
The minextents and maxextents options specify the minimum and maximum number of extents the object can allocate in the tablespace.
SQL> create tablespace test01 datafile 'D:\ORACLE\test01.dat'
2 size 1M default storage ( initial 200K
3 next 200K pctincrease 50
4 minextents 1 maxextents 5 ) temporary;
Tablespace created.
If you specify minextents greater than one and the tablespace has more than one datafile, Oracle will tend to spread extents over multiple datafiles, which can improve performance if those datafiles are also located on different disk resources.
Finally, pctincrease enables you to specify a percentage increase in the amount of space allocated for the next extent in the object.
SQL> create tablespace test01 datafile 'D:\ORACLE\test01.dat'
2 size 1M default storage ( initial 200K
3 next 200K pctincrease 50
4 minextents 1 maxextents 5 ) temporary;
Tablespace created.
If next is set to 200KB and pctincrease is 50, the second extent would be 200KB in size, the third extent would be 300KB (50 percent more than the second extent), the fourth extent would be 450KB (50 percent more than the third extent), and so on.
The minimum value is 0, and the default value is 50. The calculated value is rounded up to the next data block, which is a multiple of five times DB_BLOCK_SIZE. To make all extents the same size, specify pctincrease to be zero.
Question 9
Question 10
Question 11
Question 12
In this lesson, you learned about the common types of tablespaces used to house different types of data in an Oracle database.
You learned to use the create tablespace command to create tablespaces that house permanent and temporary segments.
You also learned to use the default storage clause to apply storage options to newly created database objects.
Lesson 5. Changing Tablespace Size
There are several ways to change the size of Oracle tablespaces.After completing this lesson, you should be able to:
- Increase tablespace size by adding new datafiles
- Increase or decrease the size of existing datafiles
- Configure datafiles to extend automatically
Once a tablespace is created, there are a few different ways to modify its size:
- Adding new datafiles
- Increasing or decreasing the size of existing datafiles
- Configuring datafiles to extend automatically
*Adding New Datafiles
You can add as many datafiles to your tablespace as you want, subject to two restrictions.
First, you cannot add datafiles that will exceed the physical size of your disk resources (that restriction is pretty straightforward).
To add datafiles, use the alter tablespace add datafile statement. Below is an example.
SQL> ALTER TABLESPACE data ADD DATAFILE
2 'G:\oradata\Oracle\data03.dat' SIZE 50M;
The length of time Oracle requires to complete this operation depends directly on the size of the datafile you want to add.
*Changing Size of Existing Datafiles
The other restriction is that you can't exceed the number of datafiles permitted for your database, as specified by the maxdatafiles parameter.
If you've reached the maximum number of files, and still need more room, you can increase the size of existing datafiles with the resize keyword.
Resizing a datafile upward is rarely met with difficulty, unless there is not enough space in the file system.
resize
Resizing a datafile upward is an operation you perform on individual datafiles, not at the tablespace level.
To do so, issue the statement shown here.
SQL> ALTER DATABASE DATAFILE
2 'G:\oradata\Oracle\data03.dat'
3 RESIZE 1088M;
You can also resize a datafile to be smaller, either through dropping datafiles with alter database datafile filename offline drop or by resizing a datafile to be smaller.
alter database datafile filename offline drop;
This is not always safe, however, especially if the datafile contains segments or extents owned by database objects. Be careful when attempting this sort of activity.
*Automatically Extending Datafiles
Another way to expand the size of your existing datafiles is through the use of the autoextend feature.
As with resizing datafiles, enabling the autoextend feature is an operation you perform on individual datafiles, not on the tablespace to which the datafile belongs.
To enable automatic extension of your datafile, execute the statement shown here.
SQL> ALTER DATABASE DATAFILE
2 'G:\oradata\Oracle\data03.dat'
3 AUTOEXTEND ON NEXT 100M MAXSIZE 1988M;
Notice a few important features of the autoextend clause. First, you define the size of the next block of space Oracle will acquire for the datafile using the next clause. In this case, we tell Oracle to acquire another 100MB whenever it needs to extend the size of the datafile.
Second, we tell Oracle the maximum size we want the datafile to be able to grow to using the maxsize clause. The value specified for this clause must be larger than the datafile's current size. In this case, we tell Oracle we want this datafile to grow to a size of just under 2GB.
SQL> ALTER DATABASE DATAFILE
2 'G:\oradata\Oracle\data03.dat'
3 AUTOEXTEND ON NEXT 100M MAXSIZE 1988M;
Question 13
Question 14
Question 15
Question 16
Question 17
In this lesson, you learned three ways of changing the size of Oracle tablespaces.
You learned to increase tablespace size by adding new datafiles.
You learned to increase or decrease the size of existing datafiles using the resize keyword.
Finally, you learned to configure datafiles to automatically extend using the autoextend feature.
Lesson 6. Using Temporary Segments
Temporary segments are segments used to store temporary data.After completing this lesson, you should be able to:
- Allocate appropriate space for temporary segments
- Manage temporary segments used in permanent tablespaces
- Manage temporary segments used in temporary tablespaces
- Create locally managed temporary tablespaces
Temporary segments can be housed in both permanent tablespaces and temporary tablespaces. This functionality is provided for backward compatibility.
However, you should always design your databases so that temporary segments are housed in temporary tablespaces and permanent segments are housed in permanent tablespaces.
You cannot put a permanent database object (a table, for example) in a temporary tablespace. You can switch a tablespace between being permanent and temporary, provided the permanent tablespace does not contain permanent database objects when you try to switch it to a temporary tablespace.
The example shown here illustrates this.
SQL> create tablespace test01 datafile 'D:\ORACLE\test01.dat'
2 size 1M default storage ( initial 10K
3 next 10K pctincrease 0
4 minextents 1 maxextents 5 ) temporary;
Tablespace created.
SQL> create table dummy3 (dummy varchar2(10)) tablespace test01;
create table dummy3 (dummy varchar2(10)) tablespace test01;
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY
tablespace
SQL> alter tablespace test01 permanent;
Command completed successfully;
SQL> create table dummy3 (dummy varchar2(10)) tablespace test01;
Table created.
SQL> alter tablespace test01 temporary;
alter tablespace test01 temporary
ERROR at line 1:
ORA-01662: tablespace 'TEST01' is non-empty and cannot be made temporary
*Temporary Segments in Permanent Tablespaces
A user may be assigned to either a permanent or temporary tablespace for sorting.
Users create temporary segments in a tablespace when a disk sort is required to support their use of select statements containing the group by, order by, distinct, or union clauses, or the create index statement.
Users can be assigned to either permanent or temporary tablespaces for creating temporary segments.
If the user is assigned to a permanent tablespace for creating temporary segments, the temporary segment will be created at the time the disk sort is required. When the disk sort is complete, the SMON process drops the temporary segment automatically to free the space for other users.
Because this activity causes high fragmentation, it is advisable to create a separate temporary tablespace to store the temporary segments for all users.
*Temporary Segments in Temporary Tablespaces
Temporary space is managed differently in temporary tablespaces.
Instead of allocating temporary segments on-the-fly, only to have them be dropped later by SMON, the Oracle instance allocates one sort segment for the first statement requiring a disk sort. All subsequent users requiring disk sorts share that segment.
There is no limit to the number of extents that can be acquired by the sort segment. The sort segment is released at instance shutdown.
Management of temporary segments in this manner improves performance in two ways.
First, Oracle saves time by assigning transactions to temporary segments that have been preallocated.
Second, Oracle does not deallocate the primary temporary segment once the sorting operation is complete. Rather, Oracle simply eliminates the extents and keeps the primary segment available for the next transaction requiring a sort.
All space management for the sort segment in a temporary tablespace is handled in a new area of the SGA called the sort extent pool. A process needing space for disk sorts can allocate extents based on information in this area.
*Locally Managed Temporary Tablespaces
Your temporary tablespaces should be locally managed.
Oracle needs fast access to free space information when performing a disk sort, and disk sorts are notoriously poor performers anyway. There's no sense in making your data dictionary yet another bottleneck in the process.
The statement shown here is an example for how to create a locally managed temporary tablespace.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/DISK2/temp_01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;
Question 18
Question 19
Question 20
Question 21
In this lesson, you learned to manage the use of temporary segments.
You learned that temporary segments can appear in both permanent and temporary tablespaces, but that you should usually use temporary tablespaces for them.
You learned how Oracle processes data when users are assigned to permanent tablespaces for sorting, and how performance improves when you use temporary tablespaces.
Finally, you learned to create locally managed temporary tablespaces.
Lesson 7. Managing Uniform Extent Sizing
Uniform extent sizing is a feature designed to simplify how extents are allocated to objects.After completing this lesson, you should be able to:
- Set a uniform extent size
- Set appropriate extent sizes for temporary tablespaces
- Display temporary segment information in data dictionary views
You've already seen the use of the tempfile and temporary keywords to define temporary tablespaces. However, the uniform extent management clause is new to you.
uniform extent management can simplify the way extents are allocated to objects.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/DISK2/temp_01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M;
Rather than having every object define its own extent allocation via a storage clause, or having the tablespace assign objects a storage allocation via the default storage clause, uniform extent management simplifies the process by assigning every object in the tablespace the exact same extent management configuration.
When an object is placed in a tablespace using uniform extent management, the tablespace's uniform extent management setting overrides any storage configuration included in the object's creation statement.
*Space Allocation in Temporary Tablespaces
Temporary tablespaces offer improved performance for disk sorts and better multiuser space management.
If you use default storage to govern how temporary segments and extents are sized in the tablespace, there are some special rules you should know when defining values for these storage options.
default storage
Because, by the definition of a disk sort, the data written to disk will equal SORT_AREA_SIZE, your extents must be at least that large.
Size your initial sort segment according to this formula:
num x SORT_AREA_SIZE + DB_BLOCK_SIZE
num is a small number of your choice used as a multiplier of SORT_AREA_SIZE.
This sizing formula allows for header block storage as well as multiple sort data to be stored in each extent.
Next, as with undo segments, sort segments should acquire extents that are all the same size, so set initial equal to next.
Also, pctincrease should be zero.
Finally, the maxextents storage option is not used in temporary tablespaces.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/DISK2/temp_01.dbf' SIZE 1M
DEFAULT STORAGE ( INITIAL 200K
NEXT 200K PCTINCREASE 0 );
You also can create multiple temporary tablespaces to support different types of disk sorts required by your users.
For example, you might have an extremely large temporary tablespace for long-running select order by statements in report batch processes or for the creation of an index on a large table that is periodically reorganized.
In addition, you might include a smaller temporary tablespace for disk sorts as the by-product of ad hoc queries run by users. Each of these temporary tablespaces can then be assigned to users based on their anticipated sort needs.
*Obtaining Temporary Segment Information
There are several data dictionary views available for obtaining information about temporary segments.
The views in the dictionary displaying this information base their content either on temporary segments that exist in the database or on dynamic performance information about temporary segments collected while the instance is running.
SQL> select owner, segment_name, segment_type, tablespace_name
2 from dba_segments;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ----------------
SYS 13.2 TEMPORARY TEST01
The views you should remember for viewing temporary segment information include the following.
| View | Explanation |
|---|---|
| DBA_SEGMENTS | Provides information about the name, tablespace location, and owner of both types of temporary segments in Oracle. Note that you will only see information on temporary segments in permanent tablespaces while those segments are allocated, but you will see information about temporary segments in temporary tablespaces for the life of the instance. |
| V$SORT_SEGMENT | Gives information about the size of the temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. |
| V$SORT_USAGE | Displays information about sorts that are happening currently on the database. This view is often joined with V$SESSION. |
You can obtain the name, segment type, and tablespace storing sort segments using the DBA_SEGMENTS view.
Note that this segment will not exist until the first disk sort is executed after the instance starts. Here is an example:
SQL> select owner, segment_name, segment_type, tablespace_name
2 from dba_segments;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
----- ------------ ------------ ----------------
SYS 13.2 TEMPORARY TEST01
You can get the size of sort segments allocated in temporary tablespaces by issuing queries against V$SORT_SEGMENT, which you will find useful in defining the sizes for your temporary tablespaces on an ongoing basis.
The query shown here illustrates how to obtain this sort segment high-water mark information from V$SORT_SEGMENT.
SQL> select tablespace_name, extent_size,
2 total_extents, max_sort_blocks
3 from v$sort_segment;
TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS MAX_SORT_SIZE
--------------- ----------- ------------- -------------
TEST01 3147776 14 44068864
Finally, you can see information about sorts currently taking place on the instance by joining data from the V$SESSION and V$SORT_USAGE views.
Here is an example:
SQL> select a.username, b.tablespace,
2 b.contents, b.extents, b.blocks
3 from v$session a, v$sort_usage b
4 where a.saddr = b.session_addr;
USERNAME TABLESPACE CONTENTS EXTENTS BLOCKS
-------- ----------- --------- ------- ------
SPANKY TEST01 TEMPORARY 14 21518
*Dictionary Views
There are a couple of new dictionary views for managing temporary tablespaces:
| View | Explanation |
|---|---|
| DBA_TEMP_FILES | This dictionary view gives you information about every datafile in your database that is associated with a temporary tablespace. |
| V$TEMPFILE | Similar to DBA_TEMP_FILES, this performance view gives you information about every datafile in your database that is associated with a temporary tablespace. |
Question 22
Question 23
Question 24
In this lesson, you learned to use Oracle's uniform extent sizing feature to simplify the allocation of extents to objects.
You learned to set a uniform extent size using the uniform extent size clause.
You learned to set appropriate extent sizes for temporary tablespaces using the default storage clause.
Finally, you learned to use data dictionary views to display temporary segment information.
Lesson 8. Changing Tablespace Status
At times, you will want to change the status of an existing tablespace — taking it online or offline, or making the tablespace read-only.After completing this lesson, you should be able to:
- Take tablespaces online and offline
- Take datafiles online and offline
- Make tablespaces read-only
One of the create tablespace code blocks in another lesson shows how to create the tablespace so that it is online and available for use as soon as it's created.
SQL> CREATE TABLESPACE DATA DATAFILE
2 'E:\oradata\Oracle\data01.dat' SIZE 20M,
3 'F:\oradata\Oracle\data02.dat' SIZE 30M
4 AUTOEXTEND ON NEXT 10M MAXSIZE 50M
5 MINIMUM EXTENT 150K
6 EXTENT MANAGEMENT LOCAL
7 PERMANENT ONLINE;
The alter tablespace name online statement also enables you to bring a tablespace online after creation.
You can also take a tablespace offline using the alter tablespace name offline statement.
You might do this if you were trying to prevent access to the data in that tablespace while simultaneously leaving the rest of the database online and available for use.
alter tablespace name offline;
Individual datafiles can be taken online and offline as well, using the alter database datafile filename online or alter database datafile filename offline statements.
alter database datafile filename online;
alter database datafile filename offline;
*Priorities
A tablespace can be taken offline with one of several priorities, including normal, temporary, and immediate.
ALTER TABLESPACE name OFFLINE;
ALTER TABLESPACE name OFFLINE NORMAL;
ALTER TABLESPACE name OFFLINE IMMEDIATE;
ALTER TABLESPACE name OFFLINE TEMPORARY;
Depending on the priority used to take the tablespace offline, media recovery on that tablespace may be required. A tablespace taken offline with normal priority will not require media recovery, but a tablespace taken offline with immediate priority will.
Note that if you leave off a priority specification, normal priority is assumed.
A tablespace taken offline with temporary priority will not require media recovery if none of the datafiles were offline prior to taking the tablespace offline.
However, if any of the datafiles were offline before the tablespace was taken offline temporarily due to read or write errors, then media recovery will be required to bring the tablespace back online.
ALTER TABLESPACE name OFFLINE TEMPORARY;
*Making a Tablespace Read-Only
You may also have situations that make use of Oracle's ability to specify tablespaces to only be readable.
The following example demonstrates the code required to make a tablespace readable but not writable, and then to change it back to being writable again:
ALTER TABLESPACE name READ ONLY;
ALTER TABLESPACE name READ WRITE;
*Dropping a Tablespace
Finally, if you want to eliminate a tablespace, use the drop tablespace command.
DROP TABLESPACE name INCLUDING CONTENTS CASCADE CONSTRAINTS;
This command has a few clauses, such as including contents for removing all database objects contained in the tablespace as well as the tablespace itself, and the cascade constraints keywords to remove any constraints that may depend on database objects stored in the tablespace being dropped.
Tip: You can now use the same command and take the datafiles out of the operating system with statement shown here.
drop tablespace name including contents cascade constraints and datafiles;
*Read-Only Tablespaces
A read-only tablespace is one where no user can make a data change to any of the objects stored in that tablespace. There are a few exceptions to this rule.
For example, you can drop items, such as tables and indexes, from a read-only tablespace, but you cannot create or alter items such as tables or indexes in read-only tablespaces.
This is possible because the drop command only updates the data dictionary, not the physical files that make up the tablespace.
In order to make a tablespace read-only, the following three conditions must be met:
- The tablespace must be online.
- It must not contain any active undo segments.
- It must not be in online backup mode.
In versions of Oracle prior to Oracle8i, to make a tablespace read-only, you had to ensure that no user was currently making changes to any of the objects in that tablespace before issuing the alter tablespace read only statement. If any active transactions were processing data in the tablespace you wanted to make read-only, an error would occur when you attempted to issue the statement.
alter tablespace read only;
In Oracle8i and later, you can issue the alter tablespace read only statement, and Oracle will wait until all active transactions complete, and then make the tablespace read-only. While Oracle waits for the transactions to complete, the tablespace is placed in a transitional read-only mode during which no further write transactions are allowed against the tablespace while the existing transactions are allowed to commit or rollback.
Tip: In Oracle8i and later releases, you do not have to wait for transactions to finish in order to make a tablespace read only.
Also, a tablespace must be read-only before you transport it to another database.
alter tablespace read only;
Question 25
Question 26
Question 27
Question 28
In this lesson, you learned to change the status of existing tablespaces.
You learned to take tablespaces online and offline using the alter tablespace statement.
You learned to take individual datafiles online and offline using the alter database datafile statement.
You learned to set the priority level when taking a tablespace offline.
Finally, you learned to make a tablespace read-only using the alter tablespace read only statement.
Lesson 9. Changing Tablespace Storage Settings
Changing the storage settings for tablespaces in your database can help you distribute disk usage more evenly.After completing this lesson, you should be able to:
- Change the default storage settings for tablespaces
- Relocate the datafiles underlying a single tablespace
- Relocate the datafiles underlying multiple tablespaces
Now, consider again the default storage parameters you set for a tablespace when you create it.
They have no bearing on the tablespace itself, but rather are used as default settings when users issue create table, create index, or create undo segment statements that have no storage parameter settings explicitly defined.
default storage
*Changing Tablespace Storage Settings
You can change the default settings for your tablespace by issuing the alter tablespace command, as shown here.
SQL> ALTER TABLESPACE name DEFAULT STORAGE ( INITIAL 2M NEXT 1M );
In this case, we're setting the size of the tablespace's initial segment to 2M, and the next allocated extent to 1M.
You needn't specify all the default storage parameters available — only the ones for which you want to change values.
However, keep in mind that changing the default storage settings has no effect on existing database objects in the tablespace. It only affects storage settings on new database objects and only when those new objects do not specify their own storage settings explicitly.
SQL> ALTER TABLESPACE name DEFAULT STORAGE ( INITIAL 2M NEXT 1M );
*Relocating Tablespace Datafiles
Depending on the type of tablespace, the database administrator can move datafiles using one of two methods: the alter tablespace command or the alter database command.
Relocating datafiles underlying a tablespace in Oracle offers tremendous value, particularly when you are trying to eliminate hot spots in the database or distribute I/O load or disk use more evenly across the host machine.
alter tablespace
alter database
When relocating or renaming the datafiles within a single tablespace, use the alter tablespace command and when relocating the datafiles for many tablespaces, use the alter database command.
In either case, executing these commands only modifies the pointers to the datafiles as recorded in the control file. They do not physically rename or move the files in the operating system.
The actual renaming or relocation of the datafiles has to be done at the operating system level. The rest of this lesson gives detailed steps how to do the relocation.
SQL> alter database rename file
2> 'D:\ORACLE\DATABASE\TMP1JSC.ORA'
3> to
4> 'D:\ORACLE\DATABASE\TEMP1JSC.ORA';
Statement Processed.
Even though you may rename the datafiles underlying the tablespaces, you cannot rename the tablespace names using Oracle commands.
In order to rename a tablespace, you have to delete the tablespace and re-create it with the new name.
*Relocating Datafiles with alter database
To relocate datafiles with the alter database command, execute the following steps:
| Step | Action |
|---|---|
| 1 | Shut down the database. |
| 2 | Use an operating system command to move the files. |
| 3 | Mount the database. |
| 4 | Execute the alter database rename file command. |
| 5 | Open the database. |
| 6 | Back up the database and the control file. |
The code block shown here illustrates these steps in Windows.
D:\ORACLE\DATABASE\> sqlplus
Oracle SQL*Plus Release 9.0.1.0.0 - Production
(c)1999, Oracle Corporation. All Rights Reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 9.0.1.0.0 - Production
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host move tmp1jsc.ora temp1jsc.ora
1 file(s) moved.
SQL> startup mount pfile=initjsc.ora
Total System Global Area 14442496 bytes
Fixed Size 49152 bytes
Variable Size 13193216 bytes
Database Buffers 1126400 bytes
Redo Buffers 73728 bytes
Database mounted.
SQL> alter database rename file
2> 'D:\ORACLE\DATABASE\TMP1JSC.ORA'
3> to
4> 'D:\ORACLE\DATABASE\TEMP1JSC.ORA';
Statement Processed.
SQL> alter database open;
Statement processed.
*Relocating Datafiles with alter tablespace
Use the following process to rename a datafile with the alter tablespace command:
| Step | Action |
|---|---|
| 1 | Take the tablespace offline. |
| 2 | Use an operating system command to move or copy the files. |
| 3 | Execute the alter tablespace rename datafile command. |
| 4 | Bring the tablespace online. |
| 5 | Back up the database and the control file. |
*Limitations in Oracle9i
In general, you will experience the following limitations in Oracle9i with respect to tablespaces:
- The maximum number of tablespaces per database is 64,000.
- The operating system-specific limit on the maximum number of datafiles allowed in a tablespace is typically 1,023 files. However, this number varies by operating system.
Question 29
Question 30
Question 31
In this lesson, you learned to change the storage settings for tablespaces in your database.
You learned to change the default storage settings for your tablespaces using the alter tablespace command.
You learned to relocate the datafiles underlying a single tablespace using alter tablespace.
Finally, you learned to relocate the datafiles underlying multiple tablespaces using the alter database command.
Lesson 10. Using OMF with Tablespaces
Oracle-Managed Files are often used to simplify datafile management for development or test databases you may be working on.After completing this lesson, you should be able to:
- Create OMF datafiles for tablespaces
- Create OMF tempfiles for temporary tablespaces
The Oracle-Managed Files (OMF) feature is covered extensively in other lessons.
We won't spend much time in this course on the functionality of this feature, except to introduce how they are used while creating or dropping tablespaces.
OMF is not intended for production systems, but more for development or test databases to simplify the datafile management while managing the tablespaces.
To create Oracle-managed datafiles for tablespaces, tempfiles, or UNDOTBS tablespaces, you need to define the parameter DB_CREATE_FILE_DEST in the init.ora file.
This parameter should point to the default location where all Oracle-managed datafiles or tempfiles need to be created. The following is an example of defining this parameter in Windows:
DB_CREATE_FILE_DEST = 'C:\Oracle\oradata\DB1'
*Creating OMF Datafiles for Tablespaces
OMF datafiles can be created for regular tablespaces and UNDOTBS tablespaces. (UNDOTBS tablespaces are discussed in another course.)
When creating the tablespace, the datafile clause is optional. If you include the datafile clause, then the datafile name is optional. If the datafile clause is omitted entirely or does not include a filename, then your datafile is created in the location specified by the DB_CREATE_FILE_DEST parameter.
DB_CREATE_FILE_DEST = 'C:\Oracle\oradata\DB1'
The following code block is an example of how OMF datafiles can be created or altered:
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
---------------------- ----------- --------------------
db_create_file_dest string C:\oracle\oradata\DB1
SQL> create tablespace data datafile size 25M;
Tablespace created.
SQL> select file_name, tablespace_name, bytes/1024/1024 as megs
2 from dba_data_files
3 where tablespace_name = 'DATA';
FILE_NAME TABLESPACE MEGS
-------------------------------------------- ---------- ----
C:\ORACLE\ORADATA\DB1\ORA_DATA_ZVSRKH00.DBF DATA 25
This code shows the creation of a tablespace with a specified size, but without filename or location specifications. When you query the location of the datafile, you will find it created at the location specified by the parameter DB_CREATE_FILE_DEST.
Note the name of the file and the size. When you drop a tablespace containing Oracle-managed datafiles, the datafiles are automatically removed from the underlying operating system.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
---------------------- ----------- --------------------
db_create_file_dest string C:\oracle\oradata\DB1
SQL> create tablespace data datafile size 25M;
Tablespace created.
SQL> select file_name, tablespace_name, bytes/1024/1024 as megs
2 from dba_data_files
3 where tablespace_name = 'DATA';
FILE_NAME TABLESPACE MEGS
-------------------------------------------- ---------- ----
C:\ORACLE\ORADATA\DB1\ORA_DATA_ZVSRKH00.DBF DATA 25
*Creating OMF Tempfiles for Temporary Tablespaces
When creating the temporary tablespace with OMF, the tempfile clause is optional. If you include the tempfile clause, then the filename is optional.
If the tempfile clause or the filename is not specified, then the datafile will be created at the location specified by the DB_CREATE_FILE_DEST parameter.
The following code block is an example of how OMF tempfiles can be created or altered.
SQL> create temporary tablespace temptbs;
Tablespace created.
SQL> select tablespace_name, contents from dba_tablespaces
2 where tablespace_name = 'TEMPTBS';
TABLESPACE CONTENTS
---------- ---------
TEMPTBS TEMPORARY
SQL> select file_name, tablespace_name
2 from dba_temp_files;
FILE_NAME TABLESPACE
---------------------------------------------- ----------
C:\ORACLE\ORADATA\DB1\TEMP01.DBF TEMP
C:\ORACLE\ORADATA\DB1\ORA_TEMPTBS_ZVSRZ200.TMP TEMPTBS
Question 32
Question 33
Question 34
Topic 10.1 Exercises
* Exercise 1
Try creating an Oracle9i database that uses OMF datafiles.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. You're designing a new database structure, and wish to create a test database to examine and evaluate certain features. Begin by creating a new directory to contain your OMF database. |
| 2 | Issue the statement to define the parameter DB_CREATE_FILE_DEST so that it points to your new directory. |
| 3 | Create one tablespace named data, and a temporary tablespace named temptbs. |
In this lesson, you learned to create databases that use the OMF feature to simplify datafile management. Often you will want to do this with test or development databases you may be working on.
You learned to set the DB_CREATE_FILE_DEST parameter to determine where files will be saved, and you learned to create OMF datafiles for tablespaces and OMF tempfiles for temporary tablespaces.
Lesson 11. 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 11.1 Review Notes
*Review Notes| 1. | Oracle supports three types of logical disk storage resources — tablespaces, segments, and extents. Underlying these logical structures are the physical structures on the host machine that make up the physical Oracle database: datafiles and Oracle blocks. Tablespaces can have many datafiles, but each datafile must be assigned to only one tablespace. |
| 2. | Oracle handles storage management of a tablespace either by storing records in the data dictionary or locally in the headers of each datafile in the tablespace. This difference produces two types of tablespaces: dictionary-managed and locally managed tablespaces, respectively. Starting with Oracle9i, Oracle creates non-SYSTEM tablespaces to be locally managed by default. |
| 3. | With the creation and dropping of database objects, tablespaces end up with noncontiguous chunks of free space that need coalescing periodically. SMON takes care of this task for you. |
| 4. | In Oracle9i, you can create a default temporary tablespace to prevent making the SYSTEM tablespace the temporary tablespace. The default temporary tablespace is created when the database is created. |
| 5. | Non-SYSTEM tablespaces in Oracle are created through the create tablespace command. |
| 6. | You can specify to autoextend the datafile as the data grows and you can specify the default storage parameters for objects that are created in it. The default storage parameters can be the sizes of initial and next extents, and the minimum and maximum number of extents for the object being created. |
| 7. | If an object is being created in this tablespace with no storage option, then Oracle takes the default storage parameters of the tablespace while creating the object. |
| 8. | Permanent tablespaces can store both permanent and temporary objects in them. Temporary tablespaces, in contrast, can house only temporary segments. |
| 9. | You can grow a tablespace by enabling auto extension of the datafile. By doing so, that datafile will automatically grow when space is needed. You can specify how large it can grow and how big each growth can be. |
| 10. | Secondly, you can resize the tablespace to either grow or shrink. |
| 11. | Finally, you can grow a tablespace by adding more datafiles. |
| 12. | For the performance of sorting, the extent size of the temporary tablespace should be properly sized. It should be equal to the multiple of SORT_AREA_SIZE x DB_BLOCK_SIZE because Oracle always sorts in sizes of SORT_AREA_SIZE. |
| 13. | Also, make sure that all extents in temporary tablespace are equal in size, the percentage increase is always zero, and the extents are not coalesced. It is also advisable to have multiple temporary tablespaces for different kinds of sorting needs. |
| 14. | Oracle provides many data dictionary views to know about the temporary segments. DBA_SEGMENTS is one static view and V$SORT_SEGMENT and V$SORT_USAGE give dynamic information about the sort segments in existence and how they are being utilized. |
| 15. | To know about the temporary tablespaces, Oracle provides DBA_TEMP_FILES and V$TEMPFILE views. |
| 16. | Tablespaces can be in many states. They can be online or offline and read-only or read-write only. Under normal situations where tablespaces are actively used, they are in an online and read-write state. However, under certain conditions, you may want to bring a tablespace in an offline mode (to rename the datafiles) or read-only (to preserve the data in the tablespace). |
| 17. | There are many ways a tablespace can brought offline — normal, immediate, and temporary. |
| 18. | In order to make a tablespace read-only, certain conditions have to be met. The tablespace should be online and there should be no active undo segments in it. For this reason, the SYSTEM tablespace can never be made read-only as it contains system undo segments in it. Also, the tablespace should not be in online backup mode. |
| 19. | When creating database objects such as tables and indexes, if no storage parameters are specified, Oracle takes the tablespace storage parameters as the defaults and uses them for creating these objects. |
| 20. | These default storage parameters of a tablespace can be modified after the tablespaces are created using the alter tablespace command. The modified default storage parameters of a tablespace take effect only on the new objects that will be created but not the existing ones. |
| 21. | You may need to rename or relocate the tablespace datafiles. Depending on how many datafiles you need to rename or relocate, you have the option to use two Oracle commands — alter tablespace and alter database. |
| 22. | When using the alter database command, you need to shut down the database, move the datafiles, mount the database, give the Oracle command, and then bring up the database. |
| 23. | When using the alter tablespace command, you can either shut down the database or bring the tablespace offline, move the datafiles, mount the database, give the Oracle command, and open the database. |
| 24. | After relocating or renaming the datafiles, it is highly advisable to back up the database and the control file. |
| 25. | Oracle-Managed Files (OMF) is a new feature of Oracle9i that eases the administration of datafiles for tablespaces, control files, and redo log files. Defining the parameter DB_CREATE_FILE_DEST in the init.ora file activates OMF for tablespaces. This parameter should point to a location on the machine where the OMF datafiles will be created by default. |
| 26. | With OMF active, you do not need to specify the datafile clause or even the datafile name while creating the tablespaces. The same holds true while creating the temporary tablespaces where you don't have to specify the tempfile clause. By skipping these clauses, Oracle creates the datafiles in the default location specified by the previous init.ora parameter. If no filename is specified, then Oracle uses a default naming convention that keeps the datafiles unique from each other. |
Question 35
Question 36
Question 37
Question 38
Question 39
Question 40
Question 41
Question 42
Question 43
Topic 11.2 Exercises
* Exercise 1
Try creating a database and several tablespaces in Oracle9i.
| Step | Action |
|---|---|
| 1 | Please Note: For the exercises in this course, you should set up an isolated Oracle9i system for practice. Do not use your production Oracle9i system for these practice exercises. Create a new database. Create the SYSTEM, temporary, and UNDOTBS tablespaces during the process of database creation. |
| 2 | Create a permanent, locally managed tablespace to hold your database's table data. Name the tablespace DATA. Set the minimum extent value to 150K. |
| 3 | Issue the command to allow the datafiles used by your tablespaces to extend automatically when space is needed. Set the block size to 100M and the maximum size to 2000M. |
| 4 | Take the DATA tablespace offline. Then bring it back online. |
| 5 | Make the DATA tablespace read-only. |
Topic 11.3 Exam Preparation
*Exam PreparationThis page contains a bulleted list of fast facts to review, or crib notes for the days leading up to the exam.
- Understand how tablespaces and datafiles relate to one another. A tablespace can have many datafiles, but each datafile can associate with only one tablespace.
- At database creation, there is one tablespace — SYSTEM. In Oracle9i, it is possible to create a default temporary tablespace for the database and an UNDOTBS tablespaces as well.
- All other tablespaces are created with the create tablespace command.
- There are two methods Oracle uses for free space management in a tablespace. Dictionary-managed tablespaces have all their free space information stored and managed via the Oracle data dictionary. Locally managed tablespaces have all their free space information stored and managed in the datafile headers of all tablespace datafiles.
- The DBA should not place all database objects into one tablespace, because often their storage needs conflict with each other. Instead, the DBA should create multiple tablespaces for the different segments available on the database and place those objects into those tablespaces.
- The SMON process handles periodic coalescence of noncontiguous free space in dictionary-managed tablespaces into larger, contiguous blocks of free space.
- Permanent segments store data in permanent database objects, such as tables.
- Temporary segments store data in temporary segments, generated whenever Oracle performs a disk sort.
- Temporary tablespaces differ from permanent tablespaces in that only temporary segments can be stored in them. In contrast, permanent tablespaces can house both permanent and temporary segments. However, for practical purposes, temporary segments should be kept out of permanent tablespaces.
- By default, tablespaces are brought online by Oracle automatically after they are created.
- In Oracle9i, all tablespaces use local extent management by default unless you specify otherwise at tablespace creation time.
- Temporary tablespaces should use local extent management because of performance reasons.
- A new memory area called the sort extent pool manages how user processes allocate extents for disk sorts in temporary tablespaces.
- SMON handles deallocation of temporary segments in permanent tablespaces when the transaction no longer needs them.
- You cannot create permanent database objects, such as tables, in temporary tablespaces. You also cannot convert permanent tablespaces into temporary ones unless there are no permanent objects in the permanent tablespace.
- You can get information about temporary segments and sort segments from the DBA_SEGMENTS, V$SORT_SEGMENT, V$SESSION, and V$SORT_USAGE dictionary views.
- A sort segment exists in the temporary tablespace for as long as the instance is available. All users share the sort segment.
- The size of extents in the temporary tablespace should be set to a multiple of SORT_AREA_SIZE, plus one additional block for the segment header, in order to maximize disk sort performance.
- 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, and DBA_FREE_SPACE_COALESCED.
- You can change the availability status of tablespaces using the alter tablespace command.
- You can use the Oracle-Managed Files (OMF) feature in conjunction with tablespace management. When OMF is used, you do not need to specify the name or location of datafiles related to the tablespaces you want to create.
No comments:
Post a Comment