FAQ
Hello



I plan to copy data from all tables to the new tables in new tablespace every night. Why? Old tablespaces will be purged every night and I have to have old date

I think that I have two options:

First

I have to copy tablespace to the new with new name plus date e.g. tablespace "name" to the new name_date.

Second:

Create tables in new tablespace "as select" but what can I do with indexes?



I do not know if first options is possible.



Maybe you have any idea to help me.

Rgds.

Wojtek

Search Discussions

  • Blanchard William at Mar 25, 2008 at 8:55 pm
    Why are you purging every night? You might be better served by copying
    the old data to a different database to use as a DW of sorts.

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wojciech Skrzynecki
    Sent: Tuesday, March 25, 2008 3:51 PM
    To: oracle-l_at_freelists.org
    Subject: copy tablespace in one database

    Hello




    I plan to copy data from all tables to the new tables in new tablespace
    every night. Why? Old tablespaces will be purged every night and I have
    to have old date



    I think that I have two options:



    First

    I have to copy tablespace to the new with new name plus date e.g.
    tablespace "name" to the new name_date.



    Second:

    Create tables in new tablespace "as select" but what can I do with
    indexes?



    I do not know if first options is possible.



    Maybe you have any idea to help me.



    Rgds.

    Wojtek
  • Stephens, Chris at Mar 25, 2008 at 8:59 pm
    From the description below, tablespaces seem irrelevant. Just create
    table _hist (or whatever convention you want),
    build the indexes necessary for and sql or dml that will be performed
    against the history tables, and create a procedure to 'insert /*+
    append */' (assuming recovery consequences are acceptable) the data from
    the original tables into the history tables prior to the original tables
    being 'cleaned' out. You may need to add a date column to the history
    table.



    You aren't copying a tablespace. You are copying data from one table to
    another. Whether they are in the same or different tablespaces is
    irrelevant, at least for the description you provided.



    If I'm way off base, please provide a more accurate description of what
    you really need to do.



    chris



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wojciech Skrzynecki
    Sent: Tuesday, March 25, 2008 3:51 PM
    To: oracle-l_at_freelists.org
    Subject: copy tablespace in one database



    Hello





    I plan to copy data from all tables to the new tables in new tablespace
    every night. Why? Old tablespaces will be purged every night and I have
    to have old date



    I think that I have two options:



    First

    I have to copy tablespace to the new with new name plus date e.g.
    tablespace "name" to the new name_date.



    Second:

    Create tables in new tablespace "as select" but what can I do with
    indexes?



    I do not know if first options is possible.



    Maybe you have any idea to help me.



    Rgds.

    Wojtek

    CONFIDENTIALITY NOTICE:

    This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
    confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
    communication in error, please notify us immediately by email reply.
  • Mark W. Farnham at Mar 25, 2008 at 10:49 pm
    The relative number of rows to be retained versus the number of rows to be
    deleted (and preserved by you) and a few other things about the data cycle
    will guide you to the best solution.



    For example, if this is purging a tiny fraction of the data that represents
    transactions that have been "completed" for the longest, then you probably
    do want either make use of row movement driving updates or actual row copies
    followed by deletes.



    If this represents getting rid of all the data that has been "handled" today
    leaving behind only the incompletes to be worked on together with new data
    added tomorrow, then you're almost certain to be better off to rename
    today's table and create new tables and copy the data back that is *not* to
    be purged. Then you have all the data to dispose of as you see fit,
    including just applying a view that supresses the data by whatever criteria
    was used to copy it back to the "current" data sets.



    Frankly we would need to know a lot more about your information flow to make
    good suggestions. Things like whether the nightly wave of data needs to be
    blended together with older data, for example. Do you have support for
    partitioning? What is the purpose of the nightly purging?



    How big is the data to be moved? Is it plausible to create the indexes you
    need after loading the data? Do your requirements for retaining the data
    mean you want the same indexes as "current", or would a subset or completely
    different indexes be better? Often data that is "purged" like this is no
    longer allowed to be change, so sometimes heavier indexing to support
    queries more directly is a good choice since you don't have repetitive
    changes to process for the indexes.



    And you have to ask yourself whether effort to make the process fast and
    efficient is worthwhile, given the plummeting cost of hardware.



    Regards,



    mwf







    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wojciech Skrzynecki
    Sent: Tuesday, March 25, 2008 4:51 PM
    To: oracle-l_at_freelists.org
    Subject: copy tablespace in one database



    Hello





    I plan to copy data from all tables to the new tables in new tablespace
    every night. Why? Old tablespaces will be purged every night and I have to
    have old date



    I think that I have two options:



    First

    I have to copy tablespace to the new with new name plus date e.g. tablespace
    "name" to the new name_date.



    Second:

    Create tables in new tablespace "as select" but what can I do with indexes?



    I do not know if first options is possible.



    Maybe you have any idea to help me.



    Rgds.

    Wojtek
  • Remigiusz Sokolowski at Mar 26, 2008 at 7:25 am

    Wojciech Skrzynecki wrote:
    Hello

    I plan to copy data from all tables to the new tables in new
    tablespace every night. Why? Old tablespaces will be purged every
    night and I have to have old date

    I think that I have two options:

    First

    I have to copy tablespace to the new with new name plus date e.g.
    tablespace "name" to the new name_date.

    Second:

    Create tables in new tablespace "as select" but what can I do with
    indexes?

    I do not know if first options is possible.

    Maybe you have any idea to help me.

    Rgds.

    Wojtek
    May be it is acceptable to you just to rename old tables to whatever you
    want and provide new tables with proper/previous names.
    Providing that you always change all names on a particular tablespace,
    you could use them interchangeably.
    While this is not the exact solution of your project, still those
    activities don't need so much time (i.e. delays in system functioning).
    Another possibility is using partitioning (even with one partition and
    of course if you have proper license) and exchange partition feature.
    After exchange you can simply transfer the data to another tablespace
    without (almost, IO still must be done) affecting the system functioning
    at all

    Regards
    Remigiusz

    --

    ------------------------------------------------------------------------
    Remigiusz Sokolowski
    WP/PTI/DIP/ZAB (+04858) 52 15 770
    MySQL v04.x,05.x; Oracle v10.x

    Zastrzezenia:
    1. Wylaczenie danej funkcjonalnosci oznacza, ze niezwlocznie przystapimy
    lub juz pracujemy nad jej uruchomieniem
    2. Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora
    i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa
    Wirtualna Polska S.A.
    ------------------------------------------------------------------------

    "WIRTUALNA POLSKA" Spolka Akcyjna z siedziba w Gdansku przy ul.
    Traugutta 115 C, wpisana do Krajowego Rejestru Sadowego - Rejestru
    Przedsiebiorcow prowadzonego przez Sad Rejonowy Gdansk - Polnoc w
    Gdansku pod numerem KRS 0000068548, o kapitale zakladowym
    67.980.024,00 zlotych oplaconym w calosci oraz Numerze Identyfikacji
    Podatkowej 957-07-51-216.
    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Mar 26, 2008 at 6:53 pm
    Have you considered exporting the data (exp or expdp depending on Oracle
    version)?

    Mark D Powell --
    Phone (313) 592-5148

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Wojciech Skrzynecki

    Sent: Tuesday, March 25, 2008 4:51 PM
    To: oracle-l_at_freelists.org
    Subject: *****SPAM***** copy tablespace in one database

    Hello

    I plan to copy data from all tables to the new tables in new

    tablespace every night. Why? Old tablespaces will be purged every night
    and I have to have old date



    I think that I have two options:



    First

    I have to copy tablespace to the new with new name plus date
    e.g. tablespace "name" to the new name_date.



    Second:

    Create tables in new tablespace "as select" but what can I do
    with indexes?



    I do not know if first options is possible.



    Maybe you have any idea to help me.



    Rgds.

    Wojtek

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 25, '08 at 8:50p
activeMar 26, '08 at 6:53p
posts6
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase