今日早上看Oracle官方文檔《Administrator's Guide》時，在密碼文件章節(jié)，關于orapwd命令entries參數(shù)的說明如下：

This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because theORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

大意是，entries參數(shù)并不是指多少個用戶可被賦予sysdba或sysoper權限。實際數(shù)量可能高于entries參數(shù)指定的值，這與操作系統(tǒng)的block size有關。

按它的說法，操作系統(tǒng)的block size為512字節(jié)，entries指定為1的話，我可以給4個用戶賦予sysdba或sysoper權限。

好奇，忍不住研究了一下。

一、查看操作系統(tǒng)的block size，本機為RHEL 6.3

[root@node2 ~]# tune2fs -l /dev/sdb |grep 'Block size'

Block size:? ? ? ? ? ? ? 4096

block size為4096字節(jié)，為512的8倍，按照上面的邏輯，可以給最多32個用戶賦予sysdba或sysoper權限。

二、創(chuàng)建密碼文件

[oracle@node2 dbs]$ orapwd file=orapworcl password=oracle entries=1 force=y

三、構造用戶進行測試

如果能給最多32個用戶賦予sysdba或sysoper權限的話，那么創(chuàng)建32個用戶并賦予sysdba權限不會有問題，賦予第33個用戶sysdba權限的時候會報錯，實驗一下。

首先創(chuàng)建32個用戶并賦予sysdba權限，腳本如下：

declare

sqltext1 varchar2(100);

sqltext2 varchar2(100);

begin

for i in 1..32

loop

sqltext1 := 'create user test'||i||' identified by test'||i;

sqltext2 := 'grant sysdba to test'||i;

execute immediate sqltext1;

execute immediate sqltext2;

end loop;

end;

執(zhí)行結果如下：

declare

*

ERROR at line 1:

ORA-01996: GRANT failed: password file

'/u01/app/oracle/product/11.2.0.1/db_1/dbs/orapworcl' is full

ORA-06512: at line 10

創(chuàng)建32個用戶并賦予sysdba權限竟然沒有成功。

四、查看創(chuàng)建成功的用戶和被賦予sysdba權限的用戶

SQL> select username from dba_users where username like 'TEST%';

USERNAME

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

TEST4

TEST5

TEST2

TEST1

TEST3

6 rows selected.

SQL> select username from v$pwfile_users where username !='SYS' and sysdba='TRUE';

USERNAME

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

TEST1

TEST2

TEST3

TEST4

發(fā)現(xiàn)只成功創(chuàng)建了5個用戶且只有4個用戶被賦予sysdba權限。

難道官方文檔關于entries的說明有誤？將operating system block size is 512 bytes作為關鍵字在百度上搜了下，原來oracle所謂的block size和系統(tǒng)的block size不一致，可根據(jù)select distinct lebsz from x$kccle進行查詢，lebsz指的是log file的塊大小，它也等于操作系統(tǒng)塊大小

Linux: file Block size is selected at the time of high-level formatting。

The log block size is platform. specific, and can be found out using the following query: (size in bytes)

most platforms have log block size of 512, but HPUX has 1k, and tru64 has blocksize of 2k if my memory serves me well.

五、查詢Oracle的block size

SQL> select distinct lebsz from x$kccle;

LEBSZ

----------

512

看來，本機的block size是512，這也不難解釋當entries指定為1的時候，最多只能給4個用戶賦予sysdba的權限。

如果是這樣的話，那我將entries指定為5，則可以給8個用戶賦予sysdba權限，下面來試試。

六、刪除上面新建的5個用戶

declare

sqltext varchar2(100);

begin

for i in 1..5

loop

sqltext := 'drop user test'||i;

execute immediate sqltext;

end loop;

end;

七、重新創(chuàng)建密碼文件，將entries指定為5

[oracle@node2 dbs]$ orapwd file=orapworcl password=oracle entries=5 force=y

八、新建8個用戶并賦予sysdba權限

declare

sqltext1 varchar2(100);

sqltext2 varchar2(100);

begin

for i in 1..8

loop

sqltext1 := 'create user test'||i||' identified by test'||i;

sqltext2 := 'grant sysdba to test'||i;

execute immediate sqltext1;

execute immediate sqltext2;

end loop;

end;

語句執(zhí)行沒有問題

九、新建第9個用戶并賦予sysdba權限

SQL> create user test9 identified by test9;

User created.

SQL> grant sysdba to test9;

grant sysdba to test9

*

ERROR at line 1:

ORA-01996: GRANT failed: password file

'/u01/app/oracle/product/11.2.0.1/db_1/dbs/orapworcl' is full

果然出現(xiàn)報錯！

由此來看， 官方文檔中所說的 “if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four” 確實沒錯！

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的linux 下orapwd 未找到命令,关于orapwd命令entries参数的探究的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。