Tablespaces Editor (Oracle)

From DBArtisan
Jump to: navigation, search

Go Up to Oracle Object Editors

The Tablespaces Editor lets you manage datafiles, space, storage, quotas, and objects for a tablespace.

To edit a tablespace

  1. Open an editor on the tablespace. For details, see Opening an Object Editor.
  2. Use the following table as a guide to understanding and modifying the settings on the tabs of this editor:
Tab Settings and tasks

Datafiles

The tab lists details for each datafile on the tablespace. Optionally you can add or delete files or edit file attributes. For detailed information on using this tab, see Tablespaces (Oracle) - Datafiles.

Properties

This tab lets you view the Name, Big File, Type, Encrypted, Locally Managed, Minimum Extent Size, Uniform Allocation, Use Default Block Size, Block Size, Automatic Segment Space Management, Initial Extent, Next Extent, Minimum Extents, Maximum Extents, and Percent Increase properties. This tab lets you modify the Status, Logging, Force Logging, and Compression Type properties. For more information on these properties, see Tablespaces (Oracle) - Properties.

Extent Details

Provides details (Name, Type, File ID, Extent ID, Block ID, Blocks, and Bytes) for each extent in the tablespace.

Space

Displays free blocks versus used blocks statistics for the tablespace, including a pie chart representation.

Map

This tab presents a graphical, segment map of the tablespace. The map is color-coded to show usage of each segment (FREE, SELECTED, OTHER, TEMPORARY, ROLLBACK, CACHE, TABLE, INDEX, TABLE PARTITION, NESTED TABLE, INDEX PARTITION, INDEX SUBPARTITION, TEXT, CLUSTER, LOB, LOBSEGMENT, LOBINDEX, or LOB PARTITION). Optionally you can:

Click Legend to view the color legend of the map.

Hover the mouse over a segment to display its Segment ID, Block ID, Size (kb), Size (in blocks), Tablespace File ID, and Segment Type details.

Click Display to open a window that aids in navigating the graphical segment map. The Overview window displays a smaller version of the segment map. The selected area can be dragged up and down the window, displaying the selected area in the larger, main map. The selected area can also be resized, to display a larger or smaller area in the main map.

Select an INDEX or TABLE segment on the map and click Reorganize to initiate a Rebuild Indexes or Reorganize (respectively) action. For details, see Rebuild Index and Reorganize /Reorg.

The Object Demographics area provides a tabular representation of the tablespace map. It provides details on a segment-by-segment basis.

For background information, see About Oracle Tablespace Storage.

Objects

Displays the objects currently stored on the tablespace, grouped under object type folders. Optionally you can:

Specify a logging option using the Log Changes When Scheme/Data is Modified? radio set.

Select an object under one of the object folders and click Edit to open an object editor on that object.

Quotas

Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota. Users with privileges to create certain types of objects can create those objects in the specified tablespace. The Quotas tab of the Tablespace editor lets you manage user space quotas for tablespaces on the current datasource. Optionally, you can:

Click Add or select a user and click Edit to assign a user unlimited or a specific space usage quota on the tablespace. For details, see Adding or Editing User Tablespace Quotas.

Select an existing user and click Drop to delete the quota for that user

DDL View

For details on using this tab, see Viewing the SQL/DDL for an Object.

3. When finished, you can submit your changes. For details, see Previewing and Submitting Object Editor Changes.

Adding or Editing User Tablespace Quotas

When you assign a quota:

  • Users with privileges to create certain types of objects can create those objects in the specified tablespace.
  • Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.
Note: This functionality is available for Oracle only.

User tablespace quotas are added and modified from the Tablespaces editor.

The table below describes the options and functionality on the Add User Quota... or Edit User Quota... dialogs:

Option Description

User selection list (Add only)

Lets you select one or more users to assign a quota.

Quota

Lets you set a quota for the selected user or users. You can select an unlimited, or a specified size. Unlimited - Lets you place an unlimited quota on the tablespace. Other - Lets you place a specified quota in KB or MB on the tablespace.

About Oracle Tablespace Storage

The Storage tab of the Tablespace Editor lets you view storage details for tablespaces on the current datasource.

Tip: Always create tablespaces for user data and never place user tables and indexes in the SYSTEM tablespace. Placing user objects in the SYSTEM tablespace can degrade performance and introduce space-related headaches to the database.

Oracle or later supports locally managed tablespaces, which can all but eliminate the problem of tablespace fragmentation. It totally does away with the storage parameters of MINEXTENTS, MAXEXENTS, PCTINCREASE, and NEXT. With locally managed tablespaces you either specify the initial extent size and let Oracle automatically size all other extents, or specify a uniform extent size for everything.

Tip: 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.