Oracle SQL*PLUS
SQL*Plus Command Reference
Список команд
Command Description

@ ("at" sign)

Runs the SQL*PLus statements in the specified script. The script can be called from the local file system or from a web server.

@@ (double "at" sign)

Runs a script. This command is similar to the @ ("at" sign) command. It is useful for running nested scripts because it looks for the specified script in the same path as the script from which it was called.

/ (slash)

Executes the SQL command or PL/SQL block.

ACCEPT

Reads a line of input and stores it in a given user variable.

APPEND

Adds specified text to the end of the current line in the buffer.

ARCHIVE LOG

Starts or stops the automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files.

ATTRIBUTE

Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes.

BREAK

Specifies where and how formatting will change in a report, or lists the current break definition.

BTITLE

Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition.

CHANGE

Changes text on the current line in the buffer.

CLEAR

Resets or erases the current clause or setting for the specified option, such as BREAKS or COLUMNS.

COLUMN

Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns.

COMPUTE

Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions.

CONNECT

Connects a given user to Oracle.

COPY

Copies results from a query to a table in a local or remote database.

DEFINE

Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.

DEL

Deletes one or more lines of the buffer.

DESCRIBE

Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.

DISCONNECT

Commits pending changes to the database and logs the current user off Oracle, but does not exit SQL*Plus.

EDIT

Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer.

EXECUTE

Executes a single PL/SQL statement.

EXIT

Terminates SQL*Plus and returns control to the operating system.

GET

Loads a host operating system file into the SQL buffer.

HELP

Accesses the SQL*Plus help system.

HOST

Executes a host operating system command without leaving SQL*Plus.

INPUT

Adds one or more new lines after the current line in the buffer.

LIST

Lists one or more lines of the SQL buffer.

PASSWORD

Allows a password to be changed without echoing the password on an input device.

PAUSE

Displays the specified text, then waits for the user to press [Return].

PRINT

Displays the current value of a bind variable.

PROMPT

Sends the specified message to the user's screen.

QUIT

Terminates SQL*Plus and returns control to the operating system. QUIT is identical to EXIT.

RECOVER

Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.

REMARK

Begins a comment in a script.

REPFOOTER

Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.

REPHEADER

Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.

RUN

Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.

SAVE

Saves the contents of the SQL buffer in a host operating system file (a script).

SET

Sets a system variable to alter the SQL*Plus environment for your current session.

SHOW

Shows the value of a SQL*Plus system variable or the current SQL*Plus environment.

SHUTDOWN

Shuts down a currently running Oracle instance.

SPOOL

Stores query results in an operating system file and, optionally, sends the file to a printer.

START

Executes the contents of the specified script. The script can only be called from a url.

STARTUP

Starts an Oracle instance and optionally mounts and opens a database.

STORE

Saves attributes of the current SQL*Plus environment in a host operating system file (a script).

TIMING

Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers.

TTITLE

Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.

UNDEFINE

Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).

VARIABLE

Declares a bind variable that can be referenced in PL/SQL.

WHENEVER OSERROR

Performs the specified action Exits SQL*Plus if an operating system command generates an error.

WHENEVER SQLERROR

Performs the specified action Exits SQL*Plus if a SQL command or PL/SQL block generates an error.
Выполнеие скриптов
Запуск командного файла 
sqlplus login/password@chelsea @setup.sql
Пример рекурсивного запуска скриптов 
SET SERVEROUTPUT OFF
SET FEEDBACK OFF
SET VERIFY OFF

@step1.sql
@step2.sql
@step3.sql
@step4.sql

EXIT
Запрос ввода данных из скрипта 
ACCEPT ADMIN_NAME CHAR PROMPT 'Enter name of security administrator:' 


-- Insert user
insert into i$sdd_user(id,login) 
select NVL((select max(id) from i$sdd_user),0)+1, '&ADMIN_NAME' from dual;

-- Set user role
insert into i$sdd_userrole(userid,roleid)
select (select max(id) from i$sdd_user), 2 from dual;
Запрос ввода данных из скрипта 
ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default]
[PROMPT text|NOPR[OMPT]] [HIDE]
ACCEPT ADMIN_NAME CHAR PROMPT 'Enter name of security administrator:' 

-- Insert user
insert into i$sdd_user(id,login) 
select NVL((select max(id) from i$sdd_user),0)+1, '&ADMIN_NAME' from dual;

