exp & imp 的一些小心得
来源:互联网 发布:淘宝闪电分期商户 编辑:程序博客网 时间:2024/05/21 14:02
今天试着用exp&imp备份及恢复文件。
具体用法我就不多说了,我只是想说一下,imp时的权限问题。
先用exp备份
SQL> ho exp cyco/hummer2008
Export: Release 10.2.0.1.0 - Production on Thu Mar 6 23:01:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp//CSDN居然把/解释成了转义符
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > users
. . exporting table USERS 2 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
Export: Release 10.2.0.1.0 - Production on Thu Mar 6 23:01:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp//CSDN居然把/解释成了转义符
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > users
. . exporting table USERS 2 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
再执行
SQL> ho imp cyco/hummer2008
Import: Release 10.2.0.1.0 - Production on Thu Mar 6 21:55:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Import file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: cyco
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: users
Enter table(T) or partition(T:P) name or . if done:
. importing CYCO's objects into CYCO
. importing CYCO's objects into CYCO
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE TABLE "USERS" ("USERNAME" VARCHAR2(10) NOT NULL ENABLE) PCTFREE 10 "
"PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI"
"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ALASKA" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Import terminated successfully with warnings.
Import: Release 10.2.0.1.0 - Production on Thu Mar 6 21:55:39 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Import file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: cyco
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: users
Enter table(T) or partition(T:P) name or . if done:
. importing CYCO's objects into CYCO
. importing CYCO's objects into CYCO
IMP-00017: following statement failed with ORACLE error 1031:
"CREATE TABLE "USERS" ("USERNAME" VARCHAR2(10) NOT NULL ENABLE) PCTFREE 10 "
"PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI"
"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "ALASKA" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Import terminated successfully with warnings.
从ORA-01031: insufficient privileges看出是没有权限,怎么会对自己份备的表没有权限呢?(这个问题想问下大家的!!!明明导出时选好了grant >yes)
ORA-01031: insufficient privileges
接着用sys用户来备份,代码如下:
SQL> ho imp 'sys/hummer2008 as sysdba'
Import: Release 10.2.0.1.0 - Production on Thu Mar 6 21:58:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Import file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CYCO, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: cyco
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: users
Enter table(T) or partition(T:P) name or . if done:
. importing CYCO's objects into SYS
. importing CYCO's objects into SYS
. . importing table "USERS" 2 rows imported
Import terminated successfully without warnings.
Import: Release 10.2.0.1.0 - Production on Thu Mar 6 21:58:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engin
e options
Import file: EXPDAT.DMP > H:/Databases/oracle/Backup/init_cyco.dmp
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CYCO, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: cyco
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: users
Enter table(T) or partition(T:P) name or . if done:
. importing CYCO's objects into SYS
. importing CYCO's objects into SYS
. . importing table "USERS" 2 rows imported
Import terminated successfully without warnings.
看来成功了,
SQL> select * from users;
no rows selected
怎么会没有记录呢?
细看上面的运行记录,importing CYCO's objects into SYS 原来,导入到sys用户下了。原来默认的touser参数就是运行imp的用户名,本例中是sys。
建议大家运行时用一整条命令的方法,最好以sys的身份运行,以免受错。
imp 'sys/hummer2008 as sysdba' fromuser=cyco touser=cyco rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=y file=H:/Databases/oracle/Backup/init_cyco.dmp log=H:/Databases/oracle/Backup/init_cyco.log tables=users
还有,网上找的一些命令中的 volsize=0 这个参数是不能用的,否则会提示
IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully
- exp & imp 的一些小心得
- 编程的一些小心得
- dede的一些小心得
- 专题实验 EXP & IMP
- java工作的一些小心得!
- ant打包的一些小心得
- 关于ScrollerView的一些小心得
- jsp中文乱码的一些小心得
- 关于MVC的一些小心得
- iOS APP开发的一些小心得
- 一些关于Python的小心得
- 安装软件包的一些小心得
- 【RequestContext】关于RequestContext的一些小心得;
- 一些小心得
- LiteIDE一些小心得
- exp/imp的Bug??
- imp/exp的使用方法
- imp exp的使用
- 客户端的JS
- 如何成为一名游戏开发程序员
- 深入探讨MFC消息循环和消息泵
- ASP.NET验证码实例
- cdp命令
- exp & imp 的一些小心得
- XNA发布,国内几无问津【转】
- Lisp之根源(转)
- 开发和使用自定义服务器控件
- MFC DLL向导
- (zz)C语言指针详解
- .NET 开发人员应该下载的十个必备工具
- 很辛苦但也很充实
- 打开一些特殊的系统窗口(如控制面板等)