4 Replies Latest reply: Dec 28, 2012 12:49 AM by 861115 RSS

    exp from 11g 64 to imp 10g 32

    861115
      Hello ,
      I want to export data from oracle 11g to 10g. I have used expdp with version . It works perfectly.
      Please check below metion questions and suggest me how I can do it

      1. If I want to tarnsfer data between diff planforms (like 11g in unix/linux and 10g in windows) what is the option for it?

      2.What are the dependency we need to check in both databases for the data movment?
      3.Can I use transporteable tablespace here ? which will be more faster?
      4.Can we use exp with some possible option which will do the same ?

      Please let me knoow your suggestion .

      Thanks and Regards,
      Chandan
        • 1. Re: exp from 11g 64 to imp 10g 32
          Nadeem M
          1. You can use Import/Export or Transportable Tablespace or RMAN with CONVERT DATABASE command. Check below notes.

          Note 733205.1 - Migration of an Oracle Database Across OS Platforms
          Note 1401921.1 - Cross-Platform Database Migration (across same endian) using RMAN Transportable Database
          Note 414878.1 - Cross-Platform Migration on Destination Host Using Rman Convert Database

          2. Endian Format, Export/Import Utility Version, TTS limitations

          3. Yes. Linux & Windows have same endian format. It would be certainly the faster approch but will have limitations.
          Note 243304.1 10g: Transportable Tablespaces Across Different Platforms
           
          SQL> select * from V$TRANSPORTABLE_PLATFORM order by 1;
          
          PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
          ----------- ---------------------------------------- --------------
                    1 Solaris[tm] OE (32-bit)                  Big
                    2 Solaris[tm] OE (64-bit)                  Big
                    3 HP-UX (64-bit)                           Big
                    4 HP-UX IA (64-bit)                        Big
                    5 HP Tru64 UNIX                            Little
                    6 AIX-Based Systems (64-bit)               Big
                    7 Microsoft Windows IA (32-bit)            Little
                    8 Microsoft Windows IA (64-bit)            Little
                    9 IBM zSeries Based Linux                  Big
                   10 Linux IA (32-bit)                        Little
                   11 Linux IA (64-bit)                        Little
                   12 Microsoft Windows x86 64-bit             Little
                   13 Linux x86 64-bit                         Little
                   15 HP Open VMS                              Little
                   16 Apple Mac OS                             Big
                   17 Solaris Operating System (x86)           Little
                   18 IBM Power Based Linux                    Big
                   19 HP IA Open VMS                           Little
                   20 Solaris Operating System (x86-64)        Little
                   21 Apple Mac OS (x86-64)                    Little
          
          20 rows selected.
          4. Regular import/export options will be enough. No specific options/parameters are required. But remember below.

          When creating an export dump file for an import into a lower release database (e.g.: from Oracle11g to Oracle10g), use a version of the Export client that is equal to the version of the target database (= lowest version = Oracle10g in this case). Always use a version of the EXPORT utility that is equal to the lowest version of either the source or the target database. Always use a version of the IMPORT utility that is equal to the version of the target database.

          Nadeem M
          http://www.nadeemmohammed.wordpress.com
          • 2. Re: exp from 11g 64 to imp 10g 32
            Dean Gagne-Oracle
            Hi,
            1. If I want to tarnsfer data between diff planforms (like 11g in unix/linux and 10g in windows) what is the option for it?
            Data Pump can handle this. Platforms don't matter if you are not using transportable. More on that below.
            2.What are the dependency we need to check in both databases for the data movment?
            You may get some data loss if you are using different character sets. If the source is not a subset of the target, then some data
            characters may be truncated/modified.
            3.Can I use transporteable tablespace here ? which will be more faster?
            Not if you are going down. Transportable dumpfiles need to be imported into a version equal or greater than the source. If you
            have different endianness, then you need to run RMAN convert to modify the datafiles. If you have different character sets, then
            there are restrictions here.
            4.Can we use exp with some possible option which will do the same ?
            exp can do the same, but remember, exp is no longer supported, so if you have new features in your database, exp may not
            export those objects. I believe you are better off using Data Pump.

            Dean
            • 3. CTAS
              861115
              Hello ,

              I have a table test(first_name varchar2(10),...) with 100 rows

              If I am using create table test1(name) as select * from test; in that what are things are moving
              like
              1.index.
              2.Primary key.
              3.foregin key

              etc

              Thanks
              Chandan
              • 4. Re: CTAS
                861115
                CREATE TABLE "HR"."EMPLOYEES1"
                (     "EMPLOYEE_ID" NUMBER(6,0),
                     "FIRST_NAME" VARCHAR2(20),
                     "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
                     "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
                     "PHONE_NUMBER" VARCHAR2(20),
                     "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
                     "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
                     "SALARY" NUMBER(8,2),
                     "COMMISSION_PCT" NUMBER(2,2),
                     "MANAGER_ID" NUMBER(6,0),
                     "DEPARTMENT_ID" NUMBER(4,0),
                     CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
                     CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
                USING INDEX ENABLE,
                     CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
                USING INDEX ENABLE,
                     CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
                     REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
                     CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
                     REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
                     CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
                     REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
                ) as select (col1,....coln from x.table)

                will it work & give me all constarint index which I have in old table.