Hey!! Sky!

Mar 29, 2006

[下载]通过分析SQL语句的执行计划优化SQL

SunnyXu 的最新大作《通过分析SQL语句的执行计划优化SQL》，喜欢这类总结性的文章，可以从中获得很多知识，SunnyXu 应该也是在 itpub 中一步步成长起来的，仔细看一下先，嘿嘿，努力努力！

PDF 版下载

 

Posted by Sky at 05:31 PM | Permalink | comments(0) | Database

与 Partitioned Table 的第一次不完全接触

    刚才逛 CNOUG 的时候看到有人问关于分区表的问题，RudolfLu 非常白话的解释了问题，不过还是不是非常明白，正好今天看到 Managing Partitioned Tables and Indexes，还没有看完，先做了些简单的实验。


    创建一个简单的 HASH 分区表：



SQL> ed
Wrote file afiedt.buf


  1  CREATE TABLE scubagear
  2       (id NUMBER,
  3        name VARCHAR2 (60))
  4     PARTITION BY HASH (id)
  5      ( PARTITION id1
  6          TABLESPACE CWMLITE,
  7        PARTITION id2
  8          TABLESPACE TOOLS,
  9        PARTITION id3
 10          TABLESPACE EXAMPLE,
 11        PARTITION id4
 12*         TABLESPACE USERS )
SQL> /


Table created. 


Oracle Concepts 上说，分区表的每个分区都是一个 segment，这个可以从下面的操作看出来，不过他们的 SEGMENT_NAME 都是分区表的名字，而 PARTITION_NAME 才是分区的名字，如果用分区名作为 SEGMENT_NAME 来查询是没有结果的：



SQL> select segment_name,PARTITION_name,tablespace_name,bytes
  2  from user_segments
  3  where segment_name='SCUBAGEAR';


SEGMENT_NAME    PARTITION_NAME  TABLESPACE_NAME      BYTES
--------------- --------------- --------------- ----------
SCUBAGEAR       ID1             CWMLITE              65536
SCUBAGEAR       ID2             TOOLS                65536
SCUBAGEAR       ID3             EXAMPLE              65536
SCUBAGEAR       ID4             USERS                65536


SQL> ed
Wrote file afiedt.buf


  1  select segment_name,PARTITION_name,tablespace_name,bytes
  2  from user_segments
  3* where segment_name='ID1'
SQL> /


no rows selected 


可见，其实分区表只是几个分区组合在一起的一个总称，他本身并不占用空间，可以说不是 segment，但表明依然作为各分区的 segment_name。这也可以从查询 user_extents 看出：



SQL> select segment_name,PARTITION_NAME,SEGMENT_TYPE,EXTENT_ID,BLOCKS
  2  from user_extents
  3  where segment_name='SCUBAGEAR';


SEGMENT_NAME    PARTITION_NAME  SEGMENT_TYPE        EXTENT_ID     BLOCKS
--------------- --------------- ------------------ ---------- ----------
SCUBAGEAR       ID1             TABLE PARTITION             0          8
SCUBAGEAR       ID2             TABLE PARTITION             0          8
SCUBAGEAR       ID3             TABLE PARTITION             0          8
SCUBAGEAR       ID4             TABLE PARTITION             0          8


SQL> ed
Wrote file afiedt.buf


  1  select segment_name,PARTITION_NAME,SEGMENT_TYPE,EXTENT_ID,BLOCKS
  2  from user_extents
  3* where segment_name='ID1'
SQL> /


no rows selected


暂时研究了这些，呵呵，等看完了再上来补充。

Posted by Sky at 04:40 PM | Permalink | comments(0) | Database

Mar 23, 2006

关于 Oracle 的 License

    Oracle 初始参数中有 3 个关于 License 的设置：



SQL> show parameter license


NAME                                 TYPE       VALUE
------------------------------------ ---------- --------
license_max_sessions                 integer    0
license_max_users                    integer    0
license_sessions_warning             integer    0


具体的含义：
    参数名：license_max_sessions
    类别：许可限制
    说明: 指定允许同时进行的并行用户会话的最大数量。达到该限制后, 只有具有 RESTRICTED SESSION 权限的用户才能连接到服务器。所有其他用户都会收到一条警告消息, 表明已达到系统最大容量的限制。
    值范围: 0 - 会话许可的数量。
    默认值: 0


    参数名：license_max_users
    类别：许可限制
    说明:指定您可在该数据库中创建的用户的最大数量。并行会话使用许可和用户使用许可不应被同时启用。LICENSE_MAX_SESSIONS 或 LICENSE_MAX_USERS 或这两者的值应为零。
    值范围: 0 - 用户许可的数量。
    默认值: 0


    参数名：license_sessions_warning
    类别：许可限制
    说明: 指定对并行用户会话数量的警告限制。达到该限制后, 其他用户仍然可以连接, 但一条消息将被写入 ALERT 文件。具有RESTRICTED SESSION 权限的用户将收到一条警告消息, 表明系统已接近最大容量。
    值范围: 0 - LICENSE_MAX_SESSIONS
    默认值: 0
