Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    81

    Unanswered: Tablespace Shrink

    Hi,

    I have an UNDOTBS tablespace sized at 32GB but actually only using 260MB.
    Is it possible to reduce the size of this tablespace and will it reduce the size
    of the .dbf file in order to reclaim disk space on the system?

    If so what is the syntax to reduce the tablespace to 1GB please?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have an UNDOTBS tablespace sized at 32GB but actually only using 260MB.
    I suspect this measurement was made shortly after a reboot.
    More will be used as more transactions occur.

    How/why is the UNDO tablespace now sized at 32GB?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2010
    Posts
    81

    Size Free Used
    Tablespace (Mb) (Mb) (Mb) Used % Free %
    -------------------- ------------- ------------- ------------- -------- --------
    SYSAUX 460.00 22.56 437.38 95.08% 4.9%
    SYSTEM 510.00 1.19 508.75 99.75% .23%
    TEMP 938.00 1.00 937.00 99.89% .11%
    UNDOTBS1 36,067.98 35,857.00 210.81 .58% 99.42%
    USERS 5.00 4.56 .38 7.6% 91.2%
    XDW_TABLESPACE 10,893.81 4,897.13 5,996.56 55.05% 44.95%
    ------------- ------------- -------------
    Totals: 48,874.79 40,783.44 8,090.88


    Hello as you can see the UNDOTBS is only using .58%.
    Why it was set to 32GB I have no idea. Is this something or anyone can suggest how to shrink and by shrinking will I reclaim back the allocated diskspace?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    These posts might help:

    http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/
    http://jonathanlewis.wordpress.com/2010/07/14/changing-undo/

  5. #5
    Join Date
    Jun 2010
    Posts
    81
    Hi,

    and thanks for your feedback in posting those links: However I have read those documents witout much help,
    so I posted the results of why I believe I need to shrink the tablespace and the errors I recieve when trying
    to resize the tablespace from 32GB to 5GB

    SQL> select tablespace_name,bytes,file_name from dba_data_files where tablespace_name = 'UNDOTBS1';

    TABLESPACE_NAME BYTES FILE_NAME
    ------------------------------ ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------
    UNDOTBS1 3.4360E+10 /u01/app/oracle/oradata/xhrdwdev/undotbs01.dbf
    UNDOTBS1 3145728000 /u01/app/oracle/oradata/xhrdwdev/undotbs02.dbf

    SQL> alter database datafile '/u01/app/oracle/oradata/xhrdwdev/undotbs01.dbf' resize 5000M;
    alter database datafile '/u01/app/oracle/oradata/xhrdwdev/undotbs01.dbf' resize 5000M
    *
    ERROR at line 1:
    ORA-03297: file contains used data beyond requested RESIZE value

    It's a 10.2.0.4 Database

    Any additional help will be much appreciated

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails Read The Fine Manual

    CREATE new UNDO & DROP old UNDO
    10 Managing the Undo Tablespace
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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