Sqlplus Command Learning

Sqlplus Command Learning

$ sqlplus/as SYSDBA

Sql> Help Index can enter Sqlplus command

Enter help [topic] for help.

@ COPY PAUSE SHUTDOWN

@@ Define PRINT SPOOL

/DEL PROMPT SQLPLUS

ACCEPT DESCRIBE QUIT START

APPEND DISCONNECT RECOVER STARTUP

ARCHIVE LOG EDIT REMARK STORE

ATTRIBUTE EXECUTE Repfooter TIMING

Break EXIT Repheader Ttitle

Btitle GET RESERVED WORDS (SQL) undefine

Change help RESERVED WORDS (PL/SQL) VARIABLE

CLEAR HOST RUN whenever OSError

COLUMN INPUT SAVE whenever SQLERROR

COMPUTE LIST SET XQUERY

CONNECT PASSWORD SHOW

Sqlplus a local SQL statement that allows you to enter SQL statements to control access to the database

1 identification number of the Scott.emp table, name

Sql> select Empno, ename from Scott.emp; Semicolon End carriage return execution

EMPNO ename

---------- ----------

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

2 How to view statements for current SQL buffer

? Command-----> See Help

? List

Sql>? List

LIST

----

Lists one or more lines of the recently executed SQL command

or PL/SQL block which is stored in the SQL buffer. Enter LIST with

No clauses to list all lines. In Sql*plus command-line can also

Use ";" to list all the lines in the SQL buffer. The buffer has no

Command History list and does not record sql*plus commands.

L[ist] [n | n m | n * | n Last | * | * n | * last | Last]

Input L

3 I want to modify a buffer of statements, such as adding a condition ename = ' SCOTT ';

Sql>? EDIT

EDIT

----

Invokes calls an operating system text editor in the contents of the

Specified file or on the contents of the SQL buffer. The buffer

have no command history list and does not record sql*plus commands.

Ed[it] [File_name[.ext]]

Sql> Ed

Wrote file Afiedt.buf

38

?

?

Q exit

Be sure to specify which text editor to use in Linux

Notepad for Windows

How to specify-----with that editor ""??? Variable designation

Method 1: Temporarily Specify

Define _editor=vi

Input ED

Method 2: Permanently specify that the variable be placed in the. bash_profile

[Email protected] ~]$ Vim/home/oracle/.bash_profile

Join: Export EDITOR=VI

[Email protected] ~]$ Source/home/oracle/.bash_profile

[Email protected] ~]$ echo $EDITOR

Vi

Ed Modify

sql> L View

1 select empno, ename from Scott.emp

The Where ename = ' SCOTT '

4 How to execute a statement in buffer cache

Sql>? /

/(Slash)

---------

Executes the most recently executed SQL command or PL/SQL block

Which is stored in the SQL buffer. Use slash (/) at the command

Prompt or line number prompt in Sql*plus command line. The buffer

have no command history and does not record sql*plus commands.

Sql> L

1 select empno, ename from Scott.emp

The Where ename = ' SCOTT '

sql>/Execute SQL statements in SQL buffer

EMPNO ename

---------- ----------

7788 SCOTT

5 Save code A script, next execution

Sql>? SAVE

SAVE

----

Saves the contents content of the SQL buffer in a script. The

Buffer has no command history list and does not record sql*plus commands.

SAV[E] [FILE] file_name[.ext] [cre[ate] | Rep[lace] | App[end]]

Save File Command Create/replace/append

Default creation

Replace

Additional

Note: Scripts must have a directory with permissions (Oracle user has permission to write)

Save/u01/app/oracle/1.sql

6 after saving the script, I think of the operating system to look at, and then come back to execute the sqlplus command (do not exit Sqlplus to execute the command on the system)

Sql>? HOST

HOST

----

Executes an operating system command without leaving Sql*plus.

Enter the HOST without command to the display an operating system prompt.

You can and then enter multiple operating system commands.

HO[ST] [command]

Usage 1:host Enter----"Operating system-----" exit-----"Sqlplus

Usage 2:host Operating system commands

Sql> host Ls-l/u01/app/oracle

Total 24

-rw-r--r--1 Oracle oinstall Oct 15:26 1.sql

Drwxr-x---3 oracle oinstall 4096 Oct 14:10 admin

Clear screen Clear---->host clear

Copy 1.sql to/tmp directory

Sql> Host Cp/u01/app/oracle/1.sql/tmp

Sql> Host LS-L/tmp

Total 524

-rw-r--r--1 Oracle oinstall Oct 15:30 1.sql

Remove 1.sql AT/tmp

Host Rm/tmp/1.sql

Host all systems are supported

Can be used under the Linux platform! Replace host

7 Exit Sqlplus

Exit

Quit

8 There is an SQL script in/u01/app/oracle that executes the script in Sqlplus???

Sql>? @

@ ("at" sign)

-------------

Runs the Sql*plus statements in the specified script. The script can be

Called from the local file system or a Web server.

or with

Sql>? Start

START

-----

Runs the Sql*plus statements in the specified script. The script can be

Called from the local file system or a Web server.

STA[RT] {Url|file_name[.ext]} [arg ...]

@/u01/app/oracle/1.sql

Start/u01/app/oracle/1.sql

9 will save the output on the screen to a file-----"for the log

? Spool

Usage

Spool a file

Execute command

Spool off end

10 How to switch between users

Conn Account name/password

Sql> Show user;

USER is "SYS"

Sql> Conn Scott/oracle

Sconnected.

Sql>show user;

USER is "SCOTT"

Sql> Conn Hr/hr

Connected.

Sql> Conn/as SYSDBA

Connected.

Sql> Show user;

USER is "SYS"

Sqlplus/nolog login requires connect connection database

Sql> Conn Scott/oracle

Connected.

Sql> Show user;

USER is "SCOTT"

This article is from a "a little" blog, make sure to keep this source http://pengai.blog.51cto.com/6326789/1891055

Sqlplus Command Learning