14 Replies Latest reply on Feb 6, 2013 1:10 PM by Rahul_India

    Taking backup of 50 tables

    Rahul_India
      What is the best possible method to take backup of 50 tables with data ?
      My DB version is 11gR2

      Thanks & Regards
      Rahul Raj
        • 1. Re: Taking backup of 50 tables
          jeneesh
          What type of back up?

          You need to create back up tables?

          Or EXPDP?

          If you are looking for CREATE TABLE, you could just do it using a text editor tool (or excel or ...) if you have the table names with you..
          create table your_table_bkp as 
          select *
          from your_table;
          • 2. Re: Taking backup of 50 tables
            Karthick2003
            Rahul India wrote:
            What is the best possible method to take backup of 50 tables with data ?
            My DB version is 11gR2

            Thanks & Regards
            Rahul Raj
            You need to define what you mean by backup?

            1. You need to create a copy of these tables in your DB itself or
            2. You need to copy these tables into another DB or
            3. You need to take a dump of these table and store it externally or
            4. You need to export the data in a human redable format, something like a delimited file.
            • 3. Re: Taking backup of 50 tables
              Rahul_India
              Thanks for the reply.

              I want to

              3. take a dump of these table and store it externally or
              4. export the data in a human redable format, something like a delimited file.

              i need to prepare a script too :)
              Thanks
              Rahul Raj
              • 4. Re: Taking backup of 50 tables
                Karthick2003
                Rahul India wrote:
                Thanks for the reply.

                I want to

                3. take a dump of these table and store it externally or
                Use [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#i1007277]DBMS_DATAPUMP
                4. export the data in a human redable format, something like a delimited file.
                Use [url http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:68212348056]DUMP_CSV from ASKTOM
                1 person found this helpful
                • 5. Re: Taking backup of 50 tables
                  Rahul_India
                  How to prepare the script?

                  I have limited privileges
                  • 6. Re: Taking backup of 50 tables
                    BluShadow
                    Rahul India wrote:
                    How to prepare the script?

                    I have limited privileges
                    That seems odd.
                    Taking backups of tables on a database (however it's done) is typically the sort of thing that is carried out by DBA's or system administrators. They're the sort of people who wouldn't be limited in the privileges they have.

                    So, if you need to do this task, get yourself the privileges you need.

                    It always surprises me how many people seem to work in businesses that expect them to do a task, but don't (or won't ) give them the access and tools to do the job.
                    • 7. Re: Taking backup of 50 tables
                      Rahul_India
                      I cant agree with you more .Once i was asked to minor performance tuning but couldn't TKPROF access TKPROF :p.




                      I have to prepare scrip like this
                      create table TBL_LOOKUP_backup as
                      select * from TBL_LOOKUP
                      I have 50 tables with names stored in an excel.Any quick method to prepare the script?
                      • 8. Re: Taking backup of 50 tables
                        Karthick2003
                        Rahul India wrote:
                        How to prepare the script?

                        I have limited privileges
                        Privileges define your Role in an organization. If you don't have a privilege for something that means you are not authorized to do it. A script like DUMP_CSV is very basic and every developer will and should have access to develop and use it.
                        • 9. Re: Taking backup of 50 tables
                          Rahul_India
                          Karthick_Arp wrote:
                          Rahul India wrote:
                          How to prepare the script?

                          I have limited privileges
                          Privileges define your Role in an organization. If you don't have a privilege for something that means you are not authorized to do it. A script like DUMP_CSV is very basic and every developer will and should have access to develop and use it.
                          I asked but no one has the privileges.Only onsite team has.They say it's a production environment
                          • 10. Re: Taking backup of 50 tables
                            EdStevens
                            Rahul India wrote:
                            I cant agree with you more .Once i was asked to minor performance tuning but couldn't TKPROF access TKPROF :p.




                            I have to prepare scrip like this
                            create table TBL_LOOKUP_backup as
                            select * from TBL_LOOKUP
                            I have 50 tables with names stored in an excel.Any quick method to prepare the script?
                            Earlier you said "
                            I want to

                            3. take a dump of these table and store it externally or
                            4. export the data in a human redable format, something like a delimited file.
                            Neither of those two operations require you to create a table. And option 4 only requires SELECT on the tables in question.
                            • 11. Re: Taking backup of 50 tables
                              Rahul_India
                              Yes.
                              I need a script so i can take a backup of the table before dropping them.

                              Thanks.
                              • 12. Re: Taking backup of 50 tables
                                EdStevens
                                Rahul India wrote:
                                Yes.
                                I need a script so i can take a backup of the table before dropping them.

                                Thanks.
                                So we are back to the beginning of this thread, where Karthick Arp asked
                                You need to define what you mean by backup?

                                1. You need to create a copy of these tables in your DB itself or
                                2. You need to copy these tables into another DB or
                                3. You need to take a dump of these table and store it externally or
                                4. You need to export the data in a human redable format, something like a delimited file.
                                Your answers have been inconsistent. Pick one and stick with it.
                                • 13. Re: Taking backup of 50 tables
                                  Karthick2003
                                  Rahul India wrote:
                                  Yes.
                                  I need a script so i can take a backup of the table before dropping them.

                                  Thanks.
                                  Your previous post said this
                                  I asked but no one has the privileges.Only onsite team has.They say it's a production environment
                                  And you are going to drop a table in production? Sure about that? I can imagine why access was not granted :)
                                  • 14. Re: Taking backup of 50 tables
                                    Rahul_India
                                    Karthick_Arp wrote:
                                    >
                                    And you are going to drop a table in production? Sure about that? I can imagine why access was not granted :)
                                    Haha no for pre-production too they don't have access.I don't have access to production instance