Create global temporary table working_emps on commit preserve rows
SQL>
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');
1 row created.
SQL>
SQL>
SQL> create global temporary table working_emps
2 on commit preserve rows
3 as select * from emp;
SQL>
SQL> select *
2 from working_emps;
EMP_ID LASTNAME FIRSTNAME M STREET CITY ST ZIP SHOR ARE PHONE COMPANY_NAME
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- --------------------------------------------------
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
12 rows selected.
SQL>
SQL> select * from working_emps;
EMP_ID LASTNAME FIRSTNAME M STREET CITY ST ZIP SHOR ARE PHONE COMPANY_NAME
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- --------------------------------------------------
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
1 Jones Joe J 1 Ave New York NY 11202 1111 212 221-4333 Big Company
2 Smith Sue J 1 Street New York NY 11444 1111 212 436-6773 Little Company
3 X Peggy J 1 Drive New York NY 45502 2222 212 234-4444 Medium Company
12 rows selected.
SQL>
SQL> insert into working_emps
2 select * from emp ;
3 rows created.
SQL>
SQL> drop table working_emps;
drop table working_emps
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
SQL> drop table emp;
Table dropped.
SQL>
Related examples in the same category