sqlplus常用命令(持续更新...)

来源：互联网发布：淘宝网购酸枣面编辑：程序博客网时间：2024/06/06 00:33

一. set 命令

1. set linesize：设置显示的宽度，即一行可以容纳的字符数，如果输出内容大于设置的linesize，折行显示。默认值为80。但是每行最多只能显示200个字符，所以一般需要显示较多内容时会设置为200.

SQL> show linesizelinesize 80SQL> set linesize 200

2. set pagesize：设置每页显示的行数，默认是24，设置成0表示不分页

SQL> set pagesize 0   SQL> show pagesizepagesize 0

3. set heading：是否显示列标题，当设置set heading off时，每页的上面不显示列标题，而是以空白行代替

SQL> show heading heading ONSQL> select * from v$tablespace;       TS# NAME                           INC BIG FLA ENC---------- ------------------------------ --- --- --- ---         1 SYSAUX                         YES NO  YES         0 SYSTEM                         YES NO  YES         5 TBS_A                          YES NO  YES         2 UNDOTBS1                       YES NO  YES         4 USERS                          YES NO  YES         3 TEMP                           NO  NO  YES         6 TBS1                           YES NO  YES7 rows selected.SQL> set heading offSQL> /         1 SYSAUX                         YES NO  YES         0 SYSTEM                         YES NO  YES         5 TBS_A                          YES NO  YES         2 UNDOTBS1                       YES NO  YES         4 USERS                          YES NO  YES         3 TEMP                           NO  NO  YES         6 TBS1                           YES NO  YES7 rows selected.

4. set long：设置一个long列的最大显示宽度。Long值默认为80，设置1000或者更大的值是为了显示更多的内容，因为很多数据字典视图中用到了Long数据类型

SQL> select text from dba_views where view_name='DBA_DATA_FILES';TEXT--------------------------------------------------------------------------------select v.name, f.file#, ts.name,       ts.blocksize * f.blocks, f.blocks,SQL> set long 999999SQL> /TEXT--------------------------------------------------------------------------------select v.name, f.file#, ts.name,       ts.blocksize * f.blocks, f.blocks,       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),       f.relfile#, decode(f.inc, 0, 'NO', 'YES'),       ts.blocksize * f.maxextend, f.maxextend, f.inc,       ts.blocksize * (f.blocks - 1), f.blocks - 1,       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),         decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fewhere v.file# = f.file#  and f.spare1 is NULLTEXT--------------------------------------------------------------------------------  and f.ts# = ts.ts#  and fe.fenum = f.file#union allselect       v.name,f.file#, ts.name,       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),       decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),       decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),       f.relfile#,       decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),       decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),TEXT--------------------------------------------------------------------------------       decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),       decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),       decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),       decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),       decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),         decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fewhere v.file# = f.file#  and f.spare1 is NOT NULL  and v.file# = hc.ktfbhcafno  and hc.ktfbhctsn = ts.ts#

5. set timing on：设置查询耗时，可以用来估计sql语句的执行时间，测试性能

sys@ORCL> set timing on;sys@ORCL> select count(*) from dba_objects;  COUNT(*)----------     869701 row selected.Elapsed: 00:00:00.13

二. col命令

1. COLUMN column_name HEADING column_heading：改变缺省的列标题

SQL> column name heading name_headingSQL> /       TS# name_heading                   INC BIG FLA ENC---------- ------------------------------ --- --- --- ---         1 SYSAUX                         YES NO  YES         0 SYSTEM                         YES NO  YES         5 TBS_A                          YES NO  YES         2 UNDOTBS1                       YES NO  YES         4 USERS                          YES NO  YES         3 TEMP                           NO  NO  YES         6 TBS1                           YES NO  YES7 rows selected.

2. COL column_name format：改变列的最大显示宽度

COL c1 FORMAT a20; --将列c1(字符型)显示最大宽度调增为20个字符

COL c1 FORMAT 9999999 --将c1(num型)显示最大宽度调整为7个字符

