Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Dec 2003
    Posts
    47

    Unanswered: How to redure the tablespace's used space after delete records

    Hi, everybody
    I have just deleted some records (1 million records) from a table in USERS tablespace. So I think the used space of USERS must be reduced but it isn't. I tried 2 commands:
    ALTER TABLESPACE USE COALESCE
    and
    ALTER TABLE <tablename> DEALLOCATE UNUSED
    but no change happened
    Please help me
    Thanks
    Last edited by fire2fire; 12-31-03 at 08:06.

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Re: How to redure the tablespace's used space after delete records

    Originally posted by fire2fire
    Hi, everybody
    I have just deleted some records (1 million records) from a table in USERS tablespace. So I think the used space of USERS must be reduced but it isn't. I tried 2 commands:
    ALTER TABLESPACE USE COALESCE
    and
    ALTER TABLE <tablename> DEALLOCATE UNUSED
    but no change happened
    Please help me
    Thanks
    Do you need the space or are you just concerned that the space was not reclaimed?

    The answer to your question is that if you need the space you will need to recreate the tablespace with a smaller size, you can not just reduce the size.

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: How to redure the tablespace's used space after delete records

    Originally posted by fire2fire
    Hi, everybody
    I have just deleted some records (1 million records) from a table in USERS tablespace. So I think the used space of USERS must be reduced but it isn't. I tried 2 commands:
    ALTER TABLESPACE USE COALESCE
    and
    ALTER TABLE <tablename> DEALLOCATE UNUSED
    but no change happened
    Please help me
    Thanks
    1) alter table <tablename> move;
    2) rebuild all the UNUSABLE indexes on the table.

    Your delete freed space only below the high water mark of the table - the only way to reclaim that space is to MOVE the table "in the same position". This leaves all indexes on the table as UNUSABLE - just rebuild them.
    Must be done without any user accessing the table, obviously.

    You can also modify the storage parameters of the table while you MOVE - or change the tablespace etc - check the docs, it's a powerful and very useful command.

    HTH
    Al

  4. #4
    Join Date
    Aug 2001
    Posts
    66

    Re: How to redure the tablespace's used space after delete records

    Unfortunately this rather comes down to how you define (or rather how Oracle defines) 'unused'.

    I recommend you read Connor McDonald's rather helpful note on this subject, which can be found here:

    http://www.jlcomp.demon.co.uk/faq/reclaim_space.html

    Padders
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Alberto, we are talking about tableSPACES not tables.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    If you are not using LMTs then try 'alter tablespace tblspc-name coalesce'

    for every table in the tablespace what about DEALLOCATE UNUSED?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    tableSPACES!! tableSPACES!!! not tables. Please start reading from the beginning of the thread!! and have a great day!

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Todd Barkus
    tableSPACES!! tableSPACES!!! not tables. Please start reading from the beginning of the thread!! and have a great day!
    Todd, if I deallocate unused from every table in a tablspace then doesn't that MAYBE/POSSIBLY reduce the used space in the tablespace as a whole??

    tell me I am wrong and please explain how I am wrong.
    Thanks.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by Todd Barkus
    Alberto, we are talking about tableSPACES not tables.
    We are talking about reducing the USED space of a tablespace, which is the space allocated to objects (a table in this case) - not the size of the tablespace.

  10. #10
    Join Date
    Aug 2001
    Posts
    66
    I would say the question is ambiguous - does 'used space of users' mean:

    a.) how much space the users tablespace takes up (i.e. how big is the whole tablespace) or

    b.) how much space is used within the users tablespace (i.e. what is the total size of the extents within the tablespace).

    However I'll side with Alberto and Duck since

    a.) that was how I read it originally,

    b.) I don't like people who use too many exclamation marks, and

    c.) it's fun winding him up (did it work yet?)
    Padderz
    SYSOP, Quest Software / RevealNet Labs PL/SQL Pipeline

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    reply to Duck - tablespace sizes are defined by their datafiles. Oracle preallocates the total size you request for the datafiles (ie. the file does not grow, the internal space is used). reducing the size of the stuff in the tablespace does not reduce the tablespace. Just becuase we lose weight does not make our house smaller. :-)

    reply to Alberto - I didn't read the initial request that way. Maybe you are correct. I guess we need more input from fire2fire about the original question.

    reply to Padderz - What!!! What is wrong with exclamation marks!!!!!!! (sorry :-) ) Winding me up? or somebody else? I suppose you don't like people who use question marks either.

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    The_Duck is right ... If you deallocate unused from the tables, the "used" space in the tablespace is reduced... If you are lucky enough to be able to reduce "used" space at the end of the TABLESPACE, then you will be able to
    "alter database datafile '.....' resize xxx;
    to reduce the size of the tablespace ...

    Now what was the real question again ???

    Gregg

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Todd Barkus
    reply to Duck - tablespace sizes are defined by their datafiles. Oracle preallocates the total size you request for the datafiles (ie. the file does not grow, the internal space is used). reducing the size of the stuff in the tablespace does not reduce the tablespace. Just becuase we lose weight does not make our house smaller. :-)
    USED space BARKUS, not Tablespace/Datafile space.

    I see where you are coming from, but from what I read, it seems this person is concerned with USED space. Such as when you attempt to reduce the size of the tablespace/datafile because you see that only 500 Meg of 1 Gig is being used, yet you get that Oracle error saying you cannot reduce the size below the USED space threshold.

    So, I am assuming this guy wants to reduce the used space to the ACTUAL used space which is the 500 Meg so he can reduce the datafile size on the server.

    Sounds like the USED space size of the tablespace/datafile grew to 900 Meg at one point, then with deletes and such the used space was reduced to 500 Meg. Now the guy needs LMTs, coalesce the TBLSPC, or deallocate unused in my opinion.
    Last edited by The_Duck; 12-31-03 at 11:44.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by Todd Barkus
    reply to Alberto - I didn't read the initial request that way. Maybe you are correct. I guess we need more input from fire2fire about the original question.
    I agree about the additional input - but the New Year is coming and i have a party to attend to, so i guess i will not be here to answer ;-)

  15. #15
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Tom??

    Gregg - if you collesced the data wouldn't that leave the end empty?

    I see that opinion is against me on the nature of the original question. Good thing I do not mind swimming against the stream.

    [We are having a great discussion here. I wonder if we are even in the ballpark of fire2fire's question. ]

    Alberto - Have a good new year. Be safe.

Posting Permissions

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