Method I. Method II. Method III. Method IV. Note: It is advisable to realize this operations during off hours. In this situation there are 2 scripts depend on oracle versions to find who use the temporary segments.
Method I :. Drop and recreate temporary tablespace. Method II :. Drop and recreate temporary tablespace when TEMP is default temporary tablespace. We reclaim 26M back leaving only 1M for temporary tablespace.
Now let us run following command to resize the temporary tablespace and increase its size. Tablespace is now resized to M.
This time we will shrink tablespace using shrink tempfile command. We can also retain the specific size of tablespace by using KEEP clause. IF temporary table contains more than one temp file and SHRINK SPACE command is issued to shrink temporary tablespace, Oracle may shrink one file completely and shrink other file for remaining amount rather than shrinking all the files equally.
We can shrink it back to its original size once that specific job is done. We cannot perform any one of this command for dictionary managed or permanent tablespaces.
We will run into the following error if we try to perform such an operation:. This entry was posted on March 13, at am and is filed under Oracle. You can follow any responses to this entry through the RSS 2. You can leave a response , or trackback from your own site. Figure Illustration of Mapping Structures. The configuration ID captures the version information associated with elements or files.
The vendor library provides the configuration ID and updates it whenever a change occurs. Without a configuration ID, there is no way for the database to tell whether the mapping has changed.
The configuration IDs are not persistent across instance shutdown. The database is only capable of refreshing the mapping information while the instance is up. This section discusses how to use the Oracle Database file mapping interface. Ensure that a valid filemap. The filemap. FMON requires that a filemap. Otherwise, it will not start successfully.
Note that the ordering of the libraries in this file is extremely important. The libraries are queried based on their order in the configuration file. The file mapping service can be even started even if no mapping libraries are available. In this case, the mapping service is constrained in the sense that new mapping information cannot be discovered. Only restore and drop operations are allowed in such a configuration. The instance does not have to be shut down to set this parameter.
You have two options:. In a cold startup scenario, the Oracle Database is just started and no mapping operation has been invoked yet. This forces all of the mapping information in the SGA to be flushed to disk. The various procedures available to you are described in the following table.
Brief descriptions of these views are presented here. This table displays the hierarchical arrangement of storage containers for objects. Each row in the table represents a level in the hierarchy. The following examples illustrates some of the powerful capabilities of the Oracle Database file mapping feature.
This includes:. The following is the result of the query. The following data dictionary views provide useful information about the datafiles of a database:. Guidelines for Managing Datafiles Datafiles are physical files of the operating system that store the data of all logical structures in the database.
Note: Tempfiles are a special class of datafiles that are associated only with temporary tablespaces. Information in this chapter applies to both datafiles and tempfiles except where differences are noted. This file number can be used in many SQL statements that reference datafiles in place of using the file name.
Relative Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number.
However, when the number of datafiles in a database exceeds a threshold typically , the relative file number differs from the absolute file number. Note: One means of controlling the number of datafiles in your database and simplifying their management is to use bigfile tablespaces.
Bigfile tablespaces comprise a single, very large datafile and are especially useful in ultra large databases and where a logical volume manager is used for managing operating system files. Bigfile tablespaces are discussed in "Bigfile Tablespaces". Consider Possible Limitations When Adding Datafiles to a Tablespace You can add datafiles to traditional smallfile tablespaces, subject to the following limitations: Operating systems often impose a limit on the number of files a process can open simultaneously.
Operating systems impose limits on the number and size of datafiles. Consider the Performance Impact The number of datafiles contained in a tablespace, and ultimately the database, can have an impact upon performance.
Determine the Size of Datafiles When creating a tablespace, you should estimate the potential size of database objects and create sufficient datafiles. Place Datafiles Appropriately Tablespace location is determined by the physical location of the datafiles that constitute that tablespace.
Store Datafiles Separate from Redo Log Files Datafiles should not be stored on the same disk drive that stores the database redo log files.
Creating Datafiles and Adding Datafiles to a Tablespace You can create datafiles and associate them with a tablespace using any of the statements listed in the following table. Changing Datafile Size This section describes the various ways to alter the size of a datafile, and contains the following topics: Enabling and Disabling Automatic Extension for a Datafile Manually Resizing a Datafile Enabling and Disabling Automatic Extension for a Datafile You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database.
The next example disables the automatic extension for the datafile. It could be that the file contains data beyond the specified decreased size, in which case the database will return an error. Altering Datafile Availability You can alter the availability of individual datafiles or tempfiles by taking them offline or bringing them online.
Reasons for altering datafile availability include the following: You want to perform an offline backup of a datafile. Note: You can make all datafiles of a tablespace temporarily unavailable by taking the tablespace itself offline. You must leave these files in the tablespace to bring the tablespace back online, although you can relocate or rename them following procedures similar to those shown in "Renaming and Relocating Datafiles".
For more information, see "Taking Tablespaces Offline". Note: This operation does not actually drop the datafile. Renaming and Relocating Datafiles You can rename datafiles to either change their names or relocate them. Some possible procedures for doing this are described in the following sections: Procedures for Renaming and Relocating Datafiles in a Single Tablespace Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed.
Procedures for Renaming and Relocating Datafiles in a Single Tablespace The section suggests some procedures for renaming and relocating datafiles that can be used for a single tablespace. See Also: "Taking Tablespaces Offline" for more information about taking tablespaces offline in preparation for renaming or relocating datafiles.
Procedure for Renaming Datafiles in a Single Tablespace To rename datafiles in a single tablespace, complete the following steps: Take the tablespace that contains the datafiles offline. The database must be open. Procedure for Relocating Datafiles in a Single Tablespace Here is a sample procedure for relocating a datafile. Assume the following conditions: An open database has a tablespace named users that is made up of datafiles all located on the same disk.
You are currently connected with administrator privileges to the open database. You have a current backup of the database. Note: Optionally, the database does not have to be closed, but the datafiles or tempfiles must be offline. Restrictions for Dropping Datafiles The following are restrictions for dropping datafiles and tempfiles: The database must be open.
If a datafile is not empty, it cannot be dropped. You cannot drop the first or only datafile in a tablespace. You cannot drop datafiles in a read-only tablespace. If a datafile in a locally managed tablespace is offline, it cannot be dropped. See Also: Dropping Tablespaces. Copying Files Using the Database Server You do not necessarily have to use the operating system to copy a file within a database, or transfer a file between databases as you would do when using the transportable tablespace feature.
This configuration pushes the file. This configuration pulls the file. Note: If a single restartable job transfers several files, then you should consider restart scenarios in which some of the files have been transferred already and some have not been transferred yet.
Mapping Files to Physical Devices In an environment where datafiles are simply file system files or are created directly on a raw device, it is relatively straight forward to see the association between a tablespace and the underlying device.
It provides an easy to use graphical interface for mapping files to physical devices. Note: The file mapping interface is not available on Windows platforms. This information is composed of the following structures: Files File system extents Elements Subelements These structures are explained in "Mapping Structures".
Refreshing mapping information when a change occurs because of: Changes to datafiles size Addition or deletion of datafiles Changes to the storage configuration not frequent Saving mapping information in the data dictionary to maintain a view of the information that is persistent across startup and shutdown operations Restoring mapping information into the SGA at instance startup.
Mapping Libraries Oracle Database uses mapping libraries to discover mapping information for the elements that are owned by a particular mapping library. Mapping Structures The mapping structures and the Oracle Database representation of these structures are described in this section. The following are the primary structures that compose the mapping information: Files A file mapping structure provides a set of attributes for a file, including file size, number of file system extents that the file is composed of, and the file type.
File system extents A file system extent mapping structure describes a contiguous chunk of blocks residing on one element. Note: File system extents are not the same as Oracle Database extents. File system extents are physical contiguous blocks of data written to a device as managed by the file system.
Oracle Database extents are logical structures managed by the database, such as tablespace extents. All of the mapping structures are illustrated in Figure Configuration ID The configuration ID captures the version information associated with elements or files. There are two kinds of configuration IDs: Persistent These configuration IDs are persistent across instance shutdown Non-persistent The configuration IDs are not persistent across instance shutdown. Caution: While the format and content of the filemap.
Until such time that vendors supply there own libraries, there will be only one entry in the filemap. This file should be modified manually by uncommenting this entry only if an EMC Symmetrix array is available. Each row represents a level in the hierarchy. See Also: Oracle Database Reference for a complete description of the dynamic performance views.
Used in RAID5 and striped files. In RAID5, the number of contiguous units also include the parity stripes.
0コメント