Re: A script to reorganize a tablespace !!!
Date: Tue, 14 Dec 1993 19:20:22 GMT
Message-ID: <sjsCI1Hpy.FHz_at_netcom.com>
Yves Noel (noel_at_omega.univ-lille1.fr) wrote:
: Hi all ! : After some months of exploitation, some tablespaces are like "Gruyere" (French : cheese) --> full of holes. : So, I'm writing a script (which combined UNIX commands and SQL) to reorganize : these tablespaces.
Well, I ran into a similar problem with one of our tablespaces. Here is the script which I threw together to do the job of cleaning it up. You may have to modify it considerably. For example, I did not manually take the tablespace off line, I just DROPPED it(This database has no transactions for end users).
There may be other specifics which you will need to modify.
Here is the Korn shell/SQL script to do the job. If you have any questions, please email me directly.
Good luck
- CUT HERE =====================================
!/bin/ksh
# Steve Schow
# Collagen Corporation
# $Revision: 1.4 $
# $Header: defrag,v 1.4 93/11/22 13:38:54 lotanad Exp $
# $Source: /section4/lotana/Dev/InOut/RCS/defrag,v $
# This script will export an entire tablespace, drop the tablespace,
# create a new one with improved storage parameters(possibly) and
# then import the objects back in with the improved storage parameters
#
#============================================================================
if [ $# -lt 1 ]
then
echo "Usage: $0 tablespace" >&2 exit 1
fi
TABLESPACE=$1
#============================================================================
# This section will setup the temp files with a trap to remove them upon
# program exit
#============================================================================
TMP1=$(mktemp).dum
TMP1b=$(mktemp).dum
TMP1c=$(mktemp).dum
TMP2=$(mktemp)
TMP3=defrag.exp
trap 'rm -f ${TMP1} ${TMP1b} ${TMP1c} ${TMP2}.sql;exit' 0 1 2 3 5 15
#============================================================================
# This section will identify the names of tables and owners that need
# to be exported along with other tablespace info such as size, datafile,etc
#============================================================================
echo "Compiling SQL for Tablespace ${TABLESPACE}..." >&2
#============================================================================
# First we need to get a list of all tables in the named tablespace. We
# Also need the current initial extent size. We are going to make sure
# that the next extent size is THE SAME as the initial extent and later
# we will set the PCT increase to 0 as well. This will greatly reduce
# future fragmentation. We also need the total file size, even if spread
# across multiple files. The new tablespace will all be in one big file.to
#
# First the SQL script will be cat'd into a file and then that file will
# be used by sqlplus.
#============================================================================
cat > ${TMP2}.sql <<EOB
PROMPT Wait a Moment...
set echo off
set pagesize 0
set feedback off
set heading off
set term off
set trim on
spool ${TMP1}
select owner||'.'||table_name
from dba_tables
where tablespace_name = upper('${TABLESPACE}');
spool ${TMP1b}
select file_name||'|'||bytes from dba_data_files
where tablespace_name = upper('${TABLESPACE}')
and status = 'AVAILABLE'
order by file_id;
spool ${TMP1c}
select initial_extent from dba_tablespaces
where tablespace_name = upper('${TABLESPACE}');
quit
EOB echo "Executing SQL to get Tablespace ${TABLESPACE} info..." >&2 echo "Logging in to SQL*PLUS as SYS..." >&2
#============================================================================
# Execute the SQL script created above as user SYS. You will need to
# enter the password here.
#============================================================================
sqlplus -s sys _at_${TMP2}
if [ $? -eq 1 ]
then
echo "ERROR running sql script" >&2 exit 1
fi
#============================================================================
# Use awk to extract information from the SQL output listings which were
# created above. The first one extracts everything from the listing, but
# puts a leading comma in front of all but the first line.
#============================================================================
TABLIST=$(\
awk '
NR==1 { printf("%s",$0) }
NR > 1 { printf(",%s",$0) }
END { printf("\n") }' ${TMP1})
if [ $? -eq 1 ]
then
echo "ERROR running awk script" >&2 exit 1
fi
#============================================================================
# This awk script gets the name of the datafile used for this tablespace.
# Note that if there are more than one datafiles assosiated with this
# tablespace, only the name of the first one will be returned.
#============================================================================
DATAFILE=$(\
nawk '
BEGIN { FS="|" }
NR==1 { print $1}' ${TMP1b} |\
sed -e "s/^[ ]*//g")
if [ $? -eq 1 ]
then
echo "ERROR running awk script" >&2 exit 1
fi
#============================================================================
# This awk script will add up the sizes of all datafiles assosiated with
# this tablespace and convert the number from BYTES to Megabytes. Only
# one new tablespace of a larger size is going to be created
#============================================================================
SIZE=$(\
awk '
BEGIN {
total=0 FS="|"
}
{
mega=int($2/1048000) total=total+mega
}
END { printf("%sM\n",total) }' ${TMP1b})
if [ $? -eq 1 ]
then
echo "ERROR running awk script" >&2 exit 1
fi
#============================================================================
# This awk script will extract the initial extent size and convert it
# to Kilobytes. This size will be automatically used for the new tablespace
# NEXT parameter, since they should be equal or at least a multiple. equal
# is better. If you need to adjust the extent size of a tablespace, then
# alter the tablespace default storage parameters before running this script
#============================================================================
EXTENT_SIZE=$(\
awk '
NR==1 { printf("%sK\n",int($1/1024)) }' ${TMP1c})
if [ $? -eq 1 ]
then
echo "ERROR running awk script" >&2 exit 1
fi
#============================================================================
# export all tables from the tablespace into a temp file. This will include
# all files owned by anyone
#============================================================================
echo >&2
echo "Exporting Tables..." >&2
echo >&2
echo "Please logon to Oracle as SYS" >&2
cat > ${TMP1} <<EOB
compress=N
grants=Y
constraints=Y
file="${TMP3}"
tables=(${TABLIST})
indexes=Y
EOB exp parfile=${TMP1}
if [ $? -eq 1 ]
then
echo "ERROR running export script" >&2 exit 1
fi
#============================================================================
# Drop the tablespace and all of its contents and create a new one with
# appropriate storage parameters
#============================================================================
echo "Dropping the ${TABLESPACE} tablespace..." >&2
cat > ${TMP2}.sql <<EOB
PROMPT This may take a while....
drop tablespace ${TABLESPACE} including contents;
prompt Creating new tablespace ${TABLESPACE}
create tablespace ${TABLESPACE} datafile '${DATAFILE}'
size ${SIZE} reuse default storage (
initial ${EXTENT_SIZE} next ${EXTENT_SIZE} pctincrease 0
);
quit
EOB echo "Signing in as SYS.." >&2
sqlplus -s sys _at_${TMP2}
if [ $? -eq 1 ]
then
echo "ERROR running sql script" >&2 exit 1
fi
#============================================================================
# Import the tables back into the new tablespace from the export temp
# file create above
#============================================================================
echo "Importing data back into tablespace ${TABLESPACE}" >&2
echo "Signing is as user SYS ..." >&2
imp sys full=Y file="${TMP3}"
if [ $? -eq 1 ]
then
echo "ERROR running import script" >&2 exit 1
fi
rm -f ${TMP3}
echo >&2
echo "Tablespace ${TABLESPACE} has been rebuilt" >&2
-- ------------------------------------------------------------------ Steve Schow | But you don't need to use the claw, if you sjs_at_netcom.com | pick the pear with the big paw paw...... (415) 354-4908 | Have I given you a clue......? 800-722-2007x4908 | - Baloo the Bear ------------------------------------------------------------------Received on Tue Dec 14 1993 - 20:20:22 CET