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:
- 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.
- What would be the implications for backups (there should be no backup of the ephemeral data).
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.