Sqlplus Command Manual

Show Errorshow Allshow Usersqlplus

The show and set commands are two commands for maintaining Sqlplus system variables:

Sql> Show All--view all system variable values

Sql>show user--show current connected users

Sql>show Error--Display errors (when compiling function,procedure,package,pl/sql, you can view specific error hints)

Sql>set sqlprompt SQL--Set default prompt, default value is SQL

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

Sql>set autocommit on--Set whether to submit automatically, default to OFF

Sql>set pause on--default is off, set pause, will stop the screen display, wait for the ENTER key, and then display the next page

Sql>set arraysize 1--Default is 15

Sql>set Long 1000--default is 80

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

Sql>set feedback off-suppresses counting feedback for the last line, with a default value of 6 or more records, loopback on

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

Questions raised:

1, the user needs to perform an identical SQL operation for each table under the database user, it is troublesome to type the SQL statement over and over again.

Implementation method:

SQL set heading off--Suppresses output column headers

SQL set feedback off-suppresses the counting feedback for the last line

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

sql> SELECT ' desc ' tname from tab where tabtype= ' synonym ';

Grant the SELECT permission to public for all tables that match the criteria

Select ' Grant SELECT on ' table_name ' to public; ' from User_tables where condition;

Delete various objects under the user

Sql> set Heading off

Sql> Set Feedback off

sql> select ' Drop ' tabtype,tname '; ' from Tab;

Drop TABLE countries;

Drop TABLE departments;

Drop TABLE EMPLOYEES;

Drop TABLE JOBS;

Drop TABLE job_history;

Drop TABLE LOCATIONS;

Drop TABLE regions;

Drop VIEW Emp_details_view;

Drop VIEW Extra_pay;

Drop TABLE emp_picture;

Drop TABLE personnel;

Drop TABLE review_table;

Delete eligible users

Sql> select ' Drop user ' username ' cascade; ' from All_users where user_id=33;

Drop user HR cascade;

2, quickly compile all views

----When the database is dumped on a new server (database rebuild), the view needs to be recompiled again.

----because of problems with the table-space view's connection to tables in other tablespaces, you can use Plsql's language features to quickly compile.

Sql> Spool View.sql

Select ' Alter View ' tname ' compile; ' from Tab;

Sql> Spool Off

Then execute the on.sql.

Sql> @view. Sql

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

Select ' Grant select on ' Tname ' to Watson; ' from Tab;

3, command list:

Assume that the current execution command is: Select from tab where rownum<10;

(a) ppend add text to buffer current line end a order by Tname

Sql> a order by Tname;

Result: 1* select * from tab where rownum<10 order by tname

(Note: A followed by 2 spaces)

(c) Hangeoldnew replaces the old text with the new text in the current line ctname result: select Tname from Tab;

(c) Hangetext Delete text from the current line ctab result: select Tname from;

Del Delete when moving forward

Del n Deletes the 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 row in the buffer

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

Run command to execute the current buffer

command to execute the current buffer

R command to execute the current buffer

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

SQL Edit S Enter

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

In which select from tab is entered, and the disk exits.

SQL @s Carriage return

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

@@ 文件名 name is used when calling a. sql file in a. sql file

The Save file name Buffer command is saved as a file, and the default file name extension is. sql

Get file name in SQL file to disk

Start file name runs the SQL file that is transferred into memory

Spool file name the various operations and execution results taken after this is spooled to disk files, with the default file name extension. lst

Spool showing the current spool status

Spool off stop output

Cases:

SQL Spool A

SQL Spool

Being spooled to A.lst

SQL Spool Off

SQL Spool

Currently no spool

Exit Sqlplus

DESC table name shows the structure of the table

Show user displays the currently connected users

Show Error shows errors

Show all displays all system variable values

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

The edit file name puts the. sql file specified in the current directory into the editor for editing

Clear screen clears the current display

Two Oracle sqlplus Statement Editing commands

First we enter such an instruction:

SELECT emp_id, Emp_name

From Employees

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

Input WHERE emp_age > 30

The following command 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 statement that is entered, such as after the statement is run:

L1 the currently selected statement line is the

SELECT emp_id, Emp_name

(There is a representation before the statement)

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

A, emp_dept

Then the executed instruction becomes:

SELECT emp_id, Emp_name, emp_dept

From Employees

WHERE Emp_age 30

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

C emp_nameemp_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 instruction, for example, after the above statement is run:

DEL 3

Then the executed instruction becomes:

SELECT emp_id, Emp_age, emp_dept

From Employees