Re: Rebuilding a tablespace
Date: 14 Dec 1994 09:26:59 GMT
Message-ID: <3cmdp3$het_at_hustle.rahul.net>
In <3cj546$ose_at_nntp.Stanford.EDU> myia_at_leland.Stanford.EDU (Mark AuBuchon) writes:
>Hi There, >I didn't get many responses last time out with this >question so I'd like to give it another try:
>I have a tablespace which has grown & grown & grown it must be made up >of 17 data files.
>And, I must have 20 users with objects in it.
>And, many of the tables are fragmented such that some are getting near >the 121 extent limit.
>I'd like to rebuild the tablespace, but Oracle export only allows me >to rebuild the entire database or a single user or a single table.
>There MUST be a software package which I can buy (or beg) which will >rebuild the tablespace.
>Help!
Mark (& the rest of you),
You can buy real slick reorg products from either Aris or Platinum. They will want several $1,000 or many $1,000 depending on the size of your machine. Also they are gui based.
I have a large shell script which I might think about marketing.
Here is some info about it:
Dan's Tablespace Rebuilder
Dan's Tablespace Rebuilder (DTSR) is a tool for defragmenting tables & indexes in a "typical" tablespace. It is not designed to defragment the SYSTEM tablespace or a tablespace with rollback segments in it.
The main idea behind DTSR is described in the comments below which were grepped directly from the the shell script itself:
#! /bin/sh
# DTSR.sh
# Copyright 1994 Daniel B. Bikle
# Script which rebuilds a tablespace with a combination of exports.
# initialize #######################################################
# Generate CREATE TABLESPACE syntax from a full=y, rows=n export
# Take inventory of objects in the dbs which will be affected by this script
# generate rows=n user level export scripts for each user who owns objects
# in the tablespace.
# Generate rows=y table level export scripts for each table residing in tspace.
# Generate rows=y table level import scripts for each table residing in tspace.
# Generate a sql script which will call the index creation scripts.
# Generate index creation scripts.
# Run the first user level export script which will just run a rows=n
# export for each user. This is done simply as a backup of each user's
# metadata
# Run the table level export script to back up the rows in each table.
# Drop tablespace's objects
# Take tablespace offline
# Remove tablespace data file(s)
# Drop tablespace
# Recreate tablespace
# Import the tables from the rows=y table level export files
# Create any indexes which got missed
# Take inventory of objects in the dbs which have been affected by this script
# Look for differences between this inventory and the first one
# Done
DTSR.sh is a script of moderate complexity. It is about 900 lines long and required about 100 hours of effort (so far).
It is available from BIKLE Software for well under $1,000. Since it was written in Bourne shell & sqlplus is should be portable to most UNIX machines running Oracle7.
Oracle7 is an Oracle Corp trademark.
I think the UNIX trademark is owned by Novell.
Daniel B. Bikle/Independent Oracle Consultant dbikle_at_alumni.caltech.edu | 415/854-9542 | P.O. BOX 'd' MENLO PARK CA 94026
Received on Wed Dec 14 1994 - 10:26:59 CET