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 Oracle9i; 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
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.
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 Oracle9i 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 Oracle9i, 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
CREATE VIEW free_space
(tablespace, file_id, pieces, free_bytes, free_blocks,
SELECT tablespace_name, file_id, COUNT(*),
MAX(bytes), MAX(blocks) FROM sys.dba_free_space
GROUP BY tablespace_name, file_id;
The SQL procedure becomes:
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 WHO WHAT WHEN
rem Mike Ault Created 1/4/96
SET HEADING OFF FEEDBACK OFF ECHO OFF TERMOUT OFF
'ALTER TABLESPACE '||tablespace||' COALESCE;'||&&cr||
HOST rm def.sql
SET HEADING ON FEEDBACK ON TERMOUT ON
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 NAME: BOUND_OB.sql
rem FUNCTION: Show objects with extents bounded by freespace
START title80 "Objects With Extents Bounded by Free Space"
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
SELECT h.name e, g.name c, f.object_type a, e.name b,
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
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
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.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66530/viewspace-612887/，如需转载，请注明出处，否则将追究法律责任。