Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
|
View PDF |
This chapter describes the various aspects of tablespace management, and contains the following topics:
Diagnosing and Repairing Locally Managed Tablespace Problems
Migrating the SYSTEM Tablespace to a Locally Managed Tablespace
Viewing Tablespace Information
See Also: Chapter 11, " Using Oracle-Managed Files" for information about creating datafiles and tempfiles that are both created and managed by the Oracle Database server |
Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:
Assigning Tablespace Quotas to Users
See Also: Oracle Database Concepts for a complete discussion of database structure, space management, tablespaces, and datafiles |
Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:
Separate user data from data dictionary data to reduce contention among dictionary objects and schema objects for the same datafiles.
Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
Take individual tablespaces offline while others remain online, providing better overall availability.
Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
Back up individual tablespaces.
Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.
Review your data in light of these factors and decide how many tablespaces you need for your database design.
Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.
See Also: Oracle Database Security Guide for information about creating users and assigning tablespace quotas. |
Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM
tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM
tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM
tablespace or take it offline.
The SYSAUX
tablespace, which acts as an auxiliary tablespace to the SYSTEM
tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM
tablespace, management of the SYSAUX
tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX
tablespace is discussed separately in "Managing the SYSAUX Tablespace".
The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as specified.
To create a new tablespace, use the SQL statement CREATE TABLESPACE
or CREATE TEMPORARY TABLESPACE
. You must have the CREATE TABLESPACE
system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE
or ALTER DATABASE
statements to alter the tablespace. You must have the ALTER TABLESPACE
or ALTER DATABASE
system privilege, correspondingly.
You can also use the CREATE UNDO TABLESPACE
statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK
statement. Creating and managing undo tablespaces is the subject of Chapter 10, " Managing the Undo Tablespace".
The creation and maintenance of permanent and temporary tablespaces are discussed in the following sections:
Multiple Temporary Tablespaces: Using Tablespace Groups
See Also:
|
Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
Space allocation is simplified, because when the AUTOALLOCATE
clause is specified, the database automatically selects the appropriate extent size.
User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
Coalescing free extents is unnecessary for locally managed tablespaces.
All tablespaces, including the SYSTEM
tablespace, can be locally managed.
The DBMS_SPACE_ADMIN
package provides maintenance procedures for locally managed tablespaces.
See Also:
|
You create a locally managed tablespace by specifying LOCAL
in the EXTENT MANAGEMENT
clause of the CREATE TABLESPACE
statement. This is the default for new permanent tablespaces, but you must specify if it you want to specify the management of the locally managed tablespace. You can have the database manage extents for you automatically with the AUTOALLOCATE
clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM
).
If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE
is the best choice. AUTOALLOCATE
is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.
If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM
is a good choice. This setting ensures that you will never have unusable space in your tablespace.
When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows:
If the CREATE TABLESPACE
statement omits the DEFAULT
storage clause, then the database creates a locally managed autoallocated tablespace.
If the CREATE TABLESPACE
statement includes a DEFAULT
storage clause, then the database considers the following:
If you specified the MINIMUM EXTENT
clause, the database evaluates whether the values of MINIMUM EXTENT
, INITIAL
, and NEXT
are equal and the value of PCTINCREASE
is 0. If so, the database creates a locally managed uniform tablespace with extent size = INITIAL
. If the MINIMUM EXTENT
, INITIAL
, and NEXT
parameters are not equal, or if PCTINCREASE
is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
If you did not specify MINIMUM EXTENT
clause, the database evaluates only whether the storage values of INITIAL
and NEXT
are equal and PCTINCREASE
is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.
The following statement creates a locally managed tablespace named lmtbsb
and specifies AUTOALLOCATE
:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
AUTOALLOCATE
causes the tablespace to be system managed with a minimum extent size of 64K. In contrast, dictionary-managed tablespaces have a minimum extent size of two database blocks. Therefore, in systems with block size smaller than 32K, autoallocated locally managed tablespace will be larger initially.
The alternative to AUTOALLOCATE
is UNIFORM
. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE
clause of UNIFORM
. If you omit SIZE
, then the default size is 1M.
The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
You cannot specify the DEFAULT
storage clause, MINIMUM EXTENT
, or TEMPORARY
when you explicitly specify EXTENT MANAGEMENT LOCAL
. If you want to create a temporary locally managed tablespace, use the CREATE TEMPORARY TABLESPACE
statement.
Note: When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if specify theUNIFORM clause in the extent management clause but you omit the SIZE parameter, then the default extent size is 1MB. In that case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB. |
When you create a locally managed tablespace using the CREATE TABLESPACE
statement, the SEGMENT SPACE MANAGEMENT
clause lets you specify how free and used space within a segment is to be managed. You can choose either manual or automatic segment-space management.
MANUAL
: Manual segment-space management uses free lists to manage free space within segments. Free lists are lists of data blocks that have space available for inserting rows. With this form of segment-space management, you must specify and tune the PCTUSED
, FREELISTS
, and FREELIST GROUPS
storage parameters for schema objects created in the tablespace. MANUAL
is the default.
AUTO
: Automatic segment-space management uses bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. These bitmaps allow the database to manage free space automatically.
You can specify automatic segment-space management only for permanent, locally managed tablespaces. Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED
, FREELISTS
, and FREELIST GROUPS
storage parameters for schema objects created in the tablespace. If you specify these attributes, the database ignores them.
Automatic segment-space management delivers better space utilization than manual segment-space management. It is also self-tuning, in that it scales with increasing number of users or instances. In a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups. In addition, for many standard workloads, application performance with automatic segment-space management is better than the performance of a well-tuned application using manual segment-space management.
The following statement creates tablespace lmtbsb
with automatic segment-space management:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
The segment-space management you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a tablespace.
Notes: If you specifyAUTO segment management, then:
|
Locally managed tablespaces using automatic segment-space management can be created as single-file, or bigfile, tablespaces, as described in "Bigfile Tablespaces".
You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment-space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the ALTER TABLESPACE
statement on locally managed tablespaces for some operations, including the following:
Adding a datafile. For example:
ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
Altering tablespace availability (ONLINE
/OFFLINE
). See "Altering Tablespace Availability ".
Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".
Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace. See Chapter 9, " Managing Datafiles and Tempfiles".
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:
A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
Bigfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES
initialization parameter and MAXDATAFILES
parameter of the CREATE DATABASE
and CREATE CONTROLFILE
statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER
TABLESPACE
statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM
tablespace can be bigfile tablespaces even if their segments are manually managed.
Notes:
|
To create a bigfile tablespace, specify the BIGFILE
keyword of the CREATE
TABLESPACE
statement (CREATE
BIGFILE
TABLESPACE
...). Oracle Database automatically creates a locally managed tablespace with automatic segment-spec management. You can, but need not, specify EXTENT
MANAGEMENT
LOCAL
and SEGMENT
SPACE
MANAGEMENT
AUTO
in this statement. However, the database returns an error if you specify EXTENT
MANAGEMENT
DICTIONARY
or SEGMENT
SPACE
MANAGEMENT
MANUAL
. The remaining syntax of the statement is the same as for the CREATE TABLESPACE
statement, but you can only specify one datafile. For example:
CREATE BIGFILE TABLESPACE bigtbs DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G ...
You can specify SIZE
in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).
If the default tablespace type was set to BIGFILE
at database creation, you need not specify the keyword BIGFILE
in the CREATE TABLESPACE
statement. A bigfile tablespace is created by default.
If the default tablespace type was set to BIGFILE
at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE
SMALLFILE
TABLESPACE
statement to override the default tablespace type for the tablespace that you are creating.
Two clauses of the ALTER TABLESPACE
statement support datafile transparency when you are using bigfile tablespaces:
RESIZE
: The RESIZE
clause lets you resize the single datafile in a bigfile tablespace to an absolute size, without referring to the datafile. For example:
ALTER TABLESPACE bigtbs RESIZE 80G;
AUTOEXTEND
(used outside of the ADD
DATAFILE
clause):
With a bigfile tablespace, you can use the AUTOEXTEND
clause outside of the ADD
DATAFILE
clause. For example:
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
An error is raised if you specify an ADD
DATAFILE
clause for a bigfile tablespace.
The following views contain a BIGFILE
column that identifies a tablespace as a bigfile tablespace:
DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.
The default for extent management when creating a tablespace is locally managed. However, you can explicitly specify a dictionary-managed tablespace. For dictionary-managed tablespaces, the database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse.
The following statement creates the dictionary-managed tablespace tbsa
:
CREATE TABLESPACE tbsa DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
The tablespace has the following characteristics:
The data of the new tablespace is contained in a single datafile, 50M in size.
The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY
.
The default storage parameters for any segments created in this tablespace are specified.
The parameters specified in the preceding example determine segment storage allocation in the tablespace. These parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. They are referred to as storage parameters, and are described in the following table:
Storage Parameter | Description |
---|---|
INITIAL |
Defines the size in bytes of the first extent in the segment |
NEXT |
Defines the size in bytes of the second and subsequent extents |
PCTINCREASE |
Specifies the percent by which each extent, after the second (NEXT ) extent, grows |
MINEXTENTS |
Specifies the number of extents allocated when a segment is first created in the tablespace |
MAXEXTENTS |
Specifies the maximum number of extents that a segment can have. Can also be specified as UNLIMITED . |
The MINIMUM EXTENT
parameter on the CREATE TABLESPACE
statement also influences segment allocation. If specified, it ensures that all free and allocated extents in the tablespace are at least as large as, and a multiple of, a specified number of bytes. This clause provides one way to control free space fragmentation in the tablespace.
When you create a new dictionary-managed tablespace, you can specify default storage parameters for objects that will be created in the tablespace. Storage parameters specified when an object is created override the default storage parameters of the tablespace containing the object. If you do not specify storage parameters when creating an object, the object segment automatically uses the default storage parameters for the tablespace.
Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you estimate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object. You can also alter your default storage parameters at a later time.
You cannot specify default storage parameters for tablespaces that are specifically created as locally managed.
Note: If you do not specify the default storage parameters for a new dictionary-managed tablespace, Oracle Database chooses default storage parameters appropriate for your operating system. |
One common change to a database is adding a datafile. The following statement creates a new datafile for the tbsa
tablespace:
ALTER TABLESPACE tbsa ADD DATAFILE '/u02/oracle/data/tbsa02.dbf' SIZE 1M;
You can also change the default storage parameters of a tablespace using the ALTER TABLESPACE
statement, as illustrated in the following example:
ALTER TABLESPACE users DEFAULT STORAGE ( NEXT 100K MAXEXTENTS 20 PCTINCREASE 0);
New values for the default storage parameters of a tablespace affect only objects that are subsequently created, or extents subsequently allocated for existing segments within the tablespace.
Other reasons for issuing an ALTER TABLESPACE
statement include, but are not limited to:
Coalescing free space in a tablespace. See "Coalescing Free Space in Dictionary-Managed Tablespaces".
Altering tablespace availability (ONLINE
/OFFLINE
). See "Altering Tablespace Availability ".
Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".
Adding or renaming a datafile, or enabling/disabling the autoextension of the size of a datafile in the tablespace. See Chapter 9, " Managing Datafiles and Tempfiles".
Over time, the free space in a dictionary-managed tablespace can become fragmented, making it difficult to allocate new extents. This section discusses how to defragment free space and includes the following topics:
A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the database uses the free extent closest in size to the required extent. In some cases, when segments are dropped, their extents are deallocated and marked as free, but adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.
Oracle Database addresses fragmentation in several ways:
When attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent. Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.
The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE
value for a tablespace is not zero. If you set PCTINCREASE=0
, no coalescing of free extents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCREASE=0
, and periodically coalesce free space manually.
When a segment is dropped or truncated, a limited form of coalescing is performed if the PCTINCREASE
value for the segment is not zero. This is done even if PCTINCREASE=0
for the tablespace containing the segment.
You can use the ALTER TABLESPACE ... COALESCE
statement to manually coalesce any adjacent free extents.
The process of coalescing free space is illustrated in the following figure.
Note: Coalescing free space is not necessary for locally managed tablespaces, because bitmaps automatically track adjacent free space. |
If you find that fragmentation of space in a tablespace is high (contiguous space on your disk appears as noncontiguous), you can coalesce any free space using the ALTER TABLESPACE ... COALESCE
statement. You must have the ALTER TABLESPACE
system privilege to coalesce tablespaces.
This statement is useful if PCTINCREASE=0
, or you can use it to supplement SMON and extent allocation coalescing. If all extents within the tablespace are of the same size, coalescing is not necessary. This is the case when the default PCTINCREASE
value for the tablespace is set to zero, all segments use the default storage parameters of the tablespace, and INITIAL=NEXT=MINIMUM EXTENT
.
The following statement coalesces free space in the tablespace tabsp_4
:
ALTER TABLESPACE tabsp_4 COALESCE;
The COALESCE
clause of the ALTER TABLESPACE
statement is exclusive. You cannot specify any other clause in the same ALTER TABLESPACE
statement.
Note: TheALTER TABLESPACE ... COALESCE statement does not coalesce free extents that are separated by data extents. If you observe many free extents located between data extents, you must reorganize the tablespace (for example, by exporting and importing its data) to create useful free space extents. |
The following views provide information on the free space in a tablespace:
DBA_FREE_SPACE
DBA_FREE_SPACE_COALESCED
The following statement displays the free space in tablespace tabsp_4
:
SELECT BLOCK_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TABSP_4' ORDER BY BLOCK_ID; BLOCK_ID BYTES BLOCKS ---------- ---------- ---------- 2 16384 2 4 16384 2 6 81920 10 16 16384 2 27 16384 2 29 16384 2 31 16384 2 33 16384 2 35 16384 2 37 16384 2 39 8192 1 40 8192 1 41 196608 24 13 rows selected.
This view shows that there is adjacent free space in tabsp_4
(for example, blocks starting with BLOCK_IDs
2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE
statement shown previously, the results of this query would read:
BLOCK_ID BYTES BLOCKS ---------- ---------- ---------- 2 131072 16 27 311296 38 2 rows selected.
The DBA_FREE_SPACE_COALESCED
view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.
A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrence of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace can be assigned to users with the CREATE USER
or ALTER USER
statement and can be shared by multiple users.
Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.
You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT
view. The V$TEMPSEG_USAGE
view identifies the current sort users in those segments.
You cannot explicitly create objects in a temporary tablespace.
See Also:
|
Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.
You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE
and DBA_TEMP_FILES
views are analogous to the V$DATAFILE
and DBA_DATA_FILES
views.
To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TABLESPACE
statement, which requires that you have the CREATE TABLESPACE
system privilege.
The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The Oracle Database default for SIZE
is 1M. But if you want to specify another value for SIZE
, you can do so as shown in the preceding statement.
The AUTOALLOCATE
clause is not allowed for temporary tablespaces.
Note: On some operating systems, the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether the database allocates tempfile space in this way on your system. |
Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE
statement to create a single-tempfile tablespace. See the sections "Creating a Bigfile Tablespace" and "Altering a Bigfile Tablespace" for information about bigfile tablespaces, but consider that you are creating temporary tablespaces that use tempfiles instead of datafiles.
Except for adding a tempfile, as illustrated in the following example, you cannot use the ALTER TABLESPACE
statement for a locally managed temporary tablespace.
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
Note: You cannot use theALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace. |
However, the ALTER DATABASE
statement can be used to alter tempfiles.
The following statements take offline and bring online temporary files:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
The following statement resizes a temporary file:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
The following statement drops a temporary file and deletes the operating system file:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
The tablespace to which this tempfile belonged remains. A message is written to the alert file for the datafile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert file.
It is also possible, but not shown, to use the ALTER DATABASE
statement to enable or disable the automatic extension of an existing tempfile, and to rename (RENAME FILE
) a tempfile.
In earlier releases, you could create a dictionary-managed temporary tablespace by specifying the TEMPORARY
keyword after specifying the tablespace name in a CREATE TABLESPACE
statement. This syntax has been deprecated. It is still supported in case you are using dictionary-managed tablespaces, which are not supported by the CREATE TEMPORARY TABLEPSPACE
syntax. If you do use this deprecated syntax, the extent cannot be locally managed, nor can you specify a nonstandard block size for the tablespace.
Oracle strongly recommends that you create locally managed temporary tablespaces containing tempfiles, as described in the preceding sections. The creation of new dictionary-managed tablespaces is scheduled for desupport.
See Also: Oracle Database SQL Reference for syntax and semantics of theCREATE TEMPORARY TABLESPACE and CREATE TABLESPACE ... TEMPORARY statements |
You can issue the ALTER TABLESPACE
statement against a dictionary-managed temporary tablespace using many of the same keywords and clauses as for a permanent dictionary-managed tablespace. Restrictions are noted in the Oracle Database SQL Reference.
Note: When you take dictionary-managed temporary tablespaces offline with theALTER TABLESPACE ... OFFLINE statement, returning them online does not affect their temporary status. |
You can change an existing permanent dictionary-managed tablespace to a temporary tablespace, using the ALTER TABLESPACE
statement. For example:
ALTER TABLESPACE tbsa TEMPORARY;
A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:
It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.
It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.
You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.
Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.
The view DBA_TABLESPACE_GROUPS
lists tablespace groups and their member tablespaces.
See Also: Oracle Database Security Guide for more information about assigning a temporary tablespace or tablespace group to a user |
You create a tablespace group implicitly when you include the TABLESPACE GROUP
clause in the CREATE TEMPORARY TABLESPACE
or ALTER TABLESPACE
statement and the specified tablespace group does not currently exist.
For example, if neither group1
nor group2
exists, then the following statements create those groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M TABLESPACE GROUP group1; ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP
clause of the CREATE TEMPORARY TABLESPACE
or ALTER TABLESPACE
statement.
The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3
to group1
, so that group1
contains tablespaces lmtemp2
and lmtemp3
.
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M TABLESPACE GROUP group1;
The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2
already belongs to group1
, it is in effect moved from group1
to group2
:
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
Now group2
contains both lmtemp
and lmtemp2
, while group1
consists of only tmtemp3
.
You can remove a tablespace from a group as shown in the following statement:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
Tablespace lmtemp3
no longer belongs to any group. Further, since there are no longer any members of group1
, this results in the implicit deletion of group1
.
Use the ALTER DATABASE ...DEFAULT
TEMPORARY
TABLESPACE
statement to assign a tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp
and lmtemp2
.
If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first be remove from the tablespace from the tablespace group. Likewise, you cannot drop a single temporary as long as it is the default temporary tablespace.
You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE
initialization parameter. This feature lets you transport tablespaces with unlike block sizes between databases.
Use the BLOCKSIZE
clause of the CREATE TABLESPACE
statement to create a tablespace with a block size different from the database standard block size. In order for the BLOCKSIZE
clause to succeed, you must have already set the DB_CACHE_SIZE
and at least one DB_nK_CACHE_SIZE
initialization parameter. Further, and the integer you specify in the BLOCKSIZE
clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting. Although redundant, specifying a BLOCKSIZE
equal to the standard block size, as specified by the DB_BLOCK_SIZE
initialization parameter, is allowed.
The following statement creates tablespace lmtbsb
, but specifies a block size that differs from the standard database block size (as specified by the DB_BLOCK_SIZE
initialization parameter):
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
See Also:
|
For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. An example of such an operation is a CREATE TABLE...AS SELECT
statement, which can be repeated in case of database or instance failure.
Specify the NOLOGGING
clause in the CREATE TABLESPACE
statement if you wish to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or if you specify LOGGING
instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.
The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING
or NOLOGGING
at the schema object level--for example, in a CREATE TABLE
statement.
If you have a standby database, NOLOGGING
mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING
mode. When you include the FORCE LOGGING
clause in the CREATE TABLESPACE
statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.
If you transport a tablespace that is in FORCE LOGGING
mode to another database, the new tablespace will not maintain the FORCE LOGGING
mode.
See Also:
|
You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.
To alter the availability of a tablespace, use the ALTER TABLESPACE
statement. You must have the ALTER TABLESPACE
or MANAGE TABLESPACE
system privilege.
See Also: "Altering Datafile Availability" for information about altering the availability of individual datafiles within a tablespace |
You may want to take a tablespace offline for any of the following reasons:
To make a portion of the database unavailable while allowing normal access to the remainder of the database
To perform an offline tablespace backup (even though a tablespace can be backed up while online and in use)
To make an application and its group of tables temporarily unavailable while updating or maintaining the application
When a tablespace is taken offline, the database takes all the associated files offline.
You cannot take the following tablespaces offline:
SYSTEM
The undo tablespace
Temporary tablespaces
Before taking a tablespace offline, consider altering the tablespace allocation of any users who have been assigned the tablespace as a default tablespace. Doing so is advisable because those users will not be able to access objects in the tablespace while it is offline.
You can specify any of the following parameters as part of the ALTER TABLESPACE ... OFFLINE
statement:
Clause | Description |
---|---|
NORMAL |
A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. When you specify OFFLINE NORMAL , the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default. |
TEMPORARY |
A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY , the database takes offline the datafiles that are not already offline, checkpointing them as it does so.
If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online. |
IMMEDIATE |
A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE , media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode. |
Caution: If you must take a tablespace offline, use theNORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement. |
Specify TEMPORARY
only when you cannot take the tablespace offline normally. In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE
only after trying both the normal and temporary settings.
The following example takes the users
tablespace offline normally:
ALTER TABLESPACE users OFFLINE NORMAL;
You can bring any tablespace in an Oracle Database online whenever the database is open. A tablespace is normally online so that the data contained within it is available to database users.
If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL
clause of the ALTER TABLESPACE OFFLINE
statement), you must first perform media recovery on the tablespace before bringing it online. Otherwise, the database returns an error and the tablespace remains offline.
See Also: Depending upon your archiving strategy, refer to one of the following books for information about performing media recovery: |
The following statement brings the users
tablespace online:
ALTER TABLESPACE users ONLINE;
Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
Note: Making a tablespace read-only cannot in itself be used to satisfy archiving or data publishing requirements, because the tablespace can only be brought online in the database in which it was created. However, you can meet such requirements by using the transportable tablespace feature, as described in "Transporting Tablespaces Between Databases". |
You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE ... ADD
or ALTER TABLE ... MODIFY
, but you will not be able to utilize the new description until the tablespace is made read/write.
Read-only tablespaces can be transported to other databases. And, since read-only tablespaces can never be updated, they can reside on CD-ROM or WORM (Write Once-Read Many) devices.
The following topics are discussed in this section:
All tablespaces are initially created as read/write. Use the READ ONLY
clause in the ALTER TABLESPACE
statement to change a tablespace to read-only. You must have the ALTER TABLESPACE
or MANAGE TABLESPACE
system privilege.
Before you can make a tablespace read-only, the following conditions must be met.
The tablespace must be online. This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
The tablespace cannot be the active undo tablespace or SYSTEM
tablespace.
The tablespace must not currently be involved in an online backup, because the end of a backup updates the header file of all datafiles in the tablespace.
For better performance while accessing data in a read-only tablespace, you can issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*)
, executed against each table ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database to check the status of the transactions that most recently modified the blocks.
The following statement makes the flights
tablespace read-only:
ALTER TABLESPACE flights READ ONLY;
You can issue the ALTER TABLESPACE ... READ ONLY
statement while the database is processing transactions. Once the statement is issued, no transactions are allowed to make further changes (using DML statements) to the tablespace being made read-only. Transactions that have already made changes to the tablespace are allowed to commit or terminate.
If any transactions are ongoing, then the ALTER TABLESPACE ... READ ONLY
statement may not return instantaneously. The database waits for all transactions started before you issued READ ONLY
to either commit or terminate.
Note: This transitional read-only state only occurs if the value of the initialization parameterCOMPATIBLE is 8.1.0 or greater. If this parameter is set to a value less than 8.1.0, the ALTER TABLESPACE ... READ ONLY statement fails if any active transactions exist. |
If you find it is taking a long time for the ALTER TABLESPACE statement to complete, you can identify the transactions that are preventing the read-only state from taking effect. You can then notify the owners of those transactions and decide whether to terminate the transactions, if necessary.
The following example identifies the transaction entry for the ALTER TABLESPACE ... READ ONLY
statement and note its session address (saddr
):
SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS AND SQL_TEXT LIKE 'alter tablespace%'; SQL_TEXT SADDR ---------------------------------------- -------- alter tablespace tbs1 read only 80034AF0
The start SCN of each active transaction is stored in the V$TRANSACTION
view. Displaying this view sorted by ascending start SCN lists the transactions in execution order. From the preceding example, you already know the session address of the transaction entry for the read-only statement, and you can now locate it in the V$TRANSACTION
view. All transactions with smaller start SCN, which indicates an earlier execution, can potentially hold up the quiesce and subsequent read-only state of the tablespace.
SELECT SES_ADDR, START_SCNB FROM V$TRANSACTION ORDER BY START_SCNB; SES_ADDR START_SCNB -------- ---------- 800352A0 3621 --> waiting on this txn 80035A50 3623 --> waiting on this txn 80034AF0 3628 --> this is the ALTER TABLESPACE statement 80037910 3629 --> don't care about this txn
After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary, because no changes can be made to it.
See Also: Depending upon your backup and recovery strategy, refer to one of the following books for information about backing up and recovering a database with read-only datafiles: |
Use the READ WRITE
keywords in the ALTER TABLESPACE
statement to change a tablespace to allow write operations. You must have the ALTER TABLESPACE
or MANAGE TABLESPACE
system privilege.
A prerequisite to making the tablespace read/write is that all of the datafiles in the tablespace, as well as the tablespace itself, must be online. Use the DATAFILE ... ONLINE
clause of the ALTER DATABASE
statement to bring a datafile online. The V$DATAFILE
view lists the current status of datafiles.
The following statement makes the flights
tablespace writable:
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writable updates the control file entry for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.
Follow these steps to create a read-only tablespace on a CD-ROM or WORM (Write Once-Read Many) device.
Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
Alter the tablespace to make it read-only.
Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
Take the tablespace offline.
Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Use ALTER TABLESPACE
with the RENAME DATAFILE
clause. Renaming the datafiles changes their names in the control file.
Bring the tablespace back online.
When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage, you should consider setting the READ_ONLY_OPEN_DELAYED
initialization parameter to TRUE
. This speeds certain operations, primarily opening the database, by causing datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them.
Setting READ_ONLY_OPEN_DELAYED=TRUE
has the following side-effects:
A missing or bad read-only file is not detected at open time. It is only discovered when there is an attempt to access it.
ALTER SYSTEM CHECK DATAFILES
does not check read-only files.
ALTER TABLESPACE ... ONLINE
and ALTER DATABASE
DATAFILE ... ONLINE
do not check read-only files. They are checked only upon the first access.
V$RECOVER_FILE
, V$BACKUP
, and V$DATAFILE_HEADER
do not access read-only files. Read-only files are indicated in the results list with the error "DELAYED OPEN
", with zeroes for the values of other columns.
V$DATAFILE
does not access read-only files. Read-only files have a size of "0" listed.
V$RECOVER_LOG
does not access read-only files. Logs they could need for recovery are not added to the list.
ALTER DATABASE NOARCHIVELOG
does not access read-only files.It proceeds even if there is a read-only file that requires recovery.
Notes:
|
Using the RENAME TO
clause of the ALTER TABLESPACE
, you can rename a permanent or temporary tablespace. For example, the following statement renames the users
tablespace:
ALTER TABLESPACE users RENAME TO usersts;
When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS
view.
The following affect the operation of this statement:
The COMPATIBLE
parameter must be set to 10.0 or higher.
If the tablespace being renamed is the SYSTEM
tablespace or the SYSAUX
tablespace, then it will not be renamed and an error is raised.
If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.
If the tablespace is read only, then datafile headers are not updated. This should not be regarded as corruption; instead, it causes a message to be written to the alert log indicating that datafile headers have not been renamed. The data dictionary and control file are updated.
If the tablespace is the default temporary tablespace, then the corresponding entry in the database properties table is updated and the DATABASE_PROPERTIES
view shows the new name.
If the tablespace is an undo tablespace and if the following conditions are met, then the tablespace name is changed to the new tablespace name in the server parameter file (SPFILE
).
The server parameter file was used to start up the database.
The tablespace name is specified as the UNDO_TABLESPACE
for any instance.
If a traditional initialization parameter file (PFILE
) is being used then a message is written to the alert file stating that the initialization parameter file must be manually changed.
You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP
TABLESPACE
system privilege to drop a tablespace.
Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped. |
When you drop a tablespace, the file pointers in the control file of the associated database are removed. You can optionally direct Oracle Database to delete the operating system files (datafiles) that constituted the dropped tablespace. If you do not direct the database to delete the datafiles at the same time that it deletes the tablespace, you must later use the appropriate commands of your operating system to delete them.
You cannot drop a tablespace that contains any active segments. For example, if a table in the tablespace is currently being used or the tablespace contains undo data needed to roll back uncommitted transactions, you cannot drop the tablespace. The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.
To drop a tablespace, use the DROP TABLESPACE
statement. The following statement drops the users
tablespace, including the segments in the tablespace:
DROP TABLESPACE users INCLUDING CONTENTS;
If the tablespace is empty (does not contain any tables, views, or other structures), you do not need to specify the INCLUDING CONTENTS
clause. Use the CASCADE CONSTRAINTS
clause to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace.
To delete the datafiles associated with a tablespace at the same time that the tablespace is dropped, use the INCLUDING CONTENTS AND DATAFILES
clause. The following statement drops the users
tablespace and its associated datafiles:
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
A message is written to the alert file for each datafile that is deleted. If an operating system error prevents the deletion of a file, the DROP TABLESPACE
statement still succeeds, but a message describing the error is written to the alert file.
The SYSAUX
tablespace was installed as an auxiliary tablespace to the SYSTEM
tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX
tablespace.
If the SYSAUX
tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX
tablespace could fail, or function with limited capability.
The list of registered occupants of the SYSAUX
tablespace are discussed in "Creating the SYSAUX Tablespace". These components can use the SYSAUX
tablespace, and their installation provides the means of establishing their occupancy of the SYSAUX
tablespace.
You can monitor the occupants of the SYSAUX
tablespace using the V$SYSAUX_OCCUPANTS
view. This view lists the following information about the occupants of the SYSAUX
tablespace:
Name of the occupant
Occupant description
Schema name
Move procedure
Current space usage
View information is maintained by the occupants.
You will have an option at component install time to specify that you do not want the component to reside in SYSAUX
. Also, if you later decide that the component should be relocated to a designated tablespace, you can use the move procedure for that component, as specified in the V$SYSAUX_OCCUPANTS
view, to perform the move.
For example, assume that you install Oracle Ultra Search into the default tablespace, which is SYSAUX
. Later you discover that Ultra Search is using up too much space. To alleviate this space pressure on SYSAUX
, you can call a PL/SQL move procedure specified in the V$SYSAUX_OCCUPANTS
view to relocate Ultra Search to another tablespace.
The move procedure also lets you move a component from another tablespace into the SYSAUX
tablespace.
The SYSAUX
tablespace is occupied by a number of database components (see Table 2-2), and its total size is governed by the space consumed by those components. The space consumed by the components, in turn, depends on which features or functionality are being used and on the nature of the database workload.
The largest portion of the SYSAUX
tablespace is occupied by the Automatic Workload Repository (AWR). The space consumed by the AWR is determined by several factors, including the number of active sessions in the system at any given time, the snapshot interval, and the historical data retention period. A typical system with an average of 30 concurrent active sessions may require approximately 200 to 300 MB of space for its AWR data. You can control the size of the AWR by changing the snapshot interval and historical data retention period. For more information on managing the AWR snapshot interval and retention period, please refer to Oracle Database Performance Tuning Guide.
Another major occupant of the SYSAUX
tablespace is the embedded Enterprise Manager (EM) repository. This repository is used by Oracle Enterprise Manager Database Control to store its metadata. The size of this repository depends on database activity and on configuration-related information stored in the repository.
Other database components in the SYSAUX
tablespace will grow in size only if their associated features (for example, Oracle UltraSearch, Oracle Text, Oracle Streams) are in use. If the features are not used, then these components do not have any significant effect on the size of the SYSAUX
tablespace.
The DBMS_SPACE_ADMIN
package contains the following procedures:
Procedure | Description |
---|---|
SEGMENT_VERIFY |
Verifies the consistency of the extent map of the segment. |
SEGMENT_CORRUPT |
Marks the segment corrupt or valid so that appropriate error recovery can be done. Cannot be used for a locally managed SYSTEM tablespace. |
SEGMENT_DROP_CORRUPT |
Drops a segment currently marked corrupt (without reclaiming space). Cannot be used for a locally managed SYSTEM tablespace. |
SEGMENT_DUMP |
Dumps the segment header and extent map of a given segment. |
TABLESPACE_VERIFY |
Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync. |
TABLESPACE_REBUILD_BITMAPS |
Rebuilds the appropriate bitmap. Cannot be used for a locally managed SYSTEM tablespace. |
TABLESPACE_FIX_BITMAPS |
Marks the appropriate data block address range (extent) as free or used in bitmap. Cannot be used for a locally managed SYSTEM tablespace. |
TABLESPACE_REBUILD_QUOTAS |
Rebuilds quotas for given tablespace. |
TABLESPACE_MIGRATE_FROM_LOCAL |
Migrates a locally managed tablespace to dictionary-managed tablespace. Cannot be used to migrate a locally managed SYSTEM tablespace to a dictionary-managed SYSTEM tablespace. |
TABLESPACE_MIGRATE_TO_LOCAL |
Migrates a tablespace from dictionary-managed format to locally managed format. |
TABLESPACE_RELOCATE_BITMAPS |
Relocates the bitmaps to the destination specified. Cannot be used for a locally managed system tablespace. |
TABLESPACE_FIX_SEGMENT_STATES |
Fixes the state of the segments in a tablespace in which migration was aborted. |
The following scenarios describe typical situations in which you can use the DBMS_SPACE_ADMIN
package to diagnose and resolve problems.
Note: Some of these procedures can result in lost and unrecoverable data if not used properly. You should work with Oracle Support Services if you have doubts about these procedures. |
The TABLESPACE_VERIFY
procedure discovers that a segment has allocated blocks that are marked free in the bitmap, but no overlap between segments is reported.
In this scenario, perform the following tasks:
Call the SEGMENT_DUMP
procedure to dump the ranges that the administrator allocated to the segment.
For each range, call the TABLESPACE_FIX_BITMAPS
procedure with the TABLESPACE_EXTENT_MAKE_USED
option to mark the space as used.
Call TABLESPACE_REBUILD_QUOTAS
to fix up quotas.
You cannot drop a segment because the bitmap has segment blocks marked "free". The system has automatically marked the segment corrupted.
In this scenario, perform the following tasks:
Call the SEGMENT_VERIFY
procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL
option. If no overlaps are reported, then proceed with steps 2 through 5.
Call the SEGMENT_DUMP
procedure to dump the DBA ranges allocated to the segment.
For each range, call TABLESPACE_FIX_BITMAPS
with the TABLESPACE_EXTENT_MAKE_FREE
option to mark the space as free.
Call SEGMENT_DROP_CORRUPT
to drop the SEG$
entry.
Call TABLESPACE_REBUILD_QUOTAS
to fix up quotas.
The TABLESPACE_VERIFY
procedure reports some overlapping. Some of the real data must be sacrificed based on previous internal errors.
After choosing the object to be sacrificed, in this case say, table t1
, perform the following tasks:
Make a list of all objects that t1
overlaps.
Drop table t1
. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT
procedure.
Call the SEGMENT_VERIFY
procedure on all objects that t1
overlapped. If necessary, call the TABLESPACE_FIX_BITMAPS
procedure to mark appropriate bitmap blocks as used.
Rerun the TABLESPACE_VERIFY
procedure to verify the problem is resolved.
A set of bitmap blocks has media corruption.
In this scenario, perform the following tasks:
Call the TABLESPACE_REBUILD_BITMAPS
procedure, either on all bitmap blocks, or on a single block if only one is corrupt.
Call the TABLESPACE_REBUILD_QUOTAS
procedure to rebuild quotas.
Call the TABLESPACE_VERIFY
procedure to verify that the bitmaps are consistent.
You migrate a dictionary-managed tablespace to a locally managed tablespace. You use the TABLESPACE_MIGRATE_TO_LOCAL
procedure. This operation is done online, but space management operations are blocked pending completion of the migration. In other words, you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.
Let us assume that the database block size is 2K, and the existing extent sizes in tablespace tbs_1
are 10, 50, and 10,000 blocks (used, used, and free). The MINIMUM EXTENT
value is 20K (10 blocks). In this scenario, you allow the bitmap allocation unit to be chosen by the system. The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed MINIMUM EXTENT
.
The statement to convert tbs_1
to a locally managed tablespace is as follows:
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');
If you choose to specify an allocation unit size, it must be a factor of the unit size calculated by the system, otherwise an error message is issued.
Use the DBMS_SPACE_ADMIN
package to migrate the SYSTEM
tablespace from dictionary-managed to locally managed. The following statement performs the migration:
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
Before performing the migration the following conditions must be met:
The database has a default temporary tablespace that is not SYSTEM.
There are no rollback segments in dictionary-managed tablespaces.
There is at least one online rollback segment in a locally managed tablespace, or if using automatic undo management, an undo tablespace is online.
All tablespaces other than the tablespace containing the undo space (that is, the tablespace containing the rollback segment or the undo tablespace) are in read-only mode.
There is a cold backup of the database.
The system is in restricted mode.
All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL
procedure.
Note: After theSYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made read/write. If you want to be able to use the dictionary-managed tablespaces in read/write mode, Oracle recommends that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace. |
This section describes how to transport tablespaces between databases, and contains the following topics:
Note: You must be using the Enterprise Edition of Oracle8i or higher to generate a transportable tablespace set. However, you can use any edition of Oracle8i or higher to plug a transportable tablespace set into an Oracle Database on the same platform. To plug a transportable tablespace set into an Oracle Database on a different platform, both databases must have compatibility set to at least 10.0. Please refer to "Compatibility Considerations for Transportable Tablespaces" for a discussion of database compatibility for transporting tablespaces across release levels. |
You can use the transportable tablespaces feature to move a subset of an Oracle Database and "plug" it in to another Oracle Database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size.
Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are simply copied to the destination location, and you use an import utility to transfer only the metadata of the tablespace objects to the new database.
Note: The remainder of this chapter uses Data Pump as the import/export utility to use. However, the transportable tablespaces feature supports both Data Pump and the original import and export utilities. Please refer to Oracle Database Utilities for more information on these utilities. |
The transportable tablespace feature is useful in a number of scenarios, including:
Exporting and importing partitions in data warehousing tables
Publishing structured data on CDs
Copying multiple read-only versions of a tablespace on multiple databases
Archiving historical data
Performing tablespace point-in-time-recovery (TSPITR)
These scenarios are discussed in "Using Transportable Tablespaces: Scenarios"
See Also: Oracle Data Warehousing Guide for information about using transportable tablespaces in a data warehousing environment |
Starting with Oracle Database 10g, you can transport tablespaces across platforms. This functionality can be used to:
Allow a database to be migrated from one platform to another
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow
Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM
view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering). For example, the following query displays the currently supported platforms:
SQL> COLUMN PLATFORM_NAME FORMAT A30 SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ----------- ------------------------------ -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 7 Microsoft Windows NT Little 10 Linux IA (32-bit) Little 6 AIX-Based Systems (64-bit) Big 3 HP-UX (64-bit) Big 5 HP Tru64 UNIX Little 4 HP-UX IA (64-bit) Big 11 Linux IA (64-bit) Little 15 HP Open VMS Little 10 rows selected.
If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, the datafile header must identify the platform to which it belongs. In an Oracle Database with compatibility set to 10.0.0 or higher, you can accomplish this by making the datafile read/write at least once.
Be aware of the following limitations as you plan to transport tablespaces:
The source and target database must use the same character set and national character set.
You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
Most database entities, such as data in a tablespace or structural information associated with the tablespace, behave normally after being transported to a different database. Some exceptions arise with the following entities:
Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.
Types whose interpretation is application-specific and opaque to the database (such as RAW
, BFILE
, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.
BINARY_FLOAT
and BINARY_DOUBLE
types are transportable using Data Pump but not using the original export utility.
When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 10g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database.
The following table shows the minimum compatibility requirements of the source and target tablespace in various scenarios. The source and target database need not have the same compatibility setting.
To move or copy a set of tablespaces, perform the following steps.
For cross-platform transport, check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM
view.
If you are transporting the tablespace set to a platform different from the source platform, then determine if the source and target platforms are supported and their endianness. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or target database.
Ignore this step if you are transporting your tablespace set to the same platform.
Pick a self-contained set of tablespaces.
Generate a transportable tablespace set.
A transportable tablespace set consists of datafiles for the set of tablespaces being transported and an export file containing structural information for the set of tablespaces.
If you are transporting the tablespace set to a platform with different endianness from the source platform, you must convert the tablespace set to the endianness of the target platform. You can perform a source-side conversion at this step in the procedure, or you can perform a target-side conversion as part of step 4.
Transport the tablespace set.
Copy the datafiles and the export file to the target database. You can do this using any facility for copying flat files (for example, an operating system copy utility, ftp, the DBMS_FILE_COPY
package, or publishing on CDs).
If you have transported the tablespace set to a platform with different endianness from the source platform, and you have not performed a source-side conversion to the endianness of the target platform, you should perform a target-side conversion now.
Plug in the tablespace.
Invoke the Data Pump utility to plug the set of tablespaces into the target database.
These steps are illustrated more fully in the example that follows, where it is assumed the following datafiles and tablespaces exist:
Tablespace | Datafile: |
---|---|
sales_1 |
/u01/oracle/oradata/salesdb/sales_101.dbf |
sales_2 |
/u01/oracle/oradata/salesdb/sales_201.dbf |
This step is only necessary if you are transporting the tablespace set to a platform different from the source platform. If sales_1
and sales_2
were being transported to a different platform, you can execute the following query on both platforms to determine if the platforms are supported and their endian formats:
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
The following is the query result from the source platform:
PLATFORM_NAME ENDIAN_FORMAT ------------------------- -------------- Solaris[tm] OE (32-bit) Big
The following is the result from the target platform:
PLATFORM_NAME ENDIAN_FORMAT ------------------------- -------------- Microsoft Windows NT Little
You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.
There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Some examples of self contained tablespace violations are:
An index inside the set of tablespaces is for a table outside of the set of tablespaces.
Note: It is not a violation if a corresponding index for a table is outside of the set of tablespaces. |
A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.
A referential integrity constraint points to a table across a set boundary.
When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.
A table inside the set of tablespaces contains a LOB
column that points to LOB
s outside the set of tablespaces.
To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK
procedure in the Oracle supplied package DBMS_TTS
. You must have been granted the EXECUTE_CATALOG_ROLE
role (initially signed to SYS
) to execute this procedure.
When you invoke the DBMS_TTS
package, you specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK
parameter to TRUE
.
The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.
For example, it is a violation to perform TSPITR on a tablespace containing a table t
but not its index i
because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set. See the example for TSPITR in the Oracle Database Backup and Recovery Advanced User's Guide.
Note: The default for transportable tablespaces is to check for self containment rather than full containment. |
The following statement can be used to determine whether tablespaces sales_1
and sales_2
are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE
).
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS
view. If the set of tablespaces is self-contained, this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint, dept_fk
, across the tablespace set boundary, and a partitioned table, jim.sales
, that is partially contained in the tablespace set.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS --------------------------------------------------------------------------- Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table JIM.DEPT in tablespace OTHER Partitioned table JIM.SALES is partially contained in the transportable set
These violations must be resolved before sales_1
and sales_2
are transportable. As noted in the next step, one choice for bypassing the integrity constraint violation is to not export the integrity constraints.
See Also:
|
Any privileged user can perform this step. However, you must have been assigned the EXP_FULL_DATABASE
role to perform a transportable tablespace export operation.
After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by performing the following actions:
Make all tablespaces in the set you are copying read-only.
SQL> ALTER TABLESPACE sales_1 READ ONLY; Tablespace altered. SQL> ALTER TABLESPACE sales_2 READ ONLY; Tablespace altered.
Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
SQL> HOST $ EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = sales_1,sales_2
You must always specify TRANSPORT_TABLESPACES
, which determines the mode of the export operation. In this example:
The DUMPFILE
parameter specifies the name of the structural information export file to be created, expdat.dmp
.
The DIRECTORY
parameter specifies the default directory object that points to the operating system location of the dump file. You must create the DIRECTORY
object before invoking Data Pump, and you must grant REWRITE
object privilege on the directory to PUBLIC
.
Triggers and indexes are included in the export operation by default.
If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK
parameter, as shown in the following example:
EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y
In this example, the Data Pump export utility verifies that there are no dependencies between the objects inside the transportable set and objects outside the transportable set. If the tablespace set being transported is not self-contained, then the export fails and indicates that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.
Notes: The Data Pump utility is used to export only data dictionary structural information (metadata) for the tablespaces. No actual data is unloaded, so this operation goes relatively quickly even for large tablespace sets. |
When finished, exit back to SQL*Plus:
$ EXIT
If sales_1
and sales_2
are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1
and sales_2
tablespaces:
From SQL*Plus, return to the host system:
SQL> HOST
The RMAN CONVERT
command is used to do the conversion. Connect to RMAN:
$ RMAN TARGET / Recovery Manager: Release 10.1.0.0.0 Copyright (c) 1995, 2003, Oracle Corporation. All rights reserved. connected to target database: salesdb (DBID=3295731590)
Convert the datafiles into a temporary location on the source platform. In this example, assume that the temporary location, directory /temp
, has already been created. The converted datafiles are assigned names by the system.
RMAN> CONVERT TABLESPACE sales_1,sales_2 2> TO PLATFORM 'Microsoft Windows NT' 3> FORMAT '/temp/%U'; Starting backup at 08-APR-03 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=11 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00005 name=/u01/oracle/oradata/salesdb/sales_101.dbf converted datafile=/temp/data_D-10_I-3295731590_TS-ADMIN_TBS_FNO-5_05ek24v5 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45 Finished backup at 08-APR-03
Exit recovery manager:
RMAN> exit Recovery Manager complete.
Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database. You can use any facility for copying flat files (for example, an operating system copy utility, ftp, the DBMS_FILE_TRANSFER
package, or publishing on CDs).
Caution: Exercise caution when using the UNIXdd utility to copy raw-device files between databases. The dd utility can be used to copy an entire source raw-device file, or it can be invoked with options that instruct it to copy only a specific range of blocks from the source raw-device file.
It is difficult to ascertain actual datafile size for a raw-device file because of hidden control information that is stored as part of the datafile. Thus, it is advisable when using the |
If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, you must do so now. This example assumes that you have completed the following steps before the transport:
Set the source tablespaces to be transported to be read-only.
Use the export utility to create an export file (in our example, expdat.dmp).
Datafiles that are to be converted on the target platform can be moved to a temporary location on the target platform. However, all datafiles, whether already converted or not, must be moved to a designated location on the target database.
Now use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs, as shown in this hypothetical example:
RMAN> CONVERT DATAFILE 2> '/hq/finance/work/tru/tbs_31.f', 3> '/hq/finance/work/tru/tbs_32.f', 4> '/hq/finance/work/tru/tbs_41.f' 5> TO PLATFORM="Solaris[tm] OE (32-bit)" 6> FROM PLATFORM="HP TRu64 UNIX" 7> DBFILE_NAME_CONVERT= 8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru" 9> PARALLELISM=5;
You identify the datafiles by filename, not by tablespace name. Until the datafiles are plugged in, the local instance has no way of knowing the desired tablespace names. The source and destination platforms are optional. RMAN determines the source platform by examining the datafile, and the target platform defaults to the platform of the host running the conversion.
See Also: "Copying Files Using the Database Server" for information about using the DBMS_FILE_TRANSFER package to copy the files that are being transported and their metadata |
Note: If you are transporting a tablespace of a different block size than the standard block size of the database receiving the tablespace set, then you must first have aDB_ n K_CACHE_SIZE initialization parameter entry in the receiving database parameter file.
For example, if you are transporting a tablespace with an 8K block size into a database with a 4K standard block size, then you must include a See Oracle Database Reference for information about specifying values for the |
Any privileged user can perform this step. To plug in a tablespace set, perform the following tasks:
Plug in the tablespaces and integrate the structural information using the Data Pump Import utility, impdp
:
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES= /salesdb/sales_101.dbf, /salesdb/sales_201.dbf REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
In this example we specify the following:
The DUMPFILE
parameter specifies the exported file containing the metadata for the tablespaces to be imported.
The DIRECTORY
parameter specifies the directory object that identifies the location of the dump file.
The TRANSPORT_DATAFILES
parameter identifies all of the datafiles containing the tablespaces to be imported.
The REMAP_SCHEMA
parameter changes the ownership of database objects. If you do not specify REMAP_SCHEMA
, all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the target database. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned by dcranney
in the source database will be owned by smith
in the target database after the tablespace set is plugged in. Similarly, objects owned by jfee
in the source database will be owned by williams
in the target database. In this case, the target database is not required to have users dcranney
and jfee
, but must have users smith
and williams
.
After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. Check the import logs to ensure that no error has occurred.
When dealing with a large number of datafiles, specifying the list of datafile names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:
IMPDP system/password PARFILE='par.f'
where the parameter file, par.f
contains the following:
DIRECTORY=dpump_dir DUMPFILE=expdat.dmp TRANSPORT_DATAFILES="'/db/sales_jan','/db/sales_feb'" REMAP_SCHEMA=dcranney:smith REMAP_SCHEMA=jfee:williams
If required, put the tablespaces into read/write mode as follows:
ALTER TABLESPACE sales_1 READ WRITE; ALTER TABLESPACE sales_2 READ WRITE;
The following sections describe some uses for transportable tablespaces:
Typical enterprise data warehouses contain one or more large fact tables. These fact tables can be partitioned by date, making the enterprise data warehouse a historical database. You can build indexes to speed up star queries. Oracle recommends that you build local indexes for such historically partitioned tables to avoid rebuilding global indexes every time you drop the oldest partition from the historical database.
Suppose every month you would like to load one month of data into the data warehouse. There is a large fact table in the data warehouse called sales
, which has the following columns:
CREATE TABLE sales (invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL) PARTITION BY RANGE (sale_year, sale_month, sale_day) (partition jan98 VALUES LESS THAN (1998, 2, 1), partition feb98 VALUES LESS THAN (1998, 3, 1), partition mar98 VALUES LESS THAN (1998, 4, 1), partition apr98 VALUES LESS THAN (1998, 5, 1), partition may98 VALUES LESS THAN (1998, 6, 1), partition jun98 VALUES LESS THAN (1998, 7, 1));
You create a local nonprefixed index:
CREATE INDEX sales_index ON sales(invoice_no) LOCAL;
Initially, all partitions are empty, and are in the same default tablespace. Each month, you want to create one partition and attach it to the partitioned sales
table.
Suppose it is July 1998, and you would like to load the July sales data into the partitioned table. In a staging database, you create a new tablespace, ts_jul
. You also create a table, jul_sales
, in that tablespace with exactly the same column types as the sales
table. You can create the table jul_sales
using the CREATE
TABLE
... AS
SELECT
statement. After creating and populating jul_sales
, you can also create an index, jul_sale_index
, for the table, indexing the same column as the local index in the sales
table. After building the index, transport the tablespace ts_jul
to the data warehouse.
In the data warehouse, add a partition to the sales
table for the July sales data. This also creates another partition for the local nonprefixed index:
ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1);
Attach the transported table jul_sales
to the table sales
by exchanging it with the new partition:
ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales INCLUDING INDEXES WITHOUT VALIDATION;
This statement places the July sales data into the new partition jul98
, attaching the new data to the partitioned table. This statement also converts the index jul_sale_index
into a partition of the local index for the sales
table. This statement should return immediately, because it only operates on the structural information and it simply switches database pointers. If you know that the data in the new partition does not overlap with data in previous partitions, you are advised to specify the WITHOUT VALIDATION
clause. Otherwise, the statement goes through all the new data in the new partition in an attempt to validate the range of that partition.
If all partitions of the sales
table came from the same staging database (the staging database is never destroyed), the exchange statement always succeeds. In general, however, if data in a partitioned table comes from different databases, it is possible that the exchange operation may fail. For example, if the jan98
partition of sales
did not come from the same staging database, the preceding exchange operation can fail, returning the following error:
ORA-19728: data object number conflict between table JUL_SALES and partition JAN98 in table SALES
To resolve this conflict, move the offending partition by issuing the following statement:
ALTER TABLE sales MOVE PARTITION jan98;
Then retry the exchange operation.
After the exchange succeeds, you can safely drop jul_sales
and jul_sale_index
(both are now empty). Thus you have successfully loaded the July sales data into your data warehouse.
Transportable tablespaces provide a way to publish structured data on CDs. A data provider can load a tablespace with data to be published, generate the transportable set, and copy the transportable set to a CD. This CD can then be distributed.
When customers receive this CD, they can plug it into an existing database without having to copy the datafiles from the CD to disk storage. For example, suppose on a Windows NT machine D: drive is the CD drive. You can plug in a transportable set with datafile catalog.f
and export file expdat.dmp
as follows:
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES='D:\catalog.f'
You can remove the CD while the database is still up. Subsequent queries to the tablespace return an error indicating that the database cannot open the datafiles on the CD. However, operations to other parts of the database are not affected. Placing the CD back into the drive makes the tablespace readable again.
Removing the CD is the same as removing the datafiles of a read-only tablespace. If you shut down and restart the database, the database indicates that it cannot find the removed datafile and does not open the database (unless you set the initialization parameter READ_ONLY_OPEN_DELAYED
to TRUE
). When READ_ONLY_OPEN_DELAYED
is set to TRUE
, the database reads the file only when someone queries the plugged-in tablespace. Thus, when plugging in a tablespace on a CD, you should always set the READ_ONLY_OPEN_DELAYED
initialization parameter to TRUE
, unless the CD is permanently attached to the database.
You can use transportable tablespaces to mount a tablespace read-only on multiple databases. In this way, separate databases can share the same data on disk instead of duplicating data on separate disks. The tablespace datafiles must be accessible by all databases. To avoid database corruption, the tablespace must remain read-only in all the databases mounting the tablespace.
You can mount the same tablespace read-only on multiple databases in either of the following ways:
Plug the tablespace into each of the databases on which you want to mount the tablespace. Generate a transportable set in a single database. Put the datafiles in the transportable set on a disk accessible to all databases. Import the structural information into each database.
Generate the transportable set in one of the databases and plug it into other databases. If you use this approach, it is assumed that the datafiles are already on the shared disk, and they belong to an existing tablespace in one of the databases. You can make the tablespace read-only, generate the transportable set, and then plug the tablespace in to other databases while the datafiles remain in the same location on the shared disk.
You can make the disk accessible by multiple computers in several ways. You can use either a cluster file system or raw disk. You can also use network file system (NFS), but be aware that if a user queries the shared tablespace while NFS is down, the database will hang until the NFS operation times out.
Later, you can drop the read-only tablespace in some of the databases. Doing so does not modify the datafiles for the tablespace. Thus, the drop operation does not corrupt the tablespace. Do not make the tablespace read/write unless only one database is mounting the tablespace.
Since a transportable tablespace set is a self-contained set of files that can be plugged into any Oracle Database, you can archive old/historical data in an enterprise data warehouse using the transportable tablespace procedures described in this chapter.
You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).
See Also: Oracle Database Backup and Recovery Advanced User's Guide for information about how to perform TSPITR using transportable tablespaces |
You can use the transportable tablespace feature to migrate a database to a different platform by creating a new database on the destination platform and performing a transport of all the user tablespaces.
You cannot transport the SYSTEM
tablespace. Therefore, objects such as sequences, PL/SQL packages, and other objects that depend on the SYSTEM
tablespace are not transported. You must either create these objects manually on the destination database, or use Data Pump to transport the objects that are not moved by transportable tablespace.
The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.
View | Description |
---|---|
V$TABLESPACE |
Name and number of all tablespaces from the control file. |
DBA_TABLESPACES , USER_TABLESPACES |
Descriptions of all (or user accessible) tablespaces. |
DBA_TABLESPACE_GROUPS |
Displays the tablespace groups and the tablespaces that belong to them. |
DBA_SEGMENTS , USER_SEGMENTS |
Information about segments within all (or user accessible) tablespaces. |
DBA_EXTENTS , USER_EXTENTS |
Information about data extents within all (or user accessible) tablespaces. |
DBA_FREE_SPACE , USER_FREE_SPACE |
Information about free extents within all (or user accessible) tablespaces. |
V$DATAFILE |
Information about all datafiles, including tablespace number of owning tablespace. |
V$TEMPFILE |
Information about all tempfiles, including tablespace number of owning tablespace. |
DBA_DATA_FILES |
Shows files (datafiles) belonging to tablespaces. |
DBA_TEMP_FILES |
Shows files (tempfiles) belonging to temporary tablespaces. |
V$TEMP_EXTENT_MAP |
Information for all extents in all locally managed temporary tablespaces. |
V$TEMP_EXTENT_POOL |
For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance. |
V$TEMP_SPACE_HEADER |
Shows space used/free for each tempfile. |
DBA_USERS |
Default and temporary tablespaces for all users. |
DBA_TS_QUOTAS |
Lists tablespace quotas for all users. |
V$SORT_SEGMENT |
Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type. |
V$TEMPSEG_USAGE |
Describes temporary (sort) segment usage by user for temporary or permanent tablespaces. |
The following are just a few examples of using some of these views.
To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES
view:
SELECT TABLESPACE_NAME "TABLESPACE", INITIAL_EXTENT "INITIAL_EXT", NEXT_EXTENT "NEXT_EXT", MIN_EXTENTS "MIN_EXT", MAX_EXTENTS "MAX_EXT", PCT_INCREASE FROM DBA_TABLESPACES; TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE ---------- ----------- -------- ------- ------- ------------ RBS 1048576 1048576 2 40 0 SYSTEM 106496 106496 1 99 1 TEMP 106496 106496 1 99 0 TESTTBS 57344 16384 2 10 1 USERS 57344 57344 1 99 1
To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES
view:
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES; FILE_NAME BLOCKS TABLESPACE_NAME ------------ ---------- ------------------- /U02/ORACLE/IDDB3/DBF/RBS01.DBF 1536 RBS /U02/ORACLE/IDDB3/DBF/SYSTEM01.DBF 6586 SYSTEM /U02/ORACLE/IDDB3/DBF/TEMP01.DBF 6400 TEMP /U02/ORACLE/IDDB3/DBF/TESTTBS01.DBF 6400 TESTTBS /U02/ORACLE/IDDB3/DBF/USERS01.DBF 384 USERS
To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID, COUNT(*) "PIECES", MAX(blocks) "MAXIMUM", MIN(blocks) "MINIMUM", AVG(blocks) "AVERAGE", SUM(blocks) "TOTAL" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME, FILE_ID; TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL ---------- ------- ------ ------- ------- ------- ------ RBS 2 1 955 955 955 955 SYSTEM 1 1 119 119 119 119 TEMP 4 1 6399 6399 6399 6399 TESTTBS 5 5 6364 3 1278 6390 USERS 3 1 363 363 363 363
PIECES
shows the number of free space extents in the tablespace file, MAXIMUM
and MINIMUM
show the largest and smallest contiguous area of space in database blocks, AVERAGE
shows the average size in blocks of a free space extent, and TOTAL
shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.