DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: resize tablespace

  1. #1
    Join Date
    Nov 2000
    Posts
    245

    does any one can tell me:

    How to reduce the data file size of a tablespace.

    thanks


  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    ALTER DATABASE TEMPFILE
    'E:\ORACLE\ORADATA\BSG1\TEMPORARY_DATA.ORA'
    RESIZE 40M;

    You may want to do the following

    ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;

    ALTER TABLESPACE TABLESPACENAME COALESCE;


  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Actually it should be
    ALTER DATABASE
    DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 40M

    I use locally managed tablespaces.

  4. #4
    Join Date
    Nov 2000
    Posts
    245

    reduce tablespace data file size


    Hi, gandolf989,

    please verify it for me:

    1. ALTER TABLESPACE users DELETE UNUSED;

    2. ALTER TABLESPACE users COALESCE;

    3. ALTER DATABASE DATAFILE 'D:\ORADATA\BSG1\USER1.DBF' RESIZE 10M;

    is it the correct sequence?

    thanks

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: reduce tablespace data file size

    Originally posted by jm


    1. ALTER TABLESPACE users DELETE UNUSED;

    wow is that a new command

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gandolf989
    ALTER TABLESPACE TABLESPACENAME DELETE UNUSED;
    And I thought I knew all TABLESPACE related commands in Oracle :-)


  7. #7
    Join Date
    Nov 2000
    Posts
    245
    Hi,

    so what's the correct command and step?

    all I know is use

    ALTER DATABASE DATAFILE 'filename' RESIZE 10M;

    in fact, my true question is:

    if I have a tablespace was 5Mb initally, later someone create some "hugh" tables to make the tablespace growing
    to 500Mb, then those tables being dropped. I assume those space will be free.

    can I use resize to set back to original size?

  8. #8
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you can reduce the size of a datafile, providing there is NO data in the space you're removing (in the last 495 Mb in your example).
    what you should do is :

    drop table huge1;
    drop table huge2;
    ...

    once it's ok :

    alter tablespace [tbs] coalesce;

    and then :

    alter database datafile 'your datafile' resize 5M;

    if this command fails, then you certainly have data in the bad area, you should look for what it is by looking in dba_extents and dba_data_files ...

  9. #9
    Join Date
    Nov 2000
    Posts
    245

    thanks, it is loud, clear and works.

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