Oracle Pawword file

[hamhey]

Hi all,
I need to get permisson to some users to connect from the same server running the database and from another server to connect to a database as sysdba.
What I did is:
1. used orapwd and created a new file pwd
2. changed the prameter remote_login_passwordfile = exclusive
3. create users and granted sysdba and sysoper to the users
4. SQL>connect user/password as sysdba
is possible
but
-sqlplus user/password@sid is not possible

-quering of v$pwfile_users shows no rows.

Any idea what is missing or wrong.

//Hamhey

[ggnanaraj]

Originally posted by hamhey

I need to get permisson to some users to connect from the same server running the database and from another server to connect to a database as sysdba.

What database version & OS is running on the servers?

4. ...
-sqlplus user/password@sid is not possible

Where are you giving this? on the server hosting the database or on a different box?

[SANJAY_G]

SQL> connect sys as sysdba
Enter password: *********
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SANJAY                         TRUE  FALSE

SQL> create user test identified by test;

User created.

SQL> grant sysdba to test;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SANJAY                         TRUE  FALSE
TEST                           TRUE  FALSE

SQL> conn test as sysdba
Enter password: ****
Connected.
SQL> conn test/test
ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn sys as sysdba
Enter password: *********
Connected.
SQL> grant connect, resource to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL>

[hamhey]

Hi Sanjay
Do you mind give the steps how to create the password file.
OS : Solaris 8 on Intel
OORACLE: 8.1.5

//Hamhey

[SANJAY_G]

http://download-east.oracle.com/docs...1/dba.htm#1207

[akhadar]

can u restart the oracleSIDservice and check the V$pwfile_users view again.

[SANJAY_G]

Originally posted by akhadar
can u restart the oracleSIDservice and check the V$pwfile_users view again.

He is on Solaris (Intel)

[ggnanaraj]

Originally posted by hamhey
OS : Solaris 8 on Intel
OORACLE: 8.1.5

Oracle 8.0.5 & 8.1.5 has a bug filed so that when you query v$pwfile_users it generates ORA-600 or returns no rows. Check this out for more info on metalink... http://metalink.oracle.com/metalink/...&p_id=142581.1

HTH.

[hamhey]

Hi,
I followed the documentation as above and restarted the server no rows selected by quering the v$pwfile_user and the error message:
Soldb SQL>select * from v$pwfile_users;
ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []



no rows selected

Soldb SQL>!
$ oerr ora 00600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number

This is bug in oracle 8.1.5 as the descripting above.

Any idea how to fix it.

//Hamhey

[ggnanaraj]

Originally posted by hamhey
Soldb SQL>select * from v$pwfile_users;
ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []

Use a order by clause in your above query.

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

Input truncated to 31 characters
BLAZE: SYS> select * from v$pwfile_users order by 1;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
SYS                            TRUE  TRUE

BLAZE: SYS> grant sysdba to rgg;

Grant succeeded.

BLAZE: SYS>  select * from v$pwfile_users order by 1;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
INTERNAL                       TRUE  TRUE
RGG                            TRUE  FALSE
SYS                            TRUE  TRUE

BLAZE: SYS> 

BLAZE: SYS>  select * from v$pwfile_users;
ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []



no rows selected

BLAZE: SYS>