oracle常用的sql查询语句

找出访问一个对象的会话的进程信息及会话信息
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';