《Oralce系列》Oracle 常用语句汇总

查询当前数据库的连接数

select count(*) from gv$process;
1

查询当前数据库允许的最大连接数

select value from gv$parameter where name = 'processes';
1

查询当前数据库的资源限制

select * from gv$resource_limit;
1

查询当前数据库的锁表语句

select b.owner, b.object_name, a.session_id, a.locked_mode
  from gv$locked_object a, dba_objects b
 where b.object_id = a.object_id;
1
2
3

查询当前数据库的锁表程序

select b.owner,
       b.object_name,
       a.session_id,
       c.machine,
       c.osuser,
       c.process,
       c.program,
       a.locked_mode,
       c.client_info
  from gv$locked_object a, dba_objects b, gv$session c
 where b.object_id = a.object_id
   and a.session_id = c.sid;
1
2
3
4
5
6
7
8
9
10
11
12

生成强制解锁语句

select 'alter system kill session ''' || SID || ',' || SERIAL# || ''';',
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from gv$locked_object lo, dba_objects ao, gv$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;
1
2
3
4
5
6
7
8

查询单个进程所占的会话数

select s.machine,
       s.client_info,
       s.process,
       s.program,
       s.username,
       count(*) count
  from gv$session s
 where s.program = '<进程名称>'
 group by s.machine, s.client_info, s.process, s.program, s.username
 order by count desc;
1
2
3
4
5
6
7
8
9
10

查询每台服务器进程占用的总会话数

select s.machine, s.client_info, count(*) count
  from gv$session s
 where s.program = '<进程名称>'
 group by s.machine, s.client_info
 order by count desc;
1
2
3
4
5

查询进程服务占的总会话数

select count(*) count
  from gv$session s
 where s.program = '<进程名称>';
1
2
3

查询当前数据库查询次数最多的SQL语句

select *
  from (select s.sql_text,
               s.executions "执行次数",
               s.parsing_user_id "用户名",
               rank() over(order by executions desc) exec_rank
          from v$sql s
          left join all_users u
            on u.user_id = s.parsing_user_id) t
 where exec_rank <= 100;
1
2
3
4
5
6
7
8
9

查询当前数据库执行查询最慢的前50条语句

select *
  from (select sa.SQL_TEXT,
               sa.SQL_FULLTEXT,
               sa.EXECUTIONS "执行次数",
               round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
               round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
               sa.COMMAND_TYPE,
               sa.PARSING_USER_ID "用户ID",
               u.username "用户名",
               sa.HASH_VALUE
          from v$sqlarea sa
          left join all_users u
            on sa.PARSING_USER_ID = u.user_id
         where sa.EXECUTIONS > 0
         order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询表的数据条数、表名、中文表名

select a.num_rows, a.table_name, b.comments
  from user_tables a, user_tab_comments b
 where a.table_name = b.table_name
 order by table_name;
1
2
3
4

查询当前数据库中单表数据大约10万条的所有表

select table_name, num_rows
  from user_tables
 where num_rows >= 100000
 order by num_rows desc;
1
2
3
4

查询当前数据库的所有表空间

select * from v$tablespace;
1

查询当前用户下的所有table、view、sequence、trigger等信息

select * from user_tables;
select * from user_views;
select * from user_sequences;
select * from user_triggers;
1
2
3
4

查询当前数据库表空间的位置

select * from dba_data_files;
1

查询当前数据库某用户下的表空间

select tablespace_name
  from dba_segments
 where owner = '<用户名>'
 group by tablespace_name;
1
2
3
4

查询当前数据库的所有用户

select * from dba_users;
select * from all_users;
select * from user_users;
1
2
3

查询当前数据库中各表使用索引的数量

select table_name, count(*) index_count
  from user_indexes
 group by table_name
 order by index_count desc;
1
2
3
4

生成删表语句（表名中包含某个字符）

select 'drop table ' || lower(table_name) || ';'
  from user_tables
 where upper(table_name) like '%TEMP%'
 order by table_name asc;
1
2
3
4

生成清理表中数据语句（表名中包含某个字符）

select 'truncate table ' || lower(table_name) || ';'
  from user_tables
 where table_name like '%TEMP%'
 order by table_name asc;
1
2
3
4

生成删表语句（表名中包含数字）

select 'drop table ' || lower(table_name) || ';'
  from user_tables
 where regexp_like(upper(table_name), '[0-9]*[0-9]')
 order by table_name asc;
1
2
3
4