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

Thread: temp space during index creation?

  1. #1
    Join Date
    Aug 2000
    Posts
    163

    Red face

    When Oracel creates indexes does it use user temp space or system temp space?
    Thank you.

  2. #2
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    Depends on the temp ts granted to the user that is creating the index.

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    See Oracle will always use temporary tablespace of the user creating the index.If the u have granted the user SYSTEM tablespace then he will use that tablespace otherwise temporary tablespace of the user .
    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohtisn@altavista.com


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    somehow when you create indexes oracle also uses user's default tablepsace as temp tablespaces, may be it's a bug no clue but I have seen a few times

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    more to add

    Hi, 19th May 2001 14:40 hrs chennai

    When you install Oracle, all the standard accounts (SYS, SYSTEM, etc.) are assigned the SYSTEM tablespace as their default and temporary tablespaces. The temporary tablespace should be reset to TEMP for these accounts after installation. No user accounts should have SYSTEM defined as default or temporary, however this will be the default if not specified when a user is created. Run this script to check each instance for potential problems.

    >select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM'


    Specify the Tablespace for Each Index
    ==========================
    Indexes can be created in any tablespace. An index can be created in the same or different tablespace as the table it indexes.

    If you use the same tablespace for a table and its index, then database maintenance may be more convenient (such as tablespace or file backup and application availability or update) and all the related data will always be online together.

    Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace, due to reduced disk contention.

    If you use different tablespaces for a table and its index and one tablespace is offline (containing either data or index), then the statements referencing that table are not guaranteed to work.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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