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/[EMAIL PROTECTED] - > insert dest_table - > using - > select * from source_table; 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 [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> said: > Really-really excellent suggestions in this thread. > > My respect, > Tanel. > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> > > > Sent: Friday, November 07, 2003 3:44 PM > > > > > > > Arup > > > > select count(*) from table? > > > > What is your goal? Corruption detection? > > > > > > > > > > > > Dennis Williams > > > > DBA > > > > Lifetouch, Inc. > > > > [EMAIL PROTECTED] > > > > > > > > -----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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
