oracle常用的sql查询语句-凯发app官方网站

找出访问一个对象的会话的进程信息及会话信息
select distinct s.username, p.pid, osuser, p.spid, s.process, s.lockwait  
from v$process p, v$session s, v$access a
where a.sid = s.sid  
and p.addr = s.paddr  
and s.sid = 33 ;
 
 
看一个sql运行了多少次，导致的disk reads为多大：
select executions, version_count, parse_calls, disk_reads,
buffer_gets, rows_processed, hash_value, sql_text
from v$sqlarea
where sql_text like '%spare2%obj$%' and sql_text not like '%from v$sqlarea%';
 
 
一个session产生的i/o量,通常用来查看一个session(正在执行语句的)是否已经hang.
select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
 from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null;
 
 
在当前session中查询当前session的sid:
column pid new_value pid
column sid new_value sid
column serial# new_value serial
 
select p.pid, s.sid, s.serial#
from sys.v_$session  s, sys.v_$process  p
where s.sid = (select sid from sys.v_$mystat where rownum = 1) and
  p.addr = s.paddr
/
 
-- or
 
select sid,serial# from v$session where audsid =
(select userenv('sessionid') from dual);
 
查询session与相关的process的情况:
set linesize 290
col machine format a30 wrap
col username format a15 wrap
col sql format a40
col program format a45
col logon_time format a25
set pagesize 500
select s.sid, s.serial#, s.username, b.name "bg process", p.spid servpid,
s.status, s.server, s.machine, s.program,
to_char(s.logon_time,'yyyy.mm.dd hh24:mi:ss') logon_time, s.osuser, 
trunc(s.last_call_et/60,1) idel_mins
from v$session s, v$process p, v$bgprocess b
where p.addr = s.paddr
and b.paddr( ) = p.addr;
 
idel_mins为该session空闲的秒数,如果该空闲的时间过长, 则说明有可能程序没有及时的关闭数据库连接, 这时可以借助该session已经执行过的sql语句发现是那个应用或应用的那个部分发出的,从而解决问题.
 
 
只查询后台进程的信息：
set linesize 290
col machine format a30 wrap
col name for a15
col username format a15 wrap
col sql format a40
col program format a45
col logon_time format a25
set pagesize 500
 
select s.sid, s.serial#, b.name, s.username, p.spid servpid,
       s.machine, s.program,
to_char(s.logon_time,'yyyy.mm.dd hh24:mi:ss') logon_time,
       s.osuser, trunc(s.last_call_et/60,1) idel_mins
from v$session s, v$process p, v$bgprocess b
where p.addr = s.paddr and p.addr = b.paddr;
 
 
生成杀死session的语句
set linesize 290
col machine format a30 wrap
col username format a15 wrap
col kill_sql format a40
set pagesize 500
select 'alter system kill session ''' || to_char(s.sid)||','||to_char(s.serial#)||''';' kill_sql, s.username, machine, ‘kill -9 ‘||to_char(p.spid) kill_process, s.server
from v$session s, v$process p
where p.addr = s.paddr ;
 
查询出一个表中指定范围的行(用rownum进行限制)
select t1.rn, t1.col1
from (select rownum rn, col1
from tab1) t1
where t1.rn between 5 and 7;
 
查找当前用户的用户权限：
1) 用户拥有的角色
       user_role_privs
2) 用户拥有的表权限
       user_tab_privs
3) 用户拥有的列权限
       user_col_privs
5) 用户拥有的系统权限
user_sys_privs
6) 查询角色中的系统权限
role_sys_privs
4.) 查询当前用户拥有的所有权限(不包括表权限与列权限)
session_privs;
 
 
看一个排序操作占用多少临时表空间：
select tablespace_name, extent_size, total_extents, used_extents,
               free_extents, max_used_size
from v$sort_segment;
 
查看一个查询是否并行运行:
在语句运行时, 运行:
select * from v$pq_sesstat;
 
 
查看并行查询进程:
select * from v$pq_slave;
 
 
查看用户的各个队象的依存情况:
select * from user_dependencies
where name <> referenced_name
         and referenced_type <> 'non-existent'
         and referenced_name <> 'dual'
      and referenced_name <> 'dbms_output'
         and referenced_name <> 'standard'
 
 
