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

Thread: Partition Table

  1. #1
    Join Date
    Jun 2005
    Posts
    2

    Lightbulb Partition Table

    Hi,

    We have this requirement

    We have a partition table that contains current data and previous year data. The data for each year is in different partition and different tablespace. Only the curreny year partition tablespace is in read and write mode only. The partition tablespace for the earlier year is in read mode only so that the user can view the data but not change the data.

    We want to drop partition having data that is two year old. We also need some mechanism such that we are able to put back the partition if the user wished to view the data again.

    We are dropping the partition using the below mechanism

    Alter table drop partition;
    alter tablespace offline; (Reover the space)
    drop tablespace test including contents; (Recover the space)

    The problem now is two fold.

    1) How do we restore back the tablespace, if the user now wants to view the old data.

    2) If the table structure of the original partition table is changed due to some enhancement, will there be a any problem when the tablespace is recovered and the dropped
    partition is added back to the table.


    Waiting for your expert opnion

    Thanks

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    If the user wishes to view the data again why would you drop the partition?
    what is the goal of dropping the partition?
    how large is such a partition?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yeah, this requirement is a bit weird for my taste. What is the benefit of dropping the old data?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Jun 2005
    Posts
    2
    Hi,

    I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.

    Tabiul

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by tabiul
    Hi,

    I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.

    Tabiul
    So where are you going to keep the data that you don't want, so that you can restore it later?

    How about some really cheap, slow disks, attached by the cheapest method? No RAID, no fuss, just grab some 100GB+ disks and plug them in anywhere. It'd still be cheaper and less hassle.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    Quote Originally Posted by tabiul
    Hi,

    I understand, but we have a limitation in term of disk space. The customer does not wish to purchase new disk.

    Tabiul
    If they don't have enough space now, then how are they going to put the old data back if they want it?

    You might want to look at transportable tablespaces. If it is a read only tablespace, you may be able to move it off to some off-line media (CD DVD?), then transport it back in from the CD when/if they want it. Never tried anything like that, but it may work.

    John

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    as far as I knwo you can only use transpoprtable tablespace (TT) for self containend partitiones. It's not possible to use TT with a tablespace that contains only some of the partitions of a table.

    A workaround may be to execute an alter table exchange partition before making use of TT.
    Last edited by mike9; 06-24-2005 at 04:14 AM.

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    In case you are using Oracle9ir2 or Oracle10g you should have a look at table compression. With the same disk space it would probably be possible to store up to 3 times more data.

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