Oracle Sqlplus Common Commands Encyclopedia

Sql> Show All--View all 68 system variable values

Sql> Show user--Displays current connected users

Sql> Show Error--Display errors

Sql> set heading off--Suppresses the output column header, the default value is on

Sql> set Feedback off--suppresses display of the last line of count feedback, the default value is "6 or more records, loopback on"

Sql> set timing on--default to OFF, set query time consuming, can be used to estimate the execution times of SQL statements, test performance

Sql> set Sqlprompt "sql>"--Set the default prompt, the default is "Sql>"

Sql> set Linesize 1000--Set screen display line width, default 100

Sql> set autocommit on--Sets whether autocommit is automatic, and defaults to OFF

Sql> set pause on-default to OFF, set pause, cause screen to stop, wait for the ENTER key to be pressed, and then display the next page

Sql> set arraysize 1--Default is 15

Sql> set Long 1000--default is 80

Description

The Long value defaults to 80, and set 1000 to show more content, because a long data type is used in many data dictionary views, such as:

Sql> desc user_views

Column name nullable value no type

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

View_name not NULL VARCHAR2 (30)

Text_length number

TEXT LONG

sql> Define a = ' 20000101 12:01:01 '--Define a local variable, if you want to use a constant like the carriage return included in various displays,

--You can use the Define command to set the

Sql> select &a from dual;

The original value 1:select &a from dual

New value 1:select ' 20000101 12:01:01 ' from dual

' 2000010112:01:01

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

20000101 12:01:01

Questions raised:

1, the user needs to the database user each table to perform a same SQL operation, at this time, again, again and again type the SQL statement is very troublesome

Implementation method:

Sql> set heading off--Prohibit output column headers

Sql> set Feedback off--suppresses display of the last line of count feedback

Lists the definitions of all synonyms under the current user that can be used to test the true existence of synonyms

Select ' Desc ' | | Tname from tab where tabtype= ' synonym ';

Query the number of records in all tables under the current user

Select ' Select ' | | tname| | ', COUNT (*) from ' | | tname| | '; ' from tab where tabtype= ' TABLE ';

Grant SELECT permission to public for all eligible tables

Select ' Grant Select on ' | | table_name| | ' to public ', ' from user_tables where conditions ';

Delete various objects under the user

Select ' Drop ' | | tabtype| | ' '|| Tname from Tab;

Delete qualified users

Select ' Drop user ' | | username| | ' Cascade ' from All_users where user_id>25;

Quickly compile all views

----When you pour a database into a new server (database rebuild), you need to recompile the view again,

----Because of the problem with the connection of the table space view to tables in other table spaces, you can quickly compile with the Pl/sql language features.

Sql> SPOOL on. Sql

Sql> SELECT ' ALTER VIEW ' | | tname| | ' COMPILE; ' From TAB;

Sql> SPOOL off

Then execute the on.sql.

Sql> @ON. Sql

Of course, authorization and creation synonyms can also be done quickly, such as:

sql> Select ' GRANT select on ' | | tname| | ' to user name; ' From TAB;

sql> SELECT ' CREATE synonym ' | | tname| | ' for user name. ' | | tname| | '; ' From TAB;

List of commands:

Assume that the current execution command is: SELECT * from Tab;

(a) ppend add text to buffer current line end a order by Tname result: SELECT * from tab to Tname;

(Note: A followed by 2 spaces)

(c) Hange/old/new replaces the old text with the new text in the current line c/*/tname result: select Tname from Tab;

(c) Hange/text deletes text from the current line c/tab result: select Tname from;

Del Delete when moving forward

del n Delete nth row

(i) Nput text adds a row after the current line

(l) IST displays all rows in the buffer

(l) ist n shows the nth line in the buffer

(l) IST m n Display buffer m to n rows

Run executes the command for the current buffer

/execute the current buffer command

R executes the command for the current buffer

The @ file name runs the SQL file that is transferred into memory, such as:

sql> Edit s< Carriage return >

If the S.sql file does not exist in the current directory, the system automatically generates the S.sql file.

Enter "SELECT * from Tab;", and save to exit.

sql> @s< Carriage return >

The system automatically queries all tables, views, and synonyms under the current user.

The @@ file name is used when calling an. sql file in the. sql file.

Save file name the command for the buffer is saved as a file, and the default file name extension is. sql

Get filename to disk SQL file

Start filename Run SQL file that is transferred into memory

Spool file name to the various operations and execution results "spool" is saved to disk file, the default file name extension is. lst

Spool Displays the current spool status

Spool off stop output

Cases:

Sql> Spool A

Sql> Spool

Taking a false offline to A.lst

Sql> Spool Off

Sql> Spool

Currently no spooling

Exit Exits Sql*plus

DESC table name shows the structure of the table

Show user displays the current connection user

Show Error displaying errors

Show all 68 system variable values

Edit opens the default editor, and the default is Notepad.exe in the Windows system, and the last SQL statement in the buffer is transferred to the Afiedt.buf file for editing

The edit filename edits the. sql file specified in the current directory into the editor

Clear screen clears the current display

Two. Oracle sqlplus Statement Edit command

First we enter this instruction:

SELECT emp_id, Emp_name

From Employees

The input command can then add a statement after the previous instruction, such as after the statement is run:

Input WHERE emp_age > 30

The following instructions can be obtained:

SELECT emp_id, Emp_name

From Employees

WHERE emp_age > 30

The ln command is used to specify the operation of the nth line of input, such as after the statement is run:

L1 the currently selected statement line is

SELECT emp_id, Emp_name

(There is a "*" expression before the statement)

The a command is used to add characters directly to the end of the current line, such as after the statement is run:

A, emp_dept

The instruction that is executed becomes:

SELECT emp_id, Emp_name, emp_dept

From Employees

WHERE emp_age > 30

The C command modifies characters in the current statement, such as after the statement is run:

c/emp_name/emp_age/the instruction that is executed becomes:

SELECT emp_id, Emp_age, emp_dept

From Employees

WHERE emp_age > 30

The del n command is used to delete the nth line of instructions, such as after the statement is run:

DEL 3

The instruction that is executed becomes:

SELECT emp_id, Emp_age, emp_dept

From Employees