Common sqlplus commands

Sqlplus provides many common commands. The following describes how to use them.

1-> sqlplus login and exit

Sqlplus-H |-v-H displays the version and help information of sqlplus, and-V displays the login Syntax of its version information:
  
   
Is :(
   
    
[/
    
     
] [@
     
      
] |/) [As sysdba | as sysoper] |/nolog
      
       
[/
       
         ]: Login username and password @
        
          : Database connection identifier. If this parameter is not specified, connect to the default identifier as sysdba | as sysoper: these two parameters use the permissions of the database administrator to log on to nolog: start sqlplus that is not connected to the database. After that, you can use conn to log on to the following three methods: [Oracle @ Linux ~] $ Sqlplus Scott/tiggersql * Plus: Release 10.2.0.1.0-production on Tue Mar 30 14:04:06 2010 copyright (c) 1982,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, OLAP and Data Mining options [Oracle @ Linux ~] $ Sqlplus/nologsql * Plus: Release 10.2.0.1.0-production on Tue Mar 30 14:04:45 2010 copyright (c) 1982,200 5, Oracle. all rights reserved. SQL> conn Scott enter password: connected. SQL> exit/* use exit or quit to exit */SQL> exitdisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, OLAP and Data Mining options [Oracle @ Linux ~] $ Sqlplus "/As sysdba" SQL * Plus: Release 10.2.0.1.0-production on Tue Mar 30 14:05:44 2010 copyright (c) 1982,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, OLAP and Data Mining options Exit: Exit using exit or quit, as shown in the example
        
       
      
     
    
   
  

2-> help to get help information for a command

SQL> help desc DESCRIBE -------- Lists the column definitions for a table, view, or synonym, or the specifications for a function or procedure. DESC[RIBE] {[schema.]object[@connect_identifier]

3-> list [m] [*] [N] (abbreviated as l) shows all the contents of the buffer. * The current row, m rows M, N rows n, m n appear at the same time, m rows to N rows

SQL> l  1  select * from emp  2  where sal > 2000  3* and deptno = 20SQL> l 2 3  2  where sal > 2000  3* and deptno = 20

4->/execution buffer content

SQL> l  1  select * from emp  2  where sal > 2000  3  and deptno = 20  4* and ename = 'SCOTT'SQL> /     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20  

5-> N: set the current row

SQL> 2  2* where sal > 2000SQL> 3  3* and deptno = 20  

6-> N text replaces line N with text

SQL> l  1  select * from emp  2  where deptno = 20  3* and sal > 2000SQL> 2 where ename = 'SCOTT'SQL> l  1  select * from emp  2  where ename = 'SCOTT'  3* and sal > 2000

7->. For SQL statements that have been entered, enter the number indicating that the statement has been entered. You can use a semicolon instead of a set statement, as shown in the preceding help DESC.
8-> append text (abbreviated as a text): append the text content to the end of the buffer.

SQL> l  1* select * from empSQL> a where sal > 2000;  1* select * from empwhere sal > 2000

9-> change/old/new (C/old/new) replace old with new in the current row

SQL> l  1  select * from emp  2  where sal > 2000  3*    and deptno = 20SQL> 3  3*    and deptno = 20SQL> c /20/10  3*    and deptno = 10SQL> l  1  select * from emp  2  where sal > 2000  3*    and deptno = 10

10-> change/text (C/Text) delete text from the current row

SQL> l  1  select * from emp  2  where sal > 2000  3*    and deptno = 10SQL> 3  3*    and deptno = 10SQL> c /and deptno = 10  3*SQL> l  1  select * from emp  2  where sal > 2000  3*

11-> clear buffer (CL buff) clears the entire SQL Buffer

SQL> cl buffbuffer clearedSQL> lSP2-0223: No lines in SQL buffer.

12-> Del Delete the current row

SQL> l  1  select * from emp  2* where sal > 2000SQL> del 2SQL> l  1* select * from emp

13-> show User: displays the current Login User

SQL> show user  USER is "SYS"SQL> conn scott/tiggerConnected.SQL> show userUSER is "SCOTT"

14-> Save the content of the current buffer to the file

SQL> l  1  select *  2  from emp  3* where sal > 2000SQL> save query.sqlCreated file query.sql  

15-> get transfers the command file on the disk to the Current Buffer Zone

SQL> cl buffbuffer clearedSQL> get query.sql  1  select *  2  from emp  3* where sal > 2000

16-> Start/@ filename run command file

SQL> get query.sql  1  select *  2  from emp  3* where sal > 2000SQL> @query.sql

17-> set linesize N: set the number of characters in each line. The default value is 80. If the output content of a line is greater than the number of characters that can be accommodated in the Set line, the line is displayed.

SQL> select * from Scott. EMP where ename = 'Scott '; /* The following are unset results */empno ename job Mgr hiredate Sal comm ------------ ------------- ---------- --------- ---------- ------------ deptno -------- 7788 Scott analyst 7566 19-apr-87 3000 20sql> set linesize 200sql select * from Scott. EMP where ename = 'Scott '; /* The following is the set result */empno ename job Mgr hiredate Sal comm deptno ---------- --------- ---------- 7788 Scott analyst 7566 19-apr-87 3000 20

18-> use of dual pseudo tables. Pay attention to the difference between Oracle and MSSQL. The following operations can be completed without the need for pseudo tables in MSSQL, and some specific functions can be completed without the need for from dual.

SQL> select 3+2 from dual;       3+2----------         5

19-> spool filename: Output all input content on the screen to the file, including the entered SQL statement.

20-> spool off must be off before the content can be output to the file.

More: Install Oracle 10g R2 in Linux (RHEL 5.4) and use uniread to implement SQL plus paging.