Oracle Database Temp Table Learning notes

The concept of a temporary table

There is also a type of table in the Oracle database, called a temporary table. The biggest difference between this temporary table and the permanent table is that the data in the table doesn't exist forever. When the end of a session or the end of the transaction, the data in this temporary table, not deleted by the user themselves, the database will automatically clear itself. (table is global, only data disappears)

A session-level temporary table refers to data in a temporary table that exists only during the session lifecycle, and when the user exits the session, Oracle automatically clears the data in the temporary table. Nanjing Oracle Certification Training

A transaction-level temporary table means that data in a temporary table exists only in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table. Data in a temporary table is valid only for the current session, each session has its own temporary data, and cannot access data from other temporary tables in the session. Therefore, a staging table does not require a DML lock. When a session ends (the user normally exits the user from an abnormal exit of the Oracle instance) or a transaction ends, Oracle clears the temporary table data from the table executing the TRUNCATE statement for the session. The data in the other session temp table is not emptied.

You can index temporary tables and build views on the basis of temporary tables. Similarly, indexes built on temporary tables are temporary and are valid only for current sessions or transactions. Temporary tables can have triggers.

1. Session-specific temporary tables

The code is as follows	Copy Code
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>) On COMMIT PRESERVE ROWS;

2. Transaction-specific temporary tables

The code is as follows	Copy Code
CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>) On COMMIT DELETE ROWS;

3.SESSION Level temp Table

The code is as follows	Copy Code
1. Establishment of temporary tables Create global temporary table Temp_tbl (col_a varchar2 (30)) On commit Preserve rows 2. Inserting data INSERT into TEMP_TBL values (' Test session table ') 3. Commit a commit; 4. Query data Select *from temp_tbl

You can see that the data ' test session table ' record is still in.

End session, login again, query data Select *from TEMP_TBL, this time the record no longer exists because the system automatically clears the record when the session is ended

3, transaction-level temporary table

The code is as follows	Copy Code
1. Establishment of temporary tables Create global temporary table Temp_tbl (col_a varchar2 (30)) On commit Delete rows 2. Inserting data INSERT into TEMP_TBL values (' Test Transaction table ') 3. Submit Commit 4. Query data Select *from temp_tbl

At this point you can see that the record you just inserted ' Test transaction table ' no longer exists; again, if you do not commit and end the session directly, the login record does not exist

4. Create with stored procedure:

The code is as follows	Copy Code
Create Procduce Test Is ISQL VARCHAR2 (200); Dptable varchar2 (MB): = ' drop table test '; Begin isql:= ' Create global temporary table test (SID Int,sname Varchar2 ()) on commit delete rows; Execute immediate isql; --Create a temporary table INSERT into test values (1, ' abc '); Execute immediate dptable; ---delete temporary tables End

For an Oracle temporary table called in a stored procedure, I think it makes no sense, Oracle provides predefined temporary tables in the database and allocates space in the temp table space only when called, which means you can create temporary tables in the database in advance. And then go to the stored procedure and call the temp table directly.

similarities and differences between 5.ORACLE temporary tables and SQL Server temporary tables

SQL Server Temp Table

You can also create temporary tables. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and automatically deleted when they are no longer in use.
There are both local and global types of temporary tables, which differ in name, visibility, and availability. The names of local temporary tables start with a single number sign (#), and they are visible only to the current user connection, when the user is from Microsoft? SQL Server? 2000 instance is deleted when disconnected. The name of the global temporary table begins with a mathematical symbol (# #), which is visible to any user after it is created, and is deleted when all users referencing the table are disconnected from SQL Server.
For example, if you create a table named employees, anyone who has security permissions to use the table in the database can use the table unless it has been deleted. If you create a local temporary table named #employees, only you can perform an operation on the table and delete the table when you disconnect. If you create a global temporary table named # #employees, any user in the datasheet can perform an action on the table. If the table is not used by another user after you create it, the table is deleted when you disconnect. If the table is used by another user after you create it, SQL Server deletes the table after all users have disconnected
Different:
1. A SQL Server temp table is a "memory table" that is stored in memory. Oracle Temporary tables The table definition remains in the data dictionary unless the drop table is executed.
2. SQL Server temp tables do not have features similar to the Oracle Temp table transaction level.
3 SQL Server local temporary table (#) is similar to Oracle's session-level temporary table, but Oracle does not delete the table when the session exits.
4 SQL Server's global temporary table (# #) means that multiple connections share the same piece of memory. SQL Server automatically releases global temporary tables when there is no pointer to the memory area.
5 because Oracle is not an in-memory database. So if Oracle is similar to SQL SERVER's frequent creation and deletion of temporary tables, it will certainly affect performance. So Oracle retains the definition of the temporary table until the user drops table.
6 in Oracle, if multiple users are required to share a table (SQL Server-like global temporary table # #). You can take advantage of a permanent table and add some columns to the table that uniquely identify the user. Use triggers and views. When the user exits, Deletes data from the corresponding table based on the unique information of the logged in user. This approach brings a certain amount of load to Oracle.