ORACLE系统查询语句


ORACLE系统查询语句
 
  1、序列：
　　　select * from dba_sequences;
　2、视图：
　　　select * from dba_views;
　　　select * from all_views;
  3、查询表名、列名、标注释：  这个比较实用，可以用于导出数据时，不用再去写一张静态数据来存储。
     select * from user_col_comments；指当前这个用户下的表
     select * from  all_col_comments；所有
     select * from   dba_col_comments; 具备DBA权限的
  4  查看某表的创建时间
      select object_name,created from user_objects where object_name=upper('&table_name');
  5  查看某表的大小
      select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
 
  6  查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)
  7  删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people 
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)
  8  查找表中多余的重复记录（多个字段） 
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
  9  删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
  10  1.GRANT 赋于权限   
  常用的系统权限集合有以下三个:   
  CONNECT(基本的连接),   RESOURCE(程序开发),   DBA(数据库管理)   
  常用的数据对象权限有以下五个:   
  ALL   ON   数据对象名,   SELECT   ON   数据对象名,   UPDATE   ON   数据对象名,   
  DELETE   ON   数据对象名,     INSERT   ON   数据对象名,       ALTER     ON   数据对象名   
    
  GRANT   CONNECT,   RESOURCE   TO   用户名;   
  GRANT   SELECT   ON   表名   TO   用户名;   
  GRANT   SELECT,   INSERT,   DELETE   ON表名   TO   用户名1,   用户名2;   
    
  2.REVOKE   回收权限   
    
  REVOKE   CONNECT,   RESOURCE   FROM   用户名;   
  REVOKE   SELECT   ON   表名   FROM   用户名;   
  REVOKE   SELECT,   INSERT,   DELETE   ON表名   FROM   用户名1,   用户名2; 
   表名为 dept ，其中name字段是中文，下面分别实现按照单位名称的笔划、部首和拼音排序。 
1: //按照笔划排序 
2: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M'); 
3: //按照部首排序 
4: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M'); 
5: //按照拼音排序，此为系统的默认排序方式 
6: select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 注意，该SQL指令并非标准指令，在SQLServer下面的实现方式并不相同。
optimizer_cost 值越大影响越大.
 
/* 查询锁 */
SELECT /*+ ordered */
       o.object_name, o.object_type, l.locked_mode, s.machine, l.os_user_name, 
       s.terminal, l.oracle_username, o.subobject_name, s.program, s.module
  FROM v$session s, v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
   AND s.sid = l.session_id
 
/* 查询锁 */
SELECT /*+ ordered */
       o.object_name, o.object_type, k.type, l.locked_mode lock_mode_value, 
       DECODE(l.locked_mode, 0, 'NONE',
                             1, 'NULL',
                             2, 'ROW SHARE',
                             3, 'ROW EXCLUSIVE',
                             4, 'SHARE',
                             5, 'SHARE ROW EXCLUSIVE',
                             6, 'EXCLUSIVE') lock_mode,
       s.sid, s.serial#,
       s.machine, l.os_user_name, s.terminal, l.oracle_username, o.subobject_name, 
       s.program, s.module
  FROM v$session s, v$locked_object l, v$lock k, dba_objects o
WHERE l.object_id = o.object_id
   AND s.sid = l.session_id
   AND s.sid = k.sid
ORDER BY s.sid
 
/* 查询事务中的锁和相关信息 */
SELECT /*+ ordered */
       o.object_name, o.object_type,k.type, l.locked_mode, s.machine, l.os_user_name, 
       s.terminal, l.oracle_username, o.subobject_name, s.program, s.module, 
       k.id1, k.id2, k.request, k.ctime, k.block,
       t.status, t.start_time, t.xidusn seg_num, r.name seg_name
  FROM v$rollname r, v$lock k, v$session s, v$locked_object l, dba_objects o, 
       v$transaction t
WHERE l.object_id = o.object_id
   AND s.sid = l.session_id
   AND s.sid = k.sid
   AND t.xidusn = r.usn
   AND l.xidusn = t.xidusn
 
/* 缓冲区命中率 */
/* The data in V$SYSSTAT reflects the logical and physical reads for all buffer pools
   within one set of statistics */
/* 50%以下(危险,立即增加缓冲区缓存) 95%以上分配过度 90%-95%为调整的目标 */
SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 
             3) || '%' "Buffer Cache Hit Ratio"
  FROM v$sysstat phy, v$sysstat cur, v$sysstat con
WHERE phy.name = 'physical reads'
   AND cur.name = 'db block gets'
   AND con.name = 'consistent gets'
   
/* 每个Buffer Pool的命中率 */