ORACLE数据库导出 在数据库建立了一个用户,该用户下有一些对象,如何导出呢?用什么用户执行导出操作? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 sqlplus /nolog conn / as sysdba exp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp 是否符合你的要求? 可以使用plsql developer进行数据导出 很方便的 --下面用实验说明两个schemas之间的导出导入,希望能帮到你。[oracle@rles tools]$ pwd/tools[oracle@rles tools]$ mkdir expdir[oracle@rles tools]$ pwd/tools[oracle@rles tools]$ exitexitSQL> conn system/oracleConnected.SQL> SQL> SQL> show userUSER is "SYSTEM"SQL> SQL> create directory expdir as '/tools/expdir';Directory created.SQL> conn scott/tigerConnected.SQL> SQL> select count(*) from user_objects; COUNT(*)---------- 8SQL> conn / as sysdbaConnected.SQL> SQL> create user expdp identified by expdp default tablespace test temporary tablespace temp quota unlimited on test;User created.SQL> grant connect,resource,create session to expdp;Grant succeeded.SQL> conn expdp/expdpConnected.SQL> SQL> select count(*) from user_objects; COUNT(*)---------- 0SQL> ![oracle@rles ~]$ expdp system/oracle dumpfile=expdir:expscott.dat schemas=scottExport: Release 10.2.0.1.0 - Production on Friday, 07 May, 2010 19:38:48Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=expdir:expscott.dat schemas=scott Estimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."DEPT" 5.656 KB 4 rows. . exported "SCOTT"."EMP" 7.820 KB 14 rows. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rows. . exported "SCOTT"."PLAN_TABLE" 0 KB 0 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tools/expdir/expscott.datJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:39:41[oracle@rles ~]$ ls -l /tools/expdir/ total 276-rw-r----- 1 oracle oinstall 278528 May 7 19:39 expscott.dat[oracle@rles ~]$ [oracle@rles ~]$ impdp system/oracle dumpfile=expdir:expscott.dat remap_schema=scott:expdpImport: Release 10.2.0.1.0 - Production on Friday, 07 May, 2010 19:44:03Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=expdir:expscott.dat remap_schema=scott:expdp Processing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:"EXPDP" already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "EXPDP"."DEPT" 5.656 KB 4 rows. . imported "EXPDP"."EMP" 7.820 KB 14 rows. . imported "EXPDP"."SALGRADE" 5.585 KB 5 rows. . imported "EXPDP"."BONUS" 0 KB 0 rows. . imported "EXPDP"."PLAN_TABLE" 0 KB 0 rowsProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSJob "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:44:10[oracle@rles ~]$ exitexitSQL> conn expdp/expdpConnected.SQL> SQL> select count(*) from user_objects; COUNT(*)---------- 8SQL> select * from emp where rownum<6; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30SQL> SQL> 说下上面实验的数据库版本,expdp/impdp 是 10g 的工具,如果是 10g 以下版本,可以考虑 exp/imp。select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> 可以再加上个日志sqlplus /nologconn / as sysdbaexp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp log=D:\files\Test1.log 参考我的blog:ORACLE 数据库逻辑备份 简单 EXP/IMPhttp://blog.csdn.net/tianlesoftware/archive/2009/10/24/4718366.aspx------------------------------------------------------------------------------ Blog: http://blog.csdn.net/tianlesoftware 网上资源: http://tianlesoftware.download.csdn.net 相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx DBA1 群:62697716(满); DBA2 群:62697977 我不需要导出sysdba下所有用户的对象啊!只要导出某个user下的all object,需要授权吗? 在exp后面再加上owner=... 指定导出某个用户的对象 只需要指定owner=user就可以了 exp 用户名/密码@数据库名 file='d:\emp.sql' tables=emp---file='d:\emp.sql'导出的表放在哪个地方---tables=emp导出表的名字 oracle net manager不启动!!急需。。在线等~~ OracleDBConsoleorcl 请高手指点这个存储过程该如何写! 恢复的问题,急! group by 的分类问题 20行的简单pro*c代码为啥编译通不过,帮俺找出原因就给分. Oracle客户端的安装 oracle初學者 关于ORACLE中date字段的问题 java可以调用oracle里的包(package)吗 怎么关闭哪个WEB管理?或者删除? 高分求救一个SQL语句
conn / as sysdba
exp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp 是否符合你的要求?
--下面用实验说明两个schemas之间的导出导入,希望能帮到你。[oracle@rles tools]$ pwd
/tools
[oracle@rles tools]$ mkdir expdir
[oracle@rles tools]$ pwd
/tools
[oracle@rles tools]$ exit
exitSQL> conn system/oracle
Connected.
SQL>
SQL>
SQL> show user
USER is "SYSTEM"
SQL>
SQL> create directory expdir as '/tools/expdir';Directory created.SQL> conn scott/tiger
Connected.
SQL>
SQL> select count(*) from user_objects; COUNT(*)
----------
8SQL> conn / as sysdba
Connected.
SQL>
SQL> create user expdp identified by expdp default tablespace test temporary tablespace temp quota unlimited on test;User created.SQL> grant connect,resource,create session to expdp;Grant succeeded.SQL> conn expdp/expdp
Connected.
SQL>
SQL> select count(*) from user_objects; COUNT(*)
----------
0SQL> !
[oracle@rles ~]$ expdp system/oracle dumpfile=expdir:expscott.dat schemas=scottExport: Release 10.2.0.1.0 - Production on Friday, 07 May, 2010 19:38:48Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=expdir:expscott.dat schemas=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.656 KB 4 rows
. . exported "SCOTT"."EMP" 7.820 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."PLAN_TABLE" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/tools/expdir/expscott.dat
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:39:41[oracle@rles ~]$ ls -l /tools/expdir/
total 276
-rw-r----- 1 oracle oinstall 278528 May 7 19:39 expscott.dat
[oracle@rles ~]$
[oracle@rles ~]$ impdp system/oracle dumpfile=expdir:expscott.dat remap_schema=scott:expdpImport: Release 10.2.0.1.0 - Production on Friday, 07 May, 2010 19:44:03Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=expdir:expscott.dat remap_schema=scott:expdp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"EXPDP" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "EXPDP"."DEPT" 5.656 KB 4 rows
. . imported "EXPDP"."EMP" 7.820 KB 14 rows
. . imported "EXPDP"."SALGRADE" 5.585 KB 5 rows
. . imported "EXPDP"."BONUS" 0 KB 0 rows
. . imported "EXPDP"."PLAN_TABLE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 19:44:10[oracle@rles ~]$ exit
exitSQL> conn expdp/expdp
Connected.
SQL>
SQL> select count(*) from user_objects; COUNT(*)
----------
8SQL> select * from emp where rownum<6; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30SQL>
说下上面实验的数据库版本,expdp/impdp 是 10g 的工具,如果是 10g 以下版本,可以考虑 exp/imp。select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - ProductionSQL>
conn / as sysdba
exp Test1/Test1passwd owner=Test1 file=D:\files\Test1.dmp log=D:\files\Test1.log
参考我的blog:
ORACLE 数据库逻辑备份 简单 EXP/IMP
http://blog.csdn.net/tianlesoftware/archive/2009/10/24/4718366.aspx------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
只要导出某个user下的all object,需要授权吗?
只需要指定owner=user就可以了