Results 1 to 13 of 13

Thread: oracle export

  1. #1
    Join Date
    Sep 2003
    Location
    kerala
    Posts
    24

    Unanswered: oracle export

    I have a dump of the Oracle 9i database. How can I import it into Oracle 7.3.4

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can't go backwards ...

    Gregg

  3. #3
    Join Date
    Sep 2003
    Location
    kerala
    Posts
    24
    Then ,what is the use Catexp7.sql
    reagards
    praveen

  4. #4
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    How to exp 8i into 7

    I know you were asking for 9i to 7, but I would assume it should work the same as from 8i to 7. Here is the document from metalink. good luck.


    Metalink Note:1072470.6
    Subject: EXP/IMP: CREATE ORACLE7 EXPORT FILE FROM ORACLE8 DATABASE

    Problem Description:
    ====================

    You want to export data from your Oracle8 database and import it into
    your Oracle7 database and don't know how to do this.


    Problem Explanation:
    ====================

    According to Oracle8 Utilities book, P. 1-50, to create an Oracle Release 7
    Export file from an Oracle8 database you must run Oracle release 7 Export
    against an Oracle8 server.

    Before you do the export you must first run "CATEXP7.SQL" script to create the
    export views that make the database look, to Export, like an Oracle release 7
    database.


    Solution Description:
    =====================

    In order to create an Oracle7 Export file, you must run Oracle7 Export
    against your Oracle8 server using the connect string for your Oracle8
    database and you must run "CATEXP7.SQL" script to create the export views
    that make the database look like an Oracle7 database.


    EXAMPLE TEST TO CREATE AN ORACLE7 EXPORT FILE FROM AN ORACLE8 DATABASE:
    ================================================== ====================

    1) First, you must run the "CATEXP7.sql" script as SYS (or connect internal).
    This creates the export views that make the database look, to Export, like
    an Oracle7 database.

    To find the "CATEXP7.SQL" script CD to "ORACLE_HOME/RDBMS/ADMIN" and then
    startup the database from this directory (if you don't start the database
    from this directory, you will have to give the complete path and filename
    when running the script).


    2) Create a user and table on the Oracle8 database:

    SVRMGR> create user sadavid identified by sadavid;
    Statement processed.
    SVRMGR> grant dba to sadavid;
    Statement processed.
    SVRMGR> grant unlimited tablespace to sadavid;
    Statement processed.

    SVRMGR> connect sadavid/sadavid
    Connected.
    SVRMGR> create table test_table AS SELECT * FROM user_objects;
    Statement processed.
    SVRMGR> desc test_table
    Column Name Null? Type
    ------------------------------ -------- ----
    OBJECT_NAME VARCHAR2(128)
    SUBOBJECT_NAME VARCHAR2(30)
    OBJECT_ID NUMBER
    DATA_OBJECT_ID NUMBER
    OBJECT_TYPE VARCHAR2(15)
    CREATED DATE
    LAST_DDL_TIME DATE
    TIMESTAMP VARCHAR2(19)
    STATUS VARCHAR2(7)
    TEMPORARY VARCHAR2(1)
    GENERATED VARCHAR2(1)

    ***********************************************

    3) Find the "service_name" for your Oracle8 instance. Look in the
    "tnsnames.ora" file located in the "ORACLE_HOME/network/admin" directory.

    For example, on tiger5 you can find the "tnsnames.ora" in the following
    directory:

    [tiger5]/u01/app/oracle/product/8.0.4/network/admin>

    wally_V805.world =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= 138.1.102.165)(Port= 1521))
    (CONNECT_DATA = (SID = V805))
    )

    *************************************************

    4) Go to "ORACLE_HOME" for the Oracle7 database and do the export using
    the service name for the Oracle8 database (wally_V805.world):

    [tiger5]/u08/users/sadavid> exp sadavid/sadavid@wally_V805.world
    tables=test_table file=sadavid.dmp

    Export: Release 7.3.4.3.0 - Production on Fri May 28 09:27:44 1999

    Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


    Connected to: Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
    With the Partitioning and Objects options
    PL/SQL Release 8.0.5.0.0 - Production
    Export done in US7ASCII character set

    About to export specified tables via Conventional Path ...
    . . exporting table TEST_TABLE 1 rows
    exported
    Export terminated successfully without warnings.

    **************************************************

    5) Do fromuser/touser import on your Oracle7 database:

    [tiger5]/u08/users/sadavid> imp sadavid/sadavid file=sadavid.dmp
    fromuser=sadavid touser=sadavid tables=test_table

    Import: Release 7.3.4.3.0 - Production on Fri May 28 09:33:11 1999

    Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.


    Connected to: Oracle7 Server Release 7.3.4.3.0 - Production
    With the distributed, replication, parallel query and Spatial Data
    options
    PL/SQL Release 2.3.4.3.0 - Production

    Export file created by EXPORT:V07.03.04 via conventional path
    . . importing table "TEST_TABLE" 1 rows
    imported
    Import terminated successfully without warnings.

    ************************************************

    6) Make sure the table imported correctly:



    Solution Explanation:
    =====================

    To create an Oracle Release 7 Export file from an Oracle8 database you must run
    Oracle release 7 Export against an Oracle8 server.

    Before you do the export you must first run "CATEXP7.SQL" script to create the
    export views that make the database look, to Export, like an Oracle release 7
    database.
    Michellea Southern-David

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Above is correct.

    export using the export version of the database you want to load into.

    So, using your Oracle 7 export utility, export the 9i db and then import with same Oracle 7 imp utility.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi

    all above is correct. u have to be aware of possible loss of functionality, since u are exporting from 9 to a lower desupported version.

    Greetz
    Edwin van Hattem
    OCP DBA / System analyst

  7. #7
    Join Date
    Sep 2003
    Location
    kerala
    Posts
    24
    hi all
    thanks for ur reply
    i tried to connect oracle9i from oracle 7.3.4 as a user with dba role.But wheren i am giving the name of the table to export..its giveing reply that that table is not existing
    like this
    from 7.3.4 i gave oe/oe@pra1
    export tbale name -- i gave emp
    oe.emp does not exist
    this is the message i got
    can u gide me further in this
    regards
    praveen

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I sent you a PM, but also include the owner of the table in the tablename.
    Also, I think in 7.* you need to specify ROWS=Y since the old default was no rows.

    so: scott.emp

    otherwise just connect as the owner, but import as a 7.* DBA using fromuser / touser clauses.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Sep 2003
    Location
    kerala
    Posts
    24

    praveen Oracle export

    Hi duck
    Thanks for ur reply
    i tried in both ways
    i connect as the owner of the table ie as oe like this
    exp oe/oe@pra1 tables=emp rows=y indexes=y
    but it came out with this error message ..
    oe.emp does not exist
    is it the problem with versions.
    then i tried to export the entire scheme by connect system/manager@pra1
    user=oe
    but it didnot show of exporting any table
    >if it is the problem with verios..why it is alowing me to give export command..It should give error at the time of giving export command
    pls provied me some more information in this
    regards
    praveen

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    try connecting through sqlplus.

    then run:

    select * from dba_objects where object_name = 'EMP';

    that should show the emp table and who owns it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2003
    Location
    kerala
    Posts
    24

    praven

    hi duck
    thaanks for ur reply
    I checked taht.The owner of the table from dba_objects is oe.
    then i connected 9.0.1 as oe and gave a select statment from the emp table.i could see the records
    but when i am giving the export stasment like
    exp oe/oe@pra1 tables=oe rawa=y
    it immediatly responding with oe.emp does not exist
    more intrestingly if i am doinf tyhe complete schema export as
    system/manager@pra1 no table are being exported

    i doubt certain othe things
    my 7.3.4 is with blocl size 2k.The system tablespace of 9.0.1 is 8k
    ..wether this make any problem...
    can u tell further in this
    regards
    praveen

  12. #12
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    maybe??

    I PM'ed you today, but I will give the message here too. Maybe this would work. Export from 9i to 8.1.7 then take the 8.1.7 and export to 7. Just an idea. I wish I could help more, but I have no idea about this one.
    Michellea Southern-David

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    i would like to see everything cut/pasted from your DOS window as it shows when you run that line. You have a lot of typos and it is difficult to tell what is exactly on your commandline.

    exp oe/oe@pra1 tables=emp rows=y file=emp.dmp


    You might want to check your tnsnames.ora file, but I doubt that is the problem here.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •