DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: index in users tablespace

  1. #1
    Join Date
    Apr 2001
    Posts
    126

    Unhappy index in users tablespace

    i want to transfer all the indexes created in USERS tablespace to diff tablespace, how do we do it?.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    The simplest way is to rebuild them e.g.

    Code:
    ALTER INDEX xxx REBUILD TABLESPACE xxx
    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Apr 2001
    Posts
    126
    will this operation clear the space alloted in users tablespace?.

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    The space in the tablespace is free, subseqent creates, extends and so on will use it.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203

    Re: index in users tablespace

    Originally posted by fordikon
    i want to transfer all the indexes created in USERS tablespace to diff tablespace, how do we do it?.

    You know indexes and tables don't have to be separated.... If you are running under a RAID array, or even under different disks arrarys, seperating your indexes and tables will not gain your database any performance benefit. Index reads and and table reads are a sequential.

    If you have to separate and are NOT running RAID separate your ojects which are HOT, this might be TABLES contenting with TABLES or INDEXES contenting with INDEXES.

    But, there are MANY who will maintain that it IMPERITIVE to separate your indexes and tables... (even Official Oracle Documentation). It's not the case.

    Under RAID it comes down to administrative choice, if you want to seperate you indexes for tables, fine, do so, but understand.... it's not a performance benefit.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    grjohnson,

    I agree partially, but would put it another way: before LMT, the performance reason to separate "user" objects between tablespaces was to get them on to independant physical disks. If everything is lumped together under one RAID5 array (PLEASE not the log files!) nothing is gained. If you can afford several RAID1 pairs, then the index/data split is one way to carve up the db - perhaps the best if you have no clue about what may be the HOT areas.

    (You said: "Index reads and and table reads are a sequential" - I suspect what you mean and what I understand from this are different!)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    check this one out, here the oracle experts are talking on the same topic.

    http://asktom.oracle.com/~tkyte/google.html

    i think this link was posted by slimdave a few days ago
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by adewri

    here the oracle experts are talking on the same topic.
    This thread is an excellent read (may take you a while, it's quite long) But the information provided by Howard Rogers and the examples on HOT segmets provided by Jonathan Lewis, are invaluable to dispelling the myth of INDEX and TABLE segment separation.
    OCP 8i, 9i DBA
    Brisbane Australia

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    now if only we could dispel that "high cache hit ratio=good, low cache hit ratio = bad" myth.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'm with you on that one also slimdave.

    How on UNIX which has it's OWN buffer cache and the Hard Disks which also have their own buffer cache can you take a Physical Read as READING from disk???????
    OCP 8i, 9i DBA
    Brisbane Australia

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