sys不能远程登录的问题-Oracle 数据库导入导出命令-演示确定共享池中是否存在未绑定变量的 SQL

当前位置: 数据库>oracle

本页文章导读:

▪sys不能远程登录的问题环境：在数据服务器A（Red Hat Enterprise Linux Server release 5.5 (Tikanga)）上装有数据库ORACLE 10g，但是我们现在从机器B上用DBA账号远程连接数据库时，登录不了。如下所示：问题一：SYSDBA不能远程登.........

▪Oracle 数据库导入导出命令 Oracle数据导入导出imp/exp 功能：Oracle数据导入导出imp/exp就相当与oracle数据还原与备份。大多情况都可以用Oracle数据导入导出完成数据的备份和还原（不会造成数据的丢失）。 Orac.........

▪演示确定共享池中是否存在未绑定变量的 SQL 本文内容 remove_constants 函数测试环境测试共享池中是否存在绑定变量的 SQL remove_constants 函数确定系统中是否存在绑定变量的情况，ASKTOM 网站提供了一个 remove_constants 函.........

[1]sys不能远程登录的问题

来源: 发布时间: 2013-10-29

环境：在数据服务器A（Red Hat Enterprise Linux Server release 5.5 (Tikanga)）上装有数据库ORACLE 10g，但是我们现在从机器B上用DBA账号远程连接数据库时，登录不了。如下所示：

问题一：SYSDBA不能远程登录（此处是不是指CRT远程登录）

SYSDBA不能远程登录分两种情况：

1：提示的是ORA-01017:用户名密码出错

C:\Users\kerry>sqlplus "sys/wgods123@gsp as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 1月 11 11:08:07 2013

ERROR:

ORA-01017: invalid username/password; logon denied

这样的错误，明明密码和用户名明明输入的都是正确的。这又是为什么叻，出现这种情况，一般是系统参数REMOTE_LOGIN_PASSWORD的设置问题：PFILE文件中有个参数REMOTE_LOGIN_PASSWORD 对passwordfile进行控制：

remote_login_passwordfile =NONE时，停用口令文件验证，Oracle数据库不允许远程SYSDBA/SYSOPER身份登录，无法通过远程进行数据库起停等操作管理；

remote_login_passwordfile =exclusive时，启用口令文件，允许远程登录；

remote_login_passwordfile =shared 时，多个数据库可以共享一个口令文件,但是只可以识别一个用户:SYS；

当口令文件丢失时，用/NOLOG方式登录，通过orapwd重建口令文件来解决。如果口令文件丢失,可以使用orapwd可以重建口令文件。

这里需要启动密码文件的话，需要前提就是这个值，这里的值可以NONE，EXCLUSIVE和SHARE。EXCLUSIVE和SHARE都可以，EXCLUSIVE是独占的文件，SHARE是多实例的体系中的共享模式。这个问题基本上就是这个值导致的

SQL>alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

然后重启服务就可远程以sysdba登录。

但是还有一种情况如下：

检查一下系统参数REMOTE_LOGIN_PASSWORD的值，发现是EXCLUSIVE。而且密码文件也存在，但是就是报上面错误。

SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORD

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

remote_login_passwordfile string EXCLUSIVE

最后发现：SYS账户的密码是错误的，重置SYS密码后

SQL> alter user sys identified by wgods123;

问题解决了。

2：ORA-01031: insufficient privileges

C:\Users\kerry>sqlplus "sys/wgods123@GSP as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 1月 11 14:37:11 2013

ERROR:

ORA-01031: insufficient privileges

这种情况一般是由于你的密码文件缺损或者不存在了,例如口令文件删除了，而Oracle实例对sysdba的权限是根据这个密码文件来判断的，如果你的用户被grant to sysdba了，那么这个用户的密码和用户名也在这个密码文件了。这样也就发现你不是sysdba的角色，当然as sysdba就会提示权限不足了哟。为什么这样做了，如果我们的数据库没有open，那么对于的一些动态视图是不能打开的，所以oracle必须把这些用户的口令和username放在一个独立的文件里，这样在数据库还不是open的状况写，有个地方判断了。

如何解决了。我们首先来判断一下

SQL> SELECT * FROM V$PWFILE_USERS;

USERNAME SYSDBA SYSOPER

------------------------------ ------ -------

如果这里选择不出了记录的话，就是密码文件没有叻。

这里解决的方法就是重建orapwd文件

使用密码文件orapwd 命令 [oracle@wgods dbs]$ orapwd file=/database/oracle/product/dbhome/dbs/orapwgsp entries=4 password=wgods123 force=y

再次查看 v$pwfile_users 这时候有一条记录了

本文链接

[2]Oracle 数据库导入导出命令

来源: 发布时间: 2013-10-29

