In Oracle, can you create a table that exists only while the database is running?

Question

Is there a way in Oracle to create a table that exists only while the database is running and is only stored in memory? So, if the database is rebooted, will I have to recreate the table?

Edit: I want the data to be stored in different sessions. The reason is that these roads are recreational, but also very sensitive.

Using a temporary table is likely to help improve performance over what is happening today, but its still not a great solution.

Answer 1

You can use the Oracle startup mechanism to invoke a stored procedure when the database starts or ends.

That way you can have a trigger trigger creating a table, and a shutdown trigger will disable it.

You probably also want the start trigger to handle cases where the table exists and truncate it just in case the server suddenly stops and the shutdown trigger was not called.

Oracle trigger documentation

Answer 2

You can create a 100% ephemeral table that can be used throughout the session (usually shorter than the database execution time), called the TIME table. The whole purpose of a table in memory is to make it faster to read. You will need to re-populate the table for each session, as the table will be forgotten (both structure and data) after the session ends.

Answer 3

No no. Oracle has the concept of a "global temporary table". With a global temporary table, you create a table once, just like in any other table. The definition of the table will be persistent, as in any other table.

The contents of the table, however, will not be permanent. Depending on how you define it, the content will be stored either during the life of the session (when fixing the lines) or in the life of the transaction (when fixing the delete lines).

See the documentation for all the details: http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables003.htm#ADMIN11633

Hope this helps.

Answer 4

Using Oracle Global Temporary Tables , you can create a table in memory and delete it at the end of the transaction, or the end of the session.

Answer 5

If I understand correctly, you have some data that needs to be processed when the database is displayed on the network and remains available only as long as the database is online. The only use case I can think of will require this if you are encrypting some data and you want unencrypted data to never be written to disk.

If this is really your use case, I would recommend forgetting about trying to create your own solution for this and use Oracle encrypted table spaces or Transparent data encryption instead.