Thrash Database

Introduction:

Now that we know that we can connect to the database(s) let's thrash the hell out of them. If successful, we achieve two things; we prove a hell of a lot about whether they are viable (this test touches on a LOT of basic Oracle operation), and we tell it who's boss. Databases need to know this stuff. They are a bit like kids really - they need a good thrashing every now and again.

Apologies for the 'width' of this post. There is a very long string in the code below and it really needs to be there.

Aim:

Give us a warm, fuzzy feeling about our database(s).

Requirements:

At least one database is installed and running.

Procedure:

This test should be run for each installed database:

• Log on as oracle.
• Open Terminal.
• Run the following script:
• Best. Download this.
• Or. Copy this to a file (probably called test.sql):
  

-- creating a table drop table foo; create table foo ( foo_key integer primary key, random varchar(1000) ); -- creating an index create index foo_on_random on foo ( random ); -- inserting some rows insert into foo values (1, '1'); insert into foo values (2, '2'); insert into foo values (3, '3'); insert into foo values (4, '4'); insert into foo values (5, '5'); insert into foo values (6, '6'); insert into foo values (7, '7'); insert into foo values (8, '8'); insert into foo values (9, '9'); insert into foo values (10, '10'); insert into foo values (11, null); insert into foo values (12, null); insert into foo values (13, null); insert into foo values (14, null); insert into foo values (15, null); insert into foo values (16, null); insert into foo values (17, null); insert into foo values (18, null); insert into foo values (19, null); insert into foo values (20, null); -- create another table to work with create table bar as select foo_key + 1 as bar_key, random from foo; -- joins select b.random from foo f, bar b where f.foo_key = b.bar_key and f.random like '3%'; -- update update foo set foo_key = foo_key + 100 where random is null; -- should return 10 select count(*) from foo where foo_key > 100; -- create a sequence create sequence foo_sequence start with 200; -- test whether truncate works truncate table bar; drop table bar; -- test 1) whether has privileges to create a procedure -- and 2) whether rollback segments are adequately sized -- create a pl/sql procedure create or replace procedure thrash_database(v_number_of_rows IN integer) AS i integer; BEGIN FOR i IN 1..v_number_of_rows LOOP insert into foo (foo_key, random) values (foo_sequence.nextval, 'abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij'); END LOOP; END thrash_database; / show errors -- An Oracle installation ought to be able to handle -- 100,000 rows of 500 bytes each execute thrash_database(100000); select count(*) from foo; commit; delete from foo; commit; drop table foo; drop sequence foo_sequence; drop procedure thrash_database; -- Make sure that NLS_DATE_FORMAT is correct by -- seeing that the following command returns -- DD-MON-YY. select sysdate from dual;

• Connect to a database as system:
• $ sqlplus system/*******@[dbs] (where ********* is the system user password, and [dbs] is the SID of the database.
• Run the test script:
• SQL> @test
  
• This will report a load of stuff but there should be no errors apart from; 'ERROR at line 1: ORA-00942: table or view does not exist' (this just makes sure the table the script is creating is not already there). It also takes a while - we're thrashing it, right!
• Finally, the script reports today's date. It should be of the format: 'DD-MON-YY'.
• Exit SQL*PLUS:
• SQL> exit

Don't forget to run this for all instances.