Introduction
The following scripts are useful for creating tables for testing out various features of Oracle or for using when testing application code.
Random Strings
The following script creates a 3 column table, described below. The pctfree and pctused values have been selected to help ensure one row per block. This can be particularly useful in a testing scenario. However, using low values for the padding or strval, will result in more than one row per block. For example, an STRVAL of 1 and a PADDING value of 1 on an 8k blocksize will result in 5 rows per block. As such, a link can be found below which will calculate the number of rows per block.
ID - This column contains an incrementing key
STRVAL - This column contains a random alphanumeric string of variable length.
PADDING - This column is used to pad out the row if required and contains 'x' values.
The following script can be used to check the number of rows per block: Check the Number of Rows per Block
ACCEPT pad PROMPT "Enter the length of your padding value?" ACCEPT ran PROMPT "Enter the length of your random string value? " ACCEPT rnums PROMPT "Enter the number of rows in the table? (Max Values is limited by the rows in all_objects) :" CREATE TABLE testing_table PCTFREE 99 PCTUSED 1 TABLESPACE tools AS SELECT rownum id, dbms_random.string('X',&ran*dbms_random.value) strval, rpad('x',&pad,'x') padding FROM all_objects WHERE rownum <=&rnums /
