Create temporary tables, external tables, and partitioned table instances in Oracle

Temporary tables

In Oracle, a temporary table is "static," which, like a regular datasheet, needs to be created only once, and its structure is valid for the entire period from creation to deletion. In contrast to other types of tables, a temporary table allocates space for the user when the data is actually added to the table, and the allocated space is from the temporary table space. This avoids data contention storage space with persistent objects.

The syntax for creating a temporary table is as follows:

CREATE GLOBAL Temporary TABLE table_name (column_name data_type,[column_name data_type,...]) On COMMIT delete| PRESERVE ROWS;

Because temporary tables store data that is only valid during the current transaction or session
As a result, temporary tables are classified as transaction-level temporary tables and session-level temporary tables.

transaction-level temporary tables

To create a transaction-level temporary table, you need to use the on COMMIT delete rows clause, and the record of the transaction-level temporary table is automatically deleted after each commit transaction.

Example 1:

CREATE GLOBAL Temporary TABLE tbl_user_transcation (ID number, uname VARCHAR2 (a), Usex VARCHAR2 (2), Ubirthday DATE) on C Ommit DELETE ROWS;

session-level temporary tables

To create a session-level temporary table, you need to use the on COMMIT PRESERVE rows clause, and the record of the session-level temporary table is automatically deleted after the user disconnects from the server.

Example 2:

CREATE GLOBAL Temporary TABLE tbl_user_session (ID number, uname VARCHAR2 (a), Usex VARCHAR2 (2), Ubirthday DATE) on Commi T PRESERVE ROWS;

Operation Temp Table

Inserts a data to a transaction-level temporary table but does not commit a transaction:

INSERT into Tbl_user_transcation VALUES (1, ' siege ', ' M ', to_date (' 1991-02-28 ', ' yyyy-mm-dd ')); SELECT * from Tbl_user_transcation;

At this point, the query results are as follows:

1 Siege M 28/02/1991

If a commit is made, there is no data in the table, indicating that Oracle has deleted the data.

Inserts a data to a transaction-level temporary table:

INSERT into Tbl_user_session VALUES (1, ' siege ', ' M ', to_date (' 1991-02-28 ', ' yyyy-mm-dd ')); SELECT * from Tbl_user_session; COMMIT;

At this point, there is still data in the tbl_user_session even if a transaction is committed.
At this point, when the session is closed (disconnect the database), then connect to the database, and the query will have no data.

Note: In Pl/sql developer The default configuration is to open a window, that is, to re-establish a session, so be careful to set the shared session,

External Table

An external table is a read-only table of data stored in a file system that is provided by Oracle that can read the operating system. The data in the external table is stored in the operating system's file system and can only be read and cannot be modified.

Create an external table

Log in as SYSDBA first, giving the user relevant permissions:

GRANT CREATE any DIRECTORY to siege;

Then log on as a user to create the directory:

CREATE DIRECTORY external_student as ' d:\ ';

Finally, create an external table:

Example 3:

CREATE TABLE tbl_external_student (SID number, sname VARCHAR2 (), Sclass VARCHAR2 (3), Ssubject VARCHAR2 (), Sscore NUMB ER) Organization EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY external_student ACCESS PARAMETERS (FIELDS terminated by ', ') LOCATION (' Student.csv ')

Note: The file under the external D disk Student.csv is as follows:

10001,siege,304,physics,80

The query tbl_external_student is consistent with the above display.

Partition Table

In large database applications, the amount of data that needs to be processed can even be TB-level. To improve reading and writing and query speed, Oracle provides a partitioning technique that allows users to apply partitioning techniques to save data in a partitioned format when creating tables.

Partitioning is the separation of a table or index into a relatively small, independently manageable part. The partitioned table does not differ from the unpartitioned table when it executes the DML statement.

To partition a table, you must specify the partition you belong to for each record in the table. Which partition a record belongs to is determined by the partition table's matching field for the record. A partitioned field can be a field in a table or a combination of multiple fields, as determined when the partition table is created. When a user inserts, updates, or deletes a partitioned table, Oracle automatically selects the stored partition based on the value of the partition field.