This section describes options in Tablespace Properties. Descriptions are given in alphabetic order by option name.
Use the Block Size option to specify the data block size to use for a new tablespace. It is available for databases that are configured to allow use of different block sizes for different tablespaces.
When you create a tablespace, the block size initially displayed is the default for the database. You can select a different block size from a list. This includes all the block sizes for which the buffer cache in SGA memory is configured.
The Block Size option is disabled for existing databases and databases that do not allow multiple block sizes. You cannot specify a nonstandard block size for a temporary tablespace.
Note When you select a block size for a dictionary managed tablespace, other values are adjusted automatically. If Minimum Extent is greater than zero, the value is adjusted to be equal to or a multiple of the new block size. If the Include Header option is selected for a datafile, the value for Actual Size is adjusted to reflect the change in header size caused by the change in block size.
Content type options determine what type of segments a tablespace can contain.
Option | Description |
---|---|
Permanent |
When content type is permanent, a tablespace can be used for creating and storing segments for permanent objects such as tables, indexes, and rollback segments. |
Temporary |
When content type is temporary, a tablespace can be used for allocating temporary segments for sort operations. Normally, many calls are required to allocate temporary segments for sort operations. In a temporary tablespace, one sort segment is cached and used for each instance requesting a sort. This improves performance for concurrent sorts and reduces their overhead. It also improves performance for medium-sized sorts that cannot be done completely in memory. |
Undo |
When content type is undo, a tablespace can be used for storing the undo records that are generated by Oracle. These records are used to undo (roll back) changes to the database during recovery or to achieve read consistency. They are also used as requested by ROLLBACK statements. The undo content type cannot be used for dictionary-managed tablespaces. Tablespace Properties prevents use of undo in the following ways:
|
Use the Datafile type option to specify tablespace type—bigfile or smallfile. The option is available for databases that are Oracle 11 or later. It is automatically selected when bigfile is the default tablespace type for a database. Bigfile and smallfile tablespaces can exist in the same database.
Tablespace | Description |
---|---|
Bigfile |
A bigfile tablespace contains one very large datafile or tempfile. The file can beup to 128 terabytes in size (for tablespaces using the 32K block size) or up to 32 terabytes in size (for tablespaces using the 8K block size). It provides much greater storage capacity than a smallfile tablespace and reduces the number of datafiles needed for a database. Bigfile tablespaces can be used with Oracle’s Automatic Storage Management. They work best in systems that support large file sizes and have logical volume managers that support striping or RAID. Bigfile tablespaces support local extent management and automatic segment space management. They do not support dictionary extent management. They can be used with manual segment space management if tablespace content is temporary or undo. |
Smallfile |
A smallfile tablespace can contain multiple datafiles or tempfiles. All are smaller than the sizes allowed for bigfile tablespaces. The maximum number of datafiles a smallfile tablespace can contain depends on your operating system, but is typically 1022. The maximum file size allowed also depends on your operating system, but is typically 4MB blocks. Smallfile tablespaces are available for all versions of Oracle. The tablespace type traditionally used in Oracle databases, they work with both data dictionary and local extent management. |
Extent management options determine how extents are managed in a tablespace—at the database level, by the Oracle data dictionary, or locally, by the tablespace itself.
Option |
Description |
---|---|
Dictionary |
In a data dictionary managed tablespace, space use is tracked in the Oracle data dictionary. Extents are allocated based on object storage values. If objects do not have their own storage values, extents are allocated based on default tablespace storage values. Storage can be specified using fields that are displayed in the Attributes panel when the Dictionary option is selected. Note Dictionary managed tablespaces cannot be created in a database where the SYSTEM tablespace is locally managed. |
Local |
In a locally managed tablespace, space use is tracked in bitmapsin tablespace datafiles. Extents are allocated based on allocation type—autoallocate or uniform. Allocation type can be specified using fields that are displayed in the Attributes panel when the Local option is selected:
|
Tablespace names can be specified when you create a tablespace. To change a name, enter a new name in the Tablespace field of Tablespace Properties and run the alter script generated by Tablespace Properties. The field displays the new name in bold to indicate that a change has been made. A maximum of 30 characters is allowed for tablespace names.
Rename functionality can be used for both permanent and temporary tablespaces
that are online. All of the datafiles in a tablespace must also be online.
Note Renaming a tablespace does not change the names of its datafiles.
Note the following best practices for tablespace names:
Segment space management options determine whether free space in tablespace segments is managed automatically or manually.
These options are set in the Segment Space Management panel of Tablespace Properties. Manual segment space management is automatically used for temporary locally managed tablespaces. However, the SEGMENT SPACE MANAGEMENT clause is not included in Tablespace Properties scripts.
Option | Description |
---|---|
Auto |
Automatic segment space management uses bitmaps to manage free space in tablespace segments. The bitmaps track how much space is available for row inserts in segment data blocks. Oracle uses space in data blocks based on information in these bitmaps. It does not check values for the FREELISTS, FREELIST GROUPS, and PCTUSED storage parameters. As a result, you do not need to specify these values when you reorganize objects in tablespaces that use automatic segment space management. If you specify the values in a reorganization wizard, they will be scripted and displayed in the interface, but they will not be used by Oracle. |
Manual |
Manual segment space management uses free lists to manage free space in tablespace segments. Free lists are lists of data blocks that have space available for row inserts. Oracle allocates free space based on object values for the FREELISTS, FREELIST GROUPS, and PCTUSED storage parameters. |
Status options determine whether a tablespace is online or offline. For existing tablespaces, status options also determine whether a tablespace is read-write or read-only. Status options are set in the Status panel of Tablespace Properties.
Online/offline status is set with the Online checkbox.:
Read-write status is set with the Read Only checkbox. This checkbox is displayed for existing tablespaces. It is not displayed when you create a tablespace. By default, new tablespaces are read-write.
Tablespace Properties Descriptions
Space Manager allows you to easily resize a datafile using the Datafile Resize wizard. You can launch the wizard from the Explorer after selecting a datafile to resize. Use this feature to resize a datafile prior to creating or running a reorganization script.
Note: Previous releases of Space Manager (8.0.2 or earlier) allowed you to add a "script-pause" option to a reorganization script and use the pause to resize a datafile. Beginning with Space Manager 8.1, the script-pause option is no longer available. This is because its main functionality is superseded by the Datafile Resize wizard. Datafile Resize uses LiveReorg, which is preferable to standard reorganization, the method used with the script-pause function.
To resize a datafile
To decrease size, select Decrease datafile size and click Next. The next page of the wizard displays the available options for resize method and resulting datafile size.
To use the recommended size and to reorganize segments, select Recommended and click Next. This option uses a live reorganization. The next page of the wizard displays the list of segments to be reorganized and several options. Review the following for additional information:
In Same Tablespace | Select to leave tables in their current tablespace. |
Move to New Tablespace |
Select to move tables to a different tablespace. Then select the new tablespace from the list. Note: All tables in the list are moved to new tablespace. |
Turn Off Autoextent for Datafile | Select this option if you do not want this datafile to be autoextensible. |
Drop Datafile if Empty | Select to drop the datafile if it is empty after a successful reorganization. |
After specifying reorganization options, click Next. Schedule the reorganization and resize script.
Execute Immediately | Select to schedule the script to run immediately |
Start Job At | Select to schedule the job to run at a later date and time. |
Click Submit Job. The scheduled script displays in the Script/Job Monitor.
Reorganization is the main solution for space-use problems such as wasted space, fragmentation, chained rows, and table/index contention.
Space Manager provides two reorganization wizards that allow you to reorganize objects and tablespaces in a variety of ways.
Space Manager also provides two dialogs for performing simple reorganization tasks.
Reorg Manager can be used to reorganize and relocate objects from anywhere in a database.
The Reorg Manager provides two methods for reorganization—standard and live. The Standard method is offline; the live method is online.
For help selecting a reorganization method to use, see Select a Reorganization Method for Reorg Manager.
To get started using the Reorg Manager, see Launch Reorg Manager.
Tip: You can save Reorg Manager settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information. You can also use saved settings to automate reorganization script execution. See About Scheduling a Recurring Reorganization for more information.
The Partitioning Wizard can be used to partition, unpartition, and repartition tables and indexes during live reorganizations. This wizard provides a graphic interface that lets you easily visualize a partitioning scheme and specify partitioning options. The wizard also automates tasks such as setting partitioning key values, sizing partitions, and selecting tablespaces for I/O balancing. See About the Partitioning Wizard for more information.
To get started using the Partitioning Wizard, see Launch Partitioning Wizard.
Use the Move to Tablespace dialog to move an object to another tablespace without opening the Reorg Manager Wizard. See Move Objects to Tablespace for more information.
Use the Reclaim Space dialog to reclaim space without launching the Reorg Manager Wizard. This method performs a live reorganization using the default settings. See Reclaim Space for more information.
Review the following key options available in reorganization wizards:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 이용 약관 개인정보 보호정책 Cookie Preference Center