Detalhes e exemplos de uso de tabelas temporárias no Oracle

In Oracle8In version i or above, the following two types of temporary tables can be created:

1Session-specific temporary table

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT PRESERVE ROWS;　

2Transaction-specific temporary table

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE MyTempTable　　

The temporary table that is created does exist, but try inserting a record and then logging in with another connection to select, the record is empty, see? I'll paste the following two sentences again:

--ON COMMIT DELETE ROWS indicates that the temporary table is transaction-specific, and ORACLE will truncate the table (delete all rows) after each commit.
--ON COMMIT PRESERVE ROWS indicates that the temporary table is session-specific, and ORACLE will truncate the table when the session is interrupted.

The issue of conflict does not need to be considered at all.

Temporary tables only store the data used by the current session (session), and the data exists only during the transaction or session period.

A temporary table is created using the CREATE GLOBAL TEMPORARY TABLE command. For transaction-type temporary tables, data exists only during the transaction period, and for session-type temporary tables, data exists during the session period.

The session data is private to the current session. Each session can only see and modify its own data. DML locks are not added to the data of temporary tables. The following statements control the existence of rows.

● ON COMMIT DELETE ROWS The table rows are only visible during the transaction period
● ON COMMIT PRESERVE ROWS The table name row is visible throughout the session.

Indexes, views, and triggers can be created on temporary tables, and the definition of the table can be imported and exported using the export and import tools, but the data cannot be exported. The table definition is visible to all sessions.

Temporary Tables

1Introduction

In addition to saving permanent tables, ORACLE database can also create temporary tables (temporary tables). These temporary tables are used to save the data of a session SESSION,

or stored in the data needed for a transaction. When the session exits or the user submits a commit and rollback transaction, the data in the temporary table is automatically cleared,
However, the structure and metadata of temporary tables are still stored in the user's data dictionary.

Temporary tables are only available in oracle8and above products are supported.

2Detailed introduction

Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables.

Session-level temporary tables refer to the data in temporary tables existing only within the session lifecycle. When the user exits and the session ends, Oracle automatically clears the data in the temporary table.
Transaction-level temporary tables refer to the data in temporary tables existing only within the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

The data in temporary tables is only valid for the current Session, each Session has its own temporary data, and cannot access the data in other Sessions' temporary tables. Therefore,

Temporary tables do not require DML locks. When a session ends (user normal exit, user abnormal exit, ORACLE instance crash) or a transaction ends, Oracle clears the session's

The TRUNCATE statement on the table clears the data in the temporary table. However, it does not clear the data in other sessions' temporary tables.

You can index temporary tables and create views based on them. Similarly, the indexes created on temporary tables are also temporary and are only valid for the current session or transaction.

Temporary tables can have triggers.

3Establish temporary tables

The definition of temporary tables is visible to all sessions SESSION, but the data in the table is only valid for the current session or transaction.

Establishment method:

1) ON COMMIT DELETE ROWS define a method to create transaction-level temporary tables.

CREATE GLOBAL TEMPORARY TABLE admin_work_area 
(startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT DELETE ROWS;

EXEMPLO:

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 
 (startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT DELETE ROWS; 
SQL> create table permanent( a number); 
SQL> insert into admin_work_area values(sysdate,sysdate, ‘temporarily table ‘); 
SQL> insert into permernate values(1); 
SQL> commit; 
SQL> select * from admin_work_area; 
SQL> select * from permernate; 
A 
1

2)ON COMMIT PRESERVE ROWS definiu o método de criação de tabelas temporárias de nível de sessão.

CREATE GLOBAL TEMPORARY TABLE admin_work_area 
(startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT PRESERVE ROWS; 
EXEMPLO:

Sessão1:

SQL> drop table admin_work_area; 
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 
2 (startdate DATE, 
3 enddate DATE, 
4 class CHAR(20)) 
5 ON COMMIT PRESERVE ROWS; 
SQL> insert into permernate values(2); 
SQL> insert into admin_work_area values(sysdate,sysdate, ‘session temporária ‘); 
SQL> commit; 
SQL> select * from permernate;
A 
---------- 
1 
2
SQL> select * from admin_work_area;
STARTDATE ENDDATE CLASS 
---------- ---------- -------------------- 
17-1;63;63; -03 17-1;63;63; -03 session temporária

Sessão2:

SQL> select * from permernate;
A 
---------- 
1 
2
SQL> select * from admin_work_area;

Linhas não selecionadas.

Sessão2Sessão invisível1Os dados da tabela temporária.

4 Diferenças entre tabelas temporárias do ORACLE e SQLSERVER

Tabela temporária do SQL SERVER

também pode criar tabelas temporárias. As tabelas temporárias são semelhantes às tabelas permanentes, mas são armazenadas no tempdb e são excluídas automaticamente quando não mais usadas.
Existem dois tipos de tabelas temporárias, local e global, que são diferentes em nome, visibilidade e disponibilidade. O nome das tabelas temporárias locais começa com um símbolo de número (#);
elas são visíveis apenas para a conexão de usuário atual; quando o usuário se desconecta do Microsoft63; SQL Server63; 2000 é excluída quando a instância é desconectada. O nome das tabelas temporárias globais começa com símbolos matemáticos

(##) começando, tornando-se visível para qualquer usuário após sua criação, e sendo excluída quando todos os usuários que referenciam a tabela se desconectarem do SQL Server.
Por exemplo, se criar uma tabela chamada employees, qualquer pessoa que tenha permissão de segurança para usar essa tabela no banco de dados pode usá-la, a menos que ela já tenha sido excluída.
Se criar uma tabela temporária local chamada #employees, apenas você pode executar operações nessa tabela e ela será excluída ao desconectar. Se criar uma tabela temporária global chamada ##employees

Qualquer usuário na tabela de dados pode executar operações na tabela. Se a tabela não for usada por outros usuários após sua criação, ela será excluída quando você se desconectar. Se a tabela for usada

Se outro usuário usar após o uso, o SQL Server deletará a tabela após todos os usuários se desconectarem

Diferente:

1. A tabela temporária do SQL SERVER é uma "tabela em memória", a tabela é armazenada em memória. A tabela temporária do ORACLE, a menos que seja executado DROP TABLE, a definição da tabela será mantida no dicionário de dados.

2. A tabela temporária do SQL SERVER não possui funcionalidades semelhantes ao nível de transação da tabela temporária do ORACLE.

3 A tabela temporária local do SQL SERVER (#) é semelhante à tabela temporária de nível de sessão do ORACLE, mas o ORACLE não deleta a tabela quando a sessão sair.

4 A tabela temporária global do SQL SERVER (##) se refere a múltiplas conexões compartilhando o mesmo espaço de memória. Quando não há mais referências para a área de memória, o SQL SERVER libera automaticamente a tabela temporária global.

5 Como o ORACLE não é um banco de dados em memória, se o ORACLE, semelhante ao SQL SERVER, criar e excluir frequentemente tabelas temporárias, definitivamente afetará o desempenho.
Portanto, o ORACLE manterá a definição da tabela temporária até que o usuário DROP TABLE.

6 No ORACLE, se vários usuários precisarem compartilhar uma tabela (semelhante ao tabela temporária global do SQL SERVER ##), pode-se usar a tabela permanente,

E adicione algumas colunas que possam identificar exclusivamente os usuários na tabela. Utilize gatilhos e visões. Quando o usuário sair, delete os dados correspondentes da tabela com base nas informações únicas do login do usuário.

Este método traz uma quantidade significativa de carga para o ORACLE.

A seguir está a organização de informações sobre a tabela temporária do Oracle, continuaremos a complementar informações relacionadas, obrigado pelo apoio da comunidade!

Tutorial básico