Release 8.1.5
A67772-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 8.1.5 A67772-01 |
|
This chapter describes the various aspects of tablespace management, and includes the following topics:
Before working with tablespaces of an Oracle database, familiarize yourself with the guidelines provided in the following sections:
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 advantages and decide how many tablespaces you will need for your database design.
When you create a new 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. However, 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.
|
Note: If you do not specify the default storage parameters for a new tablespace, the default storage parameters of Oracle become the tablespace's default storage parameters. |
See Also: For information about estimating the sizes of objects, see Chapters 11 through 17.
Grant to users who will be creating tables, clusters, snapshots, 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.
See Also: To learn more about assigning tablespace quotas to database users, see "Assigning Tablespace Quotas".
The steps for creating tablespaces vary by operating system. 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. However, on some operating systems, you must create the datafiles before installation.
The first tablespace in any database is always the SYSTEM tablespace. Therefore, the first datafiles of any database are automatically allocated for the SYSTEM tablespace during database creation.
You might create a new tablespace for any of the following reasons:
To increase the total size of the database you can alternatively add a datafile to an existing tablespace, rather than adding a new tablespace.
|
Note: No data can be inserted into any tablespace until the current instance has acquired at least two rollback segments (including the SYSTEM rollback segment). |
To create a new tablespace, use the SQL statement CREATE TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace.
As an example, let's create the tablespace RB_SEGS (to hold rollback segments for the database), with the following characteristics:
The following statement creates the tablespace RB_SEGS:
CREATE TABLESPACE rb_segs DATAFILE 'datafilers_1' SIZE 50M DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0) OFFLINE;
If you do not fully specify filenames when creating tablespaces, the corresponding datafiles are created in the ORACLE_HOME/dbs directory.
See Also: See your operating system-specific Oracle documentation for information about initially creating a tablespace.
For more information about adding a datafile, see "Creating and Adding Datafiles to a Tablespace".
For more information about the CREATE TABLESPACE statement, see the Oracle8i SQL Reference.
Typically, tablespaces are "dictionary mapped," which means that such tablespaces rely on SQL dictionary tables to track space utilization. Locally managed tablespaces, on the other hand, use bit maps (instead of SQL dictionary tables) to track used and free space.
Creating and using locally managed tablespaces offers you the following benefits:
You allocate and deallocate space by changing the bit values (0 to 1 for allocation, 1 to 0 for deallocation).
Necessary information is stored in segment headers and bit map blocks.
The following statement creates a locally managed tablespace named TBS_1; every extent is 128K, and each bit in the bit map describes 64 blocks:
CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' BITMAP ALLOCATION UNIFORM SIZE 128K;
See Also: For detailed syntax on creating locally managed tablespaces, see the Oracle8i SQL Reference.
You can create a database with a locally managed SYSTEM tablespace. However, rollback segments for this database must also be created in uniform-managed locally managed tablespaces. A locally managed SYSTEM tablespace is always system-managed. Also, you cannot later revert to a version of Oracle earlier than 8.1.
See Also: For more information about creating a database with a locally managed SYSTEM tablespace, see the Oracle8i SQL Reference.
If you wish to improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, you can create temporary tablespaces.
Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist in every instance that performs sort operations within a given tablespace. You cannot store permanent objects in a temporary tablespace. You can view the allocation and deallocation of space in a temporary tablespace sort segment via the V$SORT_SEGMENT table.
To identify a tablespace as temporary during tablespace creation, issue the following statement:
CREATE TABLESPACE tablespace TEMPORARY;
To identify a tablespace as temporary in an existing tablespace, issue the following statement:
ALTER TABLESPACE tablespace TEMPORARY;
|
Note: You can take temporary tablespaces offline. Returning temporary tablespaces online does not affect their temporary status. |
See Also: For more information about the CREATE TABLESPACE and ALTER TABLESPACE statements, see the Oracle8i SQL Reference.
For more information about V$SORT_SEGMENT, see the Oracle8i Reference.
For more information about Oracle space management, see Oracle8i Concepts.
Temporary datafiles differ from permanent datafiles in that they do not appear in the DBA_DATA_FILES view. Instead, they appear in the DBA_TEMP_FILES view, which is similar to DBA_DATA_FILES view except that it contains information about temporary datafiles. In SQL, files belonging to temporary tablespaces are also identified as TEMPFILES, rather than DATAFILES.
See Also: For more information about temporary datafiles and DBA_TEMP_FILES, see the Oracle8i Reference.
If you wish to allocate space that can contain schema objects for the duration of a session in the database, you can create a locally managed temporary tablespace.
You must have the CREATE TABLESPACE system privilege to create a locally managed temporary tablespace.
The following statement creates a temporary tablespace in which each extent is 16M. The default database block size is 2M; each bit in the map represents one extent, thus each bit maps 8,000 blocks.
CREATE TEMPORARY TABLESPACE tbs_1 TEMPFILE 'file_1.f' BITMAP ALLOCATION UNIFORM SIZE 16M;
See Also: For more information about creating a locally managed temporary tablespace, see the Oracle8i SQL Reference.
You can alter or add a datafile (or temporary file) to a locally managed temporary tablespace.
The following statement adds files to a locally managed temporary tablespace:
ALTER TABLESPACE tbs_1 ADD TEMPFILE 'file_1.f';
The following statements take offline and bring online temporary files:
ALTER DATABASE TEMPFILE 'temp_file_1.f' OFFLINE; ALTER DATABASE TEMPFILE 'temp_file_1.f' ONLINE;
The following statement resizes temporary file TEMP_FILE_1.F to 12K:
ALTER DATABASE TEMPFILE 'temp_file_1.f' RESIZE 12K;
The following statement drops a temporary file:
ALTER DATABASE TEMPFILE 'temp_file_1.f' DROP;
See Also: For details and restrictions about statements used to alter locally managed temporary tablespaces, see the Oracle8i SQL Reference.
This section describes aspects of managing tablespace allocation, and includes the following topics:
You can change the default storage parameters of a tablespace to change the default specifications for future objects created in the tablespace. To change the default storage parameters for objects subsequently created in the tablespace, use the SQL statement ALTER TABLESPACE. Also, to alter the default storage parameters of a tablespace, you must have the ALTER TABLESPACE system privilege.
ALTER TABLESPACE users DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 20 PCTINCREASE 50);
New values for the default storage parameters of a tablespace affect only future extents allocated for the segments within the tablespace.
Space for tablespace segments is managed using extents, which are made up of a specific number of contiguous data blocks. The free extent closest in size to the required extent is used when allocating new extents to a tablespace segment. Thus, a larger free extent can be fragmented, or smaller contiguous free extents can be coalesced into one larger free extent (see Figure 9-1). However, continuous allocation and deallocation of free space fragments your tablespace and makes allocation of larger extents more difficult. By default, SMON (system monitor) processes incrementally coalesce the free extents of tablespaces in the background. If desired, you can disable SMON coalescing.
If you find that fragmentation of space is high (contiguous space on your disk appears as non-contiguous), you can coalesce your free space in a single space transaction. After every eight coalesces the space transaction commits and other transactions can allocate or deallocate space. You must have ALTER TABLESPACE privileges to coalesce tablespaces. You can coalesce all available free space extents in a tablespace into larger contiguous extents on a per tablespace basis by using the following command:
ALTER TABLESPACE tablespace COALESCE;
You can also use this command to supplement SMON and extent allocation coalescing, thereby improving space allocation performance in severely fragmented tablespaces. Issuing this command does not effect the performance of other users accessing the same tablespace. Like other options of the ALTER TABLESPACE statement, the COALESCE option is exclusive; when specified, it should be the only option.
To display statistics about coalesceable extents for tablespaces, you can view the DBA_FREE_SPACE_COALESCED view. You can query this view to determine if you need to coalesce space in a particular tablespace.
See Also: For information about the contents of DBA_FREE_SPACE_COALESCED, see the Oracle8i Reference.
You can bring an offline tablespace online to make the schema objects within the tablespace available to database users. Alternatively, you can take an online tablespace offline while the database is open, so that this portion of the database is temporarily unavailable for general use but the rest is open and available. This section includes the following topics:
You can bring any tablespace in an Oracle database online whenever the database is open. The only exception is that the SYSTEM tablespace must always be online because the data dictionary must always be available to Oracle. A tablespace is normally online so that the data contained within it is available to database users.
To bring an offline tablespace online while the database is open, use the SQL statement ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to bring a tablespace online.
The following statement brings the USERS tablespace online:
ALTER TABLESPACE users ONLINE;
You may wish to take a tablespace offline for any of the following reasons:
To take an online tablespace offline while the database is open, use the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to take a tablespace offline.
You can specify any of the following priorities when taking a tablespace offline:
Take a tablespace offline temporarily only when you cannot take it offline normally; in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Take a tablespace offline immediately only after trying both the normal and temporary options.
The following example takes the USERS tablespace offline normally:
ALTER TABLESPACE users OFFLINE NORMAL;
See Also: Before taking an online tablespace offline, verify that the tablespace contains no active rollback segments. For more information see "Taking Rollback Segments Offline".
This section describes issues related to making tablespaces read-only, and includes the following topics:
Making a tablespace read-only prevents further write operations on the datafiles in the tablespace. After making the tablespace read-only, you should back it up.
Use the SQL statement ALTER TABLESPACE to change a tablespace to read-only. You must have the ALTER TABLESPACE system privilege to make a tablespace read-only. The following statement makes the FLIGHTS tablespace read-only:
ALTER TABLESPACE flights READ ONLY
After a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE RENAME.
A read-only tablespace is neither online nor offline. Issuing the ALTER TABLESPACE statement with the ONLINE or OFFLINE option does not change the read-only state of the tablespace; rather, it causes all of the datafiles in the tablespace to be brought online or offline.
The ALTER TABLESPACE...READ ONLY statement waits until active transactions are complete before performing the read-only operation. Thus, you do not have to wait for transactions to complete before making a tablspace read-only.
Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.
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 the rollback segments of a read-only tablespace are not accessible, it is recommended that you drop the rollback segments before you make a tablespace read-only.
For better performance while accessing data in a read-only tablespace, you might want to 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 will ensure 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.
See Also: For more information about read-only tablespaces, see Oracle8i Concepts.
Whenever you create a tablespace, it is both readable and writeable. To change a read-only tablespace back to a read-write tablespace, use the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to change a read-only tablespace to a read-write tablespace. The following command makes the FLIGHTS tablespace writeable:
ALTER TABLESPACE flights READ WRITE;
Making a read-only tablespace writeable updates the control file for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.
To issue this command, all of the datafiles in the tablespace must be online. Use the DATAFILE ONLINE option of the ALTER DATABASE command to bring a datafile online. The V$DATAFILE view lists the current status of a datafile.
To create a read-only tablespace on a WORM (Write Once Read Many) device when you have read-only files that do not require updating:
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, only the file pointers in the control files of the associated database are dropped. The datafiles that constituted the dropped tablespace continue to exist. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate commands of your operating system after completing this procedure.
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. For simplicity, take the tablespace offline before dropping it.
After a tablespace is dropped, the tablespace's entry remains in the data dictionary (see the DBA_TABLESPACES view), but the tablespace's status is changed to INVALID.
To drop a tablespace, use the SQL command DROP TABLESPACE. 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 check the Including Contained Objects checkbox. If the tablespace contains any tables with primary or unique keys referenced by foreign keys of tables in other tablespaces and you want to cascade the drop of the FOREIGN KEY constraints of the child tables, select the Cascade Drop of Integrity Constraints checkbox to drop the tablespace.
Use the CASCADE CONSTRAINTS option of the DROP TABLESPACE statement to cascade the drop of the FOREIGN KEY constraints in the child tables.
See Also: For more information about taking tablespaces offline, see "Taking Tablespaces Offline".
For more information about the DROP TABLESPACE statement, see the Oracle8i SQL Reference.
The DBMS_SPACE_ADMIN package provides administrators with defect diagnosis and repair functionality. The following scenarios describe typical situations in which you can use the DBMS_SPACE_ADMIN package to diagnose and resolve problems.
The DBMS_SPACE_ADMIN package contains the following procedures:
See Also: For details about these procedures, see the Oracle8i Supplied Packages Reference.
The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are marked "free" in the bit map, but no overlap between segments was reported.
In this scenario, perform the following tasks:
You cannot drop a segment because the bit map has segment blocks marked "free." The system has automatically marked it corrupt.
In this scenario, perform the following tasks:
The TABLESPACE_VERIFY procedure has reported some overlapping. Some of the real data must be sacrificed based on previous internal errors.
After choosing the object to be sacrificed, say table T1, perform the following tasks:
A set of bitmap blocks has media corruption.
In this scenario, perform the following tasks:
See Also: For more information about the DBMS_SPACE_ADMIN package, see the Oracle8i Supplied Packages Reference.
This section describes how to transport tablespaces between databases, and includes the following topics:
You can use transportable tablespaces to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. Transporting tablespaces is particularly useful for:
Moving data via transportable tablespaces can be much faster than performing either an import/export 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.
To move or copy a set of tablespaces you must perform the following tasks:
A transportable 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 O/S copying utility, ftp, or publishing on CDs)
Invoke Import to plug the set of tablespaces into the target database.
See Also: For more details about transportable tablespaces and their use in data marts and data warehousing, see Oracle8i Concepts.
For information about using transportable tablespaces to perform media recovery, see the Oracle8i Backup and Recovery Guide.
For information about transportable tablespace compatibility issues (between different Oracle releases), see Oracle8i Migration.
Be aware of the following limitations as you plan for and use transportable tablespaces:
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. For example, if there is an index in the set of tablespaces for a table that is outside of the set of tablespaces, then the set of tablespaces is not self-contained.
The tablespace set you wish to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you wish 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 determine 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. Some examples of self contained tablespace violations follow:
To determine whether a set of tablespaces is self-contained, you can invoke a built-in PL/SQL procedure, giving it the list of the tablespace names and indicating that you wish to transport referential integrity constraints. For example, suppose you want to determine whether tablespaces ts1 and ts2 are self-contained (with constraints taken into consideration). You can issue the following command:
execute dbms_tts.transport_set_check('ts1,ts2', TRUE)
Here, transport_set_check is a PL/SQL routine in the PL/SQL package DBMS_TTS, with the following prototype:
PROCEDURE transport_set_check(ts_list IN varchar2, incl_constraints IN boolean) ts_list - list of tablespace names separated by comma incl_constraints - TRUE if one would like to take constraints into consideration. FALSE otherwise.
After invoking this PL/SQL routine, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view will be empty. If the set of tablespaces is not self-contained, this view lists all the violations. For example, suppose there are two violations: a foreign key constraint, dept_fk, across the tablespace set boundary, and a partitioned table, sales, that is partially contained in the tablespace set. Querying TRANSPORT_SET_VIOLATIONS results in the following:
select * from transport_set_violations; VIOLATIONS ------------------------------------ Constraint DEPT_FK between table JIM.EMP in tablespace FOO and table JIM.DEPT in tablespace OTHER Partitioned table JIM.SALES is partially contained in the transportable set
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: For more information about REFs, see the Oracle8i Application Developer's Guide - Fundamentals.
After identifying the self-contained set of tablespaces you want to transport, generate a transportable set by performing the following tasks:
ALTER TABLESPACE sales READ ONLY;
EXP TRANSPORT_TABLESPACE=y TABLESPACES=sales_1,sales_2 TRIGGERS=y/n CONSTRAINTS=y/n GRANTS=y/n FILE=expdat.dmp
|
Note: Although the Export utility is used, only data dictionary structural information is exported. Hence, this operation is even quicker for a large tablespace. |
When prompted, connect as "sys as sysdba."
You must always specify TABLESPACES. The FILE parameter specifies the name of the structural information export file to be created.
If you set TRIGGERS=n, triggers are not exported. If you set TRIGGERS=y, triggers are exported without a validity check. Invalid triggers cause compilation errors during the subsequent import.
If you set GRANTS=y, all grants on the exported tables are exported too; otherwise, all GRANTS are ignored.
If you set CONSTRAINTS=y, referential integrity constraints are exported; otherwise, referential integrity constraints are ignored.
The default setting for all of these options is 'y.'
ALTER TABLESPACE sales_1 READ WRITE;
If the tablespace sets being transported are not self-contained, export will fail and indicate that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.
Transport both the datafiles and the export file to a place accessible to the target database. You can use any facility for copying flat files (for example, an O/S copying utility, ftp, or publishing on CDs).
To plug in a tablespace set, perform the following tasks:
IMP TRANSPORT_TABLESPACE=y DATAFILES='/db/sales_jan','/db/sales_feb',...fn TABLESPACES=sales_1,sales_2,... TTS_OWNERS=dcranney,jfee FROMUSER=dcranney,jfee TOUSER=smith,williams FILE=expdat.dmp
When prompted, connect as "sys as sysdba."
Following are two more examples:
IMP TRANSPORT_TABLESPACE=y DATAFILES='(/db/staging1.f,/db/staging2.f)' IMP TRANSPORT_TABLESPACE=y DATAFILES='/db/staging.f' TABLESPACES=jan OWNERS=smith
You must specify DATAFILES.
TABLESPACES,TTS_OWNERS, FROMUSER and TOUSER are optional. The FILE parameter specifies the name of the structural information export file.
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.
If you do not specify FROMUSER and TOUSER, all database objects (such as tables and indexes) will be created under the same user as in the source database. Those users must already exist in the target database. If not, import will return 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. For example, if you specify FROMUSER=dcranney,jfee 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 does not have 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. You should check the import logs to ensure no error has occurred. At this point, you can issue the ALTER TABLESPACE...READ WRITE statement to place the new tablespaces in read-write mode.
When dealing with a large number of datafiles, specifying the list of datafile names in the command line can be a laborious process; it may even exceed the command line limit. In this situation, you may use an import parameter file. For example, one of the commands in this step is equivalent to the following:
IMP PARFILE='par.f'
The file par.f contains the following:
TRANSPORT_TABLESPACE=y DATAFILES=/db/staging.f TABLESPACES=jan TT_OWNERS=smith
To transport a tablespace between databases, both the source and target database must be running Oracle8i, with the init.ora compatibility parameter set to 8.1.
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 via 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 conflict number 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 will return immediately because it only manipulates structural information. Moving partitions, however, may be slow because the data in the partition can be copied. See "Transporting and Attaching Partitions for Data Warehousing: Example" for an example using partitioned tables.
A transportable tablespace set can contain:
If 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 via the built-in PL/SQL routine dbms_aqadm.start_queue().
You can transport regular 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 and domain 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 will cause a compilation error during the subsequent import.
Transporting snapshot or replication structural information is not supported. If a table in the tablespace you want to transport is replicated, you must drop the replication structural information and convert the table into a normal table before you can transport the tablespace.
Typical enterprise data warehouses contain one or more large fact tables. These fact tables may 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 wish 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 indexes 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; 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 will go 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 will always succeed. 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 may 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 in to an existing database without having to copy the datafiles from the CD to disk storage. For example, suppose on an 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 will return an error indicating that Oracle cannot open the datafiles on the CD. However, operations to other parts of the datafile 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 for a read-only tablespace. If you shut down and restart the database, Oracle will indicate that it cannot find the removed datafile and will 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 via several ways. You may use either a clustered file system or raw disk, as that is required by Oracle Parallel Server. Because Oracle will only read 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 may hang until the NFS operation times out.
Later, you can drop the read-only tablespace in some of the databases. Doing so will not modify the datafiles for the tablespace; thus the drop operation will 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 via the transportable tablespace procedures described in this chapter.
See Also: For more details, see the Oracle8i Backup and Recovery Guide.
You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).
See Also: For information about how to perform TSPITR using transportable tablespaces, see the Oracle8i Backup and Recovery Guide.
The following data dictionary views provide useful information about tablespaces of a database:
The following examples illustrate how to use the views not already illustrated in other chapters of this manual. They assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally.
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 sys.dba_tablespaces; TABLESPACE INITIAL_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE ---------- ----------- -------- ------- ------- ------------ SYSTEM 10240000 10240000 1 99 50 USERS 10240000 10240000 1 99 50
To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:
SELECT file_name, bytes, tablespace_name FROM sys.dba_data_files; FILE_NAME BYTES TABLESPACE_NAME ------------ ---------- ------------------- filename1 10240000 SYSTEM filename2 10240000 USERS filename3 20480000 USERS
To see the amount of space available in the free extents of 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 sys.dba_free_space WHERE tablespace_name = 'SYSTEM' GROUP BY tablespace_name, file_id; TABLESPACE FILE_ID PIECES MAXIMUM MINIMUM AVERAGE TOTAL ---------- ------- ------ ------- ------- ------- ------ SYSTEM 1 2 2928 115 1521.5 3043
TOTAL shows the amount of free space in each tablespace, PIECES shows the amount of fragmentation in the datafiles of the tablespace, and MAXIMUM shows the largest contiguous area of space. 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.