以上参数如果是 0 表示没有限制。


    除了可以查看这些初始参数之外，还可以通过视图 v$license 来观察这些参数：



SQL> select * from v$license;


SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           0                0               53                 57          0


其中：
    SESSIONS_MAX、SESSIONS_WARNING、USERS_MAX 分别对应初始参数 license_max_sessions、license_sessions_warning、license_max_users 的数值；SESSIONS_CURRENT 表示已经连接的 SESSION 数量（注：该值并不等于 v$session 中查询出来的数量，似乎并不包括 Oracle 本身连接的 SESSION，但似乎还是有点出入，具体是怎样的对应关系，我还没有搞明白），SESSIONS_HIGHWATER 表示曾经达到过的最大 SESSION 数量。


简单做一下实验：



SQL> select username,terminal,program from v$session;

USERNAME                       TERMINAL         PROGRAM
------------------------------ ---------------- ---------------
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
                               WKS05404         ORACLE.EXE
SYS                            WKS05404         sqlplus.exe


10 rows selected.

SQL> select * from v$license;


SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           0                0                2                  2          0

（这里和上面的查询不知道怎么对应起来）
SQL> alter system set license_max_sessions=3;


System altered.


SQL> show parameter license_max_sessions


NAME                                 TYPE       VALUE
------------------------------------ ---------- --------
license_max_sessions                 integer    3
SQL> select * from v$license;


SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           3                0                2                  2          0


接下来新开一个窗口，连接另外一个用户：



C:\Documents and Settings\chent>sqlplus /nolog


SQL*Plus: Release 9.2.0.6.0 - Production on Thu Mar 23 10:51:04 2006


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


SQL> conn sky/forever
Connected.
SQL> select * from v$license;


SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           3                0                3                  3          0


再开一个用户试试看：



C:\Documents and Settings\chent>sqlplus /nolog


SQL*Plus: Release 9.2.0.6.0 - Production on Thu Mar 23 10:52:45 2006


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


SQL> conn vivian/forever
ERROR:
ORA-00019: maximum number of session licenses exceeded


可以看见，提示超出限制，无法连接了，不过，具有 RESTRICTED SESSION 权限的用户是可以连接的：



SQL> conn sky/forever
Connected.
SQL> select * from v$license;


SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           3                0                4                  4          0

可以看一下 alert 文件中的记录：

Thu Mar 23 10:47:30 2006
ALTER SYSTEM SET license_max_sessions=3 SCOPE=BOTH;
Thu Mar 23 10:52:51 2006
Non-DBA logon denied; current logons equal maximum (3)
License maximum (3) exceeded, DBA logon allowed

CP 说过 License 只有法律意义，没有技术意义，不知道具体指哪个值，是买了 30 个 License 就会在 SESSIONS_MAX 中显示出来么？还是其他什么，由于版本的原因，无法验证。


参考：
http://www.itpub.net/16145,1.html
http://oracle.chinaitlab.com/install/39632.html

Posted by Sky at 10:21 AM | Permalink | comments(0) | Database

Mar 22, 2006

如何确定 Undo Tablespace 的大小

    今天刚看到 Managing Undo Space，其中介绍了一个确定当前系统当前事务量下 Undo Tablespace 大小的公式：
 UndoSpace = UR * UPS + overhead
其中：
  UR = UNDO_RETENTION
  UPS = 每秒使用的 UNDO 块数量
  overhead = 一些冗余的数据块数量，比如：transaction tables, bitmaps 等等
算出来的是 Undo Tablespace 需要的 BLOCK 数量，再乘以 db_block_size 就是需要的字节数了。

    google 了一下，网上有关于该公式的具体使用方法。其中 UPS 可以通过 V$UNDOSTAT 计算的到，overhead 一般取 24。

    这样可以得到计算 Undo Tablespace 的 SQL ：


