Re: how to partly export/import a table?

gary.yu_at_istark.com (Gary YU) wrote in message news:<5483713a.0111200703.1d291be1_at_posting.google.com>...
> Hi,
>
> I had a very big log table which I wanted to clear. It logs some event
> for the past year, I planed to clear it and only keep the latest
> month. The problem is, due to the size limit of the redo log, I cannot
> do a delete even on a day-to-day basis. I just wonder, is there a way
> I can export only part of the table(say, the last month), and totally
> truncate the table, then import the smaller datafile.
>
> I'm new to oracle, I knew Informix has such tools to export table
> using select statement and save to a text file, then import it. Does
> Oracle have the same tools?
>
> Thanks a lot!!
>
> Gary

You can do a selective export of the table if you are running Oracle version >= 8i. Use the export utility like this:

exp username/password file=myexportfile.dmp table=ownername.mylogtable query=\"where trunc(log_date) > trunc(sysdate)-30\"

This will give you an export file called myexportfile.dmp which will have the table mylogtable and only the data from the last 30 days. Now you can truncate the whole table and import from the dump file.

Murali Received on Tue Nov 20 2001 - 15:15:08 CST