IMP/EXP之使用AS SYSDBA导出系统表

    昨天尝试导出几个对象,中间波折不断,今天在大师的指导下迅速完成。在感叹大师技艺高超的同时,总结下过去12小时内出现的失误。

    

出于需求,需要导出dba_tables,dba_views等几个视图的数据,我写了以下语句试图导出:

[[email protected] test]$ exp / tables=(dba_views) file=test.dmp log=test.log

bash: syntax error near unexpected token `(‘

 

系统提示错误,不应该有'(‘。好,我们去掉'()’再试一遍:

[[email protected] test]$ exp / tables=dba_views file=test.dmp log=test.log

 

Export: Release 11.2.0.1.0 – Production on Mon Feb 21 19:48:04 2011

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

EXP-00056: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon denied

Username:

 

好,第二个问题不请自到了,系统提示我用户名和密码无效,是不是由于没有加as sysdba导致的呢?改动一下语句再次尝试:

[[email protected] test]$ exp / as sysdba tables=dba_views file=test.dmp log=test.log

LRM-00108: invalid positional parameter value ‘as’

EXP-00019: failed to process parameters, type ‘EXP HELP=Y’ for help

EXP-00000: Export terminated unsuccessfully

 

系统又开口了:”小伙,你这个as放的位置不正点啊”。通过查询,发现ITPUB上有相关解决方案,指出用”/ as sysdba”即可解决:

[[email protected] test]$ exp “/ as sysdba” tables=dba_views file=test.dmp log=test.log

LRM-00108: invalid positional parameter value ‘as’

EXP-00019: failed to process parameters, type ‘EXP HELP=Y’ for help

EXP-00000: Export terminated unsuccessfully

 

提示的错误跟之前一样,看来单纯的加双引号已经不能解决问题。不过ITPUB帖子里提示的Interactively Invoking Export as SYSDBA起到了相当大的帮助,根据这个关键字查询到了Oracle官方文档,并查找到以下部分:

Invoking Export As SYSDBA

SYSDBA is used internally and has specialized functions; its behavior is not the same as for generalized users. Therefore, you should not typically need to invoke Export as SYSDBA, except in the following situations:

At the request of Oracle technical support

When using transportable tablespaces (see Transportable Tablespaces)

To invoke Export as SYSDBA, use the following syntax, adding any desired parameters or parameter filenames:

exp \’username/password AS SYSDBA\’


 

Optionally, you could also specify an instance name:

exp \’username/[email protected] AS SYSDBA\’


 

If either the username or password is omitted, Export will prompt you for it.

This example shows the entire connect string enclosed in single quotation marks and backslashes. This is because the string, AS SYSDBA, contains a blank, a situation for which most operating systems require that the entire connect string be placed in quotation marks or marked as a literal by some method. Some operating systems also require that quotation marks on the command line be preceded by an escape character. In this example, backslashes are used as the escape character. If the backslashes were not present, the command-line parser that Export uses would not understand the quotation marks and would remove them before calling Export.

See your Oracle operating system-specific documentation for more information about special and reserved characters on your system.

If you prefer to use the Export interactive mode, see Using the Interactive Method for more information.

仔细阅读官方文档,发现每一句都非常有帮助,我从中获取了以下信息:

  1. 通常不使用AS SYSDBA导出,除非:OTS要求;当用到传输表空间(transportable tablespaces)时。
  2. 调用Export as SYSDBA需要使用转义字符\,我输入exp \’/ AS SYSDBA\’语句,成功以sysdba身份进入到交互导出界面

    [[email protected] test]$ exp \’/ AS SYSDBA\’

    Export: Release 11.2.0.1.0 – Production on Mon Feb 21 20:04:49 2011

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

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

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

    Enter array fetch buffer size: 4096 >

  3. 为什么用到转义字符\就可以了呢?因为AS SYSDBA这个字符串中间包含空格,用单引号将字符串整体括起,用\标识单引号。这个地方单引号换成双引号依然可以。如果不加\,系统也就不认识单引号,就会出问题。

 

解决了使用sysdba身份的问题,试试改过的语句成功与否:

[[email protected] test]$ exp \’/ as sysdba\’ tables=dba_views file=test.dmp log=test.log

 

Export: Release 11.2.0.1.0 – Production on Mon Feb 21 20:23:48 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 AL32UTF8 character set (possible charset conversion)

 

About to export specified tables via Conventional Path …

EXP-00011: SYS.DBA_VIEWS does not exist

Export terminated successfully with warnings.

 

系统再次提示,SYS.DBA_VIEWS不存在。怎么会不存在呢?这一段没头绪的思考花费了很多时间,但是究其原因,特别容易理解:不管是dba_views也好,dba_tables也罢,这些对象是视图,它们在系统只保存定义,不保存数据。所以需要导出他们的基表。查询dba_views的定义:

SQL>select * from dba_views

where view_name = upper(‘dba_views’);

 

select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext,

t.oidtextlength, t.oidtext, t.typeowner, t.typename,

decode(bitand(v.property, 134217728), 134217728,

(select sv.name from superobj$ h, “_CURRENT_EDITION_OBJ” sv

where h.subobj# = o.obj# and h.superobj# = sv.obj#), null),

decode(bitand(v.property, 32), 32, ‘Y’, ‘N’),

decode(bitand(v.property, 16384), 16384, ‘Y’, ‘N’)

from sys.”_CURRENT_EDITION_OBJ” o, sys.view$ v, sys.user$ u, sys.typed_view$ t

where o.obj# = v.obj#

and o.obj# = t.obj#(+)

and o.owner# = u.user#

上面黑体数据位dba_views查询的基表。应该把他们导出来,再改语句:

[[email protected] test]$ exp \’/ as sysdba\’ tables=col$ file=test.dmp log=test.log

 

Export: Release 11.2.0.1.0 – Production on Mon Feb 21 20:35:58 2011

 

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 AL32UTF8 character set (possible charset conversion)

 

About to export specified tables via Conventional Path …

. . exporting table COL$ 108412 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

导出成功!

这个地方需要注意下,如果把\换成双引号,同样可以成功导出,如:

exp “‘/ as sysdba'” tables=col$ file=test.dmp log=test.log 注意双引号位置与\原来位置的区别。

考虑到对转义字符了解还不深入,暂时无法解释为什么用双引号也可以解决,猜想是因为与登陆9i OracleDB时,使用sqlplus “/ as sysdba”以保持安全性相关:

在Oracle9i中取消了对内部登陆的支持之后,全世界许多DBA表示反对:他们应道如何在命令行上输入SYS的口令并保持安全性?嗯,答案是在操作系统提示符中使用引号:

Sqlplus “/ as sysdba”

 

 

总结:导入过程中碰到两个问题:以SYSDBA身份导出,书写格式应如何;导出对象为系统视图,应如何解决。解决问题过程中得到了论坛和qq群中的热情帮助,但最终还是在大师的指点下完成。解决完成后发现这两个问题完全可以自己搞定,可见遇到问题后冷静思考能力和解决问题能力还有待加强

 

参考:

ITPUB《关于exp username/[email protected] AS SYSDBA 的用法》 http://www.itpub.net/thread-148074-1-1.html

Oracle Export 官方文档http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1004864

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress