Connect through a service, not through ORACLE_SID. Prove the session: SHOW USER, SHOW CON_NAME, and the USERENV service name. EXIT commits. EXIT ROLLBACK is the command that discards uncommitted work.
Oracle DBA Lesson 17A — Connect and Check Your SQL*Plus Session
Connect through a service
Easy Connect names a host, a listener port, and a service. That service is not ORACLE_SID. A CDB instance has one SID. A PDB — and any extra service you create in it — has its own service name. The listener routes the session to that service.
sqlplus -L COURSE_READER@"//dbhost:1521/LABPDB"Quotes keep the connect identifier intact for the shell. SQL*Plus then prompts for the password. Leave the password off the command line so it does not land in history.
-L (also -l; the full option is LOGON) tries the logon once. If it fails, SQL*Plus exits. Without -L, a bad password sends you back to Enter user-name: — a trap in a script.
A TNS alias is the same idea: CONNECT_DATA should use SERVICE_NAME, not SID. A PDB has no SID of its own. Connecting by instance SID typically lands in the CDB root, not in the PDB you meant.
Who is connected, and where?
SHOW USER prints the session user. SHOW CON_NAME prints the current container. After a PDB service login, check both before you change data.
SHOW USER
SHOW CON_NAMEAsk the database which service this session actually used:
SELECT SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name
FROM dual;The default PDB service often has the same name as the PDB, so LABPDB can appear in both places. A user-defined service can differ. Treat service name and container name as two facts, not one identifier.
Client command or database SQL?
SHOW USER and SHOW CON_NAME are SQL*Plus commands. The client handles them. They are not SQL, and they do not need a semicolon.
SELECT USER FROM dual is SQL. The database executes it. The semicolon submits the statement.
SELECT USER FROM dual;Choose the transaction result
EXIT and QUIT are the same command. With no clauses, SQL*Plus commits pending changes, logs out, and returns to the shell. That is the documented default. SET AUTOCOMMIT OFF does not change it. SET EXITCOMMIT is ON by default.
EXITWhen you mean rollback, say so on the EXIT line:
EXIT ROLLBACKEXIT SUCCESS ROLLBACK is the same rollback with an explicit success status. Either form ends the client session. The instance stays up.
Do not rely on closing the window. A graceful SQL*Plus exit — including end-of-file, Ctrl+D on UNIX or Ctrl+Z on Windows — commits. An abrupt kill or a dropped network can leave the server to roll back uncommitted work. When you mean rollback, type EXIT ROLLBACK.
Gotchas
sqlplus -L(or-l) logs on once and exits on failure. Without it, a failed connect waits for another username.- Easy Connect and TNS
SERVICE_NAMEtarget a service, notORACLE_SID. SHOW USERandSHOW CON_NAMEare SQL*Plus commands.SELECT USER FROM dual;is SQL.SYS_CONTEXT('USERENV','SERVICE_NAME')is the session’s service. It can differ fromSHOW CON_NAME.EXITandQUITcommit by default.SET AUTOCOMMIT OFFdoes not override that.- Ctrl+D (UNIX) or Ctrl+Z (Windows) is treated as
EXIT, so it commits. - Closing or killing the client is not a plan. Use
EXIT ROLLBACKwhen you mean rollback. EXITdoes not shut the instance down.
No comments:
Post a Comment