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!