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

Thread: Reallocate extents - possible ?

  1. #1
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    Reallocate extents - possible ?

    Hi ! I have an unusual problem here, let me expose it : each month my data goes to a tablespace specific for the current month : so, the data referring january/2005 are being inserted/updated/deleted in a tablespace named TS_200501, and when February comes, a tablespace TS_200502 will be made, and no more DMLs will occurs against January data. So, to save space (the datafiles for these things are very huge)in February 1st I want to shrink the datafiles for January’s tablespace.
    The problem here is : sometimes, in this tablespace are made INSERTs for a table (say, TABLE A), and this occupies (say) extents 001 from 099 in a data file. Later, more huge INSERTs are made for table B, occupying extents 100 to 1500 (in the same data file, say). Later yet another small INSERT is made in TABLE A, occupying extents 1501 to 1502, and finally TABLE B data is deleted. You can see the problem now : due only to the extents 1051 & 1502, I can’t shrink these data file to fewer than the space occupied by the last extent, but there is a LARGE unused space in this data file, which I want to free back to disk, to use in others tablespaces. The common answer is "MOVE TABLE A", but the system is intensive OLTP (very few off-time allowed), and A is constantly being referenced, A have many indexes (they will be invalidated) , so MOVE is not an option. What I can do ?

    Regards,


    Chiappa

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What is your oracle release?

    Tamil

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Ops, sorry : 9ir2 EE here.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: Reallocate extents - possible ?

    Originally posted by JChiappa
    What I can do ?
    Buy more storage. If you had a volume for every period dedicated to that period, then you would not worry about some extra gigs not being used. What is more important saving disk space or performance and uptime? I assume that you keep previous months data online so it can be queried. If so separating it on different volumes would potentially spread out the load.

    Are you using partitioning? It would seem that it would be helpful in this case.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    Partition by date?
    I remember when this place was cool.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Use dbms_redefinition pkg for re-orging the table.
    Then shrink your tablespace's data files.

    Tamil

  7. #7
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Gandolf, yes, "buy more storage" is the default option, but I have two full-years of data here, so "a few Gbs" in Jan, a little more in Feb, etc, etc, at the end represents a quite significative amount. And more : if I show the space saving´s figures, surelly my damagement WILL give to me some "off-peak time" to do it.

    Mr. Hanky : yes, partitioning can be useful here (I must yet rebuild/move with this option to free the space, but just last month´s partition. But, while not partitioned, , I found a better option (apparently) : 9i gave to us DBMS_REDEFINITION, and according to the docs (and the tip in http://groups.google.com/groups?selm...&output=gplain ), with it we can online "move" a table : I will test, let´s see if it works in my case!

    Regards,

    Chiappa

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Surely you are already using partitioning though -- how else are you sending different rows to different tablespaces? Unless you are using one table per month, of course.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How about "ALTER TABLE my_table shrink space" ?
    The above statement will work only in ASSM.

    Tamil

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And how wil the above suggestion help when he specificaly mentioned:
    Originally posted by JChiappa
    Ops, sorry : 9ir2 EE here.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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