SQL> select file_name,tablespace_name from dba_data_files;FILE_NAME--------------------------------------------------------------------------------TABLESPACE_NAME------------------------------/u01/app/oracle/oradata/orcl/users01.dbfUSERS/u01/app/oracle/oradata/orcl/undotbs01.dbfUNDOTBS1/u01/app/oracle/oradata/orcl/tbs_a01.dbfTBS_AFILE_NAME--------------------------------------------------------------------------------TABLESPACE_NAME------------------------------/u01/app/oracle/oradata/orcl/system01.dbfSYSTEM/u01/app/oracle/oradata/orcl/sysaux01.dbfSYSAUX/u01/app/oracle/oradata/orcl/tbs_1.dbfTBS16 rows selected.SQL> set linesize 200SQL> col file_name format a50SQL> col tablespace_name format a20SQL> /FILE_NAME                                          TABLESPACE_NAME-------------------------------------------------- --------------------/u01/app/oracle/oradata/orcl/users01.dbf           USERS/u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1/u01/app/oracle/oradata/orcl/tbs_a01.dbf           TBS_A/u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM/u01/app/oracle/oradata/orcl/sysaux01.dbf          SYSAUX/u01/app/oracle/oradata/orcl/tbs_1.dbf             TBS16 rows selected.

三. 编辑命令

1. c/str_old/str_new：change命令，替换字符串

SQL> select sql_trext,parse_calls from v$sql where sql_text like 'select * from t where id=%';select sql_trext,parse_calls from v$sql where sql_text like 'select * from t where id=%'       *ERROR at line 1:ORA-00904: "SQL_TREXT": invalid identifierSQL> c/trext/text  1* select sql_text,parse_calls from v$sql where sql_text like 'select * from t where id=%'

多次替换：

sys@ORCL> /sys@ORCL> c/owner/object_type #替换第一个  1* select object_type,count(*)  from dba_objects group by ownersys@ORCL> c/owner/object_type #替换第二个  1* select object_type,count(*)  from dba_objects group by object_type

c/text，可以用来从当前行删除文本：

sys@ORCL> select * from thello;select * from thello              *ERROR at line 1:ORA-00942: table or view does not existsys@ORCL> c/hello  1* select * from t

2. del：删除当前行，del n：删除第n行

scott@ORCL> select empno,ename,job  2  from emp  3  where sal>1000;     EMPNO ENAME                          JOB---------- ------------------------------ ---------------------------      7499 ALLEN                          SALESMAN      7521 WARD                           SALESMAN      7566 JONES                          MANAGER      7654 MARTIN                         SALESMAN      7698 BLAKE                          MANAGER      7782 CLARK                          MANAGER      7788 SCOTT                          ANALYST      7839 KING                           PRESIDENT      7844 TURNER                         SALESMAN      7876 ADAMS                          CLERK      7902 FORD                           ANALYST      7934 MILLER                         CLERK12 rows selected.scott@ORCL> del  scott@ORCL> l  1  select empno,ename,job  2* from empscott@ORCL> del 1scott@ORCL> l  1* from emp

3. a：表示直接在当前行的末尾加上字符

sys@ORCL> select owner,count(*)  from dba_objects;select owner,count(*)  from dba_objects       *ERROR at line 1:ORA-00937: not a single-group group functionsys@ORCL> a  group by owner  # a即append  1* select owner,count(*)  from dba_objects group by owner

4. input命令：可以接着上一条指令的后面添加语句

scott@ORCL> select emp_id,emp_name  2  from employees  3  /from employees     *ERROR at line 2:ORA-00942: table or view does not existscott@ORCL> input where emp_age>30scott@ORCL> l  1  select emp_id,emp_name  2  from employees  3* where emp_age>30

5. list命令

scott@ORCL> l #列出sql buffer中的所有行  1  select emp_id,emp_name  2  from employees  3* where emp_age>30scott@ORCL> l1 #列出第一行  1* select emp_id,emp_namescott@ORCL> l*   #列出当前行  1* select emp_id,emp_namescott@ORCL> l last #列出最后一行  3* where emp_age>30scott@ORCL> l 1 2 #列出第一行第二行范围内的行  1  select emp_id,emp_name  2* from employees

0 0