might not be your transaction that is causing this.

were there other transactions happening here?  look in v$transaction (perhaps you left a window open last week sometime.  we use RBS space in a circular fashion -- we cannot reuse RBS data until the transactions that were using it committed.  It only takes a tiny -- let longer lived -- transaction to cause this.  Just means you tried to wrap back around in the RBS and couldn't -- but could not grow it either).

If you have my book "expert one on one Oracle" -- i describe how RBS is used.

The concepts guide (a MUST MUST read for any Oracle professional) covers it as well.

The ADMIN guide would be a second "must read" for you followed by the backup and recovery guide (at least 2 times).

You do not say what version of Oracle you are using either -- in 9i, you should use AUM (automatic undo mgmt) instead of manually creating your own RBS's.

Autoextend lets a file grow as segments extend.

Problem here is you said "max extents is 121 for this RBS", so -- didn't matter that the file could grow, you did not let the RBS grow.  You also did not set up an INITIAL, NEXT (should probably be 1m and 1m in many generic cases to start with).



But, yes, your approach generates THE MOST undo possible.  Consider the following 4 approaches:

ops$tkyte@ORA920PC> create table cipher_rc6_sbox ( input number, output number );
 
Table created.
 
Elapsed: 00:00:00.04
ops$tkyte@ORA920PC> create index t1_idx on cipher_rc6_sbox(input);
 
Index created.
 
Elapsed: 00:00:00.05
ops$tkyte@ORA920PC> create index t2_idx on cipher_rc6_sbox(output);
 
Index created.
 
Elapsed: 00:00:00.05
ops$tkyte@ORA920PC> create index t3_idx on cipher_rc6_sbox(input,output);
 
Index created.
 
Elapsed: 00:00:00.04
ops$tkyte@ORA920PC> create index t4_idx on cipher_rc6_sbox(output,input);
 
Index created.
 
Elapsed: 00:00:00.02

<b>Just assuming you are loading into heavily indexed tables....</b>


ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> declare
  2    n integer;
  3  begin
  4    for n in 0..65535 loop
  5      insert into cipher_rc6_sbox (input, output) values (n, (2*n+1)*n);
  6    end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:47.64
ops$tkyte@ORA920PC> select used_ublk*8/1024 "RBS used in MEG" from v$transaction;
 
RBS used in MEG
---------------
     27.3984375
 
Elapsed: 00:00:00.05

<b>now, same table -- no indexes:</b>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop table cipher_rc6_sbox;
 
Table dropped.
 
Elapsed: 00:00:00.62
ops$tkyte@ORA920PC> create table cipher_rc6_sbox ( input number, output number );
 
Table created.
 
Elapsed: 00:00:00.07
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> declare
  2    n integer;
  3  begin
  4    for n in 0..65535 loop
  5      insert into cipher_rc6_sbox (input, output) values (n, (2*n+1)*n);
  6    end loop;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:18.35
ops$tkyte@ORA920PC> select used_ublk*8/1024 "RBS used in MEG" from v$transaction;
 
RBS used in MEG
---------------
       4.421875
 
Elapsed: 00:00:00.03
ops$tkyte@ORA920PC>


<b>Now, same table -- BULK load:</b>

ops$tkyte@ORA920PC> drop table cipher_rc6_sbox;
 
Table dropped.
 
Elapsed: 00:00:00.20
ops$tkyte@ORA920PC> create table cipher_rc6_sbox ( input number, output number );
 
Table created.
 
Elapsed: 00:00:00.05
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into cipher_rc6_sbox
  2  select n, (2*n+1)*n
  3    from ( select rownum-1 n
  4             from all_objects, all_objects
  5                    where rownum <= 65536 );
 
65536 rows created.
 
Elapsed: 00:00:06.97
ops$tkyte@ORA920PC> select used_ublk*8/1024 "RBS used in MEG" from v$transaction;
 
RBS used in MEG
---------------
         .15625
 
Elapsed: 00:00:00.00

<b>that is quite the difference!  but wait, it gets better:</b>

ops$tkyte@ORA920PC> create table cipher_rc6_sbox ( input number, output number );
 
Table created.
 
Elapsed: 00:00:00.05
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert /*+ APPEND */ into cipher_rc6_sbox
  2  select n, (2*n+1)*n
  3    from ( select rownum-1 n
  4             from all_objects, all_objects
  5                    where rownum <= 65536 );
 
65536 rows created.
 
Elapsed: 00:00:03.72
ops$tkyte@ORA920PC> select used_ublk*8/1024 "RBS used in MEG" from v$transaction;
 
RBS used in MEG
---------------
       .0078125
 
Elapsed: 00:00:00.00


 

(which is the online docs, freely available to anyone on the planet).  I plug in 

log switch

in the "enter word or phrase" dialog and hit search....

I get "99 topics in 19 books".  the admin guide had 12 matching topics and is listed first (it does not have the most hits, it had the most relevant ones).  I click on Admin Guide and get a VTOC (virtual table of contents...) for my search:

Administrator's Guide (12 matching topics)
 
    
    
      
About Oracle Background Processes
 Dropping Log Groups
 Dropping Online Redo Log Members
 Factors Affecting the Setting of ARCHIVE_LAG_TARGET<b>
 Forcing Log Switches</b>
 Log Switches and Log Sequence Numbers
 Managing the Online Redo Log
 Responding to Online Redo Log Failure
 Running a Database in ARCHIVELOG Mode
 Running a Database in NOARCHIVELOG Mode
 Setting the ARCHIVE_LAG_TARGET Initialization Parameter
 Setting the Size of Online Redo Log Members 


Ok, so -- if you ignore the reference (except to get explicit help on a specific command -- using the reference would be like using a spanish dictionary to try and phrase a question in spanish when you only speak english.  not very useful.  I'd rather has a PHRASE book at that point.  But, in order to find out what each word in a phrase meant, I'd want that dictionary no?)  and use the other guides, you'd find it is actually "pretty darn good"


In my last book -- I even set up a roadmap of required reading:


                  Concepts Guide (all)
                         |
                         v
                  New Features Guide (all)
                         |
 dev track +------------------------------+  dba track
           |                              |
    app. dev. guide                 backup/recovery concepts
           |                              |
    pl/sql guide                    rman guide
           |                              |
    performance planning            admin guide
           +------------------------------+
                        |
                   performance tuning 
                   guide and reference





And I wouldn't expect what I did above with the INSERT as SELECT to be "documented" as it is one of those things that "if you know sql, you should just 'know'".  It is "just sql".


You wanted 64k rows.  Well, we can generate as many rows as we want by selecting from some tables.


select null from all_objects, all_objects

just generates a result set that is the cartesian product of all_objects with all_objects.  The first row in all_objects is joined to every row in all_objects, then the second row and so on.  So, at the end of the day, we get cardinality(all_objects) * cardinality(all_objects) rows out of that.  Given that all_objects has 10's of thousands of rows in 8i and up -- that is "lots of rows".  We needed 64k so we just:


select null from all_objects, all_objects where rownum <= 65536

to stop it (rownum being a builtin very important psuedo column in Oracle -- if you use the above link to search for rownum, you can read about it if you don't know what it is).


Now, you needed "an i looping number from 0..on", well, we just 

select rownum-1 n from all_objects, all_objects where rownum <= 65536

and we get that.  Then, we just apply your formula.  



/* */ is a comment

/*+ .... */ is a HINT

/*+  APPEND */ specifically is a direct path insert, used in bulk loads for high speed loading.  You'll have to see:

even using just straightforward "multiply and divide" logic, you are talking about:

ops$tkyte@ORA9IR2> select 47264*16/60/60 from dual;
 
47264*16/60/60
--------------
    210.062222


210 hours.  

that is 2 BILLION rows that will be output -- every row in all_objects joined with every other row in all_objects! 

getting 3,000 rows per second is not something you can really measure, unless you add a ton of other assumptions.

3,000 rows via a full scan of a simple table in cache?
3,000 rows via a full scan of a simple table not in cache?
3,000 rows from a complex 5 table join with many predicates?
?????

and so on.  On my laptop - i get 10's of thousands of rows per second from all_objects (depending on the number of physical IO's i must perform to do it)

ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2> set arraysize 500
ops$tkyte@ORA9IR2> select * from all_objects;
 
30654 rows selected.
 
Elapsed: 00:00:01.46
 
Statistics
----------------------------------------------------------
         25  recursive calls
          0  db block gets
     173015  consistent gets
          0  physical reads
          0  redo size
    1935866  bytes sent via SQL*Net to client
       1170  bytes received via SQL*Net from client
         63  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      30654  rows processed
 

but like I said "rows per second" is not really a metric one typically measures.