Tablespace Properties Descriptions
This section describes options in Tablespace Properties. Descriptions are given in alphabetic order by option name.
Block Size OptionsBlock Size Options
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 Options
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:
|
Datafile Type Option
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
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:
|
Name Options
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:
- Use standard tablespace names.
- Do not attempt to rename the SYSTEM and SYSAUX tablespaces.
- Do not rename tablespaces that are included in reorganization scripts until script execution is complete. If you change a tablespace name before or during script execution, the script aborts with one of two errors. An “ORA-00959: tablespace does not exist” error occurs when the Coalesce and check free space option is not selected. An “ORA-20101: wait_for_freespace” error occurs when the Coalesce option is selected.
Segment Space Management Options
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
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
Online/offline status is set with the Online checkbox.:
- Online—Select this checkbox to put a tablespace online. (It is selected by default.) When a tablespace is online, its objects are available to users and applications. Tablespaces are normally online.
- Offline—Clear this checkbox to take a tablespace offline. When a tablespace is offline, its objects are not available to users and applications.
Read-write / Read-only
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
Resize a Datafile
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
- In the Databases tree-list in the Explorer, expand the tablespace node for the selected datafile.
- Select the Datafile node to display the list of datafiles in right pane, and then select a datafile.
- Right-click the datafile and select Resize Datafile. The Datafile Resize wizard opens.
- To increase size, select Increase datafile size and click Next.
- Specify a new size in the Resize to field and click Next.
- On the Execute SQL page, click SQL Execute to execute the SQL command to resize the 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.
- If you do not want to reorganize segments, select No Data Movement and then click Next. On the Execute SQL page, click SQL Execute to execute the SQL command to resize the datafile.
-
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.
Reorganize Objects with Reorg Manager
About Reorganization Wizards
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
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.
- Standard Reorg—Standard reorganization is an offline method. During a standard reorganization, tables are not available for DML activity. See About Standard Reorganizations for more information.
- Live Reorg—Live reorganization is an online method. During a live reorganization, tables are available for DML activity by applications and users. When the online switch style is used, tables remain available throughout a live reorganization, including the switch from original table to reorganized copy table. Live reorganizations can be performed when the LiveReorg option is licensed. See About Live Reorganizations for more information.
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.
Partitioning Wizard
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.
Move to Tablespace Dialog
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.
Reclaim Space Dialog
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.
Keys Options in Reorganization Wizards
Review the following key options available in reorganization wizards:
- Target tablespace options—Use target tablespace options to reorganize objects in their current tablespace or relocate them for better I/O balancing. If you choose to leave objects in their current tablespace during a standard reorganization, you can reorganize them using free space in another tablespace on an interim basis.
- Sort options—Use sort options to sort data and improve its proximity for faster query access. These options are available for standard and live reorganizations.
- NOLOGGING option—Use Oracle’s NOLOGGING option to speed up reorganization of indexes.
- Parallel processing by Oracle—Use Oracle’s parallel processing to speed up reorganization of tables and indexes.
- Parallel processing by QSA— Use parallel processing by the Quest Server Agent (QSA) to speed up reorganization of indexes and recompile of dependencies.
- Scheduling options—Use scheduling options to schedule a reorganization script to run at times when database activity is light and the script will have the least impact.