2018-03-07 16:05:19 qq_23311211 阅读数 10624

一:以oracle用户登录Linux,使用命令:sqlplus / as sysdba进入SQL命令模式;

oracle数据库exp命令导出数据及imp命令导入数据

1.数据导出(正式环境Linux环境下导出数据):
expdp 导出数据库用户名/'密码' 
cluster=n 
dumpfile=sys.dmp --导出文件名
directory=expdir --需要存放导出数据的路径create directory expdir as 'home/oracle/expdir';select * from dba_directories;

tables=表名(多个表用英文逗号隔开,*表示所有表) logfile=sys.log

2.数据导入(导入1在正式环境使用exp导出的数据):
impdp 导入数据库用户名/'密码' 
directory=impdir --需要导入数据的路径,查看当前已经创建好的路径:select * from dba_directories,创建新路径:create directory impdir as 'home/oracle/expdir'
dumpfile=sys.dmp --导入文件名
logfile=sys_imp.log 
remap_schema=被导入数据库用户:导入数据库用户名 table_exists_action=append--表存在的话数据追加


注意:命令要排成一行再复制到crt等客户端执行。
2012-03-07 11:05:18 tangrobin 阅读数 462

一、导出表

1、导出自己的表

exp userid=scott/giger@orcl  tables=(emp,.....)  file=d:\e.dmp;

备:导出多张表之间用,逗号分别。linux 的路径和windows 路径区别。

2、导出其它方案的表

exp userid=sys/sys@orcl tables=(swserp.tbsuser) file='/tmp/e.dmp';

备:用户要导出其它方案的表,则需要dba的权限,grant 授予exp_full_database权限。

二、导出表结构

1、导出表结构

exp userid=swserp/swserp@orcl tables=(tbsuser,....) file='/tmp/m.dmp' rows=n;

2、使用直接导出方式

exp userid=swserp/swserp@orcl tables=(tbsuser,....) file='/tmp/m.dmp'  direct=y

备:这种方式,当数据量大的时候,可以使用这样的方法。前提是,数据库的字符集与客户端的字符集要完全一致,否则会报错。

三、导出方案

1、导出自己的方案

exp swserp/swserp@orcl owner=swserp file ='/tmp/swserp.dmp'

备注:多个方案可以通过,逗号分开。

2、导出其它方案

exp sys/sys@orcl owner=(swserp) file='/tmp/swserp.dmp'

备注:需要有DBA的权限。

四、导出数据库

exp userid=system/system@orcl full=y inctype=complete file=x.dmp

备:必须有exp_full_database权限

 

 

2013-05-12 14:50:24 zhuxg221 阅读数 460

exp是oracle提供的一个对数据库进行逻辑备份的客户端工具。利用这个工具可以将数据库按表空间、用户或某些表保存到操作系统下的一个二进制格式的文件里。这个二进制文件是平台无关的,也就是说可以将windows下的exp文件imp到linux/unix系统中,利用这个特性可以完成某些数据迁移工作。10g之前的版本没有data pump,数据导入导出的时候必须要exp/imp。

1 EXP的参数说明

在命令行中输入exp help=y或者exp -help即可显示exp命令的参数。
[oracle@book ~]$ exp -help

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 19:39:58 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Export terminated successfully without warnings.
在这里我们可以看到参数的说明和默认值。
有几个参数比较重要
USERID 必须要有这个参数,但是可以不用输入USERID。  exp userid=test/test  ... 或者  exp test/oracle... 都可以
File  指定了导出文件的存储位置和存储文件名
Rows 指定导出时,是否包括表中的数据行,如果设置成N,则只导出表结构。

Compress
Compress参数不是设置对导出的内容进行压缩,而是指示exp如何控制create table中的storage语句。当compress设置为Y时,storage语句将包含一个和当前对象所占extent总和相当的一个初始extent(这个初始extent可能很大),这样在导入时所有数据将都在初始extent中。建议设置成N。
Filesize
在某些平台下文件的大小是有限制的,当导出的对象较大时就需要使用这个参数来指定文件的最大值。它必须配合FILE参数进行使用,file参数指定多个文件,当文件的值达到filesize时,exp自动转到下一个文件继续写。如
exp userid=test/test file=f1,f2,f3,f4,f5,。。。 filesize=2G
这样将创建f1.dmp, f2.dmp等一系列文件,每个大小都为2G,如果导出的总量小于10G,EXP不必创建f5.bmp。
Query
Exp通过该参数提供导出表中一部分数据的功能。利用query可以进行一些特殊操作,例如当我们需要导出一个非分区的大表时,可以使用query=where_clause 人为的将表分成几个不重叠部分进行并行导出,这样可以加快导出速度,再结合file,filesize还可以边exp边imp。
exp userid=jiang/oracle file=aa.dmp tables=aa QUERY=\"where rownum\<10\"
Direct
Direct指示exp使用常规路径导出(N)还是直接路径导出(Y)。当设置为Y它是一个很有用的提速参数,它带来的速度提升是非常惊人的。在一般的情况下都应该设置为Y,但是很遗憾,其默认值为N(在10g 的data pump中这个情况已经改变)
exp userid=test/test file=db_str.dmp tables=big_table Direct=Y

