Sqlplus Use Tips

Tips [recommend]sqlplus use Tips
What are the Sql*plus system environment variables? How do I modify it?
[September 18, 2002 of Heaven's water, reading 21 people]



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--Displays current connected users
Sql> Show Error--Display errors
Sql> set heading off--Suppresses the output column header, the default value is on
Sql> set Feedback off--suppresses display of the last line of count feedback, the default value is "6 or more records, loopback on"
Sql> set timing on--default to OFF, set query time consuming, can be used to estimate the execution times of SQL statements, test performance
Sql> set Sqlprompt "sql>"--Set the default prompt, the default is "Sql>"
Sql> set Linesize 1000--Set screen display line width, default 100
Sql> set autocommit on--Sets whether autocommit is automatic, and defaults to OFF
Sql> set pause on-default to OFF, set pause, cause screen to stop, wait for the ENTER key to be pressed, and then display the next page
Sql> set arraysize 1--Default is 15
Sql> set Long 1000--default is 80
Description
The Long value defaults to 80, and set 1000 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 a local variable, if you want to use a constant like the carriage return included in various displays,
--You can use the Define command to set the
Sql> select &a from dual;
The 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 the database user each table to perform a same SQL operation, at this time, again, again and again type the SQL statement is very troublesome
Implementation method:
Sql> set heading off--Prohibit output column headers
Sql> set Feedback off--suppresses display of the last line of count feedback
Lists the definitions of all synonyms under the current user that 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 public for all eligible tables
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
----When you pour a database into a new server (database rebuild), you need to recompile the view again,
----Because of the problem with the connection of the table space view to tables in other table spaces, you can quickly compile with the 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 creation 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;
Sql*plus list of commonly used commands
[September 18, 2002 of Heaven's water, reading 36 people]




Software Environment:
1, Windows 98 Second Edition
2. Oracle Database version: Personal Oracle7 release 7.3.4.0.0
3, the Oracle installation path is: C:\ORAWIN95
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 to 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 deletes text from the current line c/tab result: select Tname from;
Del Delete when moving forward
del n Delete 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 line in the buffer
(l) IST m n Display buffer m to n rows
Run executes the command for the current buffer
/execute the current buffer command
R executes the command for the current buffer
The @ file name runs the SQL file that is transferred into memory, such as:
sql> Edit s< Carriage return >
If the S.sql file does not exist in the current directory, the system automatically generates the S.sql file.
Enter "SELECT * from Tab;", and save to exit.
sql> @s< Carriage return >
The system automatically queries all tables, views, and synonyms under the current user.
The @@ 文件名 name is used when calling an. sql file in the. sql file.
Save file name the command for the buffer is saved as a file, and the default file name extension is. sql
Get filename to disk SQL file
Start filename Run SQL file that is transferred into memory
Spool file name to the various operations and execution results "spool" is saved to 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
Taking a false offline to A.lst
Sql> Spool Off
Sql> Spool
Currently no spooling

Exit Exits Sql*plus
DESC table name shows the structure of the table
Show user displays the current connection user
Show Error displaying errors
Show all 68 system variable values
Edit opens the default editor, and the default is Notepad.exe in the Windows system, and the last SQL statement in the buffer is transferred to the Afiedt.buf file for editing
The edit filename edits the. sql file specified in the current directory into the editor
Clear screen clears the current display