Some techniques used by sql*plus

Skills
Some of the techniques used by Sql*plus Sql*plus have many skills, and if mastered, it would be useful for rapid development and efficient maintenance of databases under Oracle databases.         below accept one or two,        1. Using the Sql*plus dynamic Build batch script to use the spool with the Select command, you can generate a script that contains statements that can perform a task in bulk. Example 1: Generate a script that deletes all the tables under Scott's user: A. Create a Gen_drop_table.sql file containing the following statement: Spool  c:\drop_table.sql      select ' drop table ' | | table_name | | '; From User_tables;      spool off B. Log in to Scott user Sqlplus > @ ... \gen_dorp_table.sqlc. A file Drop_table.sql file is generated in the C-packing directory, containing statements to delete all tables, as shown below:sql>      select ' DROP table ' | | table_name | | '; From user_tables;                                                 & nbsp;                          & nbsp;                   ' droptable ' | | table_name| | '; '                            -- ------------------------------------------------------------------------------DROP TABLE dept;                                & nbsp;        drop TABLE emp;              & nbsp;                           drop TABLE parent;                                       drop TABLE stat_vender_temp;                       DROP TABLE table_forum;      &NBSP;                           & nbsp                            & nbsp;                          & nbsp;                     5 rows selected.  & nbsp                             sql>      spool off  d. Edit the generated drop_table.sql file to remove unnecessary parts, leaving only the drop table ... statement e. Run the Dorp_table.sql file under the Scott user and delete all the tables under Scott user. Sqlplus > @ c:\dorp_table.sql in the above operation, there will be extra characters in the generated script file, such as the running SQL statement, the title, or the number of rows returned, which requires us to edit the script and then run again, causing many inconvenience to the actual operation. Laziness is human nature, which prompts us to use simpler ways to achieve the above tasks. A. Create a Gen_drop_table.sql file containing the following statement:        Set echo offset feedback offset NewPage nonesetPageSize 5000set linesize 500set Verify offset pagesize 0set term offset trims onset linesize 600set heading  of F Set Timing offset Verify offset numwidth 38spool  c:\drop_table.sql      select ' drop TAB LE ' | | table_name | | '; From User_tables;      spool off B. Log in to Scott user Sqlplus > @ ... \gen_dorp_table.sqlc. The file Drop_table.sql file is generated in the C-packing directory, containing statements that delete all tables, as follows: Drop table dept;                                        & Nbsp; drop TABLE emp;                     & nbsp                   drop TABLE parent;                               & nbsp       DROP TABLE stat_vender_temp;                      DROP TABLE table_forum;                               d. Run the Dorp_table.sql file under Scott User and delete all the tables under Scott user. Sqlplus > @ c:\dorp_table.sql2. Export data from one table to a text file with columns separated by "," from set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set Verify Offs ET pagesize 0set term offset trims onset linesize 600set heading  off Set timing offset Verify offset numwidth 3 8spool  c:\drop_table.sql      select DEPTNO | | ',' || Dname from dept;     spool when the above content is saved as a text file, log in with Scott, and the result is displayed after the file is executed: 10,accounting  20 ,research  30,sales  40,operations   through the above two examples, we can add: set echo offset feedback offset NewPage noneset pagesize 5000set linesize 500set Verify offset pagesize 0set term offsetTrims onset linesize 600set heading  off Set timing offset Verify offset numwidth 38spool  c:\ specific file name & nbsp     you want to run the SQL statement      spool off as a template, as long as the necessary statements if the template on it. In newer versions of Oracle, you can also use the Set COLSEP command to implement the above functionality:sql> set COLSEP,sql> select * from dept;       & nbsp;10,accounting   , NEW york        20,RESEARCH      , dallas        30,SALES         ,CHICAGO         40,OPERATIONS   ,boston        35,aa             ,bb3. The file name required to dynamically generate the spool command in our example above, the file name required for the spool command is fixed. Sometimes we need to spool once a day, and each spool file name is not the same, such as the file name contains the date of the day, how to achieve it? Column DAT1 new_value filename;select to_char (sysdate, ' Yyyymmddhh24mi ') dat1 from Dual;  spool c:\&& filename.. Txt  select * from Dept;spool off;4. How to FromThe value of the Windows environment variable is obtained in the script file: in WinDOS: Spool c:\temp\%ORACLE_SID%.txt   select * from dept;      spool off in the example above, the value of the environment variable ORACLE_SID is referenced by%oracle_sid%, if the Oracle_sid value is ORCL, The generated spool file name is: Orcl.txt in Unix: Spool c:\temp\ $ORACLE _sid.txt   select * from dept;      spool off in the example above, the value of the environment variable ORACLE_SID is referenced by $oracle_sid, if the Oracle_sid value is ORCL, The generated spool file name is: ORCL.TXT5. How to specify the default edit script directory         in Sql*plus, you can save the last executed SQL statement to a file by using the Save command, but how do you set the default directory for that file? With the sql> set editfile c:\temp\file.sql command, you can set its default directory to C:\tmpe and the default file name is File.sql. 6. How to drop the same row in the table to find the same row: SELECT * FROM dept a   where ROWID <> (select MAX (ROWID)                    from dept b              &nbsp ;    WHERE a.deptno = b.deptno                   and A.dname = b.dname  --Make sure all COlumns are compared                   and a.loc = B.loc); Note: if Just find the same row for the Deptno column, and the query above can be changed to: SELECT * FROM dept a   where ROWID <> (select MAX (ROWID)      &nbsp ;             from dept b            &nbs P      WHERE a.deptno = b.deptno) Deletes the same row: Delete from dept awhere ROWID <> (SELECT MAX (rowid                                from dept b  &nbsp ;                             WHERE A.deptno = B.DEPTN o                                and A.dna me = b.dname--Make sure all columns are compared                    &N Bsp     &NBsp     and a.loc = B.loc) Note: This does not delete rows with column values that are null. 7. How to insert two single quotes (') Insert Inot dept values (' AA ' ' ' ' BB ', ' a ' ' B ') into the database; when inserting, use two ' to represent one '. 8. How to set the Sql*plus search path so that when you use the @ command, you don't have to enter the full path of the file. Set the SQLPATH environment variable. such as: SQLPath = C:\ORANT\DBS; C:\APPS cripts; C:\MYSCRIPTS9. What is the difference between @ and @@ 的 @ equals the start command, used to run a SQL script file. The @ command invokes the current directory, or specifies a full path, or a script file that can be searched through the SQLPATH environment variable. @@ 用 used in script files to specify that files executed with @@ 用 in the same directory as @@ 用 without specifying the full path or looking for files from the path specified by the SQLPATH environment variable, which is typically used in nested script files. 10. The difference between & and && & is used to create a temporary variable that prompts you to enter a value whenever you encounter this temporary variable. && is used to create a persistent variable just like a persistent variable created with the Define command or column command with new_vlaue words. When the variable is referenced with the && command, the user is not prompted to type a value each time the variable is encountered, but only once on the first encounter. For example, if you save the following three lines of statements as a script file, and run the script file, you will be prompted three times to enter the value of Deptnoval: SELECT COUNT (*) from emp where deptno = &deptnoval;select count (*) From the emp where deptno = &deptnoval;select count (*) from the emp where Deptno = &deptnoval; Saves the following three lines of statements as a script file, and the script file is run. Once, let the input deptnoval value: SELECT COUNT (*) from emp where deptno = &deptnoval;select count (*) from emp where deptno = &AMP;DEP Tnoval;select Count (*) from EMP where deptno= &deptnoval;11. It is particularly useful to introduce the copy's destination copy command to copy data between two databases, especially if the command can pass data from a long field between two databases. Disadvantage: When passing data between two databases, it is possible to lose precision (lose precision). 12. Ask what happens when you modify a large number of rows and my script gets very slow? When you modify many rows in a table by Pl/sql block, you create a cursor on the table, but only when you close cursor will the rollback SEGMENT be released, so that when the cursor is still open, the modification process slows down, This is because the database has to search a large number of rollback segment in order to maintain read consistency. To avoid this, try adding a flag field on the table to describe whether the row has been modified, then close the cursor, and then open the cursor. You can modify 5000 rows at a time.

Turn this: http://www.cnoug.org/viewthread.php?tid=31451