Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Shrink Oracle Tablespace

    Good afternoon everybody!

    I have a question for u ..

    Actually I have gigabytes and gigabytes of tablespaces but most of them are used partially.

    I heard that there is possibility to shrink them, the size of the tb will be exactly the size of the data (automatically increase) till reach the maximum defined.

    I explain better my question:

    Tb1 (size 10 gb)

    1) NOW - dbf file of 10 gb - Data inside 1 gb

    2) MY DREAM - dbf file of 1 gb - Data inside 1 gb
    - Automatically increase with the data till 10gb .. at that time
    tb full.

    Hope that someone can help me.

    Thanks very much.
    Dario

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    the answer is in two parts. To shrink a tablespace, you are probably best off exporting the table(s), dropping the tables in the database, shrinking the tablespace files, then re-importing the table(s).

    Look up the ALTER TABLESPACE and ALTER DATABASE commands.

  3. #3
    Join Date
    Jan 2010
    Posts
    6
    I found the way to increasy automatically the tb via "AUTOEXTEND ON" option.

    This is valid for a new tablespace.

    Now I'm looking how to apply the same logic for an existent tb.

    I think I need to defrag/compact (but I don't know how), resize to minimum, set AUTOEXTEND ON and specify the maximum side.

    What do you think?

    The only open point is .. how defrag/compact the tb?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What do you think?
    >The only open point is .. how defrag/compact the tb?
    It is a waste of time & effort to do so.
    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.

  5. #5
    Join Date
    Jan 2010
    Posts
    6
    why waste time?

    I already have the db of 100gbs but only a couple of gbs are real used.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I already have the db of 100gbs but only a couple of gbs are real used.
    How/why were DB files so badly incorrectly sized to get into this state?
    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.

  7. #7
    Join Date
    Jan 2010
    Posts
    6
    I don't know I'm just arrived in this project.

    As u can see in the attached screen shoot, they are a log of space not used.

    There is any way to know the situation about the defrag?
    Attached Thumbnails Attached Thumbnails ScreenShot021.jpg  

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version

    Are tablespaces DMT or LMT?
    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.

  9. #9
    Join Date
    Jan 2010
    Posts
    6
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    PL/SQL Release 9.2.0.6.0 - Production
    CORE 9.2.0.6.0 Production
    TNS for Linux: Version 9.2.0.6.0 - Production
    NLSRTL Version 9.2.0.6.0 - Production

    They are LMT.

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The easiest way to do this is to make a new tablespace, move all the tables and indexes to the new tablespace

    ALTER TABLE MY_TABLE MOVE TABLESPACE NEWTS;
    ALTER INDEX MY_TABLE_I1 REBUILD TABLESPACE NEWTS;

    once all the tables are moved, shrink the tablespace by resizing the empty datafile

    ALTER DATABASE
    DATAFILE '/oracledb/oradata/orcl/OLDTS.dbf' RESIZE 1000M;

    set your autoextend and max size on the old tablespace and then remove the tables and indexes back into the Old Tablespace and then drop the New Tablespace.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jan 2010
    Posts
    6
    I agree with u .. the problem is that the entire DB is almost 300gbs .. we don't have 300gbs for make the copy.

    Anyway thanks very much .. the way is the good one.

    I think that the activity will take a lot of times!

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You only have to make it big enough to hold the couple of gigs that you were talking about.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Go down to Walfart, buy a 1TB drive, slap it in there and just copy the damned thing over.

    It is not worth your time to be screwing around and possibly borking your database when $100 provides a quick, proper solution.

Posting Permissions

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