美创科技技术社区

注册

 

发新话题 回复该主题

sys用户使用exp远程导出表测试 [复制链接]

1#










由于之前在客户那边使用sys用户exp导出base用户下的表失败,现在自己虚拟机中搭建类似环境测试是否可以成功
测试能否直接在远程使用sys用户进行exp导出:
目标端:windows2008R2,oracle11g

源端    :windows2008R2,oracle11g


将连接串写入tnsnames.ora

orcl128 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.128)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )


orcl129 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.40.129)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

  )



使用exp导出全库:
exp 'sys/111111@orcl129 as sysdba'  file=c:\full.dmp log=c:\full.log full=Y

C:\Users\Administrator>exp 'sys/111111@orcl129 as sysdba'  file=c:\full.dmp log=c:\full.log  full=Y


Export: Release 11.2.0.4.0 – Production on 星期三 3 1 16:05:58 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.



EXP-00004: invalid username or password

Username: sys/111111@orcl129 as sysdba


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set


About to export the entire database …

. exporting tablespace definitions

. exporting profiles

. exporting user definitions

. exporting roles

. exporting resource costs

. exporting rollback segment definitions

. exporting database links

. exporting sequence numbers

. exporting directory aliases

. exporting context namespaces

. exporting foreign function library names

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions

. exporting system procedural objects and actions

. exporting pre-schema procedural objects and actions

. exporting cluster definitions

. about to export SYSTEM's tables via Conventional Path …

. . exporting table                    DEF$_AQCALL          0 rows exported

. . exporting table                   DEF$_AQERROR          0 rows exported

. . exporting table                  DEF$_CALLDEST          0 rows exported

. . exporting table               DEF$_DEFAULTDEST          0 rows exported

. . exporting table               DEF$_DESTINATION          0 rows exported

. . exporting table                     DEF$_ERROR          0 rows exported

. . exporting table                       DEF$_LOB          0 rows exported

. . exporting table                    DEF$_ORIGIN          0 rows exported

. . exporting table                DEF$_PROPAGATOR          0 rows exported

. . exporting table       DEF$_PUSHED_TRANSACTIONS          0 rows exported

. . exporting table               MVIEW$_ADV_INDEX          0 rows exported

. . exporting table                 MVIEW$_ADV_OWB

. . exporting table           MVIEW$_ADV_PARTITION          0 rows exported

. . exporting table                            OL$

. . exporting table                       OL$HINTS

. . exporting table                       OL$NODES

. . exporting table        REPCAT$_AUDIT_ATTRIBUTE          2 rows exported

……

EXP-00107: Feature (BINARY XML) of column XML_CONTENT in table APEX_030200.WWV_M

IG_RPTS is not supported. The table will not be exported.


. . exporting table             MC$RULE_LOGON_RULE          3 rows exported

. . exporting table                  MC$RULE_MERGE          0 rows exported

. . exporting table     MC$RULE_OPERATION_PRIVRULE          0 rows exported

. . exporting table         MC$RULE_OPERATION_RULE         41 rows exported

. . exporting table                 MC$RULE_SCHEMA          0 rows exported

. . exporting table            MC$RULE_SPECIAL_APP          0 rows exported

. exporting synonyms

. exporting views

. exporting referential integrity constraints

. exporting stored procedures

. exporting operators

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

过程中存在报错。

查询MOS

(文档 ID 1439066.1)


Exporting with -mode export: EXP-00107: Feature (BINARY XML) … is not supported.



Problem Description:

Exporting with -mode export returns the following error:
EXP-00107: Feature (BINARY XML) of column DEPENDENT_PARENT_IDS in table DEV_PORTAL.WWUTL_EXPORT_IMPORT_DTL_XML$ is not supported. The table will not be exported.

For example:
$ ./exp.csh -mode export -s dev_portal -d exp.dmp -c orcl01
Verifying the environment variables…
Enter the password for the portal schema (Default=dev_portal): welcome1
Verifying the portal schema passed…
Verifying the availability of transport set…
Verifying database version
Verifying the status of transport set…
Calling Oracle exp or imp utility based on the mode of operation….

Export: Release 11.2.0.2.0 – Production on Wed Mar 21 16:24:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.


Username:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path …
. . exporting table WWUTL_EXPORT_IMPORT$ 1 rows exported
. . exporting table WWUTL_APP_TX_APPLICATION$ 1 rows exported
. . exporting table WWUTL_APP_TX_APPLICATION_DET$ 1 rows exported
. . exporting table WWUTL_APP_TX_MODULES$ 2 rows exported
. . exporting table WWUTL_APP_TX_MODULE_DETAILS$ 48 rows exported
. . exporting table WWUTL_EXPIMP_DEP_PARENTS 3 rows exported
. . exporting table WWUTL_EXPORT_IMPORT_DTL_XML$
EXP-00107: Feature (BINARY XML) of column DEPENDENT_PARENT_IDS in table DEV_PORTAL.WWUTL_EXPORT_IMPORT_DTL_XML$ is not supported. The table will not be exported.
. . exporting table WWUTL_EXPORT_IMPORT_LOG$ 391 rows exported
. . exporting table WWUTL_NLS_TX_STRINGS$ 2 rows exported
. . exporting table WWUTL_PRO_TX_PROVIDERS$ 1 rows exported
. . exporting table WWUTL_SEC_TX_SYS_PRIV$ 2 rows exported
Export terminated successfully with warnings.
This part of the export/ import operation is now complete.
oracle@fmw11g.vm.oracle.com $


Cause:

Export was done using -mode export on an a database Release 2 (11.2) or later.

Starting with 11g Release 2 database (11.2) there were some changes made to the default storage model for the XMLType datatype. These changes make no longer supported to perform Portal Export/Imports using the Original Database Export/Import utilities (exp/imp).

Due to the above change, the following methods to perform Portal Export/Import from the command line are not supported if your database is 11.2 or later:

  • -mode export
  • -mode import

Solution:

  • Export using -mode exportdp and specify a Directory Object in the database where you want to generate the dump file.
              
            For example:
      
    ./exp.csh -mode exportdp -s dev_portal -d exp.dmp -c orcl01 -dir DATA_PUMP_DIR










使用SYS用户导出scott用户下的emp表:
exp 'sys/111111@orcl129 as sysdba' tables=scott.emp

C:\Users\Administrator>exp 'sys/111111@orcl129 as sysdba' tables=scott.emp

Username: sys/111111@orcl129 as sysdba


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set


About to export specified tables via Conventional Path …

Current user changed to SCOTT

. . exporting table                            EMP         15 rows exported

Export terminated successfully without warnings.




可以看到导出成功。
分享 转发
TOP
发新话题 回复该主题