Sqlplus Common Command Daquan

Sql> Show All-View all 68 system variable values
Sql> Show user--show current connected users
Sql> Show Error--Show errors
Sql> set heading off-suppresses output column headers, default value on
Sql> set feedback off-suppresses the last-line count feedback, with the default value "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
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--Defaults to 15
Sql> set Long 1000--default is 80
Description
The Long value defaults to 80, and setting 1000 is 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 local variables, if you want to use a constant like a carriage return that is included in various displays,
--You can use the Define command to set
Sql> select &a from dual;
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 perform an identical SQL operation for each table under the database user, it is troublesome to type the SQL statement again and 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
Select ' Desc ' | | Tname from tab where tabtype= ' synonym ';
Query the number of records for all tables under the current user
Select ' Select ' | | tname| | ', COUNT (*) from ' | | tname| | '; ' from tab where tabtype= ' TABLE ';
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
Select ' Drop ' | | tabtype| | ' ' | | Tname from Tab;
Delete eligible users
Select ' Drop user ' | | username| | ' cascade; ' from All_users where user_id>25;
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 quickly compile with 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 creating 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 order by 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 Delete text from the current line c/tab 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< return >
If the S.sql file does not exist under the current directory, the system automatically generates the S.sql file,
Enter "SELECT * from Tab;" To save the exit.
Sql> @s< 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 following actions and execution results "spool" is saved to the 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
Being spooled to A.lst
Sql> Spool Off
Sql> Spool
Currently no spool
Exit Sql*plus
DESC table name shows the structure of the table
Show user displays the currently connected users
Show Error shows errors
Show all shows the values of all 68 system variables
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
("*" is indicated 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:
The c/emp_name/emp_age/executes an instruction that changes to:
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

Sqlplus Common Command Daquan