SELECT (ur * (ups * dbs)) + (dbs * 24) AS "Bytes"
  FROM (SELECT VALUE AS ur
          FROM v$parameter
         WHERE NAME = 'undo_retention'),
       (SELECT (SUM (undoblks) / SUM (((end_time - begin_time) * 86400))
               ) AS ups
          FROM v$undostat),
       (SELECT VALUE AS dbs
          FROM v$parameter
         WHERE NAME = 'db_block_size');

参考：
http://www.oracle.com.cn/viewthread.php?tid=51750

Posted by Sky at 03:41 PM | Permalink | comments(0) | Database

Mar 17, 2006

ORA-01994 的解决以及口令文件的相关测试

    今天下午，最美在群里寻求解决 ORA-01994 错误的方法，错误信息：

ORA-01994 GRANT failed: cannot add users to shared password file

    当无法给用户授予 sysdba 权限时，可能有两个原因，一是参数 REMOTE_LOGIN_PASSWORDFILE 没有设置成 exclusive，二是 sysdba 用户超过了口令文件参数的 entries。查了一下 Oralce Error Messages ，确定该错误是由 REMOTE_LOGIN_PASSWORDFILE 参数的设置引起的：


ORA-01994 GRANT failed: cannot add users to shared password file



Cause: A grant failed because a user could not be added to the password file. This is because the value of the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED.



Action: To add a user to the password file, shut down the database, change the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE, and restart the database.



1.REMOTE_LOGIN_PASSWORDFILE 参数设置的测试：



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


System altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area   80813176 bytes
Fixed Size                   453752 bytes
Variable Size              58720256 bytes
Database Buffers           20971520 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> grant sysdba to sky;
grant sysdba to sky
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file



    但据最美描述，当他查询 v$pwfile_users 时，是空的，可是当 REMOTE_LOGIN_PASSWORDFILE=SHARED 时，该视图不应该是空的：



SQL> show parameter REMOTE_LOGIN_PASSWORDFILE


NAME                                 TYPE    VALUE
------------------------------------ ------- --------
remote_login_passwordfile            string  SHARED
SQL> select * from v$pwfile_users;


USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE


    显然，最美的情况一定是将 REMOTE_LOGIN_PASSWORDFILE 设为 NONE 了，当设为 NONE 时是禁用使用口令文件验证的。



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


System altered.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area   80813176 bytes
Fixed Size                   453752 bytes
Variable Size              58720256 bytes
Database Buffers           20971520 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> show parameter REMOTE_LOGIN_PASSWORDFILE


NAME                                 TYPE    VALUE
------------------------------------ ------- --------------------------
remote_login_passwordfile            string  NONE
SQL> select * from v$pwfile_users;


no rows selected


SQL> grant sysdba to sky;
grant sysdba to sky
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file


    此时，我在本地再开一个命令行窗口用 sys 登陆试试看：



C:\Documents and Settings\Administrator>sqlplus /nolog


SQL*Plus: Release 9.2.0.3.0 - Production on Sat Mar 18 02:06:27 2006


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


SQL> conn sys/forever@sky as sysdba
Connected.
SQL> show user
USER is "SYS"


    其实此时并非用口令文件登陆的，而是用操作系统验证登陆的，在这种情况下，只要有 as sysdba，/ 两边输入任何字符都可以登陆，如果不输入 / 则会提示输入密码，但密码可以随便输入，不过 as sysdba 之前必须有字符，否则会出现 SP2-0306: Invalid option 错误，由于条件所限我没有做远程登陆测试，不过可以禁止本地的操作系统认证来试试看：



SQL> conn sys/foreve as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied



Warning: You are no longer connected to ORACLE.
SQL>


    无法登陆，这里其实不是非法的用户名/密码，而是无法通过口令文件认证。

    总结一下先：
    用 SYSDBA 登陆时，可以用口令文件验证和操作系统验证两种方式，如果要用操作系统验证，需要在 sqlnet.ora 中添加 SQLNET.AUTHENTICATION_SERVICES= (NTS)，此时，/ 左右可以任意输入字符，只要最后跟着 as sysdba 都可以作为 sysdba 登陆，当然也可以 / 左右没有字符，如果不输入 / ，随便输入任何字符 + as sysdba 会要求输入密码，但可以随便输入密码，在不加 / 时，as sydba 前必须有字符，否则会产生错误。如果用口令文件验证需要建立口令文件，并将参数 REMOTE_LOGIN_PASSWORDFILE 设为 EXCLUSIVE 或者 SHARED，如果设为 NONE，则无法通过口令文件认证，此外，如果想将 SYSDBA 权限授予其他用户必须保证 REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE，其他选项在授权时都会产生错误。另外，当在数据库本地登陆时，登陆选项中不加 @SID，就不需要使用监听器。


