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 /

COUNT(*)
----------
5

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
file?

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.

Thanks.



as93isAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter991Commented:
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:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#1227
0
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.

Thanks.
0
peter991Commented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsmith1962Commented:
Why do new users need the sysdba privileges?  This is really bad practice, it means everyone will have the ability to destroy the database.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.