DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 34

Thread: INDX tablespace in increasing

  1. #21
    Join Date
    Feb 2003
    Posts
    63

    do you delete large amonts of data?

    Do you delete larege amounts of data from these tables, if not then you will hardly reclaim any space.

    As for reducing the datafile size you need to find which extents are at the end of the datafile you can then rebuild them so it will move the extents

    Use the script below:

    SET VERIFY OFF
    PROMPT This database contains the following datafiles:

    SELECT file_name FROM dba_data_files;

    ACCEPT fname PROMPT 'Specify a datafile whose contents are to be determined: '

    TTITLE CENTER 'Database Segments in File &fname' SKIP 2

    COLUMN stype FORMAT A15 JUSTIFY C HEADING 'Segment Type
    COLUMN sown FORMAT A15 JUSTIFY C HEADING 'Segment Owner'
    COLUMN sname FORMAT A40 JUSTIFY C HEADING 'Segment Name'

    BREAK ON stype SKIP 1 ON sown SKIP 1

    SELECT e.segment_type stype,
    e.owner sown,
    e.segment_name sname
    FROM dba_extents e,
    dba_data_files f
    WHERE f.file_name = '&fname'
    AND e.file_id = f.file_id
    ORDER BY e.segment_type, e.owner, e.segment_name;

    TTITLE OFF
    CLEAR BREAKS
    UNDEFINE fname

    HTH

  2. #22
    Join Date
    Jun 2006
    Posts
    40
    hi,
    Yesterday on our testing database we deleted the data from table about 10 GB. After that free space in data tablespace got increased . Then i rebuilded the indexes to remove the deleted entries from indx tablespace. but i want free space in indx tbs also.
    We've taken backup of tht data so that i future if we require tht we can restore it.

    Whether is it a good way to drop and recreate tht indexes so tht we get free space in indx tablespace?
    Does it cause any loss?
    Please guide me.

    Thanks for your time.

  3. #23
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    loss of what?, rebuilding indexes (whther useful or not ) wont lose data
    Last edited by davey23uk; 08-23-2006 at 04:32 AM.

  4. #24
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    This is an extract from another forum "Ask Tom", so thanks Tom.

    -------------------------------------------------------------
    So, what do you think was happening here? Well, the fact is that indexes, like
    people, have a certain "weight" they like to be at. Some of us are chubby --
    some skinny -- some tall -- some short. Sure, we can go on a diet -- but we
    tend to gravitate BACK to the weight we were. The same is true for indexes --
    what happened to this person is their index wanted to be wide and fat and EVERY
    MONTH they rebuilt it (put it on a diet). It would spend the first half of the
    month then getting fat again and generating gobs of redo due to the block splits
    it was undergoing to get there.

    In this case, rebuilding the index on their system had these effects:

    o the system would generate 4.5 times the redo
    o the system would run slower
    o the system would consume more resources (CPU, IO, latching, etc)
    o the system would not be able to handle the same user load

    until the system got back to where the system actually wanted to be. And then
    -- AND THEN -- they (the dba's) would do it all over again!!!! They would
    destroy the equilibrium that the system worked so hard to get to. Bravo!!


    (can you tell what my opinion is on regularly scheduled index rebuilds??? I
    despise them).


    If you are suffering from fragmentation -- your problem is not the index
    rebuilds. It would be because you are using DICTIONARY managed tablespaces.
    Switch over to locally managed tablespaces and you'll never have fragmentation
    again. So, my suggestion -- one last index rebuild:

    o create a locally managed tablespace
    o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );

    and then forget about them. You will rebuild an index in response to IDENTIFIED
    and DEGRADED performance -- period. You will not rebulid indexes based on a
    schedule. Period.

    It is the RARE index that needs to be rebuilt.
    It is not a rule that indexes need to be rebuilt.

    --------------------------------------------
    Able was I ere I saw Elba

  5. #25
    Join Date
    Aug 2006
    Posts
    5
    Are you using bitmap indexes.
    You can try rebuild your large indexes once a week

  6. #26
    Join Date
    Jun 2006
    Posts
    40
    No, i've b-tree index , If i drop and recreate those indexes whether it will create space in the tablespace also on disk.

  7. #27
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by robertbalmer
    o create a locally managed tablespace
    o alter index rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );
    --------------------------------------------
    consider this as first option.
    is this a composite/bitmap?
    ---------------

  8. #28
    Join Date
    Jun 2006
    Posts
    40
    Yesterday i fired following qury to rebuild index:
    alter index index_name rebuild ONLINE
    Then i got these errors:
    ERROR at line 1:
    ORA—01652: unable to extend tenp segnent by 256 in tablespace TEMP
    ORA—27059: skqfrsz: could not reduce file size
    OSD—O4005: SetrilePointer() failure, unable to read fron file
    0/S—Error: (05 112) There is not enough space on the disk.

    My temporay tablespace is 2 GB and free space in index tablespace is 40 GB and the index is of 10 GB.
    But free space on the drive where temp01.dbf is reside is 760 kb . but before tht i've removed 4 GB of datafiles to another drive. Still its showing 760 KB. Why it is so? I will add tempfile to tht temp tablespace to anothr drive and will try again.
    Now if i give command
    alter index index_name rebuild unrecoverable tablespace indx, then whether it will give me same error?thanks in advance.
    Last edited by minal_yawale; 08-31-2006 at 02:07 AM.

  9. #29
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    do you not have a dba around who can help you?

  10. #30
    Join Date
    Jun 2006
    Posts
    40
    NO there is no other DBA to help me ,i've to do it myself
    Last edited by minal_yawale; 08-30-2006 at 09:19 AM.

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