-- Set user role
insert into i$sdd_userrole(userid,roleid)
select (select max(id) from i$sdd_user), 2 from dual;
Редактирование
  • A[PPEND] text - добавляет текст в буфер. Послндняя выполненная команда находится в буфере. Добавление идет в одну строку.
  • C[HANGE] sepchar old [sepchar [new [sepchar]]] - делает контекстную замену в буфере CHANGE /aq/aw
  • CLEAR BUFFER - очищает буфер
  • DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST] - удаляет строки в буфере
  • ED[IT] [file_name[.ext]] - редактирование буфера во внешнем редакторе
  • GET [FILE] file_name[.ext] [LIS[T]|NOL[IST]] - загрузить файл в буфер
  • I[NPUT] [text] - добавляет новую строку в буфер
  • L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST] - список строк буфера
  • SAV[E] [FILE] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]] - записывает SET EDITF[ILE] file_name[.ext] - имя файла используемого в команде EDIT
-
-
Форматирование
  • ATTRIBUTE [type_name.attribute_name [option ...]]
    • ALI[AS] alias
    • CLE[AR]
    • FOR[MAT] format
    • LIKE {type_name.attribute_name|alias}
    • ON|OFF
    ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20
  • BRE[AK] [ON report_element [action [action]]]
    report_element - {column|expr|ROW|REPORT}
    action - [SKI[P] n|[SKI[P]] PAGE][NODUP[LICATES]|DUP[LICATES]] 
    BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID - 
SKIP 1 ON SALARY SKIP 1

SELECT DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID, JOB_ID, SALARY, LAST_NAME;<        
     

    
 
 /FONT >
  • TTI[TLE] text|ON - задает заголовок, который отображается в начале каждой страницы
  • BTI[TLE] text|ON - задает подножье, которое отображается внизу каждой страницы
  • CLEAR BRE[AKS] - сбрасывает BREAKS
COL[UMN] [column option] - установка параметров колонок. Следует использовать псевдонимы.
  • CLE[AR] - сброс всех форматов
  • HEA[DING] text - устанавливает заголовок колонки, |-новая строка.
  • FOR[MAT] format - устанавливает формат
  • NUL[L] text - текст для NULL значения
  • PRI[NT] - отображать колонку
  • NOPRI[NT] - скрыть колонку
  • ALI[AS] alias - назначает псевдоним колонке
  • JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} - выравнивает заголовок
  • NEW_V[ALUE] variable - задает переменную со значением колонки
COLUMN JOB_ID NOPRINT NEW_VALUE JOBVAR
COLUMN TODAY  NOPRINT NEW_VALUE DATEVAR
BREAK ON JOB_ID SKIP PAGE ON TODAY
TTITLE CENTER 'Job Report' RIGHT DATEVAR  SKIP 2 -
LEFT 'Job:     ' JOBVAR SKIP 2
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') TODAY,
LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, DEPARTMENT_ID
FROM EMPLOYEES WHERE JOB_ID IN ('MK_MAN', 'SA_MAN')
ORDER BY JOB_ID, LAST_NAME;
Переменные
SET system_variable value - установка значения системной переменной
  • SET VERIFY ON - включает отображение текста запроса перед выполнением
  • SHOW system_variable - просмотр значения
  • SET ARRAY[SIZE] n - размер fetch
  • SET FEED[BACK] n|ON - отображать число записей, если в результате больше N строк
  • SET HEA[DING] ON - включает отображение заголовков колонок
  • SET LONG n - устанавливает максимальную ширину дисплея
  • SET ECHO {ON|OFF}- при выполнении START выводятся выполняемые команды
  • SET EMB[EDDED] {ON|OFF} - вывод с новой страницы или нет
  • SET ESC[APE] {\|c|ON|OFF} - устанавливает escape символ
  • SET LIN[ESIZE] {80|n} - количество символов в строке
  • DEF[INE] [variable]|[variable = text] - устанавливает значение переменной
  • PRI[NT] [variable ...] - вывод значения на экран
  • PRO[MPT] [text] - вывод текста на экран
  • REM[ARK] - комментарий
Изменение параметров SPFILE-а
  • Просмотр параметра
    show parameter AUDIT_TRAIL;
NAME            TYPE       VALUE
--------------------------------
audit_trail     string     NONE
  • Установка параметра
    alter system set AUDIT_TRAIL='DB'
    или 
    alter system set AUDIT_TRAIL='DB' SCOPE=spfile;
STOP/START
  • Заходим на машине oracle
    sqlplus "/ as sysdba"
  • Тормозим
    shutdown immediate;
  • Стартуем
    statup;
  • Стартуем монопольно
    statup mount force;
...
alter database open;
Другие соманды
  • {EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]
  • HELP [topic]
  • HO[ST] [command] - выполняет DOS команду
Hosted by uCoz