2.口令文件参数 entries 的测试：
    删掉口令文件，重建，并设置 entries 为 1：


C:\Documents and Settings\Administrator>orapwd file=E:\oracle\ora92\database\PWD
sky.ora password=forever entries=1

用 sys 登陆，并授权：



C:\Documents and Settings\Administrator>sqlplus /nolog


SQL*Plus: Release 9.2.0.3.0 - Production on Sat Mar 18 02:58:51 2006


Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


SQL> conn sys/forever as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.


Total System Global Area   80813176
bytes


Fixed Size                   453752
bytes


Variable Size              58720256
bytes


Database Buffers           20971520
bytes


Redo Buffers                 667648


bytes


Database mounted.
Database opened.

SQL> grant sysdba to sky;


Grant succeeded.


SQL> select * from v$pwfile_users;


USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SKY                            TRUE  FALSE

SQL> grant sysdba to system;


Grant succeeded.


SQL> select * from v$pwfile_users;


USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SKY                            TRUE  FALSE
SYSTEM                         TRUE  FALSE

SQL> conn sky/forever as sysdba
Connected.
SQL>


    可以看到，即使超出了 entries 的值依然可以授予 sysdba 权限，为什么呢，我依稀记得在哪里看到过超出 entries 的确可以再授权的，直到口令文件的大小达到 1 个 BLOCK（忘了是数据库数据块的大小，还是操作系统数据块的大小）的大小，具体的还要再查查资料，等以后再补充。


更多的介绍可以看看 Eygle 关于口令文件的介绍：
http://www.eygle.com/faq/passwordfile.htm


2006.04.02 补充：
今天在 CNOUG 上看到一篇《orapwd 命令中 entries 参数的作用》，该文详细说明了 entries 参数的设置，也证明了 entries 的设置与文件系统的 block size 大小无关，看来我前面的说法有误，不过 linux 和 windows 下不知道是不是一样，具体实验等以后有空再做。根据该文所说，entries 最大的有效数值是 65535，如果超过 65536，指定的数值将对 65536求模。并且，entries 按照 0-4,5-8,9-12,13-16,17-20 分组，每组的文件大小相同，可容纳的 SYSDBA 的用户数也相同，这就是为什么用户数可以超过 entries 的值。口令文件的大小 = (n + 1) * 512，其中 n 是每组口令文件中 0x9 上的值，既 2，3，4……


全文引用于此：


orapwd命令中entries参数的作用
作者 blue_stone
E-mail: blue_stone@xinhuanet.com
Blog: http://bluestone.cublog.cn
原始链接: http://www.cublog.cn/u/7667/?u=http://www.cublog.cn/u/7667/showart.php?id=93939
转载请保留作者信息和原始链接. 本文档的最新版本, 可以在原始链接处找到.
本文分析了orapwd中entries参数的一些性质，测试环境操作系统使用Debian GNU/Linux SID,数据库使用Oracle Database Enterprise Edition 9204。

Oracle数据库的orapwd用来建立密码文件，不加参数运行orapwd将给出orapwd命令的参数

blue_stone@blueice:~/test$ orapwd
Usage: orapwd file= password= entries=

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  There are no spaces around the equal-to (=) character.

orapwd命令可以有三个参数，file指生成的密码文件的名称，password指sys用户的密码，这两个参数是强制的，如果缺失任何参数都将输出orapwd的帮助信息。

blue_stone@blueice:~/test$ orapwd file=233.ora
Usage: orapwd file= password= entries=

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  There are no spaces around the equal-to (=) character.
blue_stone@blueice:~/test$ orapwd password=abcdef
Usage: orapwd file= password= entries=

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and OPERs (opt),
  There are no spaces around the equal-to (=) character.

如果成功的生成密码文件，orapwd将无提示的退出。

blue_stone@blueice:~/test$ orapwd file=233.ora password=abcdef
blue_stone@blueice:~/test$ ls -al 233.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 09:27 233.ora

第三个参数是entries。从orapwd的帮助信息上理解，entries是可以保存的记录个数，每个具有sysdba或sysoper权限的用户算一个记录，如果一个用户同时具有sysdba和sysoper的权限，则只占一个记录。数据库启动后可以通过v$pwfile_users来查看密码文件中记录的情况。

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

但是entries并不是一个准确的值，也就是说，如果我们设置entries为10,可能会有超过10个用户可以具有sysdba或者sysoper的权限。为了证实这个观点，我们使用如下脚本来创建多个密码文件，并对生成的密码文件进行初步的分析。

