Oracle not releasing space after DROP TABLE
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Oracle not releasing space after DROP TABLE

  1. #1
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27

    Oracle not releasing space after DROP TABLE

    We have a few big tables in a schema. Now when we are dropping the tables the space acquired by the tables is not released by ORACLE. When we try to resize the datafile of that schema, it is not allowing us to do it and throwing an error.
    But we need the space to be released and allocated to some other schema.
    We are using ORACLE 9.2.0.5 on LINUX AS 2.1 on ITANIUM server.

    Please give and workaround for this problem.

    Thanks in advance,
    Anomitro

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    have you tried coalescing the tablespace to try and get the space used own.

    You dont mention if you are using LMT's or not

  3. #3
    Join Date
    May 2001
    Posts
    736
    Here is very article go through it

    http://asktom.oracle.com/pls/ask/f?p...:153612348067,

  4. #4
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27
    Hello All,

    I'm using LMT.

    I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:

    ORA-03297: file contains used data beyond requested RESIZE value

    And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.

    I've tried ALTER TABLESPACE .. COALESCE , but the result is same.

    I've time and space constraints so I cannot do an EXP & IMP to release space.

    Please help me to solve the problem.

    Thanks,
    Ano

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    create a new tablespace
    alter table xyz move new tablespace
    alter index xyz rebulid new tablespace
    drop old tablespace
    rm datafiles
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27
    My tablespace almost 100GB and I cannot create another tablespace to move the tables due to space problem.

    I've restarted the database but it didn't release the space.

    Now I'm creating 1 small tablespace and moving 1 table to see whether it is releasing the space from the old tablespace.

    ANY OTHER WORKAROUND TO RELEASE SPACE ????



    Thanks,
    Ano

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    there is free space, but not at the end of the datafile - you have to create another tablespace and move them

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Anomitro
    Hello All,

    I'm using LMT.

    I've droppped a few large tables and released 3GB of space. This is seen in the TABLESPACE option of OEM, where it is saying 94% used. When I'm looking into datafiles its showing a lot of bytes free. Now when I try to resize the datafiles its throwing the error:

    ORA-03297: file contains used data beyond requested RESIZE value

    And I've gone thru ASKTOM's article and tried the the "maxshrink.sql" but it is also showing 'Poss. Savings = 0" for all the 4 datafiles of that tablespace.

    I've tried ALTER TABLESPACE .. COALESCE , but the result is same.

    I've time and space constraints so I cannot do an EXP & IMP to release space.

    Please help me to solve the problem.

    Thanks,
    Ano
    If you don't have the space, then you are stuck. Did someone ask you to do this online?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by abhaysk
    Try alter table tab_name move in the same TS (for all segments in that TS).. and then do shrink of datafiles..

    Abhay.
    Yeah, that might help. Remember to rebuild the unusable indexes (if any).
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

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