I have a personal Oracle installation on my development machine (Oracle 12.1 EE, Windows 7, 16 GB RAM (about 7 of which is unused at all times), SSD) which is used exclusively for running automated tests.

This means:

  • Default Oracle installation using their GUI - all defaults were accepted during installation.
  • One database user/schema.
  • The schema is wiped before and after every run of the whole test suite (i.e. database objects are regularly dropped and recreated; the user itself is not dropped). This step does not need to be optimized, its time is insignifikant in relation to the total runtime.
  • During the test run, between individual scenarios, the tables are either wiped by ROLLBACK or TRUNCATE depending on circumstances.
  • All objects, including all data rows, are recreated everytime the suite runs. There is no permanent data staying around between suite runs or individual tests.
  • It would be totally irrelevant if there were any data loss at any time, up to including having to wipe the whole schema or even the DB itself.


Obviously, there is a lot of overhead right now, since Oracle doesn't know that I don't care about my data. So it will have its redo log etc. chugging along like usual.

What is a good configuration to make my use case as quick as possible? I.e., turning off as many security/consistency/history features as possible; avoiding writing buffers to the HDD as far as possible, etc.?

The time for the one-time initial setup and teardown (i.e. CREATE TABLE ... DROP TABLE) is insignificant in relation to the runtime of the test suite.


You can either "drop user test_user cascade;", then create the user again with all needed grants and objects, or drop the database and restore a backup of the database while it is empty. Depending on the number of tables, indexes and constraints one of the two methods will be faster. The more tables, indexes and constraints, the more likely just dropping the database and restoring a backup of the empty database will be faster.

  • This relates to one part of the question, but unfortunately I am not concerned about the initial setup/teardown; I have updated the question. – AnoE May 5 '17 at 14:42
  • It still sounds like a initial setup/tear down problem to me. You won't need to turn off any security/consistency/history features if you drop the database and restore the database from an empty shell backup using RMAN. – Gandolf989 May 8 '17 at 15:02
  • "It still sounds like..." Then I guess I have failed in formulating the question. – AnoE May 8 '17 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.