1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Global temporary table in oracle -- repost

Discussion in 'Training and Certification' started by TechQueryPond, Mar 29, 2016.

  1. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA



    Global Temporary Table In Oracle

    1)The data in such a table is stored only as long as the session or transaction lasts and is private for each session.

    2)The definition is visible to all sessions.

    3)After commit or disconnection, the data is lost but the definition of the table remains.

    4)Indexes created on a temporary.

    5)Scope of TRUNCATE command is a single database session. There is no effect of TRUNCATE on other sessions.

    6)Foreign key constraints are not applicable in case of Temporary tables

    7)Temporary tables cannot contain column of persistent collection type

    Syntax :

    CREATE GLOBAL TEMPORARY TABLE table_name
    ( column_name column_data_type


    ) ON COMMIT DELETE ROWS;


    CREATE GLOBAL TEMPORARY TABLE Emp (
    id number(5) NOT NULL,
    name varchar2(50) NOT NULL,
    city varchar2(50) NOT NULL ) ON COMMIT DELETE ROWS;


    INSERT INTO Emp VALUES (1, ‘Test1’, ‘city1’);
    INSERT INTO Emp VALUES (2, ‘Test2’, ‘city2’);
    INSERT INTO Emp VALUES (3, ‘Test3’, ‘city3’);

    SELECT COUNT(*) FROM Emp;

    Result = 3 rows

    COMMIT;

    SELECT COUNT(*) FROM Emp;

    Result = 0 rows
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have completely ignored the option to preserve rows on commit:

    SQL> CREATE GLOBAL TEMPORARY TABLE Employee (
    2 id number(5) NOT NULL,
    3 name varchar2(50) NOT NULL,
    4 city varchar2(50) NOT NULL ) ON COMMIT PRESERVE ROWS;

    Table created.

    SQL>
    SQL>
    SQL> INSERT INTO Employee VALUES (1, 'Test1', 'city1');

    1 row created.

    SQL> INSERT INTO Employee VALUES (2, 'Test2', 'city2');

    1 row created.

    SQL> INSERT INTO Employee VALUES (3, 'Test3', 'city3');

    1 row created.

    SQL>
    SQL> SELECT COUNT(*) FROM Employee;

    COUNT(*)
    ----------
    3

    SQL>
    SQL> COMMIT;

    Commit complete.

    SQL>
    SQL> SELECT COUNT(*) FROM Employee;

    COUNT(*)
    ----------
    3

    SQL>

    If you're going to present information you really need to present ALL of the information, not just the pieces you want.