Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Deletion of Tablespaces


Oracle Tips by Burleson
 

At one time or another, such as when consolidating a rollback segment tablespace, the DBA will have to remove, or drop, a tablespace from the Oracle database system. Removing tablespaces is done through the DROP command. Its format follows.

DROP TABLESPACE tablespace_name [INCLUDING CONTENTS]
[AND DATAFILES] [CASCADE CONSTRAINTS];

The INCLUDING CONTENTS clause is optional, but if it isn’t included, the tablespace must be empty of tables or other objects. If it is included, the tablespace will be dropped, regardless of its contents, unless it contains an online rollback segment. The SYSTEM tablespace cannot be dropped.

The AND DATAFILES clause drops any datafiles and tempfiles in the tablespace. Other datafiles and tempfiles are not removed unless both the AND DATAFILES and INCLUDING CONTENTS clauses are included.

The CASCADE CONSTRAINTS clause will cause any constraints from objects inside the tablespace against objects outside of the tablespace to be dropped as well. If there are constraints, and this clause is not included, the DROP command will fail.

You cannot drop any UNDO tablespace that contains active transactions or contains data required to roll back uncommitted transactions. You also cannot drop a tablespace if it contains a domain index or any object created by a domain index. If the tablespace contains a partition from either a table or index, or subpartitions of a table or index but not all of the partitions or subpartitions, then the DROP command will fail even with the INCLUDING CONTENTS clause.

Tip:  This doesn’t remove the physical datafiles from the system in releases prior to Oracle; you must use operating-system-level commands to remove the physical files.

Let's look at some examples. The command:

DROP TABLESPACE ar;

This command will drop the tablespace ar if it contains no objects, rollback segments, undo segments, and isn't part of the SYSTEM tablespace.

DROP TABLESPACE ar INCLUDING CONTENTS;

This command will drop the tablespace ar even if it has contents, as long as the contents aren't partitions, subpartitions, active UNDO or rollback segments, or have constraint to objects outside of tablespace ar and ar isn't a part of the SYSTEM tablespace.

DROP TABLESPACE ar INCLUDING CONTENTS CASCADE CONSTRAINTS;

This command will drop tablespace ar even if it has contents, as long as the contents aren't partitions, subpartitions, active UNDO or rollback segments, and ar isn't a part of the SYSTEM tablespace.

DROP TABLESPACE ar INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

This command will drop tablespace even if it has contents, as long as the contents aren't partitions, subpartitions, active UNDO or rollback segments, and ar isn't a part of the SYSTEM tablespace, including datafiles and tempfiles.

Re-creating Tablespaces

There may be times when the DBA has to drop and re-create tablespaces. For instance, if the physical drive that the tablespaces are on has been damaged, the DBA will have to re-create the tablespaces on another volume or recover from a backup and apply redo logs. If the DBA has good documentation of what the database physical structure was before the incident, there should be no problem. If, on the other hand, the DBA has inherited a legacy system or the system has grown substantially without good documentation, the DBA could have his or her hands full rebuilding it.

The script TBSP_RCT9i.SQL on the Wiley Web site can be used to document existing tablespaces and their datafiles. As its name indicates, TBSP_RCT9i.SQL is for Oracle only.  Scripts for i and 8.0 are included in the scripts on the Wiley Web site.

Periodic Tablespace Maintenance

Periodic tablespace maintenance includes consolidation of extents, reorganization to push all the freespace in the file to the front, and exports to ensure recoverability. Let’s look at these topics.

Consolidation of Extents

As tables, indexes, and clusters are created and dropped in a tablespace, extents are dynamically assigned and deassigned to the objects. Like a disk system that dynamically assigns space to files, this causes fragmentation. Fragmentation results in objects being stored all over the tablespace, requiring more head moves to recover the information to fill users’ queries. This reduces response time and makes the system slow. Unless you can exactly specify the required storage and sizing information for each and every table in each tablespace, some of this internal fragmentation will occur. The SMON process automatically consolidates contiguous free extents into single large extents for tablespaces whose default value for the PCTINCREASE storage parameter is greater than zero. This reduces but doesn’t eliminate the problem. In Oracle8i and Oracle, you can avoid the overhead associated with either automatic coalescence or manual coalescence by using locally managed tablespaces. In a locally managed tablespace, the tablespace itself tracks its extents through the use of a bitmap, so any contiguous free areas of space are automatically coalesced.

So how do you correct it in earlier versions (and 8i or 9i with directory-managed tablespaces)? There are two methods. Let’s look at each of them.

Method 1: Use of Export and Import  

This method will consolidate all freespace and will consolidate all tables into single extents. However, the database won’t be available, and for large systems, the time required could be extensive.

1.        Perform an export on all objects in the tablespace. Remember that you must export each owner’s objects for all users who own objects in the tablespace.

2.        Drop the tablespace, using the INCLUDING CONTENTS clause.

