Export

[Ablakios]

Hi to all the Gurus,

I am working on a project with a database that has 5 years worth of data in 300 tables. The Developer/DBA before me, left behind a database that is practically out of space.

I was asked to wack everything and only keep
only 12 tables out of the 300
and 2 years of ONLY RECENT data.

I am thinking to do an export with TABLES= t1, t2,t3,t4,......t12 and INDEX=N as some indexes might be corrupted.

How to go by keep only 2 years of the recent data? Do I export everything and delete from the relevant tables where sysdate <-365

What is the best approach
Thanks in advance

[tamilselvan]

Use CTAS. This is the fastest method you can extract rows.
After temp table is created, rename it to original table.

You can use exp/imp. But this is a slow process.

Tamil

[Mr.Hanky]

I am not sure how to scrub your data but if I were you I would save a FULL backup if possible. I would also get in writing that they want to loose all of that data. The data scrub depends on a lot of things, there should be some key in the table to base off, again TAKE A BACKUP!! just in case something goes BOOM.

You definately want the indexes since you are only exporting the DDL to create them, you can then import them after the data if you choose or never use them, no loss.
What do you mean by corrupt?


How big are those 11 tables?

As a rule always take more then you will need (or think you will need) when doing an export. Especially when it is something as simple as index DDL.

MH

[slimdave]

You might get a useful amount of space from compressing tables that aren't used very much, although there'd be some overhead on DML operations.

[DaPi]

The right answer is:
E) All of the above.

- Full backup, of course.
- Full export (no import) as the means of recovering the one table they did need after all.
- CTAS for cleaning out the old stuff.

[Ablakios]

You're all marvelous. I did not even think of the CTAS\
Again Thank you