Space Manager with LiveReorg 9.1 - User Guide

Manage Storage with Tablespace Properties

Open Tablespace Properties

The Tablespace Properties window allows you to create a new tablespace or modify an existing tablespace.

• See Create Tablespaces to learn how to create a new tablespace.
• See Alter Tablespaces to learn how to modify an existing tablespace.
• See Tablespace Properties Descriptions for detailed descriptions of tablespace property options.

To open Tablespace Properties for a new tablespace

1. Right-click any tablespace in the Space Manager Explorer.
2. Select New Tablespace.

To open Tablespace Properties for an existing tablespace

1. Right-click a tablespace in the Explorer or a report.
2. Select Tablespace Properties.

Tip: To increase or decrease the size of a datafile, see Resize Datafile.

Create Tablespaces

Before you create a new tablespace, estimate the average size and growth rate of the objects it will contain. Datafiles in the tablespace should be sized to provide space for growth in the near future.

See Tablespace Properties Descriptions for detailed descriptions of the tablespace property options.

To create a tablespace with Tablespace Properties

1. Right-click any tablespace in the Space Manager Explorer and select New Tablespace to open Tablespace Properties.
2. Enter a name for the tablespace in the Tablespace field. The default name is NEW_TABLESPACE_0. Using a standard name is recommended. The maximum number of characters allowed is 30.
3. In the Status panel, select the Online checkbox if to create an online tablespace. Its objects can be accessed by users and applications. Clear the Online checkbox to create an offline tablespace.

4. In the Contents panel, specify the content type for the tablespace by selecting one of the following options:

   • Permanent—Select this option to use a tablespace for storing segments for permanent objects.
   • Temporary—Select this option to use a tablespace for allocating temporary segments for sort operations. Objects cannot be created or stored in a temporary tablespace.
   • Undo—Select this option to use a tablespace for storing undo records.

5. In the Extent Management panel, specify the extent management type for the tablespace by selecting one of the following options:

   • Dictionary—Select this option to create a data dictionary managed tablespace. (Dictionary-managed tablespaces cannot be created in a database where the SYSTEM tablespace is locally managed.)
   • Local—Select this option to create locally-managed tablespace.
6. Select a data block size for the tablespace from the Block Size field. This field lists all block sizes for which the buffer cache in SGA memory is configured. The size initially displayed is the default for the database. (The Block Size field is disabled for databases that are not configured to use multiple block sizes.)

7. If you are creating a permanent locally-managed tablespace, select one of the following Segment Space Management options:

   • Auto—Select this option if segment space should be managed automatically with bitmaps. The bitmaps track how much space is available for row inserts in segment data blocks. (Auto must be selected for a bigfile tablespace. If Manual is selected, the ORA-32772 error occurs when the script to create the tablespace is run.)
   • Manual—Select this option if segment space should be managed with free lists. These track which segment data blocks have space for row inserts. Oracle uses space in data blocks based on object values for the FREELISTS, FREELISTS GROUPS, and PCTUSED storage parameters.

8. If you are creating a locally-managed tablespace, specify datafile type for the tablespace. Select the Bigfile checkbox if type should be bigfile. The tablespace can contain one very large datafile. Clear the Bigfile checkbox if type should be smallfile. The tablespace can contain multiple smaller datafiles.

   Note:  The Bigfile  checkbox is automatically selected when bigfile is the default tablespace type for a database.

9. If you are creating a dictionary-managed tablespace, set default tablespace storage values in the Attributes panel (these values are used for objects without their own storage values):

   • Initial—Select the size of the first extent to allocate for tablespace objects.
   • Next—Specify the size of additional extents to allocate for tablespace objects. The second extent is the size specified for Next. Subsequent extents are the size of the last extent allocated multiplied by the value for PCTINCREASE.
   • Min Extents—Select the minimum number of extents to allocate initially for tablespace objects.
   • Max Extents—Select the maximum number of extents that can be allocated for each tablespace object. The maximum includes the INITIAL extent. To allow an unlimited number of extents to be allocated, select unlimited.
   • Pct Increase—Enter the percentage by which each additional extent can grow over the last extent allocated. If you enter zero, all additional extents are the size specified for NEXT. If PCTINCREASE is greater than zero, the second extent allocated is the size specified for NEXT. Each extent after that is the size of the last extent multiplied by PCTINCREASE.
   • Logging—Specify whether logging to Oracle redo logs should be turned on or off for tablespace objects. Select Logging to turn on logging to Oracle redo logs. Clear the check box to turn off logging.
   • Minimum Extent—Specify a minimum size for extents created in the tablespace. Extent sizes will be equal to or a multiple of the value specified.

