ORA-12522: TNS:listener could not find available instance with given INSTANCE_ROLE
Cause : There are not any available and appropriate database instances registered with the listener, that are part of the service identified by SERVICE_NAME given in the connect descriptor and that have the specified INSTANCE_ROLE (and INSTANCE_NAME, if specified).
Action - How to fix it : DBA Scripts :: www.high-oracle.com/scripts
Check to make sure that the INSTANCE_ROLE specified is correct. Run "lsnrctl services" to ensure that the instance(s) have registered with the listener and that they are ready to accept connections.
Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.