Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes the various aspects of tablespace management, and contains the following topics:
| See Also:
Chapter 3, "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:
| See Also:
Oracle9i 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. For example, when a database has multiple tablespaces, you can perform the following tasks:
Some operating systems set a limit on the number of files that can be simultaneously open. These limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system's 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 the autoextend option set on, rather than many small datafiles.
Review your data in light of these factors and decide how many tablespaces you need for your database design.
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's 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 chooses default storage parameters appropriate for your operating system. |
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's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.
Before you can create a tablespace you must create a database to contain it. The first tablespace in any database is always the SYSTEM tablespace, and the first datafiles of any database are automatically allocated in the SYSTEM tablespace during database creation.
The steps for creating tablespaces vary by operating system. In all cases, however, you should create through your operating system a directory structure in which your datafiles will be allocated. On most operating systems you indicate the size and fully specified filenames when creating a new tablespace or altering a tablespace by adding datafiles. In each situation Oracle 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.
Prior to Oracle8i, all tablespaces were created as dictionary-managed. Dictionary-managed tablespaces rely on data dictionary tables to track space utilization. Beginning with Oracle8i, you were able to create locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. These locally managed tablespaces provide better performance and greater ease of management.
You can also create a special type of tablespace called an undo tablespace. This tablespace is specifically designed to contain undo records. These are records generated by Oracle 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 13, "Managing Undo Space".
Permanent and temporary tablespaces are discussed in the following sections:
See Also:
|
Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits:
AUTOALLOCATE clause is specified, appropriate extent size is automatically selectedAll tablespaces, including the SYSTEM tablespace, can be locally managed.
Additionally, the DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.
To create a locally managed tablespace, specify LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).
If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and deallocation, AUTOALLOCATE presents a simplified way for you to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this drawback.
On the other hand, 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 ensures that you will never have an unusable amount of space in your tablespace.
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 the smallest extent size being 64K. There is an increase in initial space allocated for objects in autoallocated tablespaces. This is because the objects have a minimum size of two blocks in dictionary-managed tablespaces, whereas in autoallocated locally managed tablespaces, the minimum object size is 64K.
Alternatively, this tablespace could be created specifying the UNIFORM clause. If UNIFORM SIZE is specified, then the tablespace is managed with uniform size extents of the specified SIZE. The default SIZE is 1M.
In the following example, a 128K extent size is specified. Each 128K extent (which, if the tablespace block size is 2K, is equivalent to 64 Oracle blocks) 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.
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed. Your choices are:
MANUAL
Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
AUTO
This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that 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. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.
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 FREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.
Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self tuning in that it scales with increasing the number of users, as well as instances. For 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.
For many standard workloads, application performance when using 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;
Your specification at tablespace creation time of your method for managing available space in segments, applies to all segments subsequently created in the tablespace. Also, your choice of method cannot be subsequently altered. Only permanent, locally managed tablespaces can specify automatic segment-space management.
You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management.
Some reasons for using the ALTER TABLESPACE statement for locally managed tablespaces include:
ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
ONLINE/OFFLINE). See "Altering Tablespace Availability".Coalescing free extents is unnecessary for locally managed tablespaces.
Starting with Oracle9i, the default for extent management when creating a tablespace is locally managed. However, you can explicitly specify that you want to create a dictionary-managed tablespace. For dictionary-managed tablespaces, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse.
As an example, the following statement creates the tablespace tbsa, with the following characteristics:
EXTENT MANAGEMENT DICTIONARY.The following statement creates the tablespace tbsb:
CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
The following parameters, included in the above 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.
Another parameter on the CREATE TABLESPACE statement, MIMIMUM EXTENT, 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 (K or M). This provides one means of controlling free space fragmentation in the tablespace.
See Also:
|
One reason for using an ALTER TABLESPACE statement is to add 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 might also want to change the default storage parameters.
You can 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 future objects that are created, or extents allocated for existing segments within the tablespace.
Other reasons for issuing an ALTER TABLESPACE statement include, but are not limited to:
ONLINE/OFFLINE). See "Altering Tablespace Availability".To improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, create temporary tablespaces. A temporary tablespace can be shared by multiple users and can be assigned to users with the CREATE USER statement when you create users in the database.
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. Therefore, they can be used in standby or read-only databases.
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 default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the above statement.
The AUTOALLOCATE clause is not allowed for temporary tablespaces.
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 2M REUSE;
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 4M;
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.
To identify a tablespace as temporary during tablespace creation, specify the TEMPORARY keyword on the CREATE TABLESPACE statement. You cannot specify EXTENT MANAGEMENT LOCAL for a temporary tablespace created in this fashion. To create a locally managed temporary tablespace, use the CREATE TEMPORARY TABLESPACE statement, which is the preferred method of creating a temporary tablespace.
The following statement creates a temporary dictionary-managed tablespace:
CREATE TABLESPACE sort DATAFILE '/u02/oracle/data/sort01.dbf' SIZE 50M DEFAULT STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 PCTINCREASE 0) EXTENT MANAGEMENT DICTIONARY TEMPORARY;
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. Any restrictions are noted in the Oracle9i SQL Reference.
|
Note: When you take dictionary-managed temporary tablespaces offline with the |
You can change an existing permanent dictionary-managed tablespace to a temporary tablespace, using the ALTER TABLESPACE statement. For example:
ALTER TABLESPACE tbsa TEMPORARY;
Over time, the free space in a dictionary-managed tablespace can become fragmented, making it difficult to allocate new extents. Ways of defragmenting this free space are discussed in this section.
The following topics are contained in this section:
A free extent in a dictionary-managed tablespace is comprised of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the free extent closest in size to the required extent is used. In some cases, when segments are dropped, their extents are deallocated and marked as free, but any adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.
This fragmentation is addressed in several ways:
PCTINCREASE value for a tablespace in nonzero. If you set PCTINCREASE=0, no coalescing of free extents will occur. If you are concerned about the overhead of SMON's ongoing coalescing, an alternative is to set PCTINCREASE=0, and periodically coalesce free space manually.PCTINCREASE value for the segment is not zero. This is done even if PCTINCREASE=0 for the tablespace containing the segment.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. |
| See Also:
Oracle9i Database Concepts for detailed information on allocating extents and coalescing 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.
You might want to use this statement 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 would be the case if the default PCTINCREASE value for the tablespace were set to zero, all segments used 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;
Like other options of the ALTER TABLESPACE statement, the COALESCE option is exclusive: when specified, it must be the only option.
This statement does not coalesce free extents that are separated by data extents. If you observe that there are 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.
You can use the following views for monitoring free space in a tablespace:
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.
| See Also:
Oracle9i Database Reference for more information about these views |
You can create tablespaces of different block sizes than the standard database block size specified by the DB_BLOCK_SIZE initialization parameter. This feature enables the transporting of tablespaces with unlike block sizes between databases.
The BLOCKSIZE clause of the CREATE TABLESPACE statement enables you to create a tablespace with a block size other than the database's standard block size. However, your buffer cache in SGA memory must be configured for the nonstandard block sizes.
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;
|
Note: In order for the For information about these parameters, see "Setting the Buffer Cache Initialization Parameters". |
For some database operations, it is possible to control whether redo records are generated. Suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. This might include a CREATE TABLE...AS SELECT statement, where the operation can be repeated if there is a database or instance failure. Without redo, no media recovery is possible.
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 specify LOGGING instead, then redo is generated 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.
In the case where you have a standby database, specifying NOLOGGING causes problems with the availablity 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 this portion of the database is temporarily unavailable for general use. The rest of the database is 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, use the SQL statement ALTER TABLESPACE. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege to perform this action.
You can also take all of the datafiles or tempfiles in a tablespace offline, and bring them back online, without affecting the OFFLINE or ONLINE status of the tablespace itself.
You may want to take a tablespace offline for any of the following reasons:
When a tablespace is taken offline, Oracle takes all the associated files offline. The SYSTEM tablespace can never be taken offline.
You can specify any of the following options when taking a tablespace offline:
| Option | Description |
|---|---|
|
|
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 |
|
|
A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When If no files are offline, but you use the temporary option, 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. |
|
|
A tablespace can be taken offline immediately, without Oracle taking a checkpoint on any of the datafiles. When you specify |
|
|
Takes the database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information, see Oracle9i User-Managed Backup and Recovery Guide. |
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 options.
The following example takes the users tablespace offline normally:
ALTER TABLESPACE users OFFLINE NORMAL;
Before taking an online tablespace offline, consider taking the following actions:
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.
The following statement brings the users tablespace online:
ALTER TABLESPACE users ONLINE;
Clauses of the ALTER TABLESPACE statement enable you to change the online or offline status of all of the datafiles or tempfiles within a tablespace. Specifically, the statements that affect online/offline status are:
You are required only to enter the tablespace name, not the individual datafiles or tempfiles. All of the datafiles or tempfiles are affected, but the online/offline status of the tablespace itself is not changed.
In most cases the above ALTER TABLESPACE statements can be issued whenever the database is mounted, even if it is not open. The database must not be open if the tablespace is the SYSTEM tablespace, an undo tablespace, or the default temporary tablespace. The ALTER DATABASE DATAFILE and ALTER DATABASE TEMPFILE statements also have ONLINE/OFFLINE clauses, however in those statements require that you enter all of the filenames for the tablespace.
The syntax is different from the ALTER TABLESPACE ... ONLINE|OFFLINE statement that alters a tablespace's availability, because that is a different operation. The ALTER TABLESPACE statement takes datafiles offline as well as the tablespace, but it cannot be used to alter the status of a temporary tablespace or its tempfile(s).
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, but they also provide a means of completely protecting historical data so that no one can modify the data after the fact. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in the 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:
See Also:
|
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.
This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.
For this reason, the SYSTEM tablespace can never be made read-only, since it contains the SYSTEM rollback segment. Additionally, because any rollback segments of a read-only tablespace would not be accessible, you would have to drop the rollback segments before you made a tablespace read-only.
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 Oracle 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 do not have to wait for transactions to complete before issuing the ALTER TABLESPACE ... READ ONLY statement. When the statement is issued, the target tablespace goes into a transitional read-only mode in which no further write operations (DML statements) are allowed against the tablespace. Existing transactions that modified the tablespace are allowed to commit or rollback. Once all transactions (in the database) have completed, the tablespace becomes read-only.
If you find it is taking a long time for the tablespace to quiesce, it is possible to identify the transactions which are preventing the read-only state from taking effect. The owners of these transactions can be notified and a decision can be made to terminate the transactions, if necessary. The following example illustrates how you might identify the blocking transactions:
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
V$TRANSACTION view. Displaying this view sorted by ascending start SCN lists the transactions in execution order. Since you know the session address of the transaction entry for the read-only statement, it can be located in the V$TRANSACTION view. All transactions with lesser start SCN 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 since 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 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.
ALTER TABLESPACE with the RENAME DATAFILE clause. Renaming the datafiles changes their names in the control file.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:
ALTER SYSTEM CHECK DATAFILES does not check read-only files.ALTER TABLESPACE ... ONLINE and ALTER DATABASE DATAFILE ... ONLINE does 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.
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. Any tablespace in an Oracle database, except the SYSTEM tablespace, can be dropped. You must have the DROP TABLESPACE system privilege to drop a tablespace.
When you drop a tablespace, the file pointers in the control file of the associated database are removed. You can optionally direct Oracle to delete the operating system files (datafiles) that constituted the dropped tablespace. If you do not direct Oracle 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 an active rollback segment, 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 option. Use the CASCADE CONSTRAINTS option 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 USER 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 DBMS_SPACE_ADMIN package contains the following procedures:
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. |
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for details about the |
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:
SEGMENT_DUMP procedure to dump the ranges that the administrator allocated to the segment.TABLESPACE_FIX_BITMAPS procedure with the TABLESPACE_EXTENT_MAKE_USED option to mark the space as used.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:
SEGMENT_VERIFY procedure with the SEGMENT_VERIFY_EXTENTS_GLOBAL option. If no overlaps are reported, then proceed with steps 2 through 5.SEGMENT_DUMP procedure to dump the DBA ranges allocated to the segment.TABLESPACE_FIX_BITMAPS with the TABLESPACE_EXTENT_MAKE_FREE option to mark the space as free.SEGMENT_DROP_CORRUPT to drop the SEG$ entry.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:
t1 overlaps.t1. If necessary, follow up by calling the SEGMENT_DROP_CORRUPT procedure.SEGMENT_VERIFY procedure on all objects that t1 overlapped. If necessary, call the TABLESPACE_FIX_BITMAPS procedure to mark appropriate bitmap blocks as used.TABLESPACE_VERIFY procedure to verify the problem is resolved.A set of bitmap blocks has media corruption.
In this scenario, perform the following tasks:
TABLESPACE_REBUILD_BITMAPS procedure, either on all bitmap blocks, or on a single block if only one is corrupt.TABLESPACE_REBUILD_QUOTAS procedure to rebuild quotas.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.
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 a 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:
SYSTEM.All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure.
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. See "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's standard block size. Transporting tablespaces is particularly useful for:
Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
See Also:
|
Be aware of the following limitations as you plan for transportable tablespace use:
To use the transportable tablespaces feature, the COMPATIBLE initialization parameter for both the source and target databases must be set to 8.1 or higher. If the block size of any tablespace being transported is different from the standard block size for the target database, the COMPATIBLE initialization parameter must be set to 9.0 or higher for the target database. You are not required to be running the same release of Oracle for both the source and target database. Oracle guarantees that the transportable tablespace set is compatible with the target database. If not, an error is signaled at the beginning of the plug-in operation.
It is always possible to transport a tablespace from a database running an older release of Oracle (starting with Oracle8i) to a database running a newer release of Oracle (for example, Oracle9i).
When creating a transportable tablespace set, Oracle computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. When plugging the transportable set into a target database, Oracle signals an error if the compatibility level of the transportable set is greater than the compatibility level of the target database.
To move or copy a set of tablespaces, perform the following steps. These steps are illustrated more fully in succeeding sections that detail transporting tablespaces sales_1 and sales_2 between databases.
A transportable tablespace set consists of datafiles for the set of tablespaces being transported and a file containing structural information for the set of tablespaces.
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, or publishing on CDs).
Invoke the Import utility to plug the set of tablespaces into the target database.
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:
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.
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.
LOB column that points to LOBs 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 Oracle9i User-Managed Backup and Recovery Guide.
|
Note: The default for transportable tablespaces is to check for self containment rather than full containment. |
Here we 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 query shows 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.
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 constrain violation is to not export the integrity constraints.
Object references (such as REFs) across the tablespace set are not considered violations. REFs are not checked by the TRANSPORT_SET_CHECK routine. When a tablespace containing dangling REFs is plugged into a database, queries following that dangling REF indicate user error.
See Also:
|
After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by performing the following tasks:
ALTER TABLESPACE sales_1 READ ONLY; ALTER TABLESPACE sales_2 READ ONLY;
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
|
Note: Although the Export utility is used, only data dictionary structural information (metadata) for the tablespaces is exported. Hence, this operation goes quickly even for a large tablespace. |
When prompted, connect as SYS (or other administrative user) with the SYSDBA system privilege:
CONNECT SYS/password AS SYSDBA
You must always specify TABLESPACES. In this example, we also specify that:
If you set TRIGGERS=y, triggers are exported without a validity check. Invalid triggers cause compilation errors during the subsequent import.If you set TRIGGERS=n, triggers are not exported.
expdat.dmp.If you are performing TSPITR or transport with a strict containment check, use:
EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TTS_FULL_CHECK=Y FILE=expdat.dmp
If the tablespace sets being transported are not self-contained, export fails and indicate that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.
| See Also:
Oracle9i Database Utilities for information about using the Export utility |
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, or publishing on CDs).
|
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 a 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 Oracle9i Database Reference for information about specifying values for the |
To plug in a tablespace set, perform the following tasks:
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('/db/sales_jan','/db/sales_feb',...) TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee) FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
When prompted, connect as SYS (or other administrative user) with the SYSDBA system privilege:
CONNECT SYS/password AS SYSDBA
In this example we specify the following:
TRANSPORT_TABLESPACE=y tells the Export utility that we are transporting a tablespace.expdat.dmp.DATAFILES specifies the datafiles of the transported tablespaces and must be specified.sales_1 and sales_2.
When you specify TABLESPACES, the supplied tablespace names are compared to those in the export file. Import returns an error if there is any mismatch. Otherwise, tablespace names are extracted from the export file.
TTS_OWNERS lists all users who own data in the tablespace set.
When you specify TTS_OWNERS, the user names are compared to those in the export file. Import returns an error if there is any mismatch. Otherwise, owner names are extracted from the export file.
FROMUSER and TOUSER are specified to change the ownership of database objects.
If you do not specify FROMUSER and TOUSER, all database objects (such as tables and indexes) are created under the same user as in the source database. Those users must already exist in the target database. If not, import returns an error indicating that some required users do not exist in the target database.
You can use FROMUSER and TOUSER to change the owners of objects. In this example we specify FROMUSER=(dcranney,jfee) and TOUSER=(smith, williams). 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 successfully executes, all tablespaces in the set being copied remain in read-only mode. Check the import logs to ensure 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 Import utility as follows:
IMP PARFILE='par.f'
The file par.f file contains the following:
TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('/db/sales_jan','/db/sales_feb',...) TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee) FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)
ALTER TABLESPACE sales_1 READ WRITE ALTER TABLESPACE sales_1 READ WRITE
| See Also:
Oracle9i Database Utilities for information about using the Import utility |
Most objects, whether data in a tablespace or structural information associated with the tablespace, behave normally after being transported to a different database. However, the following objects are exceptions:
When a database contains tablespaces that have been plugged in (from other databases), the ROWIDs in that database are no longer unique. A ROWID is guaranteed unique only within a table.
REFs are not checked when Oracle determines if a set of tablespaces is self-contained. As a result, a plugged-in tablespace may contain dangling REFs. Any query following dangling REFs returns a user error.
Privileges are transported if you specify GRANTS=y during export. During import, some grants may fail. For example, the user being granted a certain right may not exist, or a role being granted a particular right may not exist.
You cannot move a partitioned table using transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces. You must ensure that all partitions in a table are in the tablespace set, or exchange the partitions into tables before copying the tablespace set. However, you should note that exchanging partitions with tables invalidates the global index of the partitioned table.
At the target database, you can exchange the tables back into partitions if there is already a partitioned table that exactly matches the column in the target database. If all partitions of that table come from the same foreign database, the exchange operation is guaranteed to succeed. If they do not, in rare cases, the exchange operation may return an error indicating that there is a data object number conflict.
If you receive a data object number conflict error when exchanging tables back into partitions, you can move the offending partition using the ALTER TABLE MOVE PARTITION statement. After doing so, retry the exchange operation.
If you specify the WITHOUT VALIDATION option of the exchange statement, the statement returns immediately because it only manipulates structural information. Moving partitions, however, may be slow because the data in the partition can be copied.
| See Also:
"Transporting and Attaching Partitions for Data Warehousing" for an example of transporting a partitioned table |
A transportable tablespace set can contain:
LOBsIf the tablespace set contains a pointer to a BFILE, you must move the BFILE and set the directory correctly in the target database.
You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0 compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up using the built-in PL/SQL routine DBMS_AQADM.START_QUEUE.
You can transport regular indexes, domain indexes, and bitmap indexes. When the transportable set fully contains a partitioned table, you can also transport the global index of the partitioned table.
Function-based indexes are not supported. If they exist in a tablespace, you must drop them before you can transport the tablespace.
Triggers are exported without a validity check. In other words, Oracle does not verify that the trigger refers only to objects within the transportable set. Invalid triggers cause a compilation error during the subsequent import.
Transporting materialized views or replication structural information is not supported. When transporting a tablespace, the materialized view or replication metadata associated with the tables in the tablespace is not exported and, thus, is not be available to the target database.
The following are some possible applications 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. In fact, 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's worth 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 option. 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's possible that the exchange operation may fail. For example, if the jan98 partition of sales did not come from the same staging database, the above 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:
IMP TRANSPORT_TABLESPACE=y DATAFILES='D:\catalog.f' FILE='D:\expdat.dmp'
You can remove the CD while the database is still up. Subsequent queries to the tablespace return an error indicating that Oracle 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, Oracle 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, Oracle 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:
You can make the disk accessible by multiple computers in several ways. You can use either a cluster file system or raw disk, because that is required by Oracle9i Real Application Clusters. Because Oracle reads only these type of datafiles on shared disk, you can also use NFS. Be aware, however, 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.
| See Also:
Oracle9i Data Warehousing Guide for more details |
You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).
| See Also:
Oracle9i User-Managed Backup and Recovery Guide for information about how to perform TSPITR using transportable tablespaces |
The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.
The following are just a few examples of using some of these views.
| See Also:
Oracle9i Database Reference for complete description 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/RBS01.DBF 1536 RBS /U02/ORACLE/IDDB3/SYSTEM01.DBF 6586 SYSTEM /U02/ORACLE/IDDB3/TEMP01.DBF 6400 TEMP /U02/ORACLE/IDDB3/TESTTBS01.DBF 6400 TESTTBS /U02/ORACLE/IDDB3/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.
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|