Password File

Posted on 2008-11-13
Last Modified: 2012-05-05
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.


Question by:as93is
    LVL 6

    Expert Comment

    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:

    Author Comment


    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.

    LVL 6

    Accepted Solution

    LVL 3

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now