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

Thread: colaescing free space at reglar intervals

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Question

    could anybody gime a shell script that i can run through crontab or at command of nt so that it can colasce free space in tablespaces and colasce it say every friday.

    can anbody also give me a an example how this can be done through DBMS_JOB utility.

    i am runing 8i on linux and NT


    thanks in advance

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why don't you just switch to localy managed tablespaces and forget about coalescing.....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    at present there are some databses running on oarcle 8 to i forgot to mention that sorry folks

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    or use uniform extent size and forget about coalescing

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    There you go: Jmodic's advise, the best to reduce the pain on your head from day to day tasks.
    Reddy,Sam

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Hey, hrishy!

    Itsn't that difficult write one on your own. All you need to do is:
    Set up oracle_home and oracle_sid in shell script and call the sql script as follows.

    Unix script
    <font face=courier>
    setenv ORAENV_ASK NO
    setenv ORACLE_SID xyz
    source /usr/bin/coraenv
    sqlplus -s abc/**** @coal_tbs.sql
    </font>
    SQL script(coal_tbs.sql)
    <font face=courier>
    set echo off
    set feedback off
    set heading off
    spool coaltbs.sql

    select 'alter tablespace '||tablespace_name||' coalesce ;' from dba_tablespaces;

    spool off
    set feedback on
    set echo on
    </font>
    Reddy,Sam

  7. #7
    Join Date
    Feb 2001
    Posts
    389
    There is a limitation in Oracle that if u do coalesce once, it may not squeeze through whole of the tablespace, u have to more than once for it to squeeze.
    If tablespace is large, it might take long time and high CPU usage to coalesce.
    Now which tablespace u want to coalesce,table or index.
    Table: As said before in thread, design tablespace and table locations accordingly and put uniform extent size for all tables on a particular tablespace.

    Or put pctincrease 1% at tablespace level for smon to work and during table creation put pctincrease as 0% or as reqd.

    Test locally managed tablespace before use, there are few bugs in 8.1.6.




  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    That 1% PCTINCREASE leads to fragmentatiom as you know!
    Just reminder.
    Reddy,Sam

  9. #9
    Join Date
    Jan 2001
    Posts
    642
    What is locally managed tablespaces and how do you test it?
    Badrianth

  10. #10
    Join Date
    Feb 2001
    Posts
    295
    A locally managed tablespace uses bitmaps to track extent information, instead of dictionary tables (available only on Oracle 8i). Since Oracle itself manages the extents, you don't have to worry about coalescing. LMT's also reduces the access to dictionary tables, improving performance of the database. It could be a good idea to adopt this feature, but I don't know if it is possible to change your Dictionary-Managed to a Locally-Managed-Tablespace. Probably you'd have to re-create it. Could anyone confirm that?

    Maybe you should also consider the use of uniform extents and/or PCTINCREASE 0, to reduce fragmentation and the need of coalescing.


    Adriano.


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