Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: How to use temporary table

    Dear all!
    I am very confused about how to use temporary table in Oracle 9.i.
    Even through, i know the way to create such kind of its,
    Could you show me the use of the table in Oracle as that in SQL server??

    Help me pls!
    Best regards,

  2. #2
    Join Date
    Nov 2004
    Posts
    3
    Dear all!

    I myself find out how to make temporary table and to
    use it, so i could share my opinion to you.

    1. To create temporary table, use following syntax:

    CREATE GLOBAL TEMPORARY TABLE temp
    (ID number(8),
    Name nvarchar2(50))
    [ON COMMIT DELETE ROWS](default)
    or [ON COMMIT PRESERVE ROWS]
    2. From now on, read below instructions for more
    details:
    (
    GLOBAL TEMPORARY

    Specify GLOBAL TEMPORARY to indicate that the table is
    temporary and that its definition is visible to all
    sessions. The data in a temporary table is visible
    only to the session that inserts the data into the
    table.

    A temporary table has a definition that persists the
    same as the definitions of regular tables, but it
    contains either session-specific or
    transaction-specific data. You specify whether the
    data is session- or transaction-specific with the ON
    COMMIT keywords.

    Note:

    You can perform DDL operations (such as ALTER TABLE,
    DROP TABLE, CREATE INDEX) on a temporary table only
    when no session is bound to it. A session becomes
    bound to a temporary table by performing an INSERT
    operation on the table. A session becomes unbound to
    the temporary table by issuing a TRUNCATE statement or
    at session termination, or, for a transaction-specific
    temporary table, by issuing a COMMIT or ABORT
    statement.
    See Also:

    Oracle9i Database Concepts for information on
    temporary tables and "Temporary Table Example"
    Restrictions on Temporary Tables

    * Temporary tables cannot be partitioned,
    clustered, or index organized.
    * You cannot specify any foreign key constraints
    on temporary tables.
    * Temporary tables cannot contain columns of
    nested table or varray type.
    * You cannot specify the following clauses of the
    LOB_storage_clause: TABLESPACE, storage_clause,
    logging_clause, MONITORING or NOMONITORING, or
    LOB_index_clause.
    * Parallel DML and parallel queries are not
    supported for temporary tables. (Parallel hints are
    ignored. Specification of the parallel_clause returns
    an error.)
    * You cannot specify the
    segment_attributes_clause,
    nested_table_col_properties, or parallel_clause.
    * Distributed transactions are not supported for
    temporary tables.
    )
    [ON COMMIT]
    ON COMMIT

    The ON COMMIT clause is relevant only if you are
    creating a temporary table. This clause specifies
    whether the data in the temporary table persists for
    the duration of a transaction or a session.
    DELETE ROWS

    Specify DELETE ROWS for a transaction-specific
    temporary table (this is the default). Oracle will
    truncate the table (delete all its rows) after each
    commit.
    PRESERVE ROWS

    Specify PRESERVE ROWS for a session-specific temporary
    table. Oracle will truncate the table (delete all its
    rows) when you terminate the session.

    Bye bye!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •