Create Temporary Table(Billing Cycle)

Create Temporary Table in Oracle

A temporary tableoften comes in handy. For example, if you were preparing a telephone billing statement, you would select the transactions from the current customer data and add them to a transaction table. Once the billing cycle was over, you would need to erase the transaction table's data in preparation for the next billing cycle.
Before Oracle, you would have done all of this was using normal database tables. A few problems with this method were:

If processing stopped somewhere in the middle of the task, you had to remove data by issuing TRUNCATE or DELETE commands before restarting the processing.
Large volumes of data written to these interim tables caused a lot of logging activity. This activity is standard for a database that uses archiving, but not necessary for this kind of table and therefore an extra expense in time and log volume.
A second run of the processing could not start until the first one was completed and the interim data was flushed from the table.

The addition of temporary tables solves these problems. A temporary table is created using the CREATE GLOBAL TEMPORARY TABLE. The table stays, but the data exists only for the duration of the session or transaction. If two sessions use the same temporary table at the same time, each session will only see its own data. Data in a temporary table is not archived for backup and recovery. It is, however, logged for a transaction rollback. You can also add an index or a view that uses a temporary table. The data in the index is temporary, just like the table data.
All these features mean that Oracle provides a simple and efficient way to store temporary data. The MouseOver below illustrates the syntax and an example of the command to create a temporary table.

DELETE ROWS means that the Oracle will truncate the table (all rows are deleted) after each commit.
PRESERVE ROWS means that Oracle will not truncate the table until the end of the session.

Create Global Temporary Table

There are some restrictions when using temporary tables:

You cannot create foreign key constraints on a temporary table.
Temporary tables cannot be partitioned, index organized, or clustered.
Temporary tables cannot contain nested tables or varrays.

Logical Records

SQL*Loader organizes the input data into physical records, according to the specified record format. By default a physical record is a logical record, but for added flexibility, SQL*Loader can be instructed to combine several physical records into a logical record. SQL*Loader can be instructed to follow one of the following logical record-forming strategies:

Combine a fixed number of physical records to form each logical record.
Combine physical records into logical records while a certain condition is true.

Data Fields

The byte position of the data field's beginning, end, or both, can be specified. This specification form is not the most flexible, but it provides high field-setting performance.

The strings delimiting (enclosing, terminating, or both) a particular data field can be specified. A delimited data field is assumed to start where the last data field ended, unless the byte position of the start of the data field is specified.

You can specify the byte offset, the length of the data field, or both. This way each field starts a specified number of bytes from where the last one ended and continues for a specified length.

Length-value data types can be used. In this case, the first n number of bytes of the data field contain information about how long the rest of the data field is.

Create Temporary Tables - Quiz

Click the quiz link below to test your knowledge of table management concepts.
Create Temporary Tables - Quiz
In the next lesson, you will learn how easy it is to drop a column.

Lesson 6	Create a temporary table
Objective	Describe why and how to create a temporary table.