Re: Deleting Large Volumes of Data

One quick hack for reducing storage on import is to run

strings defn.dmp > blah.sql

where defn.dmp is an exp with rows=n

then something like

sed 's/STORAGE(.*)//g' blah.sql

to remove the entire storage clause.

hth
connor

"Otto Norse" <ottonorse_at_yahoo.com> wrote in message news:5330c896.0111291012.53eb4bb6_at_posting.google.com...
> RE:
>

http://www.oracle.com/oramag/webcolumns/2000/index.html?DeletingData_Hart.ht ml
>
> I saw this article after I had created a script that does something
similar.
> I wondered how the two methods would differ in performance and final
> results. (my step 5 vs. the exp, and no rename). I have not fully tested
> this script but it seems to be working so far.
>
> My steps:
>
> 1) disable constraints
>
> 2) create temp_table as (select * from keep_table where 'keep clause')
> - just a place to hold the data I want to preserve
>
> 3) truncate table keep_table
> - get rid of the huge amounts of data I want to delete
>
> 4) insert into keep_table (select * from temp_table)
> - re-populate my table that has the indexes, cons, etc.
>
> 5) drop temp_table
>
> 6) repeat for all generations below (I have a loop that starts with a seed
> table and then does that delete, then the children based on
> user_constraints )
>
> 7) exp user, re-import
>
> ===
>
> I'm doing this to create a small version of a very large database -- i.e.
> something w/ n Gigs of data reduced by 80% or so. This allows me to have a
> small test database with a pretty good representation of production data.
>
> My other issue was how to effectively make this database actually smaller
> after I exp / re-imp. So far, I've been exp with SHOW=Y INDEXFILE=foo.sql.
> I then edit foo.sql's INITIAL / NEXT extents and do the imp. I need to
look
> at this some more.
>
> Thanks for any feedback,
> ON
Received on Thu Nov 29 2001 - 12:42:16 CST