The show and set commands are two commands used to maintain SQL*Plus system variables.
SQL> show all -- View all 68 system variable values
SQL> show user --Display the currently connected user
SQL> show error --show error
SQL> set heading off --Suppress output of column headings, the default value is ON
SQL> set feedback off --Suppress the display of the count feedback information of the last row. The default value is "For 6 or more records, feedback is ON"
SQL> set timing on --The default is OFF. Set the query time. It can be used to estimate the execution time of SQL statements and test performance.
SQL> set sqlprompt "SQL> " --Set the default prompt, the default value is "SQL> "
SQL> set linesize 1000 --Set the screen display line width, default 100
SQL> set autocommit ON --Set whether to automatically commit, the default is OFF
SQL> set pause on --The default is OFF. Setting pause will stop the screen display and wait for the ENTER key to be pressed before displaying the next page.
SQL> set arraysize 1 --default is 15
SQL> set long 1000 --default is 80
illustrate:
The long value defaults to 80. Setting 1000 is to display more content, because the 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 a carriage return included in various displays,
--Can be set using the define command
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
Question asked:
1. The user needs to perform the same SQL operation on every table under the database user. At this time, typing the SQL statement over and over again is a very troublesome implementation method:
SQL> set heading off --Suppress output of column headings
SQL> set feedback off --Suppress the display of the count feedback information of the last row. Lists the definitions of all synonyms under the current user, which 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 all tables that meet the conditions as public
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
----After pouring the database to a new server (database reconstruction), you need to recompile the view.
----Because there will be problems connecting this table space view to tables in other table spaces, you can use the language features of PL/SQL to compile quickly.
SQL> SPOOL ON.SQL
SQL> SELECT'ALTER VIEW '||TNAME||' COMPILE;' FROM TAB;
SQL> SPOOL OFF
Then execute ON.SQL.
SQL> @ON.SQL
Of course, authorization and creation of synonyms can also be done quickly, such as:
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 current execution command is: select * from tab;
(a)pend adds text to the end of the current line in the buffer a order by tname Result: select * from tab order by tname;
(Note: a is followed by 2 spaces)
(c)hange/old/new replaces the old text c/*/tname with new text in the current line. Result: select tname from tab;
(c)hange/text deletes the text c/tab from the current line. Result: select tname from;
del deletes the current line
del n deletes line n
(i)nput text adds a line after the current line
(l)ist displays all lines in the buffer
(l)ist n displays the nth line in the buffer
(l)ist mn displays m to n lines in the buffer
run executes the command in the current buffer
/ Execute the command of the current buffer
r Execute the command of the current buffer
@filename runs the sql file loaded into memory, such as:
SQL> edit s<Enter>
If the s.sql file does not exist in the current directory, the system automatically generates the s.sql file.
Enter "select * from tab;", save and exit.
SQL> @s<Enter>
The system will automatically query all tables, views, and synonyms under the current user.
@@File name is used when calling a .sql file in a .sql file
The save file name saves the command in the buffer as a file. The default file extension is .sql.
get file name transferred to the saved sql file
start filename runs the sql file loaded into memory
The spool file name "spools" various subsequent operations and execution results to a disk file. The default file extension is .lst.
spool displays the current "spool" status
spool off stops output example:
SQL> spool a
SQL> spool
Positive spooling to A.LST
SQL> spool off
SQL> spool
No spools currently
exit Exit SQL*PLUS
desc table name displays the structure of the table
show user displays the currently connected user
show error show error
show all displays all 68 system variable values
Edit opens the default editor, which is notepad.exe in Windows systems. Transfer the last SQL statement in the buffer into the afiedt.buf file for editing.
edit filename transfers the .sql file specified in the current directory into the editor for editing
clear screen clears the current screen display 2. Oracle sqlplus statement editing command First we enter such an instruction:
SELECT emp_id, emp_name
FROM Employees
The input command can add statements after the previous instruction. For example, after the above statement is executed, enter:
input WHERE emp_age > 30
You can get the following instructions:
SELECT emp_id, emp_name
FROM Employees
WHERE emp_age > 30
The ln command is used to specify the operation on the nth line statement entered. For example, enter after the above statement is run:
l1, the currently selected statement line is
SELECT emp_id, emp_name
(Indicated by "*" before the statement)
The a command is used to add characters directly to the end of the current line. For example, enter after the above statement is run:
a , emp_dept
The executed instruction becomes:
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, enter after the above statement is run:
c /emp_name/emp_age/ then the executed command 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. For example, enter after the above statement is run:
DEL 3
The executed instruction becomes:
SELECT emp_id, emp_age, emp_dept
FROM Employees
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/myyuren/archive/2009/12/21/5046608.aspx