Fragmentation in datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Fragmentation in datafiles

  1. #1
    Join Date
    Sep 2000
    Posts
    305

    Fragmentation in datafiles

    Dear Friends

    I have database on Linux having size of 23 GB. In Linux you can not create file more than 2GB, I have 12 data files and all of them having some data but not fully utilized now I want to coalecse the frgmented space how do I proceed?

    There is one option is that I can export the whole database and import it back but will it solve my problem I am not sure about it please help me.

    One more thing I want to ask that the percentage use of system file is almost near to 100 now what do you recomend, Shall I increase the sixe of the file or not as well as tell me one more thing what ahould be the size of the system file.

    Thanks in Advance
    Shailendra

  2. #2
    Join Date
    Mar 2002
    Posts
    200
    To find if the Tablspace is fragmented:
    =======================================

    SELECT TABLESPACE_NAME, TOTAL_EXTENTS, EXTENTS_COALESCED,
    PERCENT_EXTENTS_COALESCED
    FROM
    DBA_FREE_SPACE_COALESCED
    WHERE
    PERCENT_EXTENTS_COALESCED < 100
    ORDER BY
    PERCENT_EXTENTS_COALESCED,
    TABLESPACE_NAME;


    This will give the list of tablespaces that are fragmented. Then these tablespaces have to be coalesced to de-fragment them.

    To de-fragment, you may use --

    ALTER TABLESPACE (Tablespace Name) COALESCE;

    Alternatively, you can set the PCTINCREASE of the tablespace to a non-zero value, so that SMON can automatically wake up to detect fragments and coalesce them.



    Hope this helps.

    Quester.
    Last edited by quester; 11-21-2002 at 01:04 AM.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Well i dont really get it...Linux cannot have a file greater than 2Gb...which version of the kernel are you running ?...if you are running a very old kernel ask your Linux admin to install a new kernel...

    what version of database are you running...if you are on oracle8i then you create locally managed tablespaces and then do alter table table name move tablepsace and then rebuild your indexes...no need for cumbersome export import..

    regards
    Hrishy

  4. #4
    Join Date
    Sep 2000
    Posts
    305
    Thanks to both of you but my problem was I want to move data from all those datafiles which is having very less percentage in use I want to transafer all data into other existing datfile so will export and import solve htis problem or not?

    And what about the System File do I need to increase the size of system file or not?

    I am using Linux Red Hat 6.2 Oracle Server (8.1.6.0.0)

    Thanks again
    Shailendra

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Shailendra

    Well i suggest two things...


    1)Take a cold backup of your database ask your sys admin to install redhat Linux 7.3 which ships with newer version of kernel 2.4 which has support for files greater than 2G

    2)There is no need to do export import at all..

    1)just create new locally_managed_tablespaces
    2)Alter table tablename move locally_managed_tablepsaces
    3)Rebuild your indexes in parallel

    Definately the 2 method is much much faster and easier...You can plan this during weeknds when there is no much load on the database..

    regards
    Hrishy

  6. #6
    Join Date
    Sep 2000
    Posts
    305
    thanks hrishy

    I will do the same
    now can you please tell me about the system files which I ask you in my provious message, it always flucating bewteen 95% to 99% use.

    Thanks

    Shailendra

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Sahilendra

    Unfortunately for the system tablespace you cannot do anything...(excpet a hack of editing sql.bsq script which i dont advise on production systems).You can just add a datafile to that tablepsace...and just forget about that system tablespace...and do not create ant user objects into that tablespace and everything would be fine..

    regards
    Hrishy

  8. #8
    Join Date
    Sep 2000
    Posts
    305
    Thanks alot for the help

    Shailendra

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