Temporary tables cannot be forced into logging. : Temporary Table « Table « Oracle PL / SQL






Temporary tables cannot be forced into logging.

   
SQL>
SQL> create table employee(
  2           emp_no                 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          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );

Table created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (1,'Anderson','Nancy','N','33 Ave','London','NY','11111','1111','212','234-1111',3.75,'21-mar-1927','1-feb-1947','Sales Manager',2,null,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (2,'Last','First','F','12 Ave','Paris','CA','22222','2222','221','867-2222',7.75,'14-feb-1976','15-mar-1985','Sales Clerk',2,1,100,10,10000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (3,'Wash','Georgia','G','1 Street14','Barton','NJ','33333','3333','214','340-3333',11.50,'2-jul-1977','21-apr-2004','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (4,'Bush','Dave','D','56 Street','Island','RI','44444','4444','215','777-4444',21.65,'15-may-1945','2-aug-1975','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (5,'Will','Robin','W','56 Street','Island','MA','55555','5555','216','777-5555',24.65,'10-dec-1980','2-aug-2007','Designer',1,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (6,'Pete','Mona','M','13 Ave','York','MO','66666','6666','217','111-6666',9,'14-feb-1966','15-mar-1985','Sales Clerk',2,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (7,'Roke','John','J','67 Ave','New York','BC','77777','7777','218','122-7777',10.00,'14-jun-1955','15-mar-1975','Accountant',3,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (8,'Horry','Tedi','T','1236 Lane','Newton','NY','88888','8888','219','222-8888',13.00,'10-jun-1955','15-aug-1985','Sales Representative',3,2,100,10,50000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2                values (9,'Bar','Candi','C','400 East Street','Yorken','NY','99999','9999','220','321-9999',12.00,'10-oct-1933','15-jan-1969','Sales Representative',3,5,100,10,35000);

1 row created.

SQL>
SQL> select * from employee;
    EMP_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE        SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE            HIREDATE             TITLE                   DEPT_NO        MGR     REGION   DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
         1 Anderson             Nancy           N 33 Ave                         London               NY 11111 1111 212 234-1111          4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager                 2                   100      10  40000

         2 Last                 First           F 12 Ave                         Paris                CA 22222 2222 221 867-2222          8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          1        100      10  10000

         3 Wash                 Georgia         G 1 Street14                     Barton               NJ 33333 3333 214 340-3333         12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer                      1          2        100      10  40000

         4 Bush                 Dave            D 56 Street                      Island               RI 44444 4444 215 777-4444         22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer                      1          2        100      10  40000

         5 Will                 Robin           W 56 Street                      Island               MA 55555 5555 216 777-5555         25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer                      1          5        100      10  40000

         6 Pete                 Mona            M 13 Ave                         York                 MO 66666 6666 217 111-6666          9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          5        100      10  40000

         7 Roke                 John            J 67 Ave                         New York             BC 77777 7777 218 122-7777         10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant                    3          2        100      10  40000

    EMP_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE        SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE            HIREDATE             TITLE                   DEPT_NO        MGR     REGION   DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------

         8 Horry                Tedi            T 1236 Lane                      Newton               NY 88888 8888 219 222-8888         13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative          3          2        100      10  50000

         9 Bar                  Candi           C 400 East Street                Yorken               NY 99999 9999 220 321-9999         12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative          3          5        100      10  35000


9 rows selected.

SQL>
SQL> CREATE GLOBAL TEMPORARY TABLE temp_emp
  2    AS SELECT * FROM employee;
CREATE GLOBAL TEMPORARY TABLE temp_emp
                              *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>
SQL>
SQL> ALTER TABLE temp_emp LOGGING;
ALTER TABLE temp_emp LOGGING
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table


SQL>
SQL>
SQL> drop table employee;

Table dropped.

SQL> drop table temp_emp;

Table dropped.

SQL> --

   
    
  








Related examples in the same category

1.Create temporary table
2.Create global temporary table from existing table
3.Create global temporary table with 'on commit delete rows' option
4.Temporary tables support primary keys.
5.Temporary tables do not support foreign keys
6.You cannot alter a temporary table to change its data duration.(drop and recreate)
7.ORA-14452: attempt to create, alter or drop an index on temporary table already in use
8.Update a TEMPORARY TABLE and check the table it based on
9.create global temporary table on commit delete rows
10.Temporary table on commit preserce and delete
11.drop global temporary table
12.Insert into a temporary with select statement
13.Insert data into the temporary table.
14.truncate a global temporary table
15.Using Temporary Tables
16.Create global temporary table working_emps on commit preserve rows