Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86

    Unanswered: Reduce Tablespace Size

    I need to reduce a tablespace, due a lot of data it used to contain, was erased, and I need also to reduce backup time.
    I need to do it ONLINE, is there any command to do it?
    Tks!
    Manf

  2. #2
    Join Date
    Dec 2003
    Location
    Buenos Aires, Argentina
    Posts
    86

    Re: Reduce Tablespace Size

    Originally posted by Manf
    I need to reduce a tablespace, due a lot of data it used to contain, was erased, and I need also to reduce backup time.
    I need to do it ONLINE, is there any command to do it?
    Tks!
    Manf
    I forgot something important: the tablespace contains 6 dbf, and all of that contains data.

    Manf

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You first need to reduce the size of the original tables (if they still exist)
    You can alter table xxx deallocate unused keep x - up to the
    highwater mark of the tables. You can rebuild the indexes smaller and
    into another tablespace (online).

    coalesce the tablespace

    find the highest block number in each datafile that contains data and
    resize the datafiles up to that point ...

    HTH
    Gregg

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    To shrink tables you can also use 'alter table ... move ' to another tablespace, then coalesce the tablespace and then move the tables back. Dont forget to rebuild any indexes related to the tables though as they become invalid after a table move.

    If youve got a large number of tables & indexes write a sql query to write your table moves and index rebuilds by querying dba_tables and dba_indexes.

    Alan

Posting Permissions

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