2 Ответы Последний ответ: 09.12.2005 22:31, автор: 8734

    exporting from 10g into 9i

    469224
      Hi all



      When exporting an Oracle10g schema from Oracle9i the following error occurs : exp-0003 no storage definition found.

      I understand that when you want to import something from 10g to 9i you have to use the lower versions exp and imp tool, which is what I am doing.

      This problem is not schema specific, it occurs with each schema I try. We recently installed Oracle patch 10.1.0.4.

      Any help will be usefull

      Thanks in advance from Percy
        • 1. Re: exporting from 10g into 9i
          469753
          Can you copy the data with a database link?

          create table fu as select * from fu@remotedb

          or

          insert into fu select * from fu@remotedb
          • 2. Re: exporting from 10g into 9i
            8734
            Hi

            If you have metalink access then you should read this note 274076.1

            I am pasting the contents here anyways. Hopefully it will solve your problem.

            Solution
            The EXP-3 error only occurs when the export from the 9.2.0.5.0 or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) is done with a lower release export utility, e.g. 9.2.0.4.0. The reason to run an export from a 9.2.0.5.0
            or any higher release database (e.g. 9.2.0.6.0 or 10.1.0.4.0) with a 9.2.0.4.0 export utility, is to import the export dump file later into a target database of that lower release.

            Note: if your intention is to export from a 9.2.0.6.0 database in order to import (back) into a 9.2.0.6.0 target database, there is no need to use a pre-9.2.0.5 release export utility. In that situation use the 9.2.0.6.0 export utility.

            Workaround 1
            -------------

            Rerun the export and specify the parameter COMPRESS=Y (this is the default value for COMPRESS).

            If you have a table with a secondary object, e.g. a LOB column, then this workaround might not work and you will re-produce the EXP-3 error during export. In that case, use workaround 2.

            Workaround 2
            -------------

            a. Make a note of the export definition of exu9tne from $ORACLE_HOME/rdbms/admin/catexp.sql

            b. Copy this to a new file and add:
            "UNION ALL select * from sys.exu9tneb"
            to the end of the definition

            e.g. In the 9.2.0.5.0 or any higher release source database (e.g. 9.2.0.6.0 or 10.1.0.4.0), the workaround view would be:

            CREATE OR REPLACE VIEW exu9tne (
            tsno, fileno, blockno, length) AS
            SELECT ts#, segfile#, segblock#, length
            FROM sys.uet$
            WHERE ext# = 1
            UNION ALL
            SELECT * from sys.exu9tneb
            /

            c. Run this as the SYS user (!) against the database that needs to be exported.

            d. Re-run the export as required.

            e. Put back the original definition of exu9tne as obtained in step a.

            Note: only use this workaround when absolutely necessary, and don't forget to put back the original definition of the view SYS.exu9tne as mentioned in step e.


            Hope it helps.

            Rgds
            Adnan