讲解 Oracle数据库碎片如何整理(2)

TABLESPACE_NAME FSFI 

------------------------------------- 

RBS 74.06 

SYSTEM 100.00 

TEMP 22.82 

TOOLS 75.79 

USERS 100.00 

USER_TOOLS 100.00 

YDCX_DATA 47.34 

YDCX_IDX 57.19 

YDJF_DATA 33.80 

YDJF_IDX 75.55

alter tablespace temp 

default storage(pctincrease 1);

exp user/password file=exp.dmp compress=Y grants=Y indexes=Y 

tables=(table1,table2);

Coalesce Tablespace Automatically 
This technique comes from Sandeep
 Naik, a database administrator 
for GSXXI, Inc. in New York City, New York 
Here is a handy script which can be 
scheduled to automatically run 
and coalesces the tablespaces. 
This script is designed to run in NT 
but can be run in any operating system 
by slight modifications in the path where the file spools 
from the SQLPLUS environment. 
It assumes that the user who runs the script 
has priviledges to view the data dictionary. 
Start of code 
-------------------------------------- 
sqlplus / 
prompt this script will coalesce the 
tablespace automatically 
set verify off; 
set termout off; 
set head off; 
spool c: empcoalesce.log 
select alter tablespace 
||TABLESPACE_NAME|| coalesce ; 
from DBA_FREE_SPACE_COALESCED where 
PERCENT_EXTENTS_COALESCED <100 
or PERCENT_BLOCKS_COALESCED<100 ; 
spool off; 
@ c: empcoalesce.log 
set head on; 
set termout on; 
set verify on; 
prompt Tablespaces are coalesced successfully