查看一个session正在运行的sql语句:
通过v$sql，v$sql比v$area消耗的资源要小(v$sql中为每一条sql保留一个条目，而v$sqlarea中根据sql_text进行group by，即如果一条语句有不同的version，则在v$sql中表现为多条记录，而在v$sqlarea中只有一条记录)
 
8i:
利用v$open_cursor查询session中运行的sql, 有可能有多条，也有可能一条没有，这主要看查询的sid，多长时间没有活动了:
select s.sid, s.username, sql.hash_value,
sql.address, sql.sql_text
 from v$open_cursor sql, v$session s
 where sql.sid = s.sid and
s.sid =?;
 
利用v$sql查询session中运行的sql, 此查询节省资源:
select s.sid, s.username, sql.optimizer_mode, sql.executions, sql.disk_reads, sql.buffer_gets,
sql.hash_value,
sql.address, sql.sql_text
  from v$sql sql, v$session s
 where s.sql_hash_value = sql.hash_value
   and s.sql_address    = sql.address
   and s.username is not null
   and sid = ?;
 
利用v$sqlarea查询session中运行的sql, 此查询比较耗费资源:
select s.sid, s.username, sql.optimizer_mode, sql.executions, sql.disk_reads, sql.buffer_gets,
sql.hash_value,
sql.address, sql.sql_text
  from v$sqlarea sql, v$session s
 where s.sql_hash_value = sql.hash_value
   and s.sql_address    = sql.address
   and s.username is not null
   and sid = ?;
 
 
9i:
利用v$open_cursor查询session中运行的sql, 有可能有多条，也有可能一条没有，这主要看查询的sid，多长时间没有活动了:
select s.sid, s.username, sql.hash_value,
sql.address, sql.sql_text
 from v$open_cursor sql, v$session s
 where sql.sid = s.sid and
s.sid = ?;
 
利用v$sql查询session中运行的sql, 此查询节省资源:
select s.sid, s.username, optimizer_mode, executions, disk_reads, buffer_gets,
hash_value, sql_hash_value, prev_hash_value,
address, cpu_time, elapsed_time, sql_text
from v$sql sql, v$session s
where ((s.sql_hash_value = sql.hash_value and s.sql_address= sql.address) or
       (s.prev_hash_value = sql.hash_value and s.prev_sql_addr= sql.address))
   and s.username is not null
   and sid = ?;
 
 
利用v$sqlarea查询session中运行的sql, 此查询比较耗费资源:
select s.sid, s.username, optimizer_mode, executions, disk_reads, buffer_gets,
hash_value, sql_hash_value, prev_hash_value,
address, cpu_time, elapsed_time, sql_text
from v$sqlarea sql, v$session s
where ((s.sql_hash_value = sql.hash_value and s.sql_address= sql.address) or
       (s.prev_hash_value = sql.hash_value and s.prev_sql_addr= sql.address))
   and s.username is not null
   and sid = ?;
 
 
看数据库上是否有nologging操作:
select name, to_char(unrecoverable_change#) unrecoverable_change,
to_char (unrecoverable_time,'dd-mon-yyyy hh:mi:ss') unrecoverable_time
from v$datafile;
 
知道了一个文件号与数据块号，如何知道该处被哪个对象占有：
select segment_owner, segment_name
    from dba_extents
   where file_id=&file_id
     and &block_id between block_id and block_id blocks-1;
 
从session本身的连接查询其sid：
column pid new_value pid
column sid new_value sid
column serial# new_value serial
 
selectp.pid, s.sid, s.serial#
from sys.v_$session s,sys.v_$process p
where s.sid = (select sid from sys.v_$mystat where rownum = 1)
and p.addr = s.paddr;
 
-- or
 
 select sid,serial# from v$session where audsid =
 (select userenv('sessionid') from dual);
 
在windows下自动启动sql*plus并运行一个sql文件：
1) start.bat
sqlplus /nolog "@c:\start.sql"
2) start.sql
conn / as sysdba
startup
 
 
 
 
给log file改名：
在mount状态下：
alter database rename file 
                 '/full_path_of_old_location/and_redo_log_name.log' 
                 to 
                 '/full_path_of_new_location/and_redo_log_name.log'; 
 
给data file改名：
在mount或数据文件offline状态下:
alter database rename file 
                 '/full_path_of_old_location/and_datafile_name.dbf'
                 to 
                 '/full_path_of_new_location/and_datafile_name.dbf';