10. If you are creating a locally-managed tablespace, specify allocation type and the logging property in the Attributes panel (extents for all objects created or reorganized in the tablespace are sized based on allocation type):

    • Autoallocate—Select this option if extents should be sized automatically by Oracle. The minimum extent size is 64K. Autoallocate is recommended for tablespaces that will contain tables requiring different extent sizes. (Autoallocate cannot be used for temporary local tablespaces.)
    • Uniform—Select this option if extents should be uniform in size. Also specify a size in the Size field. The default is 1M (1024K). Using a uniform size helps minimize fragmentation. The size should be large enough to prevent an excessive number of extents from being allocated.
    • Logging—Specify whether logging to Oracle redo logs should be turned on or off for tablespace objects. Select Logging to turn on logging to Oracle redo logs. Select No Logging to turn off logging.

11. Add datafiles to the tablespace using options in the Datafiles pane. One datafile is allocated by default. Click Add Datafile for each additional datafile you want to allocate. The number of datafiles you can add depends on the maximum allowed by your operating system.

    Tip: To delete a datafile, click its row and click Delete Datafile. If a tablespace has only one datafile, the datafile cannot be deleted. (Options for adding and deleting datafiles are disabled for bigfile tablespaces, which use only one datafile.)

12. Specify the following properties for each datafile in a tablespace:

    • Name—Specify a datafile name in this field or use the default name. Default names include the name of the tablespace. The name of the first datafile ends in _01.dbf. The number increases by one for each datafile added. For example, the name of the second datafile ends in  _02.dbf.
    • Reuse—Select this checkbox to reuse an existing datafile instead of creating one. Also enter the name of the datafile in the Name field.
    • Specify Size—Select this checkbox if you are reusing an existing datafile and want to change the size of the datafile. Clear this checkbox to retain the datafile’s current size.
    • Size—Enter the amount of space to allocate for a datafile in this field. Also select M or K for megabytes or kilobytes. If you are changing the size of an existing datafile, the new size can be equal to or less than the physical size of the datafile. If you are sizing a bigfile datafile, the size can be up to 128 terabytes for a tablespace with a 32K block size. The size can be up to 32 terabytes for a tablespace with an 8K block size.
    • Include Header Block—Select this option to add extra space to a datafile for use by the file header. If a tablespace is locally managed, the space is also used by the extent bitmap. The amount of space added is equivalent to one data block for a dictionary managed tablespace and 64K for a locally managed tablespace.
    • Actual Size—If you selected the Include Header Block option, view this field to see the total amount of space to be allocated for a datafile. The total includes space for data plus space for the header. (When Include Header Block is not selected, values for Actual Size and Size are the same.)
    • Autoextend—Select ON in this field if a datafile should be autoextensible. Select OFF if a datafile should not be autoextensible. When a datafile is autoextensible, it can allocate extents automatically as objects grow. This reduces the need for immediate intervention when objects require more space. It also reduces the risk of tablespace failure.
    • Next auto extend size—If a datafile is autoextensible, specify the size of the extents it can allocate. Also select M or K for megabytes or kilobytes.
    • Max Size—If a datafile is autoextensible, specify the maximum size to which it can grow. Also select M or K for megabytes or kilobytes.
13. Click Generate Script to generate the script for creating the tablespace. The script is displayed in the SQL Editor. Press F9 to run the script.
14. Close the SQL Editor and the Tablespace Properties window.
15. Press F5 from the Explorer to refresh the Tablespaces tab.

Related Topics

Alter Tablespaces

Open Tablespace Properties

Tablespace Properties Descriptions

Alter Tablespaces

You can use the Tablespace Properties window to make changes to tablespaces, such as adding or deleting datafiles. After you modify properties, Tablespace Properties generates the script for altering a tablespace and opens the script in the SQL Editor.

The options available in the Tablespace Properties window are determined by the Oracle database version and the extent management type for a tablespace. Options that are unavailable for the selected tablespace are disabled in the window.

Note: The following options are not supported: extent management (dictionary managed or locally managed), segment space management (auto or manual), block size, and bigfile or smallfile tablespace type.

See Tablespace Properties Descriptions for detailed descriptions of the tablespace property options.

To alter a tablespace with Tablespace Properties

1. Open Tablespace Properties by right-clicking a tablespace in the Explorer or a report, and selecting Tablespace Properties.
2. You can rename the tablespace by entering a new name in the Tablespace field. The name is displayed in bold. Using a standard name is recommended. A maximum of 30 characters is allowed.
3. To alter the online/offline property for a tablespace, change the setting for the Online checkbox. Select this checkbox to put a tablespace online. Its objects can be accessed by users and applications. Clear the checkbox to take a tablespace offline.
4. To alter the read-write property for a tablespace, change the setting for the Read Only checkbox. Select this checkbox to make the tablespace read-only. Write operations cannot be performed on datafiles the tablespace. Clear the checkbox to make the tablespace read-write.

