1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Export Tablespace to other Tablespace using Data Pump.

Discussion in 'Security, Backup and Recovery' started by mzainal, May 9, 2010.

  1. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    Hi,

    Is it possible i export tablespace from server A to server B? Server A is old database and server B is new database that have only default tablespace.

    Yesterday i try to export to server B but got error:
    Code (Text):

    User azmansl does not exist.
    Cannot create table of forum.log_view
    That is some of the error i got. Please assist me.

    Thank you.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post the exact command you used for datapump so someone can provide an answer.
     
  3. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    I issue this command:
    Code (Text):
    impdp sys/pmo123 directory=extdata dumpfile=alltablespace.dmp tablespaces=SYSTEM,UNDOTBS1,SYSAUX,TEMP,USERS,PORTAL,PORTAL_DOC,PORTAL_IDX,PORTAL_LOG,IAS_META,DISCO_PTM5_META,DISCO_PTM5_CACHE,DCM,DSGATEWAY_TAB,WCRSYS_TS,B2B_RT,B2B_DT,B2B_IDX,B2B_LOB,ORABPEL,BAM,OCATS,UDDISYS_TS,OLTS_ATTRSTORE,OLTS_BATTRSTORE,OLTS_CT_STORE,OLTS_DEFAULT,OLTS_SVRMGSTORE,REP_DATA,REP_INDX,REP_SNAP_DATA,BI,UCM,HYPERION,MISSIONTEST,ORADW,ORADW_IDX,MOHEDB,MVFORUM,DIAG,MVNFORUM,IIAS,DATABANK,IDC2_TEMP,IDC2_SYS
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Importing tablespaces does not create user accounts so tables/indexes/objects owned by user accounts in database A which don't exist in database B can't be created, not with the command you used. You will need to add a REMAP_SCHEMA line for each schema owner that does not exist in the target database:

    .... REMAP_SCHEMA=azmansl:azmansl

    I know it seems redundant but without this impdp won't create the destination user(as you've seen). The account will be created with an unusable password (possible because SYS is running the import) and the objects will be created. After a succesful import connect as sysdba and change the password for azmansl to something usable.
     
  5. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    You mean I need to create schema first and do import after that? What about table? Do i need to create also in server B?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Absolutely not! Let data pump do it for you by ncluding the REMAP_SCHEMA=azmansl:azmansl parameter, an act I thought I explained fairly clearly in my last post.
     
  7. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    If i got so many schema, how to include in remap?
     
  8. mzainal

    mzainal Forum Advisor

    Messages:
    72
    Likes Received:
    0
    Trophy Points:
    130
    This is schema in server A. How to remap? Is it like this : REMAP_SCHEMA=azmansl:azmansl,b2b:b2b?
    Code (Text):
    USERNAME
    ------------------------------
    ANANG
    ANONYMOUS
    AZMANSL
    B2B
    BAM
    BI
    CNS
    CTXSYS
    DBSNMP
    DCM
    DIP

    USERNAME
    ------------------------------
    DISCOVERER5
    DMSYS
    DSGATEWAY
    ESS
    EXFSYS
    FORUM
    FRGS
    HR
    HYPERION
    HYPERION_ADMIN
    IDC1_USER

    USERNAME
    ------------------------------
    IDC2_USER
    IFMS
    IIAS
    IIASMOHE
    INTERNET_APPSERVER_REGISTRY
    IP
    JAMUSER
    JIVE
    LBACSYS
    LSMS
    MDB

    USERNAME
    ------------------------------
    MDDATA
    MDSYS
    MISSION
    MOHE
    MVFORUM
    MVNFORUM
    OCA
    ODS
    ODSSM
    OLAPSYS
    ORABPEL

    USERNAME
    ------------------------------
    ORACLE_OCM
    ORADW
    ORADW_HR
    ORADW_STAGE
    ORAESB
    ORAOCA_PUBLIC
    ORASSO
    ORASSO_DS
    ORASSO_PA
    ORASSO_PS
    ORASSO_PUBLIC

    USERNAME
    ------------------------------
    ORAWSM
    ORDPLUGINS
    ORDSYS
    OUTLN
    OWBRT_SYS
    OWF_MGR
    PORTAL
    PORTAL_APP
    PORTAL_DEMO
    PORTAL_PUBLIC
    PORTAL_TEST

    USERNAME
    ------------------------------
    PORTLET_PREFS
    REP_OWNER
    SI_INFORMTN_SCHEMA
    SSPU
    SYS
    SYSTEM
    S_NQ_SCHED
    TEST
    TSMSYS
    UCM
    UDDISYS

    USERNAME
    ------------------------------
    USERMOHE
    WCRSYS
    WIRELESS
    WKPROXY
    WKSYS
    WK_TEST
    WMSYS
    WONG
    XDB

    86 rows selected.
     
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY are you doing this the hard way when a FULL=Y would do all of this for you? You REALLY need to read the following:

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1006293
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#i1010670

    You should take a full export and then perform a full import.