Creating Testing Tables

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
/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License