3.        Re-create the tablespace. (If you created a script to create the tablespace, you can just rerun the script; be sure to include all active datafiles. It might be desirable to delete all of a tablespace’s datafiles and consolidate them into one large datafile at this time.)

4.        Import all of the exports generated in step 1.

A major problem with this method is that it won’t work on the SYSTEM tablespace.

Method 2: Use of Commands (Post-7.3)  

Method 2 itself is composed of two sub methods. For versions 7 to 7.3, you can use one of these two sub methods.

The first is to temporarily set the PCTINCREASE to 1 and await SMON’s automatic cleanup. This can take several minutes. The second submethod involves issuing a COALESCE command against the specific tablespace where the fragmentation is happening:

If you create a view against the DBA_FREE_SPACE view that summarizes the fragmentation state of all the tablespaces, a simple procedure can be created that defragments the database on command. For example:

rem Name:     view.sql
rem FUNCTION: Create free_space view for use by freespc reports
rem
CREATE VIEW free_space
         (tablespace, file_id, pieces, free_bytes, free_blocks,
          largest_bytes,largest_blks) as
SELECT tablespace_name, file_id, COUNT(*),
    SUM(bytes), SUM(blocks),
    MAX(bytes), MAX(blocks) FROM sys.dba_free_space
GROUP BY tablespace_name, file_id;

The SQL procedure becomes:

rem
rem NAME: defrg73.sql
rem FUNCTION: Uses the coalesce command to manually coalesce
rem FUNCTION: any tablespace with greater than 1 fragment. You
rem FUNCTION: may alter to exclude the temporary tablespace.
rem FUNCTION: The procedure uses the FREE_SPACE view which is a
rem FUNCTION: summarized version of the DBA_FREE_SPACE view.
rem FUNCTION: This procedure must be run from a DBA user id.
rem HISTORY:
rem WHO          WHAT                 WHEN
rem Mike Ault     Created          1/4/96
rem
CLEAR COLUMNS
CLEAR COMPUTES
DEFINE cr='chr(10)'
TTITLE OFF
SET HEADING OFF FEEDBACK OFF ECHO OFF TERMOUT OFF
SPOOL def.sql
SELECT
     'ALTER TABLESPACE '||tablespace||' COALESCE;'||&&cr||
     'COMMIT;'
FROM
     free_space
WHERE
     pieces>files;
SPOOL OFF
@def.sql
HOST rm def.sql
SET HEADING ON FEEDBACK ON TERMOUT ON
TTITLE OFF

If there is Swiss cheese fragmentation, the DBA needs to find which objects are bound by freespace in order to plan for the rebuild of these objects. The script in Source 3.1 can be run to determine the bound objects in your database.

rem  ****************************************************************
rem  NAME:     BOUND_OB.sql
rem  FUNCTION: Show objects with extents bounded by freespace
rem*************************************************************

START title80 "Objects With Extents Bounded by Free Space"
SPOOL rep_out\&db\b_ob..lis
COLUMN e FORMAT a15        HEADING "TABLE SPACE"
COLUMN a FORMAT a6         HEADING "OBJECT|TYPE"
COLUMN b FORMAT a30        HEADING "OBJECT NAME"
COLUMN c FORMAT a10        HEADING "OWNER ID"
COLUMN d FORMAT 99,999,999 HEADING "SIZE|IN BYTES"
BREAK ON e SKIP 1 ON c
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
COLUMN bls NEW_VALUE block_size NOPRINT
SELECT blocksize bls
FROM sys.ts$
WHERE name='SYSTEM';

SELECT h.name e, g.name c, f.object_type a, e.name b,
       b.length*&&block_size d
 FROM sys.uet$ b, sys.fet$ c, sys.fet$ d, sys.obj$ e,
      sys.sys_objects f,sys.user$ g, sys.ts$ h
 WHERE b.block# = c.block# + c.length
   AND b.block# + b.length = d.block#
   AND f.header_file = b.segfile#
   AND f.header_block = b.segblock#
   AND f.object_id = e.obj#
   AND g.user# = e.owner#
   AND b.ts# = h.ts#
 ORDER BY 1,2,3,4

CLEAR COLUMNS
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
TTITLE ''
TTITLE OFF
SPOOL OFF
CLEAR BREAKS

Source 3.1  Script to determine bound objects.

Bound objects need to be exported, dropped, and rebuilt after a consolidation of freespace. Many times you will have a number of objects, not just one, that are bound because of extensive dynamic extension in the tablespace being monitored. If this is the case, a reorganization of the entire tablespace is in order, which could involve a tablespace-specific export. The script TBSP_EXP.SQL on the Wiley Web site can be used to generate a tablespace-specific export script.

The problem with the script generated by TBSP_EXP.SQL is that it may exceed the limit for the size of an export parfile (This is a parameter file used to automate export processes) if you have a large number of tables under one owner in a single tablespace. The way to correct for this would be to place a counter on the table loop and just export a fixed number of tables per parfile.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter