Re: copying a large table

From: Joel Garry <joel-garry_at_home.com>
Date: 15 Sep 2004 17:26:58 -0700
Message-ID: <91884734.0409151626.713cf9b2@posting.google.com>

gdas1_at_yahoo.com (gdas) wrote in message news:<7a4ed455.0409141838.669d005e_at_posting.google.com>...
>
> 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.

How big are they and do you have optimal set? As Sybrand noted, what's wrong with making a big rollback for this one-time big job? Keep it offline until just before use if you don't want to just let Oracle do its thing with extending.

>
> 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!

I've found this (from an old metalink script) can be useful sometimes:

select

   substr(a.os_user_name,1,8)    "OS User",   
   substr(a.oracle_username,1,8) "DB User",        
   substr(b.owner,1,8)  "Schema",  
   substr(b.object_name,1,20)    "Object Name",     
   substr(b.object_type,1,10)    "Type",         
   substr(c.segment_name,1,5)  "RBS",         
   substr(d.used_urec,1,12)      "# of Records"

from

v$locked_object a,

   dba_objects b,       
   dba_rollback_segs c,

v$transaction d,
v$session e
where a.object_id = b.object_id

   and a.xidusn    =  c.segment_id      
   and a.xidusn    =  d.xidusn      
   and a.xidslot   =  d.xidslot
   and d.addr      =  e.taddr

;

--
@home.com is bogus.
What, me worry?

Received on Wed Sep 15 2004 - 19:26:58 CDT