Sqlplus Command Manual

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 replace the old text with 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 current line
del n delete nth row
(i) nput text adds a line after the current line
(l) IST display buffer all rows
(l) ist n Display buffer nth row
(l) IST m n Display Buffer Medium m to n rows
Run command to execute current buffer
command to execute current buffer
R command to execute current buffer
@ file name run the SQL files into memory, such as:
SQL edit S carriage return
If the current directory does not exist S.sql file, the system automatically generates the S.sql file, where
enters select  from tab; The
SQL @s carriage return
system automatically queries all tables, views, and synonyms under the current user. The
@@ 文件名 name in the. sql file invokes a. sql file when you use the
Save file name in the buffer command file, and the default file name extension is. SQL
Get file name calls into disk SQL file
Start file name The SQL file that is running into memory
Spool file name the various operations and execution results taken after this is spooled to disk file, the default file name extension is. LST
Spool Displays the current spool status
spool off stop output
Example:
SQL spool a
SQL spool
is spooled to a.lst
SQL spool off
SQL spool
not currently spooled

exit exit sqlplus
Desc Table name Displays the structure of the table
Show user displays the currently Connected users
Show error shows errors
Show all Show all system variable values
Edit to open the default editor, the default is Notepad.exe in the Windows system, the last SQL statement in the buffer into the Afiedt.buf file for editing
Edit file name Inserts the. sql file specified in the current directory into the editor for editing
Clear screen clears the current display

First we enter such an instruction:
SELECT emp_id, Emp_name
from Employees
Input command can then add a statement after the previous instruction, For example, after the above statement is run, enter:
input WHERE emp_age >
to get the following command:
SELECT emp_id, Emp_name
from Employees
WHERE emp_age  > The
Ln command is used to specify the operation of the nth line of input statements, such as entering after the statement is run:
L1 the currently selected statement line is
Select emp_id, Emp_name
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
executes an instruction that changes to:
SELECT emp_id, Emp_name, emp_dept
F ROM Employees
WHERE emp_age  a
C command is used to modify the characters in the current statement, such as when the above statement is run:
C Emp_nameemp_age executes the instruction into:
SELECT emp_id, Emp_age, emp_dept
from Employees
WHERE emp_age  the
del n command is used to delete the nth-line instruction, for example, after the above statement is run:
del 3
The executed instruction becomes:
SELECT emp_id, Emp_age, emp_dept
from Employees