Shulou(Shulou.com)06/03 Report--

This article will explain in detail how ORCAL temporarily creates and deletes tables. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

I. Orcal temporary table classification 1. Session-level temporary table

1)。 Save the data for a session Session.

2)。 The temporary table data is emptied automatically when the session exits. The table structure and metadata are also stored in the user data dictionary.

Summary: session-level temporary table means that the data in the temporary table only exists in the session life cycle. When the user exits the session, Oracle automatically clears the data in the temporary table.

two。 Transaction-level temporary table

1)。 Save the data needed in a transaction.

2)。 The temporary table data is automatically emptied when the transaction is committed or rolled back. The table structure and metadata are also stored in the user data dictionary.

Summary: transaction-level temporary table means that the data in the temporary table exists only in the transaction life cycle, and Oracle automatically clears the data in the temporary table when the transaction is committed or rolled back.

two。 Temporary table creation 1. Session-level temporary table

1)。 Create data before inserting data

Create Global Temporary Table Table_Name (Col1 Type1,Col2 Type2...) On Commit Preserve Rows; Insert Into Table_Name Values (",")

2)。 Insert data directly when creating

CREATE GLOBAL TEMPORARY TABLE Table_Name ON COMMIT PRESERVE ROWS AS select Col1, Col2 from Query_Table_Name where...;2. Transaction-level temporary table

1)。 Create data before inserting data

Create Global Temporary Table Table_Name (Col1 Type1,Col2 Type2...) On Commit Delete Rows; Insert Into Table_Name Values (",")

2)。 Insert data directly when creating

CREATE GLOBAL TEMPORARY TABLE Table_Name ON COMMIT Delete ROWS AS select Col1, Col2 from Query_Table_Name where...; III. Delete temporary table

If the session that created the temporary table did not end, the temporary table cannot be deleted because the temporary table is still in use. But after you finish the session (close the command window that creates the session-level temporary table), you can delete it.

Drop Table Table_name

four。 Delete Times error

Description: close the previous command window and execute Drop Table Table_name

Error: ORA-14452: an attempt was made to create, change, or delete an index in a temporary table in use

1. Clear the table, and then delete the table TRUNCATE TABLE test_table;drop table test_table;2. Kill the process, then delete SELECT sid, serial# FROM v$sessionWHERE sid = (SELECT sid FROM v$lockWHERE id1 = (SELECT object_id FROM user_objectsWHERE object_name = upper ('test_table')

Perform authorization if you do not have permission

Grant select any dictionary to user

Query the sid and serial#: of the session

Then kill the process:

Lter system kill session 'sid,serial#'

Finally, delete.

Drop table test_table

This is the end of the article on "how to temporarily create and delete tables in ORCAL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.