gdas1_at_yahoo.com (gdas) wrote in message news:<7a4ed455.0409141838.669d005e_at_posting.google.com>...
> I'm using 8.1.7.3 and could use some advice/assistance.
>
> the ultmate object I have is to copy a large table (100 million rows
> of 3 numeric columns...) from one database to another. The source
> table is an IOT and the destination table should also be an IOT with
> the same structure and properties. The size of the source table on
> disk from dba_extents is approximately 4 GBs (with index compression,
> prefix length 2)
>
> Source and target databases are on two different servers running clone
> hardware/OS (Sun/Solaris os 2.8). the servers are on the same lan, in
> fact they are physically racked right next to each other.
>
> The source IOT is self-contained in it's own tablespace. This is
> ideal for the transportable tablespace feature, however this feature
> is not available in my flavor of Oracle (Standard Edition).
>
> I need to copy this table from the source database to the target
> database in smallest amount of time possible. This implies with
> minimum redo logging. (Source and Target DBs are in archivelog mode).
> I understand all implications of nologging operations (take backup
> immediately after etc...).
>
> Additionally, nologging operations have limitations in regards to
> IOTs, most notably that direct path inserts (APPEND hint) don't work
> (they log).
>
> Thus summary of my options and assesement as I see it:
>
> transportable tablespace - feature not available
> export/import - I can't turn logging off in this situation
> direct path insert using append hint - can't disable logging for an
> IOT
> create database link from target to source and from target db, 'create
> iot nologging as select * from table_at_sourcedb'
>
> since the last option, CTAS is the only one I see that would build the
> IOT with minimum redo generated, that is the option I've been trying
> to focus on.
> This also means I need to copy this table 'in one shot' and cant do it
> incrementally.
>
> However, I keep hitting ORA-01555: snapshot too old
>
>
> I disabled anything that touches the source table yesterday, no DML on
> it whatsoever. The rest of the application is still running on the
> source db, so other tables are being 'touched.' (also this is a very
> busy OLTP database as far as other tables are concerned).
>
> This sounds like a case of the snapshot too old possibly being caused
> by delayed block cleanout (By the way, the source table is only ever
> inserted (ie, new rows added... no updates or deletes are ever
> performed on it...not sure if that matters). But I was hoping someone
> might able to confirm that this may be in fact what is happening,
> because I've made three attempts to run the CTAS. Each attempt runs
> for about 3 hours, while it runs I can see the index (primary key of
> iot) being sorted on the target db by monitoring v$sort_usage... it
> sorts approximately 6 GBs and then bombs with the snapshot too old
> error. So 9 hours wasted thus far (over the course of 3 days, 1
> attempt per day) and I'm up against a bit of deadline to get this
> data copied, so I thought I might seek some advice before I try
> something else only to have it fail and waste more time.
>
> What I am doing now is running a pl/sql job on the target database
> that is doing a full table scan on the IOT on the source, in the hopes
> that this might clean out any dirty blocks. It will take several
> hours to run and then when it's done, I am planning to run the CTAS
> again.
>
> Other advice in regards to snapshot too old says to size the rollback
> segments bigger. I don't want to do that. This 'copy' is a one-time
> maintenance thing and not part of the day to day operation of the
> existing application (which never encounters that error). I don't
> want to change the existing rollback segments for this one time job.
>
> Also, target database is a 24 x 7 production database...so I can't
> shut it down, disable archivelogmode, start it up and then do the
> insert...
>
> Appreciate any advice that anyone has? Other ideas on how to copy
> this big table? If I sound like I am already on the wrong path and
> wasting more time (I'm going to get the snapshot too old error no
> matter what I do!)
>
> Thanks in advance!
Hi,
You may want use FastReader from WisdomForce (www.wisdomforce.com).
FastReader quickly unload large (terabytes of data) Oracle tables
with NO OVERHEAD on production database into text file. At same time
FastReader will generate control files for sqlloader
Received on Sun Sep 26 2004 - 22:42:49 CDT
