Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> copying a large table

copying a large table

From: gdas <gdas1_at_yahoo.com>
Date: 14 Sep 2004 19:38:23 -0700
Message-ID: <7a4ed455.0409141838.669d005e@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! Received on Tue Sep 14 2004 - 21:38:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US