6 Replies Latest reply on Aug 19, 2016 6:40 PM by Marc120120

    Schema Export

    Marc120120

      Hello i am using oracle 11g i wanted to import data using sql developer version 4.1.3.20

      what will be the optimal way to install schema (with all tables and its data.)

      Links :

      How to import export.sql in sql developer? - Database Administrators Stack Exchange (having same problems)

      Importing and Exporting using the Oracle SQL Developer 3.0

       

      I just wanted to export schema and install it in my another environment with all dependency.

      Other alter native will be (after export) using f5 key but there are so many dependency so i have to go line by line. which is very time consuming.

       

      Can not use Pump as i do not have sys credentials.

       

      Thank you

        • 1. Re: Schema Export
          thatJeffSmith-Oracle

          An entire schema, all tables and data?

           

          Optimal way will almost always be by using Data Pump. You fan find our interface for that on the DBA panel. It requires server side access via database directories. If you don't have 'sys' privs, find someone who does.

           

          You can also use the Database Copy or Database Export utilities. Database Copy could be quite slow....Database Export should work, but you'll be inserting rows 1 by 1 if you don't use the Loader format - which you'll want to do anyway if you have BLOBs in your tables.

           

          We need to know more about your environment to know what would be 'optimal.'  I think optimal would be again to find someone with the privs and have the use Data Pump.

          • 2. Re: Schema Export
            Marc120120

            Hello Jeff,

            Thank you for you're valuable input

             

            What i did :

            Step 1:

            First of all exported Tables via Database Export (Tools --> Database Export)

            Step 2:

            Exported all objects,seqs,triggers etc.

             

            Then, Run table script(step 1) using f5 and step 2 using f5.which works in my case without any errors takes almost 1 hour though.

            Sys privs i can't have because it's server hosted environment other wise it would make my life more easier.

             

            My environment have 100 tables,50 packages(seq,procedure,function everything included),30 triggers and half a million records.

             

            Thank you.

            • 3. Re: Schema Export
              thatJeffSmith-Oracle

              Cool, so you're good to go then?

              • 4. Re: Schema Export
                Marc120120

                For sake of knowledge i would really like to know how would you deal with data export....(as a user which do not have sys prevs).

                Like, what you export first and in which way.

                Table structure...constraints....indexes....seq...etc.

                Please let me know so i can learn it faster

                • 5. Re: Schema Export
                  thatJeffSmith-Oracle

                  I'd ask the DBA to data pump it for me

                   

                  if it were a small amount of data, i'd use the wizard to build import scripts

                   

                  if it were a bigger amount, i'd ask the DBA to data pump it for me...or use SQL*loader

                  1 person found this helpful
                  • 6. Re: Schema Export
                    Marc120120

                    Great thanks for a quick response