8 Replies Latest reply: Mar 20, 2011 5:46 PM by Lubiez Jean-Valentin RSS

    basic export import...

    710236
      I am testing export from oracle 7i to 11g.

      1) Is 'incremental' export supported only on FULL database exports? Can this option be used only on a specific schemas?

      2) If i want to export full database and import, does it allow the option to remap users i.e. say in full export we have 3 schemas for user USER1, USER2, USER3 when importing can i remap these to NEWUSER1, NEWUSER2, NEWUSER3?

      3) For exports, say 1 take a full DB export which includes TBLA with 1million rows. Next day I take incremental export and two rows are added/changed in TBLA will the incremental export, export all rows in TBLA or only 2 rows which were addeed/changed?
        • 1. Re: basic export import...
          sb92075
          Handle:      user11356487
          Status Level:      Newbie
          Registered:      Jul 4, 2009
          Total Posts:      54
          Total Questions:      25 (18 unresolved)
          so many questions and so few answers.
          :-(

          1) NO

          2) NO

          3) NO
          bcm@bcm-laptop:~$ exp help=yes
          
          Export: Release 11.2.0.1.0 - Production on Sun Mar 20 14:56:27 2011
          
          Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
          
          
          
          You can let Export prompt you for parameters by entering the EXP
          command followed by your username/password:
          
               Example: EXP SCOTT/TIGER
          
          Or, you can control how Export runs by entering the EXP command followed
          by various arguments. To specify parameters, you use keywords:
          
               Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
               Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
                         or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
          
          USERID must be the first parameter on the command line.
          
          Keyword    Description (Default)      Keyword      Description (Default)
          --------------------------------------------------------------------------
          USERID     username/password          FULL         export entire file (N)
          BUFFER     size of data buffer        OWNER        list of owner usernames
          FILE       output files (EXPDAT.DMP)  TABLES       list of table names
          COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
          GRANTS     export grants (Y)          INCTYPE      incremental export type
          INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
          DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
          LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
          ROWS       export data rows (Y)       PARFILE      parameter filename
          CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
          
          OBJECT_CONSISTENT    transaction set to read only during object export (N)
          FEEDBACK             display progress every x rows (0)
          FILESIZE             maximum size of each dump file
          FLASHBACK_SCN        SCN used to set session snapshot back to
          FLASHBACK_TIME       time used to get the SCN closest to the specified time
          QUERY                select clause used to export a subset of a table
          RESUMABLE            suspend when a space related error is encountered(N)
          RESUMABLE_NAME       text string used to identify resumable statement
          RESUMABLE_TIMEOUT    wait time for RESUMABLE 
          TTS_FULL_CHECK       perform full or partial dependency check for TTS
          VOLSIZE              number of bytes to write to each tape volume
          TABLESPACES          list of tablespaces to export
          TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
          TEMPLATE             template name which invokes iAS mode export
          
          Export terminated successfully without warnings.
          Edited by: sb92075 on Mar 20, 2011 3:05 PM
          • 2. Re: basic export import...
            EdStevens
            user11356487 wrote:
            I am testing export from oracle 7i to 11g.
            I've been working with oracle since v 7.3 and never heard of oracle 7i. Please give full and actual version numbers, not marketing labels.

            1) Is 'incremental' export supported only on FULL database exports? Can this option be used only on a specific schemas?
            Please define "incremental export" and point to the oracle reference that explains it. At http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/original_export.htm#insertedID4 I see "full", "tablespace", "use", and "table", but no "incremental" export for the original exp. At http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/dp_export.htm#i1007466 I see "full", "schema", "table", "tablespace" modes for expdp, but no "incremental"
            2) If i want to export full database and import, does it allow the option to remap users i.e. say in full export we have 3 schemas for user USER1, USER2, USER3 when importing can i remap these to NEWUSER1, NEWUSER2, NEWUSER3?
            >
            3) For exports, say 1 take a full DB export which includes TBLA with 1million rows. Next day I take incremental export and two rows are added/changed in TBLA will the incremental export, export all rows in TBLA or only 2 rows which were addeed/changed?
            Again, please cite the documentation that defines an 'incremental' export ...
            • 3. Re: basic export import...
              sybrand_b
              Oracle 7i doesn't exist as a version! You mean Oracle7?

              1 This is a doc question and the documentation is still online
              Refer to http://www.oracle.com/technetwork/documentation/oracle7-091910.html

              2 This is also a doc question which can be answered by typing imp help=y on the commandline

              3 The complete table will bge exported.
              This is why incremental export is no longer supported.
              Basically this is also a doc question.
              Did you ever read about the Forums Etiquette? It recommends consulting documentation before you post.

              ------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: basic export import...
                710236
                - Sorry, the actual version is Oracle8i Release 8.1.7.4.1; I typed 7i instead of 8i

                - I was reading documentation which mentions following:

                Exports can be of three categories:
                1) Incremental Exports - An incremental export backs up only the tables that have changed since the last incremental, cumulative, or complete export. For example if one row is added or updated to a table since the last incremental, cumulative, or complete export was done the entire table is exported. Tables which have not been modified are not exported.
                Incremental exports cannot be specified as Read consistent i.e. CONSISTENT=Y
                The activity is tracked in SYS.INCEXP, then updates the table with a new ITIME and EXPID.
                Incremental exports can only be done in full database mode (FULL=Y). Only users having EXP_FULL_DATABASE role can run incremental exports.
                A look at the parameter file incexp.par FILE= incremental020999.dmp
                LOG= incremental020999.log
                INCTYPE=INCREMENTAL

                2) Cumulative Exports - An cumulative export backs up only the tables that have changed since the last cumulative, or complete export. In essence a cumulative export compresses a number of incremental exports into a single cumulative file. For example let us assume at time "A" a cumulative export was done, at time "B" a record was deleted from table emp_mast , at time "C" an incremental export was done (incremental export pickup table emp_mast, since a record is deleted). At time "D" an record was inserted in emp_dtl, at time "E" an incremental export was done (incremental export pickup table emp_dtl, since a record is inserted). At time "F" when cumulative export is done it picks up table emp_mast and also emp_dtl since these two table have changed since the last cumulative was done. A look at the parameter file cumexp.par FILE= cumulative020999.dmp
                LOG= cumulative 020999.log
                INCTYPE=CUMULATIVE

                3) Complete Exports - A complete export establishes a base for incremental and cumulative exports. It is equivalent to a full database export, except that it updates the tables that track incremental and cumulative exports. If you do not specify INCTYPE=COMPLETE then the tables that track incremental and cumulative exports are not updated. A look at the parameter file completeexp.par FILE= complete020999.dmp
                LOG= complete020999.log
                INCTYPE=COMPLETE


                hopefully, you gurus can help me understand as I could use the incremental option to export import data...
                • 5. Re: basic export import...
                  sb92075
                  does not exist in V9+
                  • 6. Re: basic export import...
                    710236
                    so even though this option exists in 8i, and i take incremental export from 8i, the 11g exp utility will not support it?
                    • 7. Re: basic export import...
                      sb92075
                      V8 is obsoleted & unsupported.
                      i take incremental export from 8i, the 11g exp utility will not support it?
                      "11g exp" has *NOTHING to do with export from 8i.

                      Feel free to test with 8i exp & V11 imp, then report back here what you learned.
                      • 8. Re: basic export import...
                        Lubiez Jean-Valentin
                        Hello,

                        I am testing export from oracle 7i to 11g.
                        I'm not sure about what you mean by Oracle 7i, are you talking about Oracle *7* or Oracle *8i* ?
                        If i want to export full database and import, does it allow the option to remap users
                        Anyway in these releases (7 or 8i) you cannot use DATAPUMP so you don't have the REMAP_SCHEMA parameter. Instead you have the parameters FROMUSER, TOUSER:

                        http://download.oracle.com/docs/cd/E11882_01/server.112/e16536/original_import.htm#SUTIL1715
                        Next day I take incremental export and two rows are added/changed in TBLA will the incremental export, export all rows in TBLA or only 2 rows which were addeed/changed?
                        Incremental export doesn't exist in the sense that it will export only the modified rows since the last export (the entire Table will be exported). If you need to Refresh datas incrementally you may use FAST REFRESH replication with Materialized View / Materialized View Log. However, you cannot connect straightly a Database in Oracle *7* or *8i* to a Database in Oracle *11g*. It's not supported and, you'll need one or two intermediate Databases.

                        On the other hand, the following Note of My Oracle Support may give you useful advices about export/import:
                        Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] [ID 132904.1]
                        Hope this help.
                        Best regards,
                        Jean-Valentin