Password File

How to Find location of Password File?
============================================================

Location & Naming in Windows :

$ORACLE_HOME/database/PWDdb_name.ora

Location & Naming in Unix :

$ORACLE_HOME/dbs/orapwdb_name.ora

Find Whether Password File is Available or not :

Select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

If V$pwfile_users shows output then password file is available and its being used. No Output means no Password file.

#########################################
# Test to Confirm this
#########################################

Now i'm going to rename the password file. In windows you will not be able to rename the file because it will be used. For testing purpose i shutdown the database and rename the file.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Renamed the Password File.

C:\Users\Administrator>set oracle_sid=ora11g

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 22 20:04:23 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  753278976 bytes
Fixed Size                  1374724 bytes
Variable Size             251659772 bytes
Database Buffers          494927872 bytes
Redo Buffers                5316608 bytes
Database mounted.
Database opened.

Now quering v$pwfile_users didnt show any output.

SQL> select * from v$pwfile_users;

no rows selected

I have renamed the password file to its original name and queried the database. Now its shows the output.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE