Re: how to partly export/import a table?

In article <5483713a.0111200703.1d291be1_at_posting.google.com>, gary.yu_at_istark.com says...
>
>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

Yes you can export with the QUERY option to provide a where clause however -- what might be best for you is:

create table log_table_new nologging as select * from log_table where....; drop log_table;
rename log_table_new to log_table;
(add indexes/grants/whatever at whatever step you want)

Or, better yet - use partitions, have the table partitioned by day or week or whatever, age out old partitions as you need to (very fast), add new partitions on the end (very fast). See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:928294572145 for an example of what I mean.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp