Re: Tablespace Rebuild Script?

From: <jl34778_at_corp02.d51.lilly.com>
Date: 15 Nov 94 19:16:22 EST
Message-ID: <1994Nov15.191622.1_at_corp02.d51.lilly.com>

In article <9411150553.AA00488_at_uu4.psi.com>, missie!dbbikle_at_uu4.psi.com writes:
> Hi There,
>
> I need to rebuild one of my tablespaces.
>
> As you may know, export has 3 levels: Full dbs, User, & Table.
>
> So here's my plan:
>
> write a sql script which SELECTs an export shell command for TABLE_NAME
> FROM DBA_TABLES
> WHERE TABLESPACE_NAME = 'the one I am rebuilding'
> /
>
> So, I run this thing and end up with a bunch of export commands.
> I run the commands and end up with a bunch of export files (one
> for each table in the tablespace).
>
> Next, I drop the tablespace and then recreate it.
>
> Then, I import each of the table level export files.
>
> So, anyone already do this? I'd hate to re-invent the wheel.
>
> -Dan
> ---------------------------------------------------------------------------
> Daniel B. Bikle/Independent Oracle Consultant
> dbikle_at_alumni.caltech.edu | 415/854-9542 | P.O. BOX 'd' MENLO PARK CA 94026
> ---------------------------------------------------------------------------

Here is a script that I wrote a while back that may help you.

rem
rem build_exp_parfile.sql
rem

rem 	Bob Swisshelm
rem 	Eli Lilly and Company
rem	swisshelm_at_lilly.com
rem
rem	This script accepts a tablespace name and builds 2 text files.
rem
rem	tsname.PARFILE	EXP parfile that exports all of the tables in
rem		the 'tsname' tablespace
rem	tsname_DROP_CONTENTS.SQL	SQL script that drops all of the
rem		tables in the 'tsname' tablespace.

rem
rem Notes:

rem	EXP tends to abort if you have lots (100+) tables in your parfile.
rem	If this happens to you, break the parfile into multiple files.
rem
rem	This script only handles tables. It doesn't indexes or any other 
rem	physical objects.

rem
set pause off
set pagesize 0
set feedback off
set verify off
set echo off
set termout off
set escape on
define tsname = &1
spool &tsname..parfile

select	'file=&tsname..dmp' 
	from dual;
select	'constraints=yes'
	from dual;
select 'tables=(' 
	from dual;
select	rtrim(owner)||'.'||rtrim(table_name)||','
	from dba_tables 
	where tablespace_name = upper('&tsname')
	order by owner,table_name;
select 'no_user.no_table)' 
	from dual;

spool off
spool &tsname._drop_contents.sql

select	'drop table',rtrim(owner)||'.'||rtrim(table_name)||';'
	from dba_tables 
	where tablespace_name = upper('&tsname')
	order by owner,table_name;

spool off

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285

Received on Wed Nov 16 1994 - 01:16:22 CET