Space Tuner - Oracle Tablespace maintenence and Reorg

Space Tuner:

Space Tuner is used by Oracle DBA's for:
Database Reorganization.
Management of Database Physical Structure.
Capacity Planning.
Space Tuner enables Oracle DBA's increase their ability to monitor and manage database allocation spaces by creating visual database maps, creating database reports, and performing space organization operations.
Space Tuner provides the accurate and complete space organization resolution by three major modules: Tablespace Map, Rebuilder, and Dynamic Reports.

1. Tablespace Map:

Tablespace Map uses to display a visual map of the database objects through a map index.
Tablespace Map is built from three major sections:
1. Visible map, which displays the physical structure of the database objects, including extents and free space.
2. Detailed information on each object displayed in the map, such as: segment name, type, user name, size, tablespace etc...
3. General information, such as: over-all objects summary.

Filtering inappropriate data: The Tablespace Map using filtering provides a way to display on a map only specific data required by the user, for example: specific table spaces, specific users, segments/extents which are smaller than a specific size, segments/extents owned by specific users, segments which have a certain number of extents, certain types of extents etc...

Map Mode: The Tablespace Map can run in either physical mode, logical mode or tablespace mode. The Mode allows to define the details of the output.

Sort map objects: Tablespace Map enables sorting objects detailed information: Type, User name, Segment Name, Size, Tablespace etc.. by primary and secondary sorting. The results of the sorting are displayed in detailed information section and also in the visual map.

Send selected objects to Rebuilder: Once a database map was loaded selected objects can be sent to the Rebuilder.

2. The Rebuilder:

The rebuild process is being modified and operated by the Rebuilder using the rebuild data generated by the Tablespace Map or the Live Reports tools.
The Rebuilder displays detailed information on the rebuild data: object information, storage information and Statistics information and the logical and physical structure of the data.

The Rebuild procedure supported by the Rebuilder:
Rebuilding the Object
Generating reorg script manually
Running the reorg script

Rebuild and Reorg Fetures:
1. Rebuild tables and indexes and compress extents
2. Reorg tablespaces or segments
3. Move tables and indexes between tablespaces
4. Alter segments' storage parameters, such as: initial extent, next extent, min extents etc..
5. Generate script
6. Rebuild methods
7. Analyze after rebuild options
8. Other options, such as: index recreation
9. Run script interactively or save script

3. Dynamic Reports:

A flexible way to display the database data in a visual report, create map based on a report or send report to the Rebuilder.
Dynamic Reports includes variety of built in reports according to specific categories and additional User Defined Reports category, which refer to reports created by the user (based on SQL statements).

Functionality:
1. Preview and print reports.
2. Creating user defined reports.
3. Send report's contents to the Rebuilder.
4. Create map based on report's contents.

Estimate object size:

Estimate the size of selected DB objects according to the selected estimation method.
The estimation methods differ from one another by the technique for performing the estimation and by the time consumed for performing the estimation.
The program estimates the objects' size without a need to run analyze on the objects (except the estimation method - 'Statistics').

Size Estimation methods:

Data size
Block being used
High Water Mark
Allocated size
Statistics
Object's Size
Object's Rows
Sampled Data & Predicted Size - see 'Capacity Planning Pack'

Data size - Summarize the size of all table rows.
This method is available only for tables that are not empty.
Estimation Size = summarize of all table actual rows size.
The method is the highest DB time consuming, especially for large tables.

Block being used - Look for blocks that are actually in use, and summarize their size.
This method is available only for tables that are not empty.
Estimation Size = summarize of all table IN USE blocks size.
The method has high DB time consuming, especially for large tables.

High Water Mark – Summarize the size of all blocks below High Water Mark.
This method is the default method.
Estimation Size = the highest point that the data inside the object captured since ever.

Allocated size – All space allocated by the segment on the Tablespace
Estimation Size = the size which is allocated for the object in the tablespace, actually copy of the size field.

Statistics - Use statistics values.
This method can be used only if the object has statistics in the DB.
The estimation size is calculated according to the object statistics.

Object's Size - Object Allocated size.
This method is being used when the exact object size is known.
Estimation Size = the size given by the user.

Object's Rows - Estimate according to given number of rows and row-length.
The estimation size is calculated based on the following variables: object rows number, average row length and PCTFREE (taken from the Object).

Example:



1 Datafile.
2 Two Extents of a Table. The first one has 15 blocks (15 * 8KB = 120KB), the second has 10 blocks (10 * 8KB = 80KB).
3 High Water Mark (marks the last block ever been used in the table).
4 Blocks above the High Water Mark (Never been used).
5 Rows of the table (every row can take a different amount of space in the block).
6 Empty blocks below High Water Mark (The rows in them were deleted from the table).

The table has two extents, first extent: 120KB, second extent: 80KB.
Total table size allocated on the Tablespace: 200KB.
Database DB_BLOCK_SIZE: 8KB.

Estimation methods
'Allocated size' estimation method returns the total size allocated by the extents of the table. In this case: 200KB.
'High Water Mark' estimation method returns the size of all the blocks that were never been used in the table (below High Water Mark). In this case: 19 * 8KB = 152KB.
'Blocks being used' estimation method returns the size of all the blocks in which table rows exist. In this case: 17 * 8KB = 136KB.
'Data size' estimation method returns the summary of all the rows size. In this case: ~100KB.

Oracle versions:

Space Tuner was originally designed for Oracle Databases version 7.3.4 and 8.0.x since those versions of Oracle did not contain the functionality required to perform Reorganization and Space Management.
Using these versions DBA's have to create huge scripts to perform "create as select", Index and constraint rebuild, Recreation of triggers, Recompilation of procedures and more...
Space Tuner can create reorganization scripts that are build in a safe way for execution on a production database.
(Space Tuner also supports newer version of oracle like 9i and 10g)

Download Space Tuner

Click here to Download 30 day's evaluation of our product Space Tuner