#!/bin/bash
# File Name        : pwtest.sh
# Usage         : Create some oracle password file and analyze them.

n=0
while [ $n -le 20 ]
do
     orapwd file=$n.ora password=oracle entries=$n
     hexdump $n.ora > $n.dmp
     n=$(($n+1))
done
n=0
while [ $n -le 20 ]
do
     ls -al $n.ora
     n=$((n+1))
done   
n=0
while [ $n -le 20 ]
do
     md5sum $n.ora
     n=$((n+1))
done

这个脚本会生成20个密码文件1.ora到20.ora ， entries参数分别是1到20,将生成的密码文件使用hexdump导出成可读的txt文档。运行结果如下：
blue_stone@blueice:~/test$ ./pwtest.sh
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 0.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 1.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 2.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 3.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 10:07 4.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 5.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 6.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 7.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 10:07 8.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 9.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 10.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 11.ora
-rwSr----- 1 blue_stone user 2560 2006-04-01 10:07 12.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 13.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 14.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 15.ora
-rwSr----- 1 blue_stone user 3072 2006-04-01 10:07 16.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 17.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 18.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 19.ora
-rwSr----- 1 blue_stone user 3584 2006-04-01 10:07 20.ora
c16f9293603f290409d2f495217e6262  0.ora
c16f9293603f290409d2f495217e6262  1.ora
c16f9293603f290409d2f495217e6262  2.ora
c16f9293603f290409d2f495217e6262  3.ora
c16f9293603f290409d2f495217e6262  4.ora
1bc8a7b9789922356fc9b804e02ea2c8  5.ora
1bc8a7b9789922356fc9b804e02ea2c8  6.ora
1bc8a7b9789922356fc9b804e02ea2c8  7.ora
1bc8a7b9789922356fc9b804e02ea2c8  8.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6  9.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6  10.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6  11.ora
3c76d4e09b4cf3e7cf8ad69db2519ff6  12.ora
6604f4088b58fc57cad8d496814ee928  13.ora
6604f4088b58fc57cad8d496814ee928  14.ora
6604f4088b58fc57cad8d496814ee928  15.ora
6604f4088b58fc57cad8d496814ee928  16.ora
9c16648155e3387d74c8c63845c33595  17.ora
9c16648155e3387d74c8c63845c33595  18.ora
9c16648155e3387d74c8c63845c33595  19.ora
9c16648155e3387d74c8c63845c33595  20.ora

从输出的结果我们初步可以判定，生成的密码文件可以分为几个不同的组，entries参数分别为0-4,5-8,9-12,13-16,17-20，每组内各个文件内容想同。hexdump的结果进一步证实了这一点。
blue_stone@blueice:~/test$ diff 0.dmp 1.dmp
blue_stone@blueice:~/test$ diff 0.dmp 2.dmp
blue_stone@blueice:~/test$ diff 0.dmp 3.dmp
blue_stone@blueice:~/test$ diff 0.dmp 4.dmp
blue_stone@blueice:~/test$ diff 7.dmp 5.dmp
blue_stone@blueice:~/test$ diff 11.dmp 9.dmp
blue_stone@blueice:~/test$ diff 16.dmp 13.dmp
blue_stone@blueice:~/test$ diff 19.dmp 17.dmp

不同组的密码文件则并不相同（大小不同，md5sum的结果也不相同），对各个密码文件使用hexdump生成的dmp文件进行比较后可以看出不同的地方非常少

1.ora的dmp文件1.dmp内容如下：

0000000 0000 0000 0200 0000 0002 0000 5c5d 5a5b
0000010 0000 0000 0000 0000 0000 0000 0000 0000
*
0000200 524f 4341 454c 5220 6d65 746f 2065 6150
0000210 7373 6f77 6472 6620 6c69 0065 0000 001b
0000220 0000 0000 0000 0000 0000 0000 0000 0000
*
0000260 4e49 4554 4e52 4c41 0000 0000 0000 0000
0000270 0000 0000 0000 0000 0000 0000 0000 0000
0000280 0008 0000 4241 3732 3542 4533 4344 4635
0000290 4645 3134 0000 0000 0000 0000 0000 0000
00002a0 0000 0000 0010 0000 000f 0000 0000 0000
00002b0 0000 0000 0000 0000 0000 0000 0000 0000
00002c0 0000 0000 0000 0000 5953 0053 0000 0000
00002d0 0000 0000 0000 0000 0000 0000 0000 0000
00002e0 0000 0000 0000 0000 0003 0000 4138 4638
00002f0 3230 3735 3733 3941 3930 4137 0000 0000
0000300 0000 0000 0000 0000 0000 0000 0010 0000
0000310 000f 0000 0000 0000 0000 0000 0000 0000
0000320 0000 0000 0000 0000 0000 0000 0000 0000
*
0000600

