oracle sqlplus common commands list

SQL> show all --View all 68 system variable values ​​
SQL> show user --Display the current connected user
SQL> show error　 --Show error
SQL> set heading off -- Disable output of column headers, the default value is ON
SQL> set feedback off -- Disable display of the count feedback information of the last row, the default value is "For 6 or more records, feedback ON"
SQL> set timing on --The default is OFF, set the query time, which 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, the default is 100
SQL> set autocommit ON --Set whether to automatically commit, the default is OFF
SQL> set pause on --The default is OFF, set Pause will stop the screen display, wait for the ENTER key to be pressed, and then display the next page
SQL> set arraysize 1 --The default is 15
SQL> set long 1000 --The default is 80
Instructions:
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 can be null value 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 similar one in various Constants such as carriage returns included in the display,
--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 raised:
1. The user needs to perform the same SQL operation on every table under the database user. At this time, it is very troublesome to type the SQL statement over and over again.
Implementation method:
SQL> set heading off --Suppress output of column titles
SQL> set feedback off --Suppress display of count feedback information for the last row
Lists the definitions of all synonyms under the current user, which can be used to test the authenticity of synonyms Existence
select 'desc '||tname from tab where tabtype='SYNONYM';
Query the number of records in all tables under the current user
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 if the conditions are met User
select 'drop user '||username||' cascade;' from all_users where user_id>25;
Quickly compile all views
----When pouring the database to the new server After (database reconstruction), the view needs to be recompiled.
----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　　 Add text to the end of the current line in the buffer a order by tname Result: select * from tab order by tname; hange/old/new Replace old text c/*/tname with new text in the current line Result: select tname from tab;
(c)hange/text Delete text c/tab from the current line Result: select tname from ;
del ist n　　 Display the nth line in the buffer
(l)ist m n　　 Display m to n lines in the buffer
run　　　 Execute the command of the current buffer
/ Execute the command of the current buffer
r If the s.sql file does not exist in the directory, the system will automatically generate the s.sql file,
Enter "select * from tab;", save and exit.
SQL> @s
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.
save file name saves the buffer command as a file. The default file extension is .sql
get file name call The sql file that is saved to the disk
start file name runs the sql file that is loaded into the memory
spool file name "spools" various subsequent operations and execution results and saves them to the disk file, the default file extension For .lst
spool 　　　 Display the current "spool" status
spool off　　 Stop output
Example:
SQL> spool a
SQL> spool
Positive spooling to A .LST
SQL> spool off
SQL> spool
Currently no spool
exit Exit SQL*PLUS
desc Table name displays the structure of the table
show user Displays the currently connected user
show error　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 Generationcred / 100%///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// before/ Edit in the file
edit file name Transfer the .sql file specified in the current directory into the editor for editing
clear screen Clear 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, enter after the above statement is run:
input WHERE emp_age > 30
You can get the following command:
SELECT emp_id, emp_name
FROM Employees
WHERE emp_age > 30
ln command is used to specify the input Operate on the nth line statement, for example, after the above statement is executed, enter:
l1, then the currently selected statement line is
SELECT emp_id, emp_name
(indicated by "*" before the statement)
a The command is used to add characters directly to the end of the current line. For example, after running the above statement, enter:
a, emp_dept
The executed command becomes:
SELECT emp_id, emp_name, emp_dept
FROM Employees
WHERE emp_age > 30
c command is used to modify the characters in the current statement. For example, after the above statement is run, enter:
c /emp_name/emp_age/, the executed command becomes:
SELECT emp_id, emp_age, emp_dept
FROM Employees
WHERE emp_age > 30
del n command is used to delete the nth line of instructions. For example, after running the above statement, enter:
DEL 3
then The executed command becomes:
SELECT emp_id, emp_age, emp_dept
FROM Employees

For more articles related to oracle sqlplus commonly used commands, please pay attention to the PHP Chinese website!