Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Question Unanswered: Temporary tablespace issue

    I am having Oracle 10g running on windows 2003 server. Database's cold backup is taken on daily basis. But still I have noticed that temp tablespace never flushes out. According to me it should get flushed after bounce. Why this is not happening?
    Is is a windows issue?

  2. #2
    Join Date
    Jul 2006
    Posts
    49
    Did you create a locally managed temp tablespace such as CREATE TEMPORARY TABLESPACE TEMP .... because without keyword TEMPORARY between CREATE and TABLESPACE it won't do what you want.

    If you created the tablespace using the alternative syntax CREATE TABLESPACE <name> TEMPORARY then cleanup does not occur.

    SMON does a cleanup of sort segments after database open. It can take a long time and spike CPU utilization. Wait until the CPU utilization drops and then look at your temp tablespace.

    If the database was last shutdown with abort option, then upon startup SMON may be too busy cleaning up dead transactions and it skips the cleanup of temp segments since that's really not necessary. They are probably already marked as free and will get used again when space pressure occurs.

    If the above does not resolve the issue then you should open a SAR with Oracle Support.

    -Mark

Posting Permissions

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