10 Replies Latest reply on Aug 2, 2011 10:51 AM by 828699

    how to import schema?

    571157
      hi

      I am using htmldb2.0...I have one schema in my system..I want to copy that schema into another system which also contains htmldb2.0...Can anyone say how to do this?

      Fazila
        • 1. Re: how to import schema?
          571157
          Hi
          i am using generate ddl script to import the database schema from one system to another system....i run the script...but while exporting(running the imported sql script file into another system), it is not run properly...the database is not exported..can anyone say how to import the database?

          Fazila
          • 2. Re: how to import schema?
            512680
            Hi Fazila,

            What is the version of the database ???
            You can use exp/imp utility to export your schema and import it in another database.

            Mike
            • 3. Re: how to import schema?
              571157
              hi,

              i used 2.0 , now i want to copy the database to apex 3.0

              fazilla
              • 4. Re: how to import schema?
                512680
                Hi Fazila,

                I want to know the version of database (9i,10g) ???
                To export a schema,

                On the database server, type

                exp userid=system/xxxxx owner=your_schema file=your_schema.dmp log=exp_your_schema.log

                To import the schema,

                On the server where the 'import database' is

                imp system/xxxxxx fromuser= "your_schema" touser="your_schema" file="your_schema".dmp commit=Y ignore=Y log=imp_"your_schema".log

                Before that , verify that your schema is created in your database with APEX3.0
                (elsewhere create user your_schema.....)

                And after import,recompile all the objects if your schema contains procedures,packages,triggers...


                Mike
                • 5. Re: how to import schema?
                  570748
                  hi mike
                  the version of the database is 10g

                  fazilla
                  • 6. Re: how to import schema?
                    512680
                    Hi,

                    If you are in 10g, so you can use datapump (even if exp/imp works)
                    Have a look at http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_api.htm

                    Mike
                    • 7. Re: how to import schema?
                      570748
                      hi
                      My system is stand alone one and installed with htmldb2.0 and my server is installed with htmldb3.0...There is no connection between my system and server system....So i could not use the following commands...

                      exp userid=system/xxxxx owner=your_schema file=your_schema.dmp log=exp_your_schema.log (to export)

                      imp system/xxxxxx fromuser= "your_schema" touser="your_schema" file="your_schema".dmp commit=Y ignore=Y log=imp_"your_schema".log (to import)



                      Can you say how to recompile all the objectrs of my schema from one system to server?

                      Fazila
                      • 8. Re: how to import schema?
                        512680
                        Hi,

                        Do you mean that you don't have network connection between your system and the server??
                        Otherwise , launch exp on your system, (compress the dmp file if it is huge),copy the dmp file on your server (ftp if you have network, USB key else maybe), (decompress the dmp file), imp on your system in the directory where you put the dmp file.

                        To recompile all the objects your schema, log as sysdba on the server
                        and launch @/rdbms/admin/utlrp . (I don't know if the method is valid in 10g but it is in 9i). It will recompile all the invalid objects of your database.


                        Mike
                        • 9. Re: how to import schema?
                          512680
                          Just replace @/rdbms/admin/utlrp by @?/rdbms/admin/utlrp
                          (? = ORACLE_HOME )

                          Mike
                          • 10. Re: how to import schema?
                            828699
                            Please find below steps for importing and exporting schema in oracle


                            exp schemausername/password file=filename.dmp log=emp.log

                            imp file=emp.dmp fromuser=username touser=username

                            username: / as sysdba