Error: "SP2-0640: Not connected" when creating an Oracle user
Last Updated August 03, 2018
When creating an Oracle user, you execute oracle_create_user.sql from SQL*Plus, and the following stream of errors appears. You are logged in as SYS as SYSDBA.
ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
Warning: You are no longer connected to ORACLE. SP2-0640: Not connected
The Oracle Listener process was not successfully created.
This error indicates that the TNSNAMES.ora file was not created during the installation process.
Specifying the local service will create a TNSNAMES.ora file which can be used as one of the methods for resolving to a connection string. While creating the protect user, the script attempts to connect to the database using TNS to resolve the connection. Without that specific infomation, SQL*Plus has no way to connect, and generates the "SP2-0640: Not connected" error.
Start the Oracle Net Configuration Assistant
In Windows, navigate to Start > All Programs > Oracle-OraDb11g_home1 > Configuration and Migration Tools > Net Configuration Assistant.
Select Local Net Service Name configuration.
Click Add, and type in a service name (the Database SID, typically "protect").
Accept the default of TCP, and click Next.
Enter the host name or IP of the Oracle database server, and accept port 1521 (the default listening port).
Click Next. You are presented with the opportunity to test.
Click YES, and then click Next. The test may fail, which is OK.
Click Change Login.
Change the password to the same passwword as the Oracle SYS account.
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe