Tablespaces Wizard (Oracle)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Object Wizards

Tablespaces are logical storage structures that act as partitions for the database. Each tablespace consists of one or more datafiles which are the physical structures holding the data. You can create a tablespace to store table data and other objects related to table performance such as indexes or large object data. Tablespaces are used to manage large complex databases. Once you have created a tablespace, you can place objects on it.

The Tablespace Wizard lets you:

  • Name the tablespace, and specify space management.
  • Specify what types of objects are stored on the tablespace, and place the tablespace online or offline.
  • Add the datafiles that comprise the tablespace and specify the parameters for the datafiles.
  • Specify how Oracle should manage the growth of the tablespace.

Important Notes

  • For auto-UNDO management to be in effect, set init.ora parameter to undo_management. When set to MANUAL (the default), it disables auto-UNDO management. When to set AUTO, auto-UNDO management is enabled.
  • To determine if the undo_management parameter is set to AUTO, use the following query:
SELECT VALUE
FROM SYS.V_$PARAMETER
WHERE NAME = 'undo_management'
Note: This parameter cannot be set dynamically via the ALTER SYSTEM or ALTER SESSION.

One of the best ways to avoid fragmentation in a tablespace is to pre-allocate the space that your objects will use. If possible, plan for one to two years' growth for each object and allocate your space accordingly. Having initial empty objects will not affect table scan times as Oracle only scans up to the high-water mark (the last used block) in a table.