5.ora的dmp文件5.dmp内容如下：

blue_stone@blueice:~/test$ more 5.dmp
0000000 0000 0000 0200 0000 0003 0000 5c5d 5a5b
0000010 0000 0000 0000 0000 0000 0000 0000 0000
*
0000200 524f 4341 454c 5220 6d65 746f 2065 6150
0000210 7373 6f77 6472 6620 6c69 0065 0000 001b
0000220 0000 0000 0000 0000 0000 0000 0000 0000
*
0000260 4e49 4554 4e52 4c41 0000 0000 0000 0000
0000270 0000 0000 0000 0000 0000 0000 0000 0000
0000280 0008 0000 4241 3732 3542 4533 4344 4635
0000290 4645 3134 0000 0000 0000 0000 0000 0000
00002a0 0000 0000 0010 0000 000f 0000 0000 0000
00002b0 0000 0000 0000 0000 0000 0000 0000 0000
00002c0 0000 0000 0000 0000 5953 0053 0000 0000
00002d0 0000 0000 0000 0000 0000 0000 0000 0000
00002e0 0000 0000 0000 0000 0003 0000 4138 4638
00002f0 3230 3735 3733 3941 3930 4137 0000 0000
0000300 0000 0000 0000 0000 0000 0000 0010 0000
0000310 000f 0000 0000 0000 0000 0000 0000 0000
0000320 0000 0000 0000 0000 0000 0000 0000 0000
*
0000800
每个dmp文件的第一列标明了这一列的数据在文件中的偏移量，后面是用16进制表示的文件的内容，每行16字节，32个16进制字符，如果多行内容相同，则只显示第一行的内容，下面重复的行将以星号（*）替代。运行hexdump时使用-v参数可以强制hexdump输出重复的行。最后一行是文件末尾的偏移量，可以看出，1.dmp文件的大小为0x600（1536）字节，5.dmp文件的大小为0x800（2048）字节。
使用diff对1.dmp和5.dmp进行比较，结果如下：
blue_stone@blueice:~/test$ diff 1.dmp 5.dmp
1c1
< 0000000 0000 0000 0200 0000 0002 0000 5c5d 5a5b
---
> 0000000 0000 0000 0200 0000 0003 0000 5c5d 5a5b
22c22
< 0000600
---
> 0000800

也就是说只有第一行的一个数值（偏移量为0xa)不同，而这个数字和文件的大小成正比，文件大小=(0x9的数值 +1) * 512。如果在使用orapwd建立密码文件时指定的参数足够大，比如65535，则0x8位上也将为非0值，如果entries为65535则0x8,0x9的值分别为0x40,0x01。 初始的密码文件非0的内容只有文件开始不到1k字节，剩下的部分都是0。

entries最大的有效数值是65535,如果超过65536,指定的数值将对65536求模。用下面的程序可以验证这一点：

#!/bin/bash
# File Name        : pwtest2.sh
# Usage         : Create some oracle password file with the entires parameter
#                : like 0*65536 + m, 1*65536 + m ......, then analyze them.

m=5
n=0
while [ $n -le 20 ]
do
     orapwd file=$(($n*65536+$m)).ora password=oracle entries=$(($n*65536+$m))
     n=$(($n+1))
done
n=0
while [ $n -le 20 ]
do
     ls -al $(($n*65536+$m)).ora
     n=$((n+1))
done   
n=0
while [ $n -le 20 ]
do
     md5sum $(($n*65536+$m)).ora
     n=$((n+1))
done

运行结果如下：

