About the entries parameter of the orapwd command

About the entries parameter of the orapwd command

When I read the Oracle official document Administrator's Guide this morning, in the password file section, the entries PARAMETERS OF THE orapwd command are described as follows:

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.

The entries parameter does not indicate how many users can be granted sysdba or sysoper permissions. The actual number may be higher than the value specified by the entries parameter, which is related to the block size of the operating system.

According to this statement, the block size of the operating system is 512 bytes. If entries is set to 1, I can grant sysdba or sysoper permissions to four users.

Curious, I couldn't help but study it.

1. view the block size of the operating system. The local machine is RHEL 6.3.

[Root @ node2 ~] # Tune2fs-l/dev/sdb | grep 'block size'
Block size: 4096

The block size is 4096 bytes, 8 times of 512. According to the above logic, you can grant sysdba or sysoper permissions to a maximum of 32 users.

Ii. Create a password file

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

3. Construct a user for testing

If you can grant sysdba or sysoper permissions to up to 32 users, it is no problem to create 32 Users and grant sysdba permissions. If you grant sysdba permissions to 33rd users, an error is reported.

Create 32 Users and grant sysdba permissions. The script is as follows:

 

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;

 

The execution result is as follows:

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

32 users were created and sysdba permissions were not granted successfully.

4. View created users and users granted sysdba Permissions

 

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

 

It is found that only five users are successfully created and only four users are granted sysdba permissions.

Is the description of entries in the official document incorrect? Operating system block size is 512 bytes is searched on Baidu as the keyword. The so-called block size of oracle is different from the block size of the system, query by select distinct lebsz from x $ kccle. lebsz refers to the block size of log file, which is equal to the block size of the operating system.

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 1 k, and tru64 has blocksize of 2 k if my memory serves me well.

The specific explanation is as follows:

5. query the block size of Oracle

SQL> select distinct lebsz from x $ kccle;

LEBSZ
----------
512

It seems that the local block size is 512, which is not difficult to explain that when entries is set to 1, only up to four users can be granted sysdba permissions.

If this is the case, set entries to 5, and grant sysdba permissions to eight users. Next, let's try again.

6. Delete the five users created above

 

Declare
Sqltext varchar2 (100 );
Begin
For I in 1 .. 5
Loop
Sqltext: = 'drop user test' | I;
Execute immediate sqltext;
End loop;
End;

 

7. Recreate the password file and set entries to 5.

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

8. Create eight new users and grant sysdba Permissions

 

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;

 

Statement execution is normal

9. Create 9th users and grant sysdba Permissions

 

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

An error occurred!

From this point of view, the "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 "is indeed correct!