Of all your tablespaces, you want to avoid fragmentation problems in your SYSTEM tablespace the most as this is the major hotbed tablespace for Oracle activities. The easiest way to avoid this is to not allow any user (even the default DBA ID's SYS and SYSTEM) to have access to it. There are three ways to do this:

  • Ensure no user has a DEFAULT or TEMPORARY tablespace assignment of SYSTEM.
  • Ensure no user has a quota set for SYSTEM.
  • Ensure no user has been granted the UNLIMITED TABLESPACE privilege.

To create a new tablespace using a wizard:

  1. Open a creation wizard for a tablespace. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Tablespaces (Oracle) - Properties

When creating or editing a tablespace, this tab/panel lets you work with the following settings:

Setting

Description

Name

Provide a name for the tablespace.

Big File

Selecting this check box results in a CREATE BIGFILE TABLESPACE statement being generated. This results in a large, single file tablespace. Leaving this check box unselected results in a tablespace containing multiple smaller datafiles.

Type

Leaving the default PERMANENT selection creates a permanent tablespace, intended to contain schema objects you want to keep on an ongoing basis. The objects are stored in datafiles. The other two options let you generate a CREATE TABLESPACE statement that specifies TEMPORARY or UNDO keywords. An Undo tablespace is a kind of permanent tablespace that holds undo data if your database is operating in automatic undo mode. Oracle recommends the automatic undo mode as the wiser choice than using rollback segments to undo. In a Temporary tablespace, shema objects will last only as long as your session continues.

Tablespace Group

This option is only available with a Type value of TEMPORARY. If there are currently entries in the SYS.DBA_TABLESPACE_GROUPS table, you can either select an existing group or type the name for a new tablespace group. If there are currently no entries in the SYS.DBA_TABLESPACE_GROUPS table, you can type the name of a new tablespace group to be created. When editing a tablespace, clearing the entry from the Tablespace Group box deletes the <tablespace_name, group_name> entry from the SYS.DBA_TABLESPACE_GROUPS table.

Retention Guarantee

This option is only available with a Type value of UNDO. Selecting this check box specifies that unexpired data should be preserved for all Undo segments of the tablespace, regardless of whether this may force failure of ongoing operations that require space in those segments. Leaving this check box unselected specifies default RETENTION NOGUARANTE behavior. Space currently being used by unexpired undo data in undo segments can be consumed as necessary by ongoing transactions.

Status

This option is only available with a Type value of PERMANENT. Use this control to specify an ONLINE/OFFLINE clause in the generated DDL. The ONLINE selection makes the tablespace available immediately after creation. The OFFLINE selection results in a table is unavailable immediately after creation.

Logging

This option is only available with a Type value of PERMANENT. Use this control to specify a LOGGING/NOLOGGING clause in the generated DDL. This sets the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within this tablespace.

Force Logging

This option is only available with a Type value of PERMANENT. Selecting this check box specifies that a FORCE LOGGING clause be included in the generated DDL. Changes to all objects in the tablespace (temporary segments excepted) are logged, overriding any NOLOGGING setting for individual objects.

Compression Type

This option is only available with a Type value of PERMANENT. This lets you select default compression type of ALL OPERATIONS (corresponds to COMPRESS FOR ALL OPERATIONS in the DDL), COMPRESS_DIRECT_LOAD (corresponds to COMPRESS FOR DIRECT_LOAD OPERATIONS in the DDL) or no compression.

Flashback

This option is only available with a Type value of PERMANENT. Use this control to specify a FLASHBACK ON/FLASHBACK OFF clause in the generated DDL. Select this check box to put the tablespace in FLASHBACK mode. In FLASHBACK mode, Oracle saves Flashback log data for the tablespace and the tablespace can participate in a FLASHBACK DATABASE operation. If you leave this check box unselected, Oracle does not save Flashback log data for the tablespace. Prior to FLASHBACK DATABASE operations, you must either take datafiles in the tablespace off line or drop them OR take the entire tablespace offline. The database does not drop existing Flashback logs.

Encrypted and Algorithm

This option is only available with a Type value of PERMANENT. Selecting the Encryption check box enables the Algorithm control. Together, they let you add an ENCRYPTION USING clause to the generated DDL. The Algorithm lets you select from AES 128, AES192, AES256, DES56, DES168, or NONE encryption algorithms.

Locally Managed, Uniform Allocation, Size, Automatic Segment Space Management, and Minimum Extent Size

These option are only available with a Type value of PERMANENT or UNDO. These controls let you work with the extent-related clauses that will be included in the generated DDL. Selecting the Locally Managed check box lets you generate an EXTENT MANAGEMENT LOCAL clause. Subsequently leaving the Uniform Allocation check box deselected adds an AUTOALLOCATE option. Alternatively, selecting the Uniform Allocation check box enables the Size check box, letting you add a UNIFORM SIZE option. Select the Automatic Segment Space Management check box to generate a SEGMENT SPACE MANAGEMENT AUTO or leave it deselected to generate a SEGMENT SPACE MANAGEMENT MANUAL clause. Leaving the Locally Managed check box deselected generates an EXTENT MANAGEMENT DICTIONARY clause. This also enables the Minimum Extent Size control, letting you select a MINIMUM EXTENT clause value.

Use Default Block Size and Block Size

Disabling the Use Default Block Size check box enables the Block Size control, generating a BLOCKSIZE clause to specify a nonstandard block size for the tablespace.

Tablespaces (Oracle) - Datafiles

When creating or editing a tablespace, this tab/panel lets you build DATAFILE or TEMPFILE clauses that will be submitted with the CREATE TABLESPACE statement creating or editing this tablespace. This lets you manage the files that make up the tablespace.

To add a datafile

1. Click Add, either select an existing file name from File Name dropdown or type the name of a new file, and then press TAB or ENTER.
The file is added to the datafiles list on the left, with default attribute values.
2. Proceed to edit the datafile attributes.

To edit datafile attributes

3. Select the file from the list on the left. The controls in the Property/Values list are updated with values of the selected file.
4. Use the following table as a guide to modifying property values.
Properties Description

Size

Lets you specify the SIZE option for the DATAFILE/TEMPFILE clause, to specify the size of the file

Reuse Existing File

Lets you specify the REUSE option for the DATAFILE/TEMPFILE clause, to specify that an existing file can be reused.

Auto Extend and Growth Amount

Use these options to select an AUTOEXTEND option for the DATAFILE/TEMPFILE clause. Leaving the Auto Extend check box unselected disables the Growth Amount disabled and specifies an AUTOEXTEND OFF option in the generated DDL. Selecting the Auto Extend check box lets you specify an AUTOEXTEND ON NEXT clause. Selecting Auto Extend enables the Growth Amount control, letting you specify the size of the next increment of disk space automatically allocated when more extents are required.

Unlimited and Value

These options are only available with Auto Extend selected. They let you specify a MAXSIZE clause. Selecting the Unlimited check box lets you specify a MAXSIZE UNLIMITED clause, placing no limit on the size of the file. Leaving the Unlimited check box unselected enables the Value disabled. This specifies a MAXSIZE clause with a size provide by the Value control.

To delete a datafile

  1. Select a datafile from the column list on the left.
  2. Click Delete to remove the datafile from the tablespace.