blue_stone@blueice:~/test$ ./pwtest2.sh
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 5.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 65541.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 131077.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 196613.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 262149.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 327685.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 393221.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 458757.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 524293.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 589829.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 655365.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 720901.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 786437.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 851973.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 917509.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 983045.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1048581.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1114117.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1179653.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1245189.ora
-rwSr----- 1 blue_stone user 2048 2006-04-01 12:23 1310725.ora
1bc8a7b9789922356fc9b804e02ea2c8  5.ora
1bc8a7b9789922356fc9b804e02ea2c8  65541.ora
1bc8a7b9789922356fc9b804e02ea2c8  131077.ora
1bc8a7b9789922356fc9b804e02ea2c8  196613.ora
1bc8a7b9789922356fc9b804e02ea2c8  262149.ora
1bc8a7b9789922356fc9b804e02ea2c8  327685.ora
1bc8a7b9789922356fc9b804e02ea2c8  393221.ora
1bc8a7b9789922356fc9b804e02ea2c8  458757.ora
1bc8a7b9789922356fc9b804e02ea2c8  524293.ora
1bc8a7b9789922356fc9b804e02ea2c8  589829.ora
1bc8a7b9789922356fc9b804e02ea2c8  655365.ora
1bc8a7b9789922356fc9b804e02ea2c8  720901.ora
1bc8a7b9789922356fc9b804e02ea2c8  786437.ora
1bc8a7b9789922356fc9b804e02ea2c8  851973.ora
1bc8a7b9789922356fc9b804e02ea2c8  917509.ora
1bc8a7b9789922356fc9b804e02ea2c8  983045.ora
1bc8a7b9789922356fc9b804e02ea2c8  1048581.ora
1bc8a7b9789922356fc9b804e02ea2c8  1114117.ora
1bc8a7b9789922356fc9b804e02ea2c8  1179653.ora
1bc8a7b9789922356fc9b804e02ea2c8  1245189.ora
1bc8a7b9789922356fc9b804e02ea2c8  1310725.ora

如果不指定entries参数，则生成的密码文件和entries参数为0-4时生成的参数文件一样。

blue_stone@blueice:~/test$ orapwd file=1.ora password=oracle entries=1
blue_stone@blueice:~/test$ orapwd file=default.ora password=oracle
blue_stone@blueice:~/test$ md5sum 1.ora default.ora
c16f9293603f290409d2f495217e6262  1.ora
c16f9293603f290409d2f495217e6262  default.ora
blue_stone@blueice:~/test$ ls -al 1.ora default.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 13:09 1.ora
-rwSr----- 1 blue_stone user 1536 2006-04-01 13:09 default.ora


在linux中可以使用tune2fs -l 可以查看文件系统的属性。
blueice:~# tune2fs -l /dev/hda6
tune2fs 1.39-WIP (31-Dec-2005)
Filesystem volume name:   
Last mounted on:         
Filesystem UUID:          0f4eb2b4-1360-48a1-8b66-3916a6838210
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      has_journal filetype needs_recovery sparse_super
Default mount options:    (none)
Filesystem state:         clean
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              3057824
Block count:              6104692
Reserved block count:     305234
Free blocks:              1002160
Free inodes:              2930491
First block:              0
Block size:               4096
Fragment size:            4096
Blocks per group:         32768
Fragments per group:      32768
Inodes per group:         16352
Inode blocks per group:   511
Filesystem created:       Sat Sep  3 16:33:16 2005
Last mount time:          Sat Apr  1 08:10:43 2006
Last write time:          Sat Apr  1 08:10:43 2006
Mount count:              8
Maximum mount count:      22
Last checked:             Sat Mar 25 20:15:04 2006
Check interval:           15552000 (6 months)
Next check after:         Thu Sep 21 20:15:04 2006
Reserved blocks uid:      0 (user root)
Reserved blocks gid:      0 (group root)
First inode:              11
Inode size:               128
Journal inode:            8
Default directory hash:   tea
Directory Hash Seed:      70ac7131-5a92-4ca3-865d-4ce83f38acb1
Journal backup:           inode blocks

从输出的结果可以看出该文件系统块大小为4096字节。这也是ext3文件系统的默认块大小。使用mkfs.ext3 -b n devicename 可以在devicename上创建块大小为n的的文件系统，n可取的值有1024,2048,4096。
blueice:/home/blue_stone/test# dd if=/dev/zero of=testfile bs=1M count=20
20+0 records in
20+0 records out
20971520 bytes (21 MB) copied, 0.449763 seconds, 46.6 MB/s
blueice:/home/blue_stone/test# mkfs.ext3 -b 1024 testfile
mke2fs 1.39-WIP (31-Dec-2005)
testfile is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=1024 (log=0)
Fragment size=1024 (log=0)
5136 inodes, 20480 blocks
1024 blocks (5.00%) reserved for the super user
First data block=1
3 block groups
8192 blocks per group, 8192 fragments per group
1712 inodes per group
Superblock backups stored on blocks:
        8193

