| Archive large table. [message #63444] |
Tue, 05 October 2004 15:53  |
Tariq
Messages: 11
Registered: July 2003
|
Junior Member |
|
|
we have a huge table, which has thousands of updates/inserts every minute. It keeps piling up. We want to archive a bunch of records every four hours or for a row count of 100k and delete those from the original table.
we want to retain those records for say7 days, how can we do it?
I'm new to this whole dba thing, appreciate any help.
BTW we are using oracle 8.1.7 on Solaris 8
Thanks,
--tariq
|
|
|
|
| Re: Archive large table. [message #63445 is a reply to message #63444] |
Tue, 05 October 2004 23:55  |
Vivek Vijai
Messages: 67
Registered: April 2004
|
Member |
|
|
use Export's QUERY option to export the selected rows
eg:
exp scott/tiger file='destination' tables=tablename
query="QUERY"
remember not use rownums rather use rowid to export new rows
all the best
Vivek
|
|
|
|
| Re: Archive large table. [message #63454 is a reply to message #63444] |
Wed, 06 October 2004 19:10 |
Balaji
Messages: 102
Registered: October 2000
|
Senior Member |
|
|
hi tariq ,
this is a very simple problem .
first find if u have a sequence number as a column in ur table .
if no then
add it as a column and let it keep incrementing by 1 you must create a sequence for this .
if yes then
you can go ahead .
create table urtable|sysdate as select 8 from urtable
where seqcol between n+1 and m ;
here n is the last sequence number in the previously created table and m is the last sequence number u need in this table being copied to.
WARNING:
never use rownums or rowids for this
because rowid is just the physical address of the row on the disk.so a row inserted later can have a lesser rowid .
rownum keeps changing with the order by clause
if you have inserted a row with a lesser value for the col in the orderby clause then this row will appear much above in the table and will havea lesser rownum.
NEVER trust these for a batch process job of this nature.
hope this will help
with best wishes
balaji
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
