SQL*Loader   «Prev  Next»

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

Create Temporary Table in Oracle

A temporary table becomes useful in the following situations. 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:
  1. 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.
  2. 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.
  3. 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.

Oracle DBA Mentor

Create Oracle Temporary Table

Creating a temporary table 
CREATE GLOBAL TEMPORARY TABLE tablename
(column specifications)
ON COMMIT (DELETE ROWS | PRESERVE ROWS)

CREATE GLOBAL TEMPORARY TABLE PHONE_BILLING
(CUSTOMER_ID NUMBER,
 BILLING_PERIOD VARCHAR2(20),
 TRANSACTION_DATE DATE,
 TRANSACTION_TYPE VARCHAR2(10),
 DURATION NUMBER,
 CHARGE_AMOUNT NUMBER,
 TAX_AMOUNT NUMBER)
ON COMMIT PRESERVE ROWS;

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

Managing Temporary Tablespace for Temporary LOBs

Temporary tablespace is used to store temporary LOB data. As a database administrator you control data storage resources for temporary LOB data by controlling user access to temporary tablespaces and by the creation of different temporary tablespaces.
There are some restrictions when using temporary tables:
  1. You cannot create foreign key constraints on a temporary table.
  2. Temporary tables cannot be partitioned, index organized, or clustered.
  3. 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:
    1. Combine a fixed number of physical records to form each logical record.
    2. Combine physical records into logical records while a certain condition is true.
  • Data Fields
    Once a logical record is formed, field setting on the logical record is done. Field setting is a process in which SQL*Loader uses control-file field specifications to determine which parts of logical record data correspond to which control-file fields. It is possible for two or more field specifications to claim the same data. Also, it is possible for a logical record to contain data that is not claimed by any control-file field specification. Most control-file field specifications claim a particular part of the logical record. This mapping takes the following forms:
    1. 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.
    2. 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.
    3. 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.
    4. 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.

SEMrush Software