RECORDLENGTH
当使用直接路径导出时,buffer参数就失效了,取而代之的是RecordLenght参数。这个参数决定exp等待缓冲数据达到多少时才写文件。如果参数设置太小将导致exp过于频繁的写文件因而性能下降。

Parfile
我们在进行exp、imp时可以将所有的参数都在命令行中进行输入,但是想想隔三差五的就要将这些命令输一遍是件多么枯燥而又容易出错的事情。因此,可以将所有的参数都写在一个参数文件中,每次只要指定这个文件就行了。
exp test/test parfile=exp.par


2 使用不带参数的EXP命令执行备份

如果只在命令行输入EXP不加任何参数,系统会以交互式的方式来执行导出。

[oracle@book ~]$ exp

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 20:10:49 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: expdat.dmp >

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >   

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
User to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

直接输入EXP,不加任何参数,将以交互式方式导出数据,输入用户名和密码并被数据库验证正确以后,则提示连接至数据库,然后是一系列的参数选择。

Enter array fetch buffer size: 4096 >

首先是缓冲区的大小,默认是4K,如果用户包含LOB,CLOB之类的对象,则至少设置为2M

Export file: expdat.dmp >

默认的导出文件名是expdat.dmp,文件在当前路径下面。可以直行指定文件名,可包含路径。

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

选择要导出的模式,可以全库导出,也可按用户导出或者按表导出。如果要全库导出,需要有EXP_FULL_DATABASE权限。这里的提示也从侧面说明,在执行EXP导出的时候,FULL USERS TABLES这三个参数不可同时出现。

Export grants (yes/no): yes >

选择是否导出权限,即是否导出对表、视图、序列或角色的授权。默认是导出

Export table data (yes/no): yes >

是否导出数据,默认导出数据。如果选择NO,则只导出表结构。

Compress extents (yes/no): yes >

选择是否压缩

3 使用EXP命令导出全库

要导出全库的,需要指定参数full=y,并且使用的用户要有EXP_FULL_DATABASE权限。

[oracle@book ~]$ exp system/kingstar full=y file='/home/oracle/full_exp.dmp' log='/home/oracle/full_exp.log';

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 20:32:16 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
。。。。。

后面内容省略

如果执行导出的用户没有EXP_FULL_DATABASE权限则会出现如下情况

[oracle@book ~]$ exp test/test full=y file=full_test.dmp log=full_test.log

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 20:38:11 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
(2)U(sers), or (3)T(ables): (2)U >

我们执行如下操作,给test用户赋予exp_full_database权限

SQL> grant exp_full_database to test;

Grant succeeded.
检验一下

SQL> select GRANTEE,GRANTED_ROLE from dba_role_privs where  GRANTEE='TEST';

GRANTEE                        GRANTED_ROLE
------------------------------ ------------------------------
TEST                           RESOURCE
TEST                           CONNECT
TEST                           EXP_FULL_DATABASE
再次执行上面的语句,就好了。

[oracle@book ~]$ exp test/test full=y file=full_test.dmp log=full_test.log

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 20:49:23 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

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
。。。。。。反馈内容省略

4 使用EXP命令导出某个用户

如果不需要导出整个数据库,而是导出当前数据库中某一个用户的所有数据库对象,作为该用户的备份,则应该使用OWNER参数。

[oracle@book ~]$ exp system/kingstar file=exp02.dmp owner=test;

Export: Release 10.2.0.4.0 - Production on Fri Feb 8 21:57:19 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                             T1          1 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.

5 使用EXP命令导出指定的表

导出特定用户的特定表,需要指定TABLES参数,该参数后面可以指定多个表名,表明之间用半角逗号分隔。如果要导出的表不是当前用户的表,需要使用schema_name.table_name的形式指定表名。