Oracle数据导入导出imp/exp 功能：Oracle数据导入导出imp/exp就相当与oracle数据还原与备份。大多情况都可以用Oracle数据导入导出完成数据的备份和还原（不会造成数据的丢失）。 Oracle有个好处，虽然你的电脑不是服务器，但是你装了oracle客户端，并建立了连接（通过Net Configuration Assistant添加正确的服务命名，其实你可以想成是客户端与服务器端修了条路，然后数据就可以被拉过来了）这样你可以把数据导出到本地，虽然可能服务器离你很远。你同样可以把dmp文件从本地导入到远处的数据库服务器中。利用这个功能你可以构建俩个相同的数据库，一个用来测试，一个用来正式使用。执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行， DOS中可以执行时由于在oracle 8i 中安装目录\$ora10g\BIN被设置为全局路径，该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。 oracle用java编写，我想SQLPLUS.EXE、EXP.EXE、IMP.EXE这俩个文件是被包装后的类文件。 SQLPLUS.EXE调用EXP.EXE、IMP.EXE他们所包裹的类，完成导入导出功能。下面介绍的是导入导出的实例，向导入导出看实例基本上就可以完成，因为导入导出很简单。数据导出： 1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中 exp system/manager@TEST file=d:\daochu.dmp full=y 2 将数据库中system用户与sys用户的表导出 exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys) 3 将数据库中的表table1 、table2导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2) 4 将数据库中的表table1中的字段filed1以"00"打头的数据导出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\" 上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。不过在上面命令后面加上 compress=y 就可以了

数据的导入 1 将D:\daochu.dmp 中的数据导入 TEST数据库中。 imp system/manager@TEST file=d:\daochu.dmp 上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在后面加上 ignore=y 就可以了。 2 将d:\daochu.dmp中的表table1 导入 imp system/manager@TEST file=d:\daochu.dmp tables=(table1) 基本上上面的导入导出够用了。不少情况我是将表彻底删除，然后导入。注意：你要有足够的权限，权限不够它会提示你。数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上。

数据导出： exp hkb/hkb@boss_14 full=y file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbfull.log;

导出注意事项：导出的是当前用户的的数据，当前用户如果有DBA的权限，则导出所有数据！

同名用户之间的数据导入： imp hkb/hkb@xe file=c:\orabackup\hkbfull.dmp log=c:\orabackup\hkbimp.log full=y

不同名之间的数据导入： imp system/test@xe fromuser=hkb touser=hkb_new file=c:\orabackup\hkbfull.dmp

log=c:\orabackup\hkbimp.log;

本文链接

[3]演示确定共享池中是否存在未绑定变量的 SQL

来源: 发布时间: 2013-10-29

本文内容

remove_constants 函数
测试环境
测试共享池中是否存在绑定变量的 SQL

remove_constants 函数

确定系统中是否存在绑定变量的情况，ASKTOM 网站提供了一个 remove_constants 函数，以检查共享池中 SQL 的运行情况。

     SQL> CREATE OR REPLACE FUNCTION remove_constants (p_query IN VARCHAR2)      2     RETURN VARCHAR2      3  AS      4     l_query       LONG;      5     l_char        VARCHAR2 (2);      6     l_in_quotes   BOOLEAN DEFAULT FALSE;      7  BEGIN      8     FOR i IN 1 .. LENGTH (p_query)      9     LOOP     10        l_char := SUBSTR (p_query, i, 1);     11     12        IF l_char = '''' AND l_in_quotes     13        THEN     14           l_in_quotes := FALSE;     15        ELSIF l_char = '''' AND NOT l_in_quotes     16        THEN     17           l_in_quotes := TRUE;     18           l_query := l_query || '''#';     19        END IF;     20     21        IF NOT l_in_quotes     22        THEN     23           l_query := l_query || l_char;     24        END IF;     25     END LOOP;     26     27     l_query := TRANSLATE (l_query, '0123456789', '@@@@@@@@@@');     28     29     FOR i IN 0 .. 8     30     LOOP     31        l_query := REPLACE (l_query, LPAD ('@', 10 - i, '@'), '@');     32        l_query := REPLACE (l_query, LPAD (' ', 10 - i, ' '), ' ');     33     END LOOP;     34     35     RETURN UPPER (l_query);     36  END;     37  /         函数已创建。         SQL>

测试环境

创建表 t，并创建匿名存储过程，执行不绑定变量的 SQL 1000 次。

    SQL> create table t(id int);         表已创建。         SQL> BEGIN      2     FOR i IN 1 .. 1000      3     LOOP      4        EXECUTE IMMEDIATE 'select * from t where id=' || i;      5     END LOOP;      6  END;      7  /         PL/SQL 过程已成功完成。         SQL>

测试共享池中是否存在绑定变量的 SQL

利用 v$sqlarea 视图创建表 t1，并调用 remove_constants 进行整理。

    SQL> create table t1 as select sql_text from v$sqlarea;         表已创建。         SQL> alter table t1 add sql_text_wo_constants varchar2(1000);         表已更改。         SQL> UPDATE t1      2     SET sql_text_wo_constants = remove_constants (sql_text);         已更新3474行。         SQL>   SELECT sql_text_wo_constants, COUNT (*)      2      FROM t1      3  GROUP BY sql_text_wo_constants      4    HAVING COUNT (*) > 100      5  ORDER BY 2;         SQL_TEXT_WO_CONSTANTS                                COUNT(*)    -------------------------------------------------- ----------    SELECT * FROM T WHERE ID=@                               1000         SQL>

从结果中，刚才执行的 select 语句被反复分析 1000 次，其中谓词条件用 @ 代替。如果绑定了变量，顶多一次。这样，通过这个函数，可以很容易地找到共享池中哪些 SQL 没有绑定变量。

下载 remove_constants 函数

本文链接