5. To alter content type for a dictionary managed tablespace, select one of the following options in the Contents panel:

   • Permanent—Select this option to use a tablespace for creating and storing segments for permanent objects.
   • Temporary—Select this option to use a tablespace for allocating temporary segments for sort operations. Objects cannot be created or stored in a temporary tablespace.

6. To alter default storage values for a dictionary-managed tablespace, modify any of the following parameters in the Attributes panel:

   • Initial—Select the size of the first extent to allocate for tablespace objects.
   • Next— Specify the size of the additional extents to allocate for tablespace objects. The second extent is the size specified for Next. Subsequent extents are the size of the last extent allocated multiplied by the value for PCTINCREASE.
   • Min Extents—Select the minimum number of extents to allocate initially for tablespace objects.
   • Max Extents—Select the maximum number of extents that can be allocated for each tablespace object. The maximum includes the INITIAL extent. To allow an unlimited number of extents to be allocated, select unlimited.
   • Pct Increase—Enter the percentage by which each additional extent can grow over the last extent allocated. If you enter zero, all additional extents are the size specified for NEXT. If PCTINCREASE is greater than zero, the second extent allocated is the size specified for NEXT. Each extent after that is the size of the last extent multiplied by PCTINCREASE.
   • Logging—Specify whether logging to Oracle redo logs should be turned on or off for tablespace objects. Select Logging to turn on logging to Oracle redo logs. Select No Logging to turn off logging.
   • Minimum Extent—Specify a minimum size for extents created in the tablespace. Extent sizes will be equal to or a multiple of the value specified.
7. If the tablespace is locally managed, select Logging in the Attributes panel to turn on logging to Oracle redo logs for tablespace objects. Select No Logging to turn off logging.

8. To add a datafile to a smallfile tablespace, click Add Datafile in the Datafiles pane. Then specify the following properties in the new datafile row:

   • Name—Specify a datafile name in this field or use the default name. Default names include the name of the tablespace. The name of the first datafile ends in _01.dbf. The number increases by one for each datafile added. For example, the name of the second datafile ends in  _02.dbf.
   • Reuse—Select this checkbox to reuse an existing datafile instead of creating one. Also enter the name of the datafile in the Name field.
   • Specify Size—Select this checkbox if you are reusing an existing datafile and want to change the size of the datafile. Clear this checkbox to retain the datafile’s current size.
   • Size—Enter the amount of space to allocate for a datafile in this field. Also select M or K for megabytes or kilobytes. If you are changing the size of an existing datafile, the new size can be equal to or less than the physical size of the datafile. If you are sizing a bigfile datafile, the size can be up to 128 terabytes for tablespace with a 32K block size. The size can be up to 32 terabytes for a tablespace with an 8K block size.
   • Include Header Block—Select this option to add extra space to a datafile for use by the file header. If a tablespace is locally managed, the space is also used by the extent bitmap. The amount of space added is equivalent to one data block for a dictionary managed tablespace and 64K for a locally managed tablespace.
   • Actual Size—If you selected the Include Header Block option, view this field to see the total amount of space to be allocated for a datafile. The total includes space for data plus space for the header. (When Include Header Block is not selected, the values for Actual Size and Size are the same.)
   • Autoextend—Select ON in this field if a datafile should be autoextensible. Select OFF if a datafile should not be autoextensible. When a datafile is autoextensible, it can allocate extents automatically as objects grow. This reduces the need for immediate intervention when objects require more space. It also reduces the risk of tablespace failure.
   • Next auto extend size—If a datafile is autoextensible, specify the size of the extents it can allocate. Also select M or K for megabytes or kilobytes.

   • Max Size—If a datafile is autoextensible, specify the maximum size to which it can grow. Also select M or K for megabytes or kilobytes.

     Tip: To alter an existing datafile for a smallfile or bigfile tablespace, click the row for that datafile and modify values for Size, Include Header Block, Autoextend, Next auto extend size, or Max Size. (Names of existing datafiles cannot be altered.) To delete the row for a datafile, click its row and click Delete Datafile.

9. Click Generate Script to generate the script for altering the tablespace. The script is displayed in the SQL Editor. Press F9 to run the script.
10. Close the SQL Editor and the Tablespace Properties window.
11. Press F5 from the Explorer to refresh the Tablespaces tab with any changes to datafiles in the tablespace.

Tip: To increase or decrease the size of a datafile, see Resize Datafile.

Related Topics

Create Tablespaces

Open Tablespace Properties

Tablespace Properties Descriptions