Sqlplus Edit command How to use

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

Sometimes it is necessary to work in a character interface environment, such as customer site, Unix, Linux, AIX operating system, etc., this article discusses some simple editing commands in Sqlplus.

1. Log in to the database

Sqlplus username/Password [@ Instance name]

For example: Sqlplus Test/test@orac

Note: 1, the instance name is your ORACLE_SID macro definition, 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/oracle@orac

4, set the row size, page size

SQL < set Linesize 200

SQL < set pagesize 10

5. Execute script, "/" "R" ";"

SQL < SELECT COUNT (*) from order

SQL </

6, List the previous script, L

Sql> L

1 SELECT * FROM order

2* where 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 Row, Del N,n represents the number of line number

Sql> L

1 SELECT * FROM order

2* where 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 paste

Sql> L

1* SELECT * from order

sql> I where num= ' 1021 '

Sql> L

1 SELECT * FROM order

2* where num= ' 1021 '

Sql> a TableB

1* SELECT * FROM Order TableB

* Note A minimum of two blank after a, or you may see a operatortableb effect

10, modify content C original content new content

Sql> C Tablebtablea

1* SELECT * FROM order TableA

In summary:

L--Column SQL

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

A--increase after the activity line (append)

I--insert new row increment after active row (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 using 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, in VI Modify SQL, save, will automatically reread into the sqlplus buffer (like Sybase isql directly type VI).

You can also save the editor settings to the configuration file, which is automatically used each time.

Modify the $oracle_home/sqlplus/admin/glogin.sql configuration file, add define _EDITOR=VI, and read the Glogin.sql script when the Sqlplus is started.