Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

For performance reasons in some scenarios, e.g. Amazon EC2, you have access to a faster and cheaper storage device, which loses all its data on reboots, so it is called "ephemeral".

This question is about taking advantage of such type of storage in installations of the Oracle database. Which breaks down into:

  1. What is a way to keep a tablespace's datafiles on ephemeral storage and have Oracle create those on boot (and possibly run some scripts to create/populate a few tables) and be ok when they are lost.
  2. What would be the implications for backups (there should be no backup of the ephemeral data).
  3. Any other possible consideration for tables and other object on top of it

    • possible optimizations: e.g. disable logging.
    • what is lost with a reboot (data or data+metadata)

The TEMP tablespace is a perfect candidate for this optimization and in fact for MS SQLServer's equivalent there are on the web howtos for doing just this.

Let's consider a data warehouse, as the reference use case, with a recurring job that imports many gigabytes of data (from CSVs or datapump) into a staging schema "STG" and a subsequent ETL process that saves the results on the production schema.

This workload would benefit a lot from having very fast read and write access on the staging schema, easily tolerating the volatility of its data.

share|improve this question

migrated from May 16 '13 at 7:23

This question came from our site for professional and enthusiast programmers.

Great question! One can certainly take a tablespace offline, see But I suspect the easiest way to automate this would be to drop the tablespaces before shutting down the Oracle instance. Then there's the issue of what would happen on a machine crash. I'm more of a "logical DBA" so I'll leave it to a "physical DBA" to answer this question. – Colin 't Hart May 16 '13 at 7:42
Can you give a use-case for this? – David Aldridge May 20 '13 at 11:23
@DavidAldridge: any Oracle deployment on the Amazon cloud is a use-case. – Andrea Ratto May 22 '13 at 12:37

Oracle does not record checkpoint information in tempfile. So, for the tempfile:

  1. Oracle is able to start even if tempfile is missing. You will get a message in the alter log and you should recreate tempfile.

  2. No backup is taken of tempfile so it's safe to loose it

  3. There is no need to trick with any storage parameter for tempfile if resides on ephemeral storage.

About physical data structure are not tempfile, it is not safe to put them in the ephemeral storage. Oracle keep track of changes in the datafile even if you have disabled logging. Disabling logging will have only effect in the recoverability of the database, redo and overall performance. Does not permit you to put datafile on ephemeral storage. Opening the DB with a missing database (database does not take care of how important were data on it) will raise.

ORA-01116: error in opening database file %s
ORA-27041: unable to open file
ORA-01157: cannot identify/lock data file %s - see DBWR trace file
ORA-01119: error in creating database file '%s'

And you have to manually deal with the issue. Hence the use of ephemeral storage with normal datafile have to be avoided.

share|improve this answer
Thanks. So do you think that just creating a temporary tablespace on top tempfiles in the ephemeral storage is enough? After a reboot it will just have all empty tables or will it loose the schema as well? – Andrea Ratto May 22 '13 at 13:10
@AndreaRatto Temporary tablespace does not contain any table but only transient (temporary) object like GTT (Global Temporary Table) and data have to be sorted in a DML process for example. There is no SCHEMA in temporary table or other standard objects. – Ste May 22 '13 at 13:15

As Ste says in his answer, temp tablespaces should be fine for this, so global temporary tables and other transient storage would be good.

If you wanted to use permanent tablespaces then you could DROP TABLESPACE my_tablespace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS before system shutdown if the storage was going to be lost, and recreate on startup. You'd probably want to have something on startup to handle loss of the data files if the RDBMS had not removed them itself.

share|improve this answer
The startup after a failure is the tricky part no doubts... – Andrea Ratto May 22 '13 at 13:30
Yes, quite so -- of course db startup ought to be a fairly rare event, conventionally. – David Aldridge May 22 '13 at 15:46

In addition to what the others have said, you could keep some data in flat files and them copy the files to the ephemeral storage on startup. You could then access it using external tables. I don't know if this would be faster or better, but it is another possible use.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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