10 Replies Latest reply: Apr 19, 2012 11:57 AM by jgarry RSS

    Data Archive_help

    899401
      Hi,

      I wanted archive the data for 2009 year and wanted to reuse it as when it is required

      how can i get the spaced utilize in xyz table for the 2009 year. it should be included the all spaced utilized by the particular table in the given year. i know the shrink and analyze option but do not know how much it is correct
      if at all i archived the data and wanted to reload the again what are the options to reload it successfully without any errors the best possible option expected

      thanks
        • 1. Re: Data Archive_help
          Fran
          You can move the data with year 2009 to another tablespace with more space for example. Later shrink/analize space of xyz table to get the free space available.
          • 2. Re: Data Archive_help
            899401
            Hi,

            can u please help me with the steps
            and is there any possibility to archive the data for 2009 year and wanted to reuse it as when it is required

            thanks
            • 3. Re: Data Archive_help
              Antonio Navarro
              You can user expdp (datapump), with estima blocks and where clause, it give you the size aprox.

              HTH
              Antonio NAVARRO
              • 4. Re: Data Archive_help
                Fran
                the easyway i thought will be:

                ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
                and is there any possibility to archive the data for 2009 year and wanted to reuse it as when it is required 
                The only way will be data pump to archive that table out of the database, but you will have to import it when you need it.
                • 5. Re: Data Archive_help
                  Lubiez Jean-Valentin
                  Hello,

                  if at all i archived the data and wanted to reload the again what are the options to reload it successfully without any errors the best possible option expected
                  I'm not sure about the most suitable solution in your case.

                  For instance, if you used Partitions and exported the Partitions storing the Year 2009 data before cleaning them, you can reload the Data easily with an Import. The DROP PARTITION and EXCHANGE PARTITION let you Purge and Load historical data:

                  http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#CACGFGJH

                  Else, in *11g* you have a new feature called FLASHBACK ARCHIVE which let you store all the changes on specific Tables of your choice during a retention period:

                  http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm#BJFFDCEH


                  Hope this help.
                  Best regards,
                  Jean-Valentin
                  • 6. Re: Data Archive_help
                    899401
                    Hi,



                    Actually, i wanted to archive the data for 2009 year and wanted to reuse it as when it is required

                    PLease help with steps or practical example

                    thanks
                    • 7. Re: Data Archive_help
                      Lubiez Jean-Valentin
                      Hello,


                      For instance, if you have a single Table with historical data with a Column which stores the "date" information, you may proceed as follow:

                      - 1. Export the Year 2009 data of this Table by using the QUERY Parameter. With this parameter you'll define the WHERE Clause to select the year 2009 data. You'll find here an example of QUERY Parameter usage:

                      http://mohamedazar.com/2010/11/09/datapump-query-parameter/

                      - 2. Check for any error during the Export (define a logfile for the Export with the Parameter LOGFILE).

                      - 3. Delete the Year 2009 data by using the same WHERE Clause (as for the QUERY parameter).

                      - 4. Then, you may SHRINK the Table so as to reclaim the space to the Tablespace.

                      Afterwards, if you have to Reload the Year 2009 data you can Import the Dump previously generated, with the Parameter TABLE_EXISTS_ACTION=APPEND.

                      If you have a Range Partitioned Table with Partition Key on the Column which stores the "date" information. You may define a Partition for each year.
                      Then, Exchange the Partition (the one for the year 2009) to a an "ArchiveTable" (which has the same structure as the Partitioned Table). Export the "Archive Table" and drop it. To Reload the Data, you have to Import the "Archive Table" and Exchange the Partition to get back the data into the Partition.

                      You'll find here useful explanations:

                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:363723200346913109

                      Anyway, test the method you choose to apply on a test Database, so as to be sure that it's safe and you can Reload easily your data. And, never Archive your data on a Production Database without a good BACKUP.


                      Hope this help
                      Best regards,
                      Jean-Valentin
                      • 8. Re: Data Archive_help
                        jgarry
                        Separate it off to a separate table, then use a view that unions the two tables when you need it. If you don't need to cross years, you might just change queries between tables, have one report for the new data and another report for the archived data. If people are putting together "reports" in Excel or something, let them do the unioning manually. It depends on what you have and what you need.

                        I have transactional tables that go back a decade, because really, why archive at all? Do you have inappropriate full table scans?
                        • 9. Re: Data Archive_help
                          899401
                          Hi,

                          can i get some example on this

                          thanks
                          • 10. Re: Data Archive_help
                            jgarry
                            All over the net. Here is but one: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1381934643557