DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2001

    How Oracle Manage TEMP tablespace

    Hi all, I'm quite confused about how Oracle manages Temporary tablespace. I search the Oracle Concept guide and found two, I think,
    excerpts which are contradicting each other. I thought Oracle won't do space deallocation until it's restarted. I don't know
    if Oracle drops temporay segments(deallocate??) when the statement completes. Below are the two excerpts from Oracle
    Concept Guide.. Please advise, thanks

    " 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."

    "Oracle allocates temporary segments as needed during a user session,
    in the temporary tablespace of the user issuing the statement.
    You specify this tablespace with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.
    If no temporary tablespace has been defined for the user, the default temporary tablespace is the SYSTEM tablespace.
    Oracle DROPS temporary segments when the statement completes.Because allocation and deallocation of temporary segments
    occur frequently, it is reasonable to create a special tablespace for temporary segments"

  2. #2
    Join Date
    Apr 2002
    Shenzhen, China
    I think that the first experts is for TEMPORARY TABLESPACE, and the second experts is for PERMANENT TABLESPACE which is specified as default temporary tablespace to a certain user.

    This is another experts: "a single sort allocates its own temporary segment in the temporary tablespace of the user issuing the statement and then returns the extents to the tablespace. Multiple sorts, however, can use the segments in a temporary tablespace designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort but remain available for other multiple sorts."
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.