Manual segment management

Until the tablespace metadata is imported, the local instance has no way of knowing the desired tablespace names. The source and destination platforms are optional. RMAN determines the source platform by examining the datafile, and the target platform defaults to the platform of the host running the conversion.

To import a tablespace set, perform the following tasks:. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned by dcranney in the source database will be owned by smith in the target database after the tablespace set is imported. Similarly, objects owned by jfee in the source database will be owned by williams in the target database. In this case, the target database is not required to have users dcranney and jfee , but must have users smith and williams.

After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. Check the import logs to ensure that no error has occurred. When dealing with a large number of datafiles, specifying the list of datafile names in the statement line can be a laborious process.

It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:. Typical enterprise data warehouses contain one or more large fact tables. These fact tables can be partitioned by date, making the enterprise data warehouse a historical database. You can build indexes to speed up star queries. Oracle recommends that you build local indexes for such historically partitioned tables to avoid rebuilding global indexes every time you drop the oldest partition from the historical database.

Suppose every month you would like to load one month of data into the data warehouse. There is a large fact table in the data warehouse called sales , which has the following columns:. 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 , and you would like to load the July sales data into the partitioned table. In the data warehouse, add a partition to the sales table for the July sales data. This also creates another partition for the local non-prefixed index:. This statement places the July sales data into the new partition jul98 , attaching the new data to the partitioned table. This statement should return immediately, because it only operates on the structural information and it simply switches database pointers.

Otherwise, the statement goes through all the new data in the new partition in an attempt to validate the range of that partition. If all partitions of the sales table came from the same staging database the staging database is never destroyed , the exchange statement always succeeds.

In general, however, if data in a partitioned table comes from different databases, it is possible that the exchange operation may fail. For example, if the jan98 partition of sales did not come from the same staging database, the preceding exchange operation can fail, returning the following error:. 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 add the CD contents to an existing database without having to copy the datafiles from the CD to disk storage.

You can import a transportable set with datafile catalog. You can remove the CD while the database is still up. Subsequent queries to the tablespace return an error indicating that the database cannot open the datafiles on the CD.

However, operations to other parts of the database are not affected. Placing the CD back into the drive makes the tablespace readable again. Removing the CD is the same as removing the datafiles of a read-only tablespace. 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. The following are two scenarios for mounting the same tablespace read-only on multiple databases:. The tablespace originates in a database that is separate from the databases that will share the tablespace.

You generate a transportable set in the source database, put the transportable set onto a disk that is accessible to all databases, and then import the metadata into each database on which you want to mount the tablespace.

It is assumed that the datafiles are already on a shared disk. In the database where the tablespace already exists, you make the tablespace read-only, generate the transportable set, and then import the tablespace into the other databases, leaving the datafiles in the same location on the shared disk.

You can make a disk accessible by multiple computers in several ways. You can use either a cluster file system or raw disk. You can also use network file system NFS , but be aware that if a user queries the shared tablespace while NFS is down, the database will hang until the NFS operation times out.

Later, you can drop the read-only tablespace in some of the databases. Doing so does not modify the datafiles for the tablespace. Thus, the drop operation does not corrupt the tablespace.

You can use the transportable tablespace feature to migrate a database to a different platform by creating a new database on the destination platform and performing a transport of all the user tablespaces.

You must either create these objects manually on the destination database, or use Data Pump to transport the objects that are not moved by transportable tablespace. The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:. 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.

Guidelines for Managing Tablespaces Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections: Using Multiple Tablespaces Assigning Tablespace Quotas to Users See Also: Oracle Database Concepts for a complete discussion of database structure, space management, tablespaces, and datafiles.

Using Multiple Tablespaces Using multiple tablespaces allows you more flexibility in performing database operations. Back up individual tablespaces. Assigning Tablespace Quotas to Users Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota space allowance or limit in the tablespace intended to hold the object segment.

See Also: Oracle Database Security Guide for information about creating users and assigning tablespace quotas. Creating Tablespaces Before you can create a tablespace, you must create a database to contain it.

Locally Managed Tablespa ces Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits: Fast, concurrent space operations. Enhanced performance Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.

Note: When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management the extent bitmap or space header segment which are part of user space. In that case, the size specified for the datafile must be larger at least one block plus space for the bitmap than 1MB.

Specifying Segment Space Management in Locally Managed Tablespaces In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. Altering a Locally Managed Tablespace You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management. Bigfile Tablespaces A bigfile tablespace is a tablespace with a single, but very large up to 4G blocks datafile.

The benefits of bigfile tablespaces are the following: A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. Temporary Tablespaces A temporary tablespace contains transient data that persists only for the duration of the session. You cannot explicitly create objects in a temporary tablespace. Note: The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace. See "Creating a Temporary Table" for more information.

Creating a Locally Managed Temporary Tablespace Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Note: On some operating systems, the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used.

Please refer to your operating system documentation to determine whether the database allocates tempfile space in this way on your system. Creating a Bigfile Temporary Tablespace Just as for regular tablespaces, you can create single-file bigfile temporary tablespaces. Note: You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. Multiple Temporary Tablespaces: Using Tablespace Groups A tablespace group enables a user to consume temporary space from multiple tablespaces.

A tablespace group has the following characteristics: It contains at least one tablespace. It shares the namespace of tablespaces, so its name cannot be the same as any tablespace. See Also: Oracle Database Security Guide for more information about assigning a temporary tablespace or tablespace group to a user. Controlling the Writing of Redo Records For some database operations, you can control whether the database generates redo records.

