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

Thread: how eliminate the tables and index fragements

  1. #1
    Join Date
    Oct 2002
    Posts
    17

    how eliminate the tables and index fragements

    dear all:


    solaris and oracle version 8.1.6.3

    a table as a temporary table, when I load the data into the table, after other operation,I will delete all records. thus can fragement, how can I find the fragement level of the user schemas and how to eliminate the fragements!


    please help me!

  2. #2
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    After you delete all records, the index will be fragmental but table. You can rebuild index, but I think it is not a good idea to create index in a temp table which will be reloaded frequently.

    To find the fragment level of an index, monitoring an index's efficiency of space usage using INDEX_STATS at regular intervals by first analyzing the index's structure.
    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

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    Re: how eliminate the tables and index fragements

    Originally posted by joyloce
    dear all:


    solaris and oracle version 8.1.6.3

    a table as a temporary table, when I load the data into the table, after other operation,I will delete all records. thus can fragement, how can I find the fragement level of the user schemas and how to eliminate the fragements!


    please help me!
    Do you have any other objects in that tablespace? Instead of delete you can truncate the table and coalesce tablespace.

    Sanjay

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Truncate table with keeping storage, then the tablespace will not get fragmental if there are same amount of data that will be loaded everytime.
    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

  5. #5
    Join Date
    Oct 2002
    Posts
    17
    Originally posted by Calvin_Qiu
    Truncate table with keeping storage, then the tablespace will not get fragmental if there are same amount of data that will be loaded everytime.




    thank u ...

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