[oracle@book ~]$ exp system/kingstar file=exp03.dmp tables=test.t1 log=exp03.log

Export: Release 10.2.0.4.0 - Production on Fri Feb 8 22:00:13 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table                             T1          2 rows exported
Export terminated successfully without warnings.

如果需要导出有相似特征的一些列表,可以使用模糊指定的方法。

[oracle@book ~]$ exp test/test tables=t% file=table_like.dmp log=table_like.log

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 21:56:11 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                             T1         60 rows exported
. . exporting table                             T2         21 rows exported
Export terminated successfully with warnings.

6 使用EXP命令备份指定的记录

使用query参数,可以对返回表中的记录做限制。在Linux中,需要加上结合转义字符。在WINDOWS平台则不需要

在Linux平台下面:

[oracle@book ~]$ exp system/kingstar file=exp_table.dmp tables=test.t1 query=\'where object_id=35\';

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 21:16:19 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table                             T1          2 rows exported
Export terminated successfully without warnings.
在WINDOWS平台下面:

C:\Users\zhuxg221>exp system/kingstar@HEADDB_10G file=C:\exp_table.dmp tables=test.t1 query='where object_id=35'

Export: Release 10.2.0.3.0 - Production on 星期日 5月 12 12:25:34 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 WE8ISO8859P1 字符集 (可能的字符集转换)

即将导出指定的表通过常规路径...
当前的用户已更改为 TEST
. . 正在导出表                              T1导出了           2 行
成功终止导出, 没有出现警告。

7 使用EXP命令备份指定的表空间

可以使用tablespaces指定要导出的表空间,如果需要导出多个表空间,可以使用半角的逗号分隔。

[oracle@book ~]$ exp system/kingstar tablespaces=users,ts_test file=tbs_exp.dmp log=tbs_exp.log

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 21:39:17 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export selected tablespaces ...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                          BONUS          0 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       SALGRADE          5 rows exported
For tablespace TS_TEST ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                             T1         60 rows exported
. . exporting table                             T2         21 rows exported
. . exporting table                             T1          2 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully with warnings.

8 在EXP命令中使用参数文件

使用参数文件有以下好处:1.指定了参数文件以后,这些设置可以重复使用;2. 在参数文件中可以省去前面遇到的需要转义字符的情况

[oracle@book ~]$ vi my_parfile
userid=system/kingstar
tables=test.t1
grants=y
file=parfile_exp.dmp
query="where object_id=35"

[oracle@book ~]$ exp parfile=my_parfile

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 22:50:37 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table                             T1          2 rows exported
Export terminated successfully with warnings.

使用参数文件会遇到这样的情况,万一在参数文件中指定了一个参数的值,命令行中也指定了一个参数的值,到底以哪一个为准呢?

前面我们在文件中指定了生成的文件名是parfile_exp.dmp,我们在命令行中再给它指定一个文件名。

首先看目录的文件情况

[oracle@book ~]$ ls
backup  con_object.ctl  Desktop  flash_recover_area  mydump  my_parfile  object.bad  object.csv  patch  practice  rman_script  test_csv.csv

[oracle@book ~]$ exp parfile=my_parfile file=parfile_exp2.dmp

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 22:56:54 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table                             T1          2 rows exported
Export terminated successfully with warnings.
[oracle@book ~]$ ls
backup  con_object.ctl  Desktop  flash_recover_area  mydump  my_parfile  object.bad  object.csv parfile_exp2.dmp  patch  practice  rman_script  test_csv.csv
[oracle@book ~]$ rm *.dmp
[oracle@book ~]$ ls
backup  con_object.ctl  Desktop  flash_recover_area  mydump  my_parfile  object.bad  object.csv  patch  practice  rman_script  test_csv.csv
[oracle@book ~]$ exp file=parfile_exp2.dmp  parfile=my_parfile

Export: Release 10.2.0.4.0 - Production on Sat Feb 9 22:59:03 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TEST
. . exporting table                             T1          2 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@book ~]$ ls
backup  con_object.ctl  Desktop  flash_recover_area  mydump  my_parfile  object.bad  object.csv parfile_exp.dmp  patch  practice  rman_script  test_csv.csv

由此可以发现,如果使用了参数文件来指定一个参数,而在命令行也指定此参数的时候,以哪一个为准,要看先后顺序,以后面出现的那个指定为准。










Linux下Oracle命令

阅读数 519

linux 常用命令

阅读数 22

没有更多推荐了,返回首页