Altering Tablespace Availability You can take an online tablespace offline so that it is temporarily unavailable for general use. See Also: "Altering Datafile Availability" for information about altering the availability of individual datafiles within a tablespace. Taking Tablespaces Offline You may want to take a tablespace offline for any of the following reasons: To make a portion of the database unavailable while allowing normal access to the remainder of the database To perform an offline tablespace backup even though a tablespace can be backed up while online and in use To make an application and its group of tables temporarily unavailable while updating or maintaining the application To rename or relocate tablespace datafiles See "Renaming and Relocating Datafiles" for details.

When a tablespace is taken offline, the database takes all the associated files offline. You cannot take the following tablespaces offline: SYSTEM The undo tablespace Temporary tablespaces Before taking a tablespace offline, consider altering the tablespace allocation of any users who have been assigned the tablespace as a default tablespace.

No datafile in the tablespace can be currently offline as the result of a write error. Bringing Tablespaces Online You can bring any tablespace in an Oracle Database online whenever the database is open.

Using Read-Only Tablespaces Making a tablespace read-only prevents write operations on the datafiles in the tablespace. Note: Making a tablespace read-only cannot in itself be used to satisfy archiving or data publishing requirements, because the tablespace can only be brought online in the database in which it was created. However, you can meet such requirements by using the transportable tablespace feature, as described in "Transporting Tablespaces Between Databases".

Before you can make a tablespace read-only, the following conditions must be met. If this parameter is set to a value less than 8. See Also: Depending upon your backup and recovery strategy, refer to one of the following books for information about backing up and recovering a database with read-only datafiles: Oracle Database Backup and Recovery Basics Oracle Database Backup and Recovery Advanced User's Guide.

Alter the tablespace to make it read-only. Take the tablespace offline. Bring the tablespace back online. The server parameter file was used to start up the database. Dropping Tablespaces 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. Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable.

Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.

See Also: "Dropping Datafiles". This view lists the following information about the occupants of the SYSAUX tablespace: Name of the occupant Occupant description Schema name Move procedure Current space usage View information is maintained by the occupants. Provides an option to select a slightly abbreviated dump, which includes segment header and includes bitmap block summaries, without percent-free states of each block. 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. Scenario 2: Dropping a Corrupted Segment You cannot drop a segment because the bitmap has segment blocks marked "free". After choosing the object to be sacrificed, in this case say, table t1 , perform the following tasks: Make a list of all objects that t1 overlaps.

There are no rollback segments in the dictionary-managed tablespace. Modify or switch between automatic or Manual space management Note: There is no way to convert the tablespace from automatic to manually or vise versa. Simple method is create a new tablespace with desired options and move the objects from the old tablespace to new tablespace. With following query you find the objects present in your tablespace and commands of movement: Note: Specify the tablespace name from which you choose to move means source tablespace.

Option 2: Use Export or import utility to move the data from one tablespace to other tablespace. You are commenting using your WordPress. You are commenting using your Google account. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. Restrictions on Undo Tablespaces Undo tablespaces are subject to the following restrictions:. You cannot create database objects in this tablespace. It is reserved for system-managed undo data. Creating a Temporary Tablespace: Example This statement shows how the temporary tablespace that serves as the default temporary tablespace for database users in the sample database was created:.

If we assume that the default database block size is 2K, and that each bit in the map represents one extent, then each bit maps 2, blocks. The following example sets the default location for datafile creation and then creates a tablespace with an Oracle-managed tempfile in the default location. The tempfile is M and is autoextensible with unlimited maximum size. These are the default values for Oracle-managed files:.

If the tablespace group does not already exist, then Oracle Database creates it during execution of this statement:. When more space is required, kilobyte extents will be added up to a maximum size of megabytes:. This statement creates a locally managed tablespace in which every extent is K and each bit in the bit map describes 64 blocks.

Specifying Segment Space Management for a Tablespace: Example The following example creates a tablespace with automatic segment-space management:. Creating Oracle-managed Files: Examples The following example sets the default location for datafile creation and creates a tablespace with a datafile in the default location. The datafile is M and is autoextensible with an unlimited maximum size:. The following example creates a tablespace with an Oracle-managed datafile of M that is not autoextensible:.

Note: Oracle strongly recommends that you run your database in automatic undo management mode. For more information, please refer to Oracle Database Administrator's Guide. Note: Media recovery does not recognize tempfiles. Note: On some operating systems, Oracle does not allocate space for a tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used.

To avoid potential problems, before you create or resize a tempfile, ensure that the available disk space exceeds the size of the new tempfile or the increased size of a resized tempfile. The excess space should allow for anticipated increases in disk space use by unrelated operations as well.

Then proceed with the creation or resizing operation. Please refer to Oracle Database Administrator's Guide for information on when to use this setting. Note: After you have specified extent management with this clause, you can change extent management only by migrating the tablespace. Remember, the freelist structure with ASSM has changed from one-way linked lists to bitmap freelists.

References: MOSC note: If performance is critical, multiple free lists can be used to improve concurrent access, possibly at the expense of additional space used. There are three main types of freelists used to manage segment space; the Master Freelist, the Process Freelist and the Transaction Freelist. Each one controls its own set of datablocks. When using multiple process freelists, the amount of unused space within datablocks can increase.

The reason for this is a user process maps onto a particular process freelist using an algorithm described below , and will not search other process freelists for space if none is found within its own. If a large number of blocks are linked in a particular process freelist, and another user process has no free blocks on its process freelist or no free blocks exist on the master freelist, the process may request movement of the HWM or creation of a new extent.

This could leave free blocks on the other process freelist unused.? If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated. Free lists are lists of data blocks that have space available for inserting rows. 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. If such attributes should be specified, they are ignored. If you like Oracle tuning, see the book " Oracle Tuning: The Definitive Reference ", with pages of tuning tips and scripts.