DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: temp segments/sort segments

  1. #1
    Join Date
    Feb 2002
    Posts
    267
    hi folks,

    what is the difference between temporaray segments and sort segments....

    or to be more precise when does oracle creates temporary segments and sort segments.

    regards
    sonia

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Thumbs up

    A sort segment can be in memory or disk; i.e., if the sort cannot be wholly performed in memory (as specified by sort_area_size) then it is performed on disk.

    A temporary segment is used for this sort operation on disk. This temporary segement is created in a temporary tablespace, such as TEMP (default).

    The following tells u if a tbs is temporary or permanent...

    SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM dba_tablespaces;

    HTH.


  3. #3
    Join Date
    Feb 2002
    Posts
    267
    no ...
    i am not talking abt sort segment in memory...

    I am talking of sort segments created in the tablespace....

    As far as i understood, if a segment is creted in temporary tablespace for sorting, that is know an sortsegment and if a segment is created to hold data from temporary tables that is know as temporary segment. This is only in case of temporary tablespace.

    But if it is a permanent tablespace , it is always a temporary segment, which will be deleted after the completion of job.

    I am not sure whether i am right or wrong.
    Could any body confirm this

    regards
    sonia

  4. #4
    Join Date
    Aug 2000
    Posts
    236
    NORMALLY, YOU CAN FINS ALL THESE IN THE ORACLE MANUALS. PLEASE TRY TO FIND IT BEFORE POSTING ON TO THE FORUM.

    Here goes:

    Nizar


    Temporary Tablespaces
    To improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, create temporary tablespaces. A temporary tablespace can be shared by multiple users and can be assigned to users with the CREATE USER statement when you create users in the database.
    Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.

    Temporary Tables
    In addition to permanent tables, Oracle can create temporary tables to hold
    session-private data that exists only for the duration of a transaction or session.
    The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table
    that can be transaction-specific or session-specific. For transaction-specific
    temporary tables, data exists for the duration of the transaction. For session-specific
    temporary tables, data exists for the duration of the session. Data in a temporary
    table is private to the session. Each session can only see and modify its own data.
    DML locks are not acquired on the data of the temporary tables. The LOCK
    statement has no effect on a temporary table, because each session has its own
    private data.
    A TRUNCATE statement issued on a session-specific temporary table truncates data
    in its own session. It does not truncate the data of other sessions that are using the
    same table.
    DML statements on temporary tables do not generate redo logs for the data
    changes. However, undo logs for the data and redo logs for the undo logs are
    generated. Data from the temporary table is automatically dropped in the case of
    session termination, either when the user logs off or when the session terminates
    abnormally such as during a session or instance crash.
    You can create indexes for temporary tables using the CREATE INDEX statement.
    Indexes created on temporary tables are also temporary, and the data in the index
    has the same session or transaction scope as the data in the temporary table.
    You can create views that access both temporary and permanent tables. You can also
    create triggers on temporary tables.

    Segment Allocation
    Temporary tables use temporary segments. Unlike permanent tables, temporary
    tables and their indexes do not automatically allocate a segment when they are
    created. Instead, segments are allocated when the first INSERT (or CREATE
    TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or
    DELETE is performed before the first INSERT, then the table appears to be
    empty.
    You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE
    INDEX, and so on) on a temporary table only when no session is currently bound
    to it. A session gets bound to a temporary table when an INSERT is performed on
    it. The session gets unbound by a TRUNCATE, at session termination, or by doing
    a COMMIT or ABORT for a transaction-specific temporary table.
    Temporary segments are deallocated at the end of the transaction for
    transaction-specific temporary tables and at the end of the session for
    session-specific temporary tables.

  5. #5
    Join Date
    Feb 2002
    Posts
    267
    Originally posted by nabaig
    NORMALLY, YOU CAN FINS ALL THESE IN THE ORACLE MANUALS. PLEASE TRY TO FIND IT BEFORE POSTING ON TO THE FORUM.

    What do u mean by this?


    What ever u have posted doesn't answer my question ?

    Try to understand my question first then post the reply...

    For ur kind info, i posted the question only after reading documentation.....




Posting Permissions

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


Click Here to Expand Forum to Full Width