Re: how to partly export/import a table?

A version and platform would be nice here. Assuming 8.1.x, you can export with the query option.
to export just the data you want. Here is an example: exp <user>/<passwd> file=<filename> tables=<tablename> query=\"where log_dt \> trunc\(sysdate\,'MON'\)\"
the "\" characters are a unix thing to escape quotes and other special characters.
I think a better way to do this would be to issue the following command: CREATE TABLE tmp_log AS SELECT * FROM <your table> WHERE log_dt > TRUNC(sysdate,'MON').
if 8.1.x then
DROP TABLE <your table>;
RENAME tmp_log TO <your table>;
else
TRUNCATE TABLE <your table>;
INSERT /*+ APPEND */ INTO <your table> (SELECT * FROM tmp_log);

The BEST way to accomplish this is to use partitioning and just drop old partitions
but partitioning costs extra so you may not have it in your env.

Finally, I think you are confusing redo logs with rollback segments. The size of redo
logs should have nothing to do with how many rows your transaction can affect. Small
rollback segments will limit how many rows the transaction can affect because there needs
to be enough space to store the entire before image of the transaction.

John
"Gary YU" <gary.yu_at_istark.com> 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

-- 
Posted from d225s240.hotbank.com [63.83.225.240] 
via Mailgate.ORG Server - http://www.Mailgate.ORG