how to reduce size of datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: how to reduce size of datafile

  1. #1
    Join Date
    Mar 2003
    Location
    Gujarat
    Posts
    19

    how to reduce size of datafile

    Dear all,

    I am facing problem as below

    i hv temp tablespace in my db. the name of
    tablespace : temp (file name temp01.dbf) by default it was in autoextend datafile mode when full)
    due to autoextend my developer has done some mistake in writing query statement (in loop). my temp size increase upto maximum extend & full(defined space on OS).
    Now i have remove this query & restart database now used temp space is free (only zero). but now
    i will try to reduce the size of temp01.dbf file but it giving error
    (we can not reduce the size ..)
    how to reduce the size of temp01.dbf ? please advice

    regs
    ramji

  2. #2
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Thumbs up

    You have to query the data dictionary views, then find the min size you can shrink the datafile. A ready to use script is posted Here (Script for calculating the MAX size to which datafile can shrink )


    Hope that helps,

    clio_usa

    OCP 8/8i/9i DBA

  3. #3
    Join Date
    Feb 2002
    Posts
    70
    ALTER DATABASE DATAFILE '' resize 200m;

    What are the storage parameters of your temp tablespace?? Is contents of your tablespace permanent or temporary. If it permanent change it to temporary.
    Last edited by shandj; 04-29-2003 at 03:00 AM.
    ________________
    ShanDJ

  4. #4
    Join Date
    May 2001
    Posts
    736
    Create one more temp tablespace of required size and drop the old one.

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by clio_usa
    You have to query the data dictionary views, then find the min size you can shrink the datafile. A ready to use script is posted Here (Script for calculating the MAX size to which datafile can shrink )


    Hope that helps,

    clio_usa

    OCP 8/8i/9i DBA
    The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.

    In his case, the best solution is to:

    CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata1/.../temp02.dbf' size 15M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

    drop tablespace temp including contents and datafiles;

    CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
    AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    ramji

    If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
    1) I was unable to resize it
    2) will not deallocate even when there is no sorting going and I hate
    to see 8 gig allocated to temporary when I could help with load balancing.
    So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:

    1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
    **************************
    SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
    FROM v$session t1, V$sort_usage t2
    WHERE t1.saddr = t2.session_addr ;

    If no is returned proceed withn the 2nd step.

    2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
    ********************************
    ****Alter tablespace temp coalesce ;

    3) THIRD RUN THIS TO RECLAIM THE SPACE
    ***************************
    alter tablespace temp
    default storage (pctincrease 1);

    FOLLOWED BY

    alter tablespace temp
    default storage (pctincrease 0);

    On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
    HTH
    Last edited by Ablakios; 04-29-2003 at 12:10 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  7. #7
    Join Date
    Mar 2003
    Location
    Gujarat
    Posts
    19
    Step:2 ,3 are not runing . it display error
    "SQL> Alter tablespace temp coalesce ;
    Alter tablespace temp coalesce
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE



    SQL> alter tablespace temp
    2 default storage (pctincrease 1);
    alter tablespace temp
    *
    ERROR at line 1:
    ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
    --------------------------------------------------------"
    Regs
    ramji


    Originally posted by julian
    The query you refer to addresses dba_data_files. He wants to reduce the TEMP file. Even so, resizing TEMP files is often pointless for their HWM is usually hitting the top.

    In his case, the best solution is to:

    CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u03/oradata1/.../temp02.dbf' size 15M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

    drop tablespace temp including contents and datafiles;

    CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
    AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

  8. #8
    Join Date
    May 2001
    Posts
    736
    What u have done as per the julian reply?

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Ablakios
    ramji

    If you have a locally managed temporary tablespace you are stucked. I particularly went thru that problem where I find myself adding space to the LMTT because
    1) I was unable to resize it
    2) will not deallocate even when there is no sorting going and I hate
    to see 8 gig allocated to temporary when I could help with load balancing.
    So I drop and recreated a DMTT(Dictionary Managed Temporary Tablespace)where I can take control of manually deallocate the temp tablespce as follows:

    1) FIND USERS WHO ARE SORTING IN THE TEMP TABLESPACE:
    **************************
    SELECT t1. username, t2.tablespace, t2.contents, t2.extents, t2.blocks
    FROM v$session t1, V$sort_usage t2
    WHERE t1.saddr = t2.session_addr ;

    If no is returned proceed withn the 2nd step.

    2) SECOND RUN THIS TO COALESCE FREE EXTENTS*
    ********************************
    ****Alter tablespace temp coalesce ;

    3) THIRD RUN THIS TO RECLAIM THE SPACE
    ***************************
    alter tablespace temp
    default storage (pctincrease 1);

    FOLLOWED BY

    alter tablespace temp
    default storage (pctincrease 0);

    On more thing about autoextend on. after reading all docs I personnally have my system tablespace autoextend on that was it.
    HTH
    Sounds like you've got yourself a job for life there.

    How often do you have to resize TEMP? Pretty rarely, i would think.

    Why do you need to coalesce free space?
    Because you are using a dictionary managed tablespace.

    Why do you hate it that the temp space is allocated in an LMT, even when it's not actually in use?
    Because it's not what you're used to.

    You know how to load balance TEMP? Use a LMT, uniform extent size equal to sort_area_size, and multiple datafiles spread across all your devices. Very easy.


    So for interfering with progress and artificially inflating your own workload at the expense of good DB management techniques, you're fired.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by julian
    CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u03/oradata1/.../temp01.dbf' SIZE 1024M reuse
    AUTOEXTEND ON NEXT 256K MAXSIZE 6144M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    Julian, is there a reason for you suggesting the autoextend value being 256K when (presumably) the extra space will be needed in units of 1M? Or did you mean 256M? Tx.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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