Re: Getting Number of Rows in CTAS across DBLink

Arup

I suggest you make it a two stage process. - CTAS where rownum < 1 to create the structure and follow with
- COPY command.

<quote>

SQL> set copycommit 1
SQL> set arraysize 1000
SQL> copy from dest_user/dest_pw_at_tns -

Array fetch/bind size is 1000. (arraysize is 1000) Will commit after every array bind. (copycommit is 1) Maximum long size is 80. (long is 80)

   22634 rows selected from source_user_at_tns.    22634 rows inserted into SOURCE_TABLE.    22634 rows committed into SOURCE_TABLE at DEFAULT HOST connection.

</quote>

This way you can avoid need for grants to v$ views.

HTH
GovindanK
Oracle Certified Professional(8,8i)
Brainbench Certified Master DBA(8)

On Sun, 09 Nov 2003 13:29:39 -0800, "Tanel Poder" <tanel.poder.003_at_mail.ee> said:
> Really-really excellent suggestions in this thread.
>
> My respect,
> Tanel.
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Saturday, November 08, 2003 12:49 AM
>
>
> > Arup,
> >
> > I am currently devising something I have already more or less done in
> > the past (version 6, pre-analyse) to get a low-cost and fast estimate of
> > the size of huge tables, which I have recently redone at a site where
> > some of their applications are stubbornly stats-free.
> >
> > Restrictions :
> > - Must be dictionary managed
> > - May be more complicated and slower with partitioned tables.
> >
> > The idea is to heavily use dbms_rowid. First compute in how many
> > blocks are, say, the first 2,000 rows. Then get the extent list in
> > reverse order, and try to identify which is the last block to contain
> > rows. Easy to do with a binary search, by building (dbms_rowid) the
> > rowid of the first row in each block. Especially after a CTAS, you are
> > sure to have a row #1. If no row at all is found, skip to the next (ie
> > previous) extent.
> > I have always found estimates obtained in this way pretty close to
> > reality, and often better than ANALYZE ... ESTIMATE STATISTICS. In under
> > one second.
> >
> > In your particular case, I also believe that you may find something in
> > V$SQL - perhaps the SELECT * on the source database. You should get the
> > number of rows processed here.
> >
> > HTH,
> >
> > SF
> >
> > Arup Nanda wrote:
> > >
> > > Dennis,
> > >
> > > Thanks. Sorry for not being explicit about it. Since the table created
> is
> > > huge, I want to avoid the count(*) if I can get the number in some other
> > > way.
> > >
> > > Arup
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > > Sent: Friday, November 07, 2003 3:44 PM
> > >
> > > > Arup
> > > > select count(*) from table?
> > > > What is your goal? Corruption detection?
> > > >
> > > >
> > > > Dennis Williams
> > > > DBA
> > > > Lifetouch, Inc.
> > > > dwilliams_at_lifetouch.com
> > > >
> > > > -----Original Message-----
> > > > Sent: Friday, November 07, 2003 2:34 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > List,
> > > >
> > > > When I create a table as select * from another table across a dblink,
> how
> > > do
> > > > I find out how many rows were created in the table? Is there a
> statistic
> > > > somewhere, documented or otherwise, that tells me how many rows were
> > > > fetched?
> > > >
> > > > Currently I am using a rather convoluted approach - using the
> statistic,
> > > > bytes received via SQL*Net to dblink, and dividing that by the average
> row
> > > > size to get an approximate idea of the number of rows. However, this
> > > > approximation is far from even reasonably accurate; and since the
> rowsize
> > > > can change radically, it can be way off the mark. Any help or pointers
> > > will
> > > > be highly appreciated.
> > > >
> > > > Thanks.
> > > >
> > > > Arup Nanda
> > > >
> > --

-- 
http://www.fastmail.fm - Consolidate POP email and Hotmail in one place
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: GKatteri
  INET: gkatteri_at_fastmail.fm

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).