SQL*Plus Common Commands Summary¶
1. Connecting and Disconnecting¶
- Connect to the database:
sqlplus username/password@hostname:port/service_name
Or, after entering SQL*Plus:
CONNECT username/password@hostname:port/service_name;
- Disconnect from the database:
DISCONNECT;
- Exit SQL*Plus:
EXIT;
2. System Commands¶
- Show the current user:
SHOW USER;
- Change user:
CONNECT username/password;
- Check database version:
SELECT * FROM V$VERSION;
- Show instance name:
SELECT INSTANCE_NAME FROM V$INSTANCE;
- Set the display title:
TTITLE 'Your Title Here';
- Clear the title:
CLEAR TTITLE;
3. Query and Display Settings¶
- Set the number of rows displayed per page:
SET PAGESIZE number;
- Set column width:
COLUMN column_name FORMAT A20;
- Show row numbers:
SET NUMBER ON;
- Turn off row numbers:
SET NUMBER OFF;
- Set line width for SQL*Plus buffer:
SET LINESIZE number;
- Run an SQL file:
@filename.sql;
4. Result Formatting¶
- Describe the structure of a table:
DESCRIBE table_name;
- Set output results as CSV format:
SET COLSEP ',';
- Suppress column headers:
SET HEADING OFF;
- Show column headers:
SET HEADING ON;
- Display executed SQL commands:
SET ECHO ON;
- Turn off SQL command display:
SET ECHO OFF;
5. File Operations¶
- Save query results to a file:
SPOOL file_name;
SELECT * FROM table_name;
SPOOL OFF;
- Edit the current SQL statement:
EDIT;
6. Transaction Control¶
- Commit a transaction:
COMMIT;
- Rollback a transaction:
ROLLBACK;
7. Session Control¶
- Show current session information:
SELECT SID, SERIAL# FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
- Kill a session:
ALTER SYSTEM KILL SESSION 'sid,serial#';
8. Environment Settings¶
- Set output buffer size:
SET LONG number;
- Set date format:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
- Enable auto-commit:
SET AUTOCOMMIT ON;
- Disable auto-commit:
SET AUTOCOMMIT OFF;
9. Miscellaneous Commands¶
- Show last executed SQL statement:
LIST;
- Clear the current input buffer:
CLEAR BUFFER;
- Execute the last command again:
/;
---¶
T