[Recommended] SQLPLUS Skills
What are the SQL * PLUS system environment variables? How to modify? [Paradise Water September 18, 2002, the number of readers 21] Software environment: 1. Windows NT4.0 Oracle 8.0.42, ORACLE installation path is: c: / ORANT implementation method: show and set commands are two Command SQL> SHOW All - View all 68 system variables SQL> show user - Display current connection user SQL> show error - Display error SQL> SETHEADING OFF - Disable output Column header, the default value is ON SQL> Set feedback OFF - Disable the counting feedback information of the last line, the default value is "6 or more records," SQL> SET TIMING ON "SQL> SET TIMING ON - default is OFF, When setting the query consumption, you can use to estimate the execution time of the SQL statement, test performance 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, the default is OFF SQL> SET PAUSE ON - The default is OFF, set the pause, make the screen display stop, wait until the Enter key, then display the next page SQL > SET ARRAYSIZE 1 - Default 15 SQL> SET Long 1000 - Default is 80 Description: long value defaults to 80, setting 1000 is to display more content because many data dictionary views use long data types, such as : SQL> DESC user_VIEWS column names can be empty type ----------------------------------- - --- View_name not null varchar2 (30) Text_length Numbertext Long SQL> Define A = '' 20000101 12:01:01 '20000101 12: 01' '- Defines local variables, if you want to use a constant like a carriage return similar in various displays, - can use the define command to Set 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 Problem proposing: 1. Users need to execute an identical SQL operation on each table under the database user, at this time
Others, typing the SQL statement is a cumbersome implementation method: SQL> Set Heading OFF - Disable output column Title SQL> SET feedback OFF - Disable the display of the last row of counting feedback information List the definition of all synonyms under the current user, The real presence of synonyms can be used to test synonyms. SELECT 'DESC' || TNAME from Tab Where Tabtype = 'Synynym'; Query the number of records of all tables under users SELECT 'SELECT' '' || TNAME || '' ', Count ( *) from '|| TNAME ||'; 'from tab where tabtype =' Table '; grant all the requirements of the custom table Select SELECT ON' || Table_name || 'to public;' from user_tables WHERE "Conditions"; Delete Users DROP '|| TabType ||' || TNAME from Tab; Remove Effective User 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 to other tablespace The connection will have problems, which can be quickly compiled using the language characteristics of PL / SQL. SQL> Spool on.sqlsql> select'Alter view '|| tname ||' Compile; 'from tab; sql> spool off then executes ON.SQL.
