Need DDL for an Oracle Table that would be 1 row = 1 8K Block
If all you want to do is waste a lot of space, you don't need a fancy table at all. A single varchar(4000) (filled up) should be enough:

SQL&gt; create table foo(a varchar(4000));

Table created.

SQL&gt; insert into foo select rpad(to_char(rownum), 4000, '*')
                     from dual connect by level &lt;= 1024 ;

1024 rows created.

SQL&gt; exec dbms_stats.gather_table_stats(ownname =&gt; user, tabname =&gt; 'FOO');

PL/SQL procedure successfully completed.

SQL&gt; select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126


Reason: there's no room to put another 4000 byte row in a 8k block given the block and row headers.

Or play with a small row but a stupid PCT_FREE. For example:

SQL&gt;  create table foo (a varchar(128)) pctfree 99 ;

Table created.

SQL&gt; insert into foo select rpad(to_char(rownum), 128, '*') 
                     from dual connect by level &lt;= 1024 ;

1024 rows created.

SQL&gt; commit ;

Commit complete.

SQL&gt; exec dbms_stats.gather_table_stats(ownname =&gt; user, tabname =&gt; 'FOO');

PL/SQL procedure successfully completed.

SQL&gt; select num_rows, blocks from user_tables where table_name = 'FOO';

  NUM_ROWS     BLOCKS
---------- ----------
      1024   1126


Add an ordinary primary key and you can select whatever you want from there, with one row eating up about one block in SGA.

What you must really avoid is full-scanning that table (or anything that goes to direct reads and bypasses the SGA).



That being said, have you looked into simply reducing the SGA on your test system? If your volume of data (or the "width" of the dataset you can test) is X times smaller than production, start testing with X times less SGA and tweak (up or down) to get your IO subsystem to work. (You'll probably want more than X times less if your production SGA isn't large.)

I believe you'll have a better chance of having the SGA contents more representative of a real system (especially index/data block mix).