Forum Stats

  • 3,734,273 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

Help in exp and imp command

lesak
lesak Member Posts: 532 Blue Ribbon
edited Sep 11, 2008 6:59AM in General Database Discussions
Hi,

How in simpliest way export all user objects and import this objects to another database on the same user account??

Best.
Tagged:

Answers

  • 408327
    408327 Member Posts: 130
    hi,


    exp with owner option and imp with fromuers, touser option is the best way to exp/imp a schema

    Rgds
  • Niall Litchfield
    Niall Litchfield Member Posts: 976 Bronze Badge
    tutus wrote:
    Hi,

    How in simpliest way export all user objects and import this objects to another database on the same user account??

    Best.
    Two simple ways.

    1) Export as that user.
    C:\>c:\oracle\product\10.2.0\db\bin\exp userid=u1/u1 file=u1.dmp
    
    Export: Release 10.2.0.3.0 - Production on Thu Sep 11 09:33:38 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user U1
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user U1
    About to export U1's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export U1's tables via Conventional Path ...
    . . exporting table                              T      41170 rows exported
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully without warnings.
    2) export as SYSTEM and specify OWNER=<USERNAME> and FROMUSER,TOUSER in import.
    C:\>c:\oracle\product\10.2.0\db\bin\exp userid=system file=u1.dmp owner=u1
    
    Export: Release 10.2.0.3.0 - Production on Thu Sep 11 09:34:51 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Password:
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
    
    About to export specified users ...
    . exporting pre-schema procedural objects and actions
    . exporting foreign function library names for user U1
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user U1
    About to export U1's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export U1's tables via Conventional Path ...
    . . exporting table                              T      41170 rows exported
    . exporting synonyms
    . exporting views
    . exporting stored procedures
    . exporting operators
    . exporting referential integrity constraints
    . exporting triggers
    . exporting indextypes
    . exporting bitmap, functional and extensible indexes
    . exporting posttables actions
    . exporting materialized views
    . exporting snapshot logs
    . exporting job queues
    . exporting refresh groups and children
    . exporting dimensions
    . exporting post-schema procedural objects and actions
    . exporting statistics
    Export terminated successfully without warnings.
    Niall Litchfield
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,903 Gold Crown
    Hi,

    Refer to Doc, you can able to understand the Options ...too when and what scenario's you an use..
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm

    Based on Version Check the Doc's...

    - Pavan Kumar N
    Pavan Kumar
  • Anand...
    Anand... Member Posts: 3,590
    edited Sep 11, 2008 4:45AM
    In 9i

    While exporting use OWNER=schema_name parameter, create the owner in target database and then import using FROMUSER=schema_name TOUSER=schema_name paramerter.

    If u are importing more than 1 schema then user

    fromuser=('ABC','XYZ') touser=('ABC','XYZ')


    In 10g use REMAP_SCHEMA paramter.



    Redards,
    Anand
    Anand...
  • Robert Geier
    Robert Geier Member Posts: 2,989
    You could use exp/imp as above, or dpexp/dpimp. Both allow you to remap schemas when you import.
  • It you are going to export only one user then you can use


    exp users/[email protected] file=c:\user.dmp owner=test log=c:\userexp.log buffer=34444

    and import with

    imp users/[email protected] file=c:\user.dmp fromuser=test touser=test log=c:\imptest.log
    Maran Viswarayar
  • Guys here so fast ;)
  • lesak
    lesak Member Posts: 532 Blue Ribbon
    Wow, thanks for your answers :) Export was OK, but when I try to import, then I have this errors:
    IMP-00058: ORACLE error 6550 encountered
    ORA-06550: line 1, column 33:
    PLS-00302: component 'SET_NO_OUTLINES' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    IMP-00000: Import terminated unsuccessfully
  • Niall Litchfield
    Niall Litchfield Member Posts: 976 Bronze Badge
    tutus wrote:
    Wow, thanks for your answers :) Export was OK, but when I try to import, then I have this errors:
    IMP-00058: ORACLE error 6550 encountered
    ORA-06550: line 1, column 33:
    PLS-00302: component 'SET_NO_OUTLINES' must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    IMP-00000: Import terminated unsuccessfully
    is the database to which you are importing the same version as the imp executable that you are using

    Niall Litchfield
    http://www.orawin.info/
    Niall Litchfield
  • 577284
    577284 Member Posts: 100
    Hi you are using different versions of Oracle I persume

    You cannot import 9.2 with the 10.2 utilities. you can imptor into 10.2 from dump created with the lower version utilities and you can export from the 10.2 with the lower version utilities

    Regards,
    Vijayaraghavn K
  • Niall Litchfield
    Niall Litchfield Member Posts: 976 Bronze Badge
    Vijayaraghavan wrote:
    Hi you are using different versions of Oracle I persume

    You cannot import 9.2 with the 10.2 utilities. you can imptor into 10.2 from dump created with the lower version utilities and you can export from the 10.2 with the lower version utilities

    Regards,
    Vijayaraghavn K
    To add to Vijayaraghavan and my earlier post http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2852 has the compatibility reference you need when moving between versions.

    Niall Litchfield
    http://www.orawin.info/
This discussion has been closed.