Create and use a temporary table.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Create and use a temporary table.:

Temporary table ORACLE has a significant role in the application system, it can let users can only operate their own data and do not interfere with each other, without fear of damage or affect other SESSION/TRANSACTION data, a method for solving it is data security.
The temporary table definition contains all the sessions can access, but the line is inserted for session specific. Programmers can use it as a private storage area for handling large amounts of data, the syntax is as follows:
Create global temporary table temp_tab_name
(column datatype[,clomndatatype])
[on commit {delete| preserve } rows]

There is no difference between ordinary table column definition, but can query to provide. Insert any line of the life cycle to determine the end of the optional clause; default: completed in the insert row of affairs, delete rows, but you can also change the behavior, so that they retain to insert rows the session ends, regardless of the option, the data is specific to each session: different users can his line into the copy of the table itself, who can not see other people's Bank,

Temporary tables and a permanent table like. The two difference is that: the temporary table data is temporary, dedicated to the corresponding session, for all of the SQL commands its execution speed than for a permanent table command quickly;
For all of the SQL commands its execution speed than for a permanent table command quickly:
(1) The temporary table is not a permanent table space segment, ideally they only exist in the use of their PGA session, it does not involve the disk activity, nor even in the active database buffer cache. If PGA cannot be expanded to store temporary table level (if inserted into millions of lines, this can occur, but in the process of complex report, this is rarely seen), then the table will write to a user temporary table space. The temporary table space on the I/O than a permanent table space on the I/O speed, the reason is temporary tablespace does not use the database buffer cache, and all by the session server process directly to disk in execution,
(2) For the temporary table DML does not generate redo data, because the data is in session survival period (may only be retained during the transaction), is not necessary to generate the redo data. This brings the dual benefits: the list of session DML faster.

The temporary table is divided into SESSION, TRANSACTION two, temporary table data class SESSION exist in the whole SESSION, until the end of the SESSION; and the temporary table data class TRANSACTION disappeared after TRANACTION, i.e. COMMIT/ROLLBACK or over SESSION will clear TRANACTION temporary table data.
Two temporary table.:
Create global temporary table on commit preserve|delete rows temporary table name for preserve is SESSION temporary tables, temporary table delete is TRANSACTION class
A temporary table, SESSION
1, Create temporary table
create globaltemporary table temp_test1(a varchar2(30))
on commit preserverows
2, Insert data
insert into temp_testvalues('test session')
3, Submit commit,
4, Query data
select *from temp_test
Test data'test session'still, because of the temporary table is at the session level.
The end of SESSION, another open session, and then query data select *from temp_test1, this record has not existed, because the system automatically clear the record at the end of SESSION

In two, grade TRANSACTION temporary table
1, Create temporary table
Sql code
create globaltemporary table temp_test2 (a varchar2(30))
on commit deleterows
2, Insert data
insert into temp_test2values('test transaction ')
3, Submit
4, Query data
select *fromtemp_test2

This time the transaction has ended (COMMIT), can be seen record'test transaction'was inserted to do not exist already; of course, if not presented directly over SESSION, log back in the record of the same does not exist, because the session ended with a default commit operations, leading to the end of the transaction!!!

1 to HR users connect to the database
23:34:29 sys@felixSQL>conn hr/oracle

2 in the following way to build tables
23:34:34 hr@felix SQL>createglobal temporary table tmp_emps on commit preserve rows as select * fromemployees where 1=2;

Table created.
3 insert some rows
23:36:55 hr@felix SQL>insert intotmp_emps select * from employees where department_id=30;

6 rows created.

23:39:29 hr@felixSQL>commit;

4 Commit complete. HR users to open a session
[oracle@felix ~]$ sqlplushr/oracle
5 in second paintings in confirming the first insert the first session has been submitted but not visible:

23:37:47 hr@felixSQL>select count(*) from tmp_emps;


23:38:41 hr@felixSQL>insert into tmp_emps select * from employees where department_id=50;

45 rows created.

23:39:29 hr@felixSQL>commit;

Commit complete.

6 first session, the table truncation:

23:40:31 hr@felixSQL>truncate table tmp_emps;

Table truncated.

7 in the second session, that session duplicate table still contains the row:
23:39:33 hr@felix SQL>select *from tmp_emps;
45 rows selected.
8 demo in second session session termination is not clear, the need to disconnect and reconnect:
23:42:25 hr@felix SQL>disconnect;
Disconnected from Oracle Database 11gEnterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
23:42:47 hr@felix SQL>connect hr/oracle

23:42:55 hr@felixSQL>select count(*) from tmp_emps;


9 in the two session by deleting the table to clean up
23:43:19 hr@felixSQL>truncate table tmp_emps;

Table truncated.

23:54:39 hr@felixSQL>drop table tmp_emps purge;

Table dropped.

23:55:02 hr@felixSQL>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Carry at December 09, 2013 - 9:51 PM