Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: Rebuilding indexes

    I have a 30G hard drive and had 4G of free space. I tried rebuilding some indexes and ran out of disk space( I have only 4Mb left). Any idea how I can free up some temporary space?

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Reformat the hard drive - only joking!!!

    It depends on what else is on the hard drive - first of all, check that the recycle bin is empty (you may have deleted stuff, but if the files are still in the bin then as far as Windoze is concerned they're still taking up room). Trawl through & empty temp folders, delete files ending in .tmp, delete files no longer needed (e.g. backups made months ago that have been superceded by others), move non-critical files onto another drive (or onto removable media e.g a CD) for now, um, can't think of anything else...
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Don't be cheap and spend $50 for an additional 40G drive!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You may want to check the listener.log (can get really big !!!)...
    May have to stop the listener, delete the .log, restart listener ...

    HTH
    Gregg

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by vld
    I have a 30G hard drive and had 4G of free space. I tried rebuilding some indexes and ran out of disk space( I have only 4Mb left). Any idea how I can free up some temporary space?
    Windows or UNIX/Linux??

    For Unix/Linux you can run this script to find all files greater than 1 meg.
    It should order from largest to smallest.
    (note: someone else wrote this but I have found it very handy so I use it all the time to find files that use a lot of space that I might not ever know existed)
    PHP Code:
    #!/bin/ksh
    # With df -k find the directory that is full:
    dir=$(df -k|sort +4n|tail -1|awk '{print $6;}')
    # set the maximum file size:
    export MAXSZ=1024000
    #lookup all files > MAXSZ
    find $dir -ls |awk '{if (int($7) >= int(sz)) printf "%15s %s\n",$7,$11;}' sz=$MAXSZ sort -
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2002
    Posts
    77
    This particular is on a windows 2000 server, But I do work with unix systems so I'll keep it handy. Thanks. BTW I took some your suggestions and deleted the listener log file, and dropped and recreated some indexes, Managed to save about 2G of hard drive space. I guess before rebuilding indexes I'll have to check whether there is enough space. If there isn't enough space then drop and recreate the index would be the best deal.

    Thanks for all your help.

  7. #7
    Join Date
    Aug 2004
    Posts
    43
    Newbie OCA here...

    How do you rebuild indexes... is it not done automatically, like when you insert into or update the indexed column.?

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Index rows are inserted as you insert or update data in the database ...
    They are not actually modified ... As you update the data that changes
    the index, the index key will be inserted in the proper lndex leaf location
    (keeping the integrity of the index in check). The "old" value is marked for
    deletion, but is not actually deleted from the index itself ... This is called
    index "Browning" where you have deleted rows throughout the index leafs...
    This will be usually cause any problems until you get a large percentage of the total rows in the index that have actually been marked for deletion...
    In this case, by rebuilding the index, you remove the deleted rows...

    You can also rebuild the index (structure and rows) to remove extents or to resize the index ...

    HTH
    Gregg

  9. #9
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Quote Originally Posted by vld
    I have a 30G hard drive and had 4G of free space. I tried rebuilding some indexes and ran out of disk space( I have only 4Mb left). Any idea how I can free up some temporary space?
    You can try to shrink your datafiles, since some of the space could be over-allocated but actually never used. In this case you can issue:

    SQL> ALTER DATABASE DATAFILE '/datafile.dbf' RESIZE size [K|M];


    To find the MAX size to which datafiles could be resized use this script:
    Script for calculating the MAX size to which datafile can shrink



    Hope that helps,

    clio_usa - OCP 8/8i/9i DBA

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    I agree with clio.
    It is possible your temp tablespace grew to a large degree for a huge transaction. Now it is only using a fraction of the space, but the datafile is taking up a huge amount. This could also be the case with the UNDO tablespace/datafile.

    try this:
    PHP Code:
    col megs_alloc format 999,999,999
    col megs_used format 999
    ,999,999

    select  a
    .tablespace_name,
           
    round(a.bytes_alloc 1024 10242megs_alloc,
           
    round((a.bytes_alloc nvl(b.bytes_free0)) / 1024 10242megs_used,
           
    round((nvl(b.bytes_free0) / a.bytes_alloc) * 100,2Pct_Free
    from  
    select  f.tablespace_name,
                   
    sum(f.bytesbytes_alloc,
                   
    sum(decode(f.autoextensible'YES',f.maxbytes,'NO'f.bytes)) maxbytes
            from dba_data_files f
            group by tablespace_name
    a,
          ( 
    select  f.tablespace_name,
                   
    sum(f.bytes)  bytes_free
            from dba_free_space f
            group by tablespace_name
    b
    where a
    .tablespace_name b.tablespace_name (+)
    union
    select  tablespace_name
    ,
           
    round(sum(bytes_used bytes_free) / 10485762),
           
    round(sum(bytes_used) / 1048576,2),
           
    round((sum(bytes_free) / sum(bytes_used bytes_free)) * 100,2Pct_Free
    from   sys
    .v_$TEMP_SPACE_HEADER
    group by tablespace_name
    ORDER BY 1
    /

    TABLESPACE_NAME    MEGS_ALLOC    MEGS_USED   PCT_FREE
    ---------------- ------------ ------------ ----------
    SYSTEM                    250          202       19.1
    TEMP                   10
    ,000        3,335      66.65
    TOOLS                      50            0      99.88
    UNDOTBS1                9
    ,000            8      99.91 
    as you see above, the TEMP tablespace is taking up 10Gig
    on the HD but only about 3Gig is currently being used.
    Reducing that to 6-8Gig probably wouldn't hurt anything.

    Also, UNDO is 9Gig allocated. Unless you run a good deal of
    large DML transactions you could probably reduce that to 3-5Gig
    (all depends on your db, but it seems to me that space management
    is more important to you right now).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •