The show and set commands are two commands used to maintain the SQL * Plus system variables.
The show and set commands are two commands used to maintain the SQL * Plus system variables.
SQL> show all -- View all 68 system variable values
SQL> show user -- display the current connected user
SQL> show error -- Display error
SQL> set heading off -- disable the output column title. The default value is ON.
SQL> set feedback off -- disable counting feedback of the last row. The default value is "send back ON" for 6 or more records"
SQL> set timing on -- the default value is OFF. It sets the query time, which can be used to estimate the SQL statement execution time and test the performance.
SQL> set sqlprompt "SQL>" -- sets the default prompt. The default value is "SQL>"
SQL> set linesize 1000 -- set the row width on the screen. The default value is 100.
SQL> set autocommit ON -- sets whether to submit automatically. The default value is OFF.
SQL> set pause on -- the default value is OFF. When this parameter is set to suspend, the screen will be stopped. Wait for the ENTER key to display the next page.
SQL> set arraysize 1 -- default value: 15
SQL> set long 1000 -- the default value is 80.
Note:
The default value of long is 80, and 1000 is set to display more content, because the long data type is used in many data dictionary views, such:
SQL> desc user_views
The column name can be null or not.
-------------------------------------------
VIEW_NAME not null VARCHAR2 (30)
TEXT_LENGTH NUMBER
TEXT LONG
SQL> define a = ''' 20000101 12:01:01 ''' -- defines a local variable. If you want to use a constant similar to the carriage return value 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 '2014 12:01:01 'from dual
'1970: 01: 01
-----------------
12:01:01 20000101
Question:
1. You need to perform the same SQL operation on each table of the database user. It is very troublesome to type SQL statements once or once.
Implementation Method:
SQL> set heading off -- disable the output Column Title
SQL> set feedback off -- disable counting feedback of the last row
Lists definitions of all synonyms under the current user and can be used to test the real existence of synonyms.
Select 'desc' | tname from tab where tabtype = 'synonym ';
Queries the number of records of all tables under the current user
Select 'select' | tname | ''', count (*) from' | tname | ';' from tab where tabtype = 'table ';
Grant the select permission to all qualified tables to public.
Select 'Grant select on' | table_name | 'to public;' from user_tables where conditions;
Delete objects under a 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
---- After pouring the database to a new server (rebuilding the database), you need to recompile the view,
---- The connection from the tablespace view to other tablespaces may cause problems. You can use the language features of PL/SQL to quickly compile the table.
SQL> spool on. SQL
SQL> SELECT 'alter view' | TNAME | 'compile; 'FROM TAB;
SQL> SPOOL OFF
Run ON. SQL.
SQL> @ ON. SQL
Of course, you can also quickly authorize and create synonyms, such:
SQL> SELECT 'Grant SELECT on' | TNAME | 'to username; 'FROM TAB;
SQL> SELECT 'create synonym' | TNAME | 'for username. '| TNAME |'; 'FROM TAB;
Command list:
Assume that the currently executed command is: select * from tab;
(A) ppend: add text to the end of the current row in the buffer. Result: select * from tab order by tname;
(Note: a is followed by two spaces)
(C) hange/old/new replace the old text with the new text in the current row c/*/tname result: select tname from tab;
(C) hange/text: delete text from the current row. c/tab result: select tname from;
Del delete current row
Del n Delete row n
(I) add a line after the current line of nput text
(L) ist: Display All rows in the buffer
(L) ist n displays the nth row in the buffer
(L) ist m n: Display m to n rows in the buffer
Run
/Execute the command of the Current Buffer
R: Execute the Current Buffer command
@ Run the SQL file called into memory in the file name, for example:
SQL> edit s <回车>
If the s. SQL file does not exist in the current directory, the system automatically generates the s. SQL file,
Enter "select * from tab;" to save the disk and exit.
SQL> @ s <回车>
The system automatically queries all tables, views, and synonyms of the current user.
@ The file name is used when a. SQL file is called in the. SQL file.
Save the file name to save the buffer command as a file. The default file extension is. SQL.
Get file name refers to the SQL file stored in the disk
Start file name run SQL file transferred to memory
The spool file name stores all the subsequent operations and execution results on the disk. The default file extension is. lst.
Spool displays the current "offline" status
Spool off stop output
Example:
SQL> spool
SQL> spool
To A. LST
SQL> spool off
SQL> spool
No fake offline currently
Exit to exit SQL * PLUS
Desc table name display table structure
Show user: displays the current connected user
Show error Display error
Show all: show all 68 system variable values
Edit: Open the compile editor. In Windows, the statement is notepad.exe, and transfer the last SQL statement in the buffer to the afiedt. buf file for editing.
The edit file name transfers the specified. SQL file in the current directory to the editor for editing.
Clear screen clear current screen display
Ii. Oracle sqlplus statement Editing Command
First, enter the following command:
SELECT emp_id, emp_name
FROM Employees
The input command can be followed by a statement. For example, after the preceding statement is run, enter:
Input WHERE emp_age> 30
You can get the following command:
SELECT emp_id, emp_name
FROM Employees
WHERE emp_age> 30
The ln command is used to specify the nth line of the statement to be input. For example, after the preceding statement is run, enter:
L1 indicates that the currently selected statement line is
SELECT emp_id, emp_name
(The statement is preceded)
Command a is used to directly add characters at the end of the current line. For example, after the preceding statement is run, enter:
A, emp_dept
The executed command is:
SELECT emp_id, emp_name, emp_dept
FROM Employees
WHERE emp_age> 30
The c command is used to modify the characters in the current statement. For example, after the preceding statement is run, enter:
C/emp_name/emp_age:
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 command. For example, after the preceding statement is run, enter:
DEL 3
The executed command is:
SELECT emp_id, emp_age, emp_dept
FROM Employees
