SQL*Plus - Oracle Command Line Interface

JDBC for Oracle - Herong's Tutorial Examples

∟Oracle Express Edition Installation on Windows

∟SQL*Plus - Oracle Command Line Interface

This section describes how to use Oracle command line interface, SQL*Plus.

Oracle XE server offers a command line interface called SQL*Plus. The program file of SQL*Plus is located at \local\orablexe\app\oracle\product\21c\bin\sqlplus.exe. "\local\orablexe\app\oracle\product\21c\bin" should be added to the %path% variable already by the Oracle XE installation process. If not, you can add it manually to avoid typing the full path to invoke the "sqlplus" command.

Here is what I did in a command window with "sqlplus", to obtain a list of all users and checking privileges of user "Herong":

herong> sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production ...
Version 21.3.0.0.0

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

SQL> connect SYS/HerongY@ng AS SYSDBA
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

SQL> connect SYS/anything AS SYSDBA
Connected.

SQL> SELECT USERNAME FROM ALL_USERS;

USERNAME 
------------------------- 
SYS 
AUDSYS 
SYSTEM 
SYSBACKUP 
SYSDG 
SYSKM 
SYSRAC 
OUTLN 
GSMADMIN_INTERNAL 
GSMUSER 
GSMROOTUSER 
 
USERNAME 
------------------------- 
DIP 
XS$NULL 
REMOTE_SCHEDULER_AGENT 
DBSFWUSER 
...

37 rows selected.

This tutorial shows a number of interesting behaviors of Oracle server:

Use the "/nolog" option with the "sqlplus" command so it will be started without login, which give you a chance to run the "CONNECT" or other commands.
"SYS" is a very special built-in user account in Oracle server. It has the password set to the value provided during the installation process.
When correct user name and password "SYS/HerongY@ng" is provided, Oracle 11.2 up to 21.3 server returns with the "ORA-12154: TNS" error, which could be related to some settings I missed during the installation.
However, when incorrect user name and password "SYS/anything" is provided, Oracle 11.2 up to 21.3 server provides me the connection. This is very funny. After some searching on the Internet, I think Oracle has a logic to default incorrect user name and password combination to use Windows user group authentication to establish the connection if "AS SYSDBA" is specified.
Application Express users are not included in the "ALL_USERS" table in Oracle 11.2 and newer versions. But in Oracle 10.2, the user added in Application Express is listed in the "ALL_USERS" table.