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