SQLPLUS Skills

Software Environment:

1, Windows NT4.0 Oracle 8.0.4

2, the Oracle installation path is: C: / ORANT

Implementation method: show and set commands are two commands for maintaining SQL * PLUS system variables

SQL> SHOW All - View all 68 system variable values

SQL> Show User - Show current connection users

SQL> Show Error - Display Error

SQL> SET Heading Off - Disable output column headings, the default is ON

SQL> Set feedback OFF - Disable the counting feedback information of the last line, the default value is "6 or more records, return to"

SQL> SET TIMING ON - The default is OFF, set the query consumption, can be used to estimate the execution time, test performance of the SQL statement

SQL> SET SQLPROMPT "SQL>" - Set the default prompt, the default is "SQL>"

SQL> SET LINESIZE 1000 - Set the screen display line width, default 100

SQL> Set AutoCommit ON - Set whether it is automatically submitted, default is OFF

SQL> SET PAUSE ON - The default is OFF, set the pause, make the screen display stop, wait to press the Enter button, then display the next page

SQL> SET Arraysize 1 - default is 15

SQL> Set long 1000 - defaults to 80

Note: long value is default 80, setting 1000 is to display more content because many data types are used in the Dictionary view, such as:

SQL> Desc user_views column name can be empty value -------------------------------------- ---- View_name not null varchar2 (30) Text_length Numbertext Long

SQL> Define a = '' '20000101 12:01:01' '- Define local variables, if you want to use a constant similar to the carriage return included in various displays, - can be set with a 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

The problem is proposed: 1. Users need to execute one of the same SQL operations on each table under the database user. At this time, it is very troublesome to type the SQL statement over again.

Implementation Method: SQL> SET Heading Off - Disable Output Column Title SQL> Set Feedback Off - Disabled Display Fast Row Count Feedback Information

List the definition of all synonyms under the current user, can be used to test the true presence of synonyms SELECT 'DESC' || TNAME from Tab WHERE TABTYPE = 'synonym'; query the number of records of all tables under the user | SELECT 'SELECT' '' "| | TNAME || '' ', count (*) from' || TNAME || ';' from tab where tabtype = 'table';

Give all the qualified tables to the select author to publicSelect 'grant select on' || table_name || 'to public;' from user_tables where "Condition";

Delete users under various objects 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 you pour the database into the new server (database reconstruction), you need to recompile the view again, --- Because the tablespace view is connected to the table of other tablespace There is a problem, which can be quickly compiled with the language characteristics of PL / SQL.

SQL> Spool on.sqlsql> select'Alter view '|| tname ||' Compile; 'from tab; sql> spool off then executes ON.SQL. SQL> @ on.sql, of course, authorize and create synonyms can also be made quickly, such as: SQL> SELECT 'GRANT SELECT ON' || TNAME || 'to user name;' from tab; sql> select 'create synonym' || TNAME || 'for username.' || tname || ';' from tab;

SQL * PLUS common command list

[Paradise Water September 18, 2002, 36 readers] Software Environment: 1. Windows 98 Second Edition 2, Oracle Database Version: Personal Oracle7 Release 7.3.4.0.03, Oracle Installation Path is: C: / ORAWIN95

Command List: Suppose the current execution command is: select * from tab;

(a) Ppend Add Text to Buffer Current Row A ORDER BY TNAME Results: Select * from Tab ORDER BY TNAME; (Note: A Back with 2 Spaces) (C) Hange / Old / New In the current new Text replacing the old text c / * / tname results: select tname from tab; (c) Hange / text Delete text C / Tab from the current row: SELECT TNAME FROM; DEL Delete Del N Delete Nth Ring (i) NPUT text Add a line (L) IST display buffer after the current line (L) IST N display buffer in the N-line (1) IST MN display buffer in the N-line RUN executes the command of the current buffer / Execute the command R in the current buffer to execute the command @ file name of the current buffer, run the SQL file that is transferred to the memory, such as: SQL> Edit S If there is no S.sql file in the current directory, the system is automatically generated S.SQL file, in which "Select * from Tab;", the storage is exited.

SQL> @S The system will automatically query all the tables, views, synonyms under the current user.

The @@ file name is called to make a .sql file in the .sql file.

The Save file name uses the command of the buffer in a file mode store, the default file extension is called the SQL file START file name that is called into the storage disk. SQL file.

The spool file name puts this after the various operations and execution results "Spoolery" ready-on-board to disk files, the default file extension is .lstspool Display current "spool" status spool off output

Example: SQL> Spool Asql> Spool Positive Offline to a.lstsql> Spool Offsql> Spool Current No Spanning

EXIT Exit SQL * Plusdesc Table Name Display Table Structure Show User Display Current Connection User Show Error Show Error SHOW ALL Show All 68 System Variable Value Edit Open the Default Editor, the default is NotePad.exe in the Windows system, the buffer is last A SQL statement is transferred to the Afiedt.buf file to edit the Edit file name to edit the .sql file specified in the current directory.

Clear Screen Clear Current Screen Display

SQLPlus username / passwd @ serviceename