Password File

ORA-01996: GRANT failed: password file is full.

We have to create multiple user accounts with SYSDBA system privilege. All the existing users
and the new users must have the SYSDBA privilege.

I tried to create a user with sysdba system privilege. I got the following Oracle error:

ORA-01996: GRANT failed: password file is full

I queried the v$pwfile_users view and got the following output:

SQL> select count(*) from v$pwfile_users
2 /


Please clarify the following:

1. How do I find out how many entries are there in the current password file?

2. How do I modify the password file and increase the number of entries to 30? What are
the steps to increase the number of entries to 30?

3. Should a current password file be deleted?

4. Will all the users with the sysdba sysdba privilege be dropped when the password file is deleted?
Or, will the users be there in the database and only the sysdba privilege revoked?

5. If the current password file has to be deleted, how do I save the user accounts
already created?

6. How do I drop the current password file?

7. How do I create a new password file? Please let me know the steps.

8. Do I have to re-create user accounts with sysdba system privilege when I create the new
password file or just grant the sysdba system privilege to the users?

9. Should the new password file have the same name as the old password file name?

10. What is location of the password file?

11. Should I shutdown and re-start the database after dropping and recreating the new password

12. Should I restart the Oracle Service after re-creating the password file?

13. If the password file has to be deleted to increase the number of entries, will I able to login as SYS to do the DBA tasks when there is no password file?

14. How can I do the DBA tasks with a password file?

15. Do I have to keep a backup of the old password file in order to re-create the user in the new password file?

16. What will happen to the users during the time the current password file is deleted, the new password file is created, and the users recreated? Will they be able to use the database?

The ORA-01996: GRANT failed: password file is full has to be fixed in about forty database.

Please let me know how the above error can be quickly fixed in all the databases. I am concerned about the users when the password file is deleted.

This is a high priority task.


Who is Participating?
You will have to recreate the file..

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.
To Replace a Password File

   1. Note the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
   2. Shut down the database.
   3. Delete the existing password file.
   4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
   5. Follow the instructions in "Adding Users to a Password File".

Read more:
as93isAuthor Commented:

We have an Oracle 10g database not Oracle 9i.

Can you give some steps to delete and re-create the password file?
What will happen to the users during the time when the password file is deleted and recreated?

Should the users be re-created after the new password file is created?

Please answer all my questions.   This is a time-bound task.

Why do new users need the sysdba privileges?  This is really bad practice, it means everyone will have the ability to destroy the database.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.