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

最新推荐文章于 2023-03-28 16:53:36 发布

bitko

最新推荐文章于 2023-03-28 16:53:36 发布

阅读量1.2k

点赞数

分类专栏： Oracle-Tools

本文链接：https://blog.csdn.net/gumengkai/article/details/53126943

版权

Oracle-Tools 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

一. set 命令

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

SQL> show linesize
linesize 80
SQL> set linesize 200

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

SQL> set pagesize 0   
SQL> show pagesize
pagesize 0

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

SQL> show heading 
heading ON
SQL> 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  YES

7 rows selected.
SQL> set heading off
SQL> /

         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  YES

7 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 999999
SQL> /

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 fe
where v.file# = f.file#
  and f.spare1 is NULL

TEXT
--------------------------------------------------------------------------------
  and f.ts# = ts.ts#
  and fe.fenum = f.file#
union all
select
       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 fe
where 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(*)
----------
     86970

1 row selected.

Elapsed: 00:00:00.13

二. col命令

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

SQL> column name heading name_heading
SQL> /

       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  YES

7 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.dbf
USERS

/u01/app/oracle/oradata/orcl/undotbs01.dbf
UNDOTBS1

/u01/app/oracle/oradata/orcl/tbs_a01.dbf
TBS_A


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
SYSTEM

/u01/app/oracle/oradata/orcl/sysaux01.dbf
SYSAUX

/u01/app/oracle/oradata/orcl/tbs_1.dbf
TBS1


6 rows selected.

SQL> set linesize 200
SQL> col file_name format a50
SQL> col tablespace_name format a20
SQL> /

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             TBS1

6 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 identifier


SQL> 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 owner
sys@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 exist


sys@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                         CLERK

12 rows selected.

scott@ORCL> del  
scott@ORCL> l
  1  select empno,ename,job
  2* from emp
scott@ORCL> del 1
scott@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 function


sys@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 exist


scott@ORCL> input where emp_age>30
scott@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>30
scott@ORCL> l1 #列出第一行
  1* select emp_id,emp_name
scott@ORCL> l*   #列出当前行
  1* select emp_id,emp_name
scott@ORCL> l last #列出最后一行
  3* where emp_age>30
scott@ORCL> l 1 2 #列出第一行第二行范围内的行
  1  select emp_id,emp_name
  2* from employees

bitko

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
sqlplus常用命令(持续更新...)

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

扫一扫

专栏目录