Sqlplus Editing Command usage

Sqlplus Editing command line usage

Keywords: Oracle sqlplus command-line usage

Oracle database has a lot of query tools, such as the famous plsql, accustomed to the graphical interface, and then face the character interface Sqlplus is really a torture, but I do not know that the immortal said "existence is reasonable."

Sometimes it just needs to work in the context of a character interface, such as a customer site, Unix, Linux, AIX operating system, and so on, and this article discusses some simple editing commands in Sqlplus.

1. Login Database

Sqlplus username/Password [@ Instance name]
Example: Sqlplus test/[email protected]

Note: 1, instance name is the definition of your ORACLE_SID macro, note that there is no space between the password and the instance

2. Disconnect the database

SQL < Disconnect

3. Change database connection user

SQL < Connect system/[email protected]

4. Set the row size, page size

SQL < set Linesize 200

SQL < set pagesize 10

5, execute the script, "/" "R" ";" Can be

SQL < SELECT COUNT (*) from order

SQL </

6. List the previous script, L

Sql> L
1 SELECT * FROM order
num= ' 8888 '

1, 2 for the line number, with the * symbol for the forward

7, switch line number, directly enter the number line number can be

Sql> 1
1* SELECT * from order

8. Delete rows, Del n,n for line numbers
sql> L
1 Select * FROM Order
num= ' 8888 '
sql> del 2
sql> L
1* SELECT * from Order

9, add line, I activity line after inserting new line paste, a current line after pasting

sql> L
1* SELECT * from Order
sql> i where num= ' 1021 '
sql> L
1 Select * FROM Order
num= ' 1021 '

Sql> a TableB
1* SELECT * FROM Order TableB

* Note that there is a minimum of two cells after a, otherwise the operatortableb effect may occur

10, modify content c\ original content \ New content

Sql> C \tableb\tablea
1* SELECT * FROM order TableA

Summary below:

L--Column SQL

N--Toggles the active row (n represents the row number)

A--Increase after activity line (append)

I--insert new row after active row increase (imput)

C--replace (change)

Del N--delete row n

/--Execute SQL

11. Set the default editor

Above said Sqlplus some basic grammar, of course, if you are accustomed to use VI, you can set the default edit to VI, as follows:

Sql> Set _editor=vi

Sql> Ed

Type Ed, then sqlplus automatically switch to VI, modify SQL in VI, save, automatically re-read into the sqlplus buffer (similar to Sybase isql directly type VI).

You can also save the editor settings to the configuration file, each time you automatically use the configuration;

Modify the $oracle_home/sqlplus/admin/glogin.sql configuration file, add define _EDITOR=VI, Sqlplus start will read Glogin.sql script.

This article is from the "Dongfeng Makino" blog, please be sure to keep this source http://franklin5.blog.51cto.com/8722989/1583123

Sqlplus Editing Command usage