Writing inode tables: done
Creating journal (1024 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 21 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
blueice:/home/blue_stone/test# mkdir test
blueice:/home/blue_stone/test# mount testfile test -o loop
blueice:/home/blue_stone/test# mount
/dev/hda1 on / type ext3 (rw,errors=remount-ro)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
usbfs on /proc/bus/usb type usbfs (rw)
tmpfs on /dev/shm type tmpfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/hda3 on /mnt/amd64 type ext3 (rw)
/dev/hda6 on /home type ext3 (rw)
/dev/hda9 on /bak type ext3 (rw)
/dev/hda7 on /vmware type ext3 (rw)
tmpfs on /dev type tmpfs (rw,size=10M,mode=0755)
nfsd on /proc/fs/nfsd type nfsd (rw)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
/home/blue_stone/test/testfile on /home/blue_stone/test/test type ext3 (rw,loop=/dev/loop/0)
blueice:/home/blue_stone/test# tune2fs -l testfile  
tune2fs 1.39-WIP (31-Dec-2005)
Filesystem volume name:   
Last mounted on:         
Filesystem UUID:          561bb9bf-2455-4677-aa0d-27c868aa99fd
Filesystem magic number:  0xEF53
Filesystem revision #:    1 (dynamic)
Filesystem features:      has_journal filetype needs_recovery sparse_super
Default mount options:    (none)
Filesystem state:         clean
Errors behavior:          Continue
Filesystem OS type:       Linux
Inode count:              5136
Block count:              20480
Reserved block count:     1024
Free blocks:              18784
Free inodes:              5125
First block:              1
Block size:               1024
Fragment size:            1024
Blocks per group:         8192
Fragments per group:      8192
Inodes per group:         1712
Inode blocks per group:   214
Filesystem created:       Sat Apr  1 12:33:59 2006
Last mount time:          Sat Apr  1 12:34:15 2006
Last write time:          Sat Apr  1 12:34:15 2006
Mount count:              1
Maximum mount count:      21
Last checked:             Sat Apr  1 12:33:59 2006
Check interval:           15552000 (6 months)
Next check after:         Thu Sep 28 12:33:59 2006
Reserved blocks uid:      0 (user root)
Reserved blocks gid:      0 (group root)
First inode:              11
Inode size:               128
Journal inode:            8
Default directory hash:   tea
Directory Hash Seed:      02dfc011-04f6-4a92-b763-99fe8aa76e96
Journal backup:           inode blocks


在新创建的文件系统中做上面的测试，可以得出相同的结果，由此可见，oracle密码文件和文件系统的大小并无关系。

当不指定entries值或者指定的entries的值为0-4的时候，可以有五个用户具有sysdba或者sysoper的权限。

oracle@blueice:~/product/9.2.0/dbs$ rm orapworcl
oracle@blueice:~/product/9.2.0/dbs$ orapwd file=orapworcl password=oracle
oracle@blueice:~/product/9.2.0/dbs$ hexdump -v orapworcl >default.dmp
oracle@blueice:~/product/9.2.0/dbs$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 1 13:14:22 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn sys/oracle as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  147920392 bytes
Fixed Size                   452104 bytes
Variable Size             121634816 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$pwfile_users
  2  ;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE

SQL> grant sysdba to scott,hr,sh,xdb;

Grant succeeded.

SQL> grant sysdba to wmsys;
grant sysdba to wmsys
*
ERROR at line 1:
ORA-01996: GRANT failed: password file '/home/oracle/product/9.2.0/dbs/orapworcl' is full


SQL> select * from v$pwfile_users
  2  ;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SCOTT                          TRUE  FALSE
HR                             TRUE  FALSE
SH                             TRUE  FALSE
XDB                            TRUE  FALSE

使用string可以看到oracle密码文件中的一些内容
oracle@blueice:~/product/9.2.0/dbs$ strings orapworcl
]\[Z
ORACLE Remote Password file
ORCL
INTERNAL
2C0791C2F3E3BB92
D4C5016086B2DC6A
r1BSCOTT
F894844C34402B67
6399F3B38EDF3288
9793B3777CD3BD1A
88D8364765FCE6AF
oracle@blueice:~/product/9.2.0/dbs$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Apr 1 13:51:20 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> select password from dba_users where username in ('HR','SH','SCOTT','XDB');

PASSWORD
------------------------------
88D8364765FCE6AF
F894844C34402B67
9793B3777CD3BD1A
6399F3B38EDF3288

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

给用户授与sysdba权限的过程就是把密码从数据字典复制到了密码文件中的过程。
 

Posted by Sky at 11:15 PM | Permalink | comments(1) | Database