如何用sys as sysdba权限连接数据库进行EXP/IMP
来源:互联网 发布:2017百万公众网络答案 编辑:程序博客网 时间:2024/05/13 05:17
使用sys as sysdba权限进行EXP/IMP与其它用户稍有不同,详细内容如下(摘自metalink)Applies to:Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.0Oracle Server - Personal Edition - Version: 8.1.7.0 to 10.2.0.0Oracle Server - Standard Edition - Version: 8.1.7.0 to 10.2.0.0Information in this document applies to any platform.GoalThis document demonstrates how to connect AS SYSDBA when starting an export or import.Incorrect usage of single or double quotes can result in errors such as:LRM-00108: invalid positional parameter value 'as'EXP-00019: failed to process parameters, type 'EXP HELP=Y' for helpEXP-00000: Export terminated unsuccessfullyOr:LRM-00108: invalid positional parameter value 'sysdba'Or:LRM-00108: Message 108 not found; No message file for product=ORACORE, facility=LRMSolutionSYSDBA is used internally in the Oracle database and has specialized functions. Its behavior is not the same as for generalized users. For example, the SYS user cannot do a transaction level consisent read (read-only transaction). Queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY. Therefore export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and FLASHBACK_TIME cannot be used.Starting with Oracle10g, the export shows a warning that the export is not consistent when the export is started with CONSISTENT=Y and connects to the database with the user SYS (or as SYSDBA): EXP-00105: parameter CONSISTENT is not supported for this userNote that Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). For export this means that the export of table data is consistent. However, if a table contains nested tables, the outer table and each inner table are exported as separate transactions. And if a table is partitioned, each partition is exported as a separate transaction. If a nested table or a partitioned table was updated during the export, the data that is exported while connected as the SYS schema could be inconsistent.Typically, there is no need to invoke Export or Import as SYSDBA, except in the following situations:- at the request of Oracle technical support;- when exporting a transportable tablespace set with the old-style export utility (Oracle9i and Oracle8i);- when importing a transportable tablespace set with the old-style import utility (Oracle10g, Oracle9i, and Oracle8i).The examples below are based on:- the export of table emp, owned by the demo schema scott.- schema SYS with the password: change_on_install.- alias 'instance' that is specified in the tnsnames.ora file and used for a connect to the database.To invoke Export or Import as SYSDBA, use the following syntax (this syntax is similar when invoking import and the syntax has not changed with the new Oracle10g Export DataPump and Import DataPump utilities):1. Command line.Enclose the connect string with a single quote character:Windows:exp 'sys/change_on_install@instance as sysdba' tables=scott.empUnix (you need to 'escape' the single quote):exp \'sys/change_on_install@instance as sysdba\' tables=scott.empVMS (use [double_quote][single_quote]...[single_quote][double_quote]):exp "'sys/change_on_install@instance as sysdba'" tables=scott.empNote that this VMS syntax is also a valid syntax on Unix and on Windows.2. InteractiveDo not specify any connect string on the command line, so you will be prompted to enter it. E.g.:% exp tables=scott.empExport: Release 10.1.0.2.0 - Production on Fri Jun 25 07:39:46 2004Copyright (c) 1982, 2004, Oracle. All rights reserved.Username: sys/change_on_install@instance as sysdbaConnected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production... etc.3. Parameter file.You can also specify the username in the parameter file. In this situation, you have to enclose the connect string with a double quote character. However, to prevent possible security breaches we advice you to stop using the USERID parameter in a parameter file.Contents of file exp.par:USERID="sys/change_on_install@instance as sysdba"TABLES=scott.empRun export with:exp parfile=exp.parRemarks:1. If you have setup operating system authentication, it is not necessary to specify the SYS schema name, and password. E.g: exp "'/@instance as sysdba'" tables=scott.emp2. In addition, if you have set the environment variable TWO_TASK (on Unix) or LOCAL (on Windows) or on the server where the database is installed you have set ORACLE_HOME and ORACLE_SID, it is not necessary to specify the @instance. E.g: exp "'/ as sysdba'" tables=scott.emp3. The export parameters FLASHBACK_SCN and FLASHBACK_TIME cannot be used if the user that invoked the export is connected AS SYSDBA.4. Known issues:Bug 1616035 "EXPORT FAILED WITH ORA-1031 WHEN LOGIN AS SYSDBA" (not a public bug; fixed in 8.1.7.3 and higher)Bug 2936288 "ORA-1925 OCCURS WHEN IMPORTING AS SYS ACCOUNT"Bug 2996947 "EXP DID NOT RAISE ERROR WHEN SYSDBA EXPORTS WITH CONSISTENT=Y" (not a public bug; fixed in Oracle10g and higher)ReferencesBug 2936288 - Ora-1925 Occurs When Importing As Sys AccountNote 112269.1 - How to set Unix env. variable TWO_TASK and Windows NT counterpart, LOCALNote 130332.1 - Export / Import Connecting "AS SYSDBA" Fails with LRM-00108 and EXP-00019Note 204334.1 - Parameters FLASHBACK_SCN And FLASHBACK_TIME: Point In Time ExportNote 228482.1 - Schema's CTXSYS, MDSYS and ORDSYS are Not ExportedNote 277606.1 - How to Prevent EXP-00079 or EXP-00080 Warning (Data in Table xxx is Protected) During Export
0 0
- 如何用sys as sysdba权限连接数据库进行Exp/Imp
- 如何用sys as sysdba权限连接数据库进行Exp/Imp
- 如何用sys as sysdba权限连接数据库进行Exp/Imp
- 如何用sys as sysdba权限连接数据库进行EXP/IMP
- exp/imp as sysdba
- IMP-00058: 遇到 ORACLE 错误 28009 ORA-28009: connection as SYS should be as sysdba or sysora
- 如何使用SYSDBA执行EXP/IMP?
- 如何用spring连接数据库
- Sys和system用户的区别,as normal、sysdba 和sysoper系统权限区别
- Sys和system用户的区别,as normal、sysdba 和sysoper系统权限区别
- Sys和system用户的区别,as normal、sysdba 和sysoper系统权限区别
- 数据库总结--exp,imp
- exp/imp数据库迁移
- 使用imp/exp进行ORACLE数据库备份与恢复
- 在用scott用户导出dmp文件后,用sys用户执行IMP语句出现ORA-28009 connection to sys should be as sysdba or sysoper错误
- exp、imp命令连接Oracle数据库报错
- 数据库——Oracle中sys和system用户、sysdba 和sysoper系统权限、sysdba和dba角色的区别
- scott、sys、system用户 sysdba、sysoper权限
- MySQL管理员珍藏:十大必备工具盘点
- 查看linux命令的源码
- hdu1050
- Android BroadcastReceiver广播详解
- Java SE Java EE和Java ME的区别
- 如何用sys as sysdba权限连接数据库进行EXP/IMP
- 使用Git上传本地项目到http://git.oschina.net
- 最近看的一些关于数据结构和C++的面试知识点总结
- ArrayList的使用方法【转载】
- CornerStone配置SVN,HTTP及SVN简单使用说明
- android应用程序如何调用支付宝接口
- ORACLE与SQL SERVER的区别
- Ubuntu中QT打开Makefile文件,无法高亮显示问题
- 友元函数