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的命中率 */
select name "Buffer Pool",1 - (physical_reads / (db_block_gets + consistent_gets)) "Buffer Pool Hit Ratio"
from v$buffer_pool_statistics order by name;

/* 查询某个会话的缓冲区命中率(查询的响应时间比较长) */
SELECT s.sid, s.username,
       ROUND((1 - phy_read.value / (cons_get.value + block_get.value)) * 100, 3) || '%'
       "BUFFER_HIT_RATIO"
  FROM v$session s,
       v$sesstat cons_get, v$statname cons,
       v$sesstat block_get, v$statname block,
       v$sesstat phy_read, v$statname phy
WHERE s.sid = cons_get.sid
   AND cons_get.statistic# = cons.statistic#
   AND cons.name = 'consistent gets'
   AND s.sid = block_get.sid
   AND block_get.statistic# = block.statistic#
   AND block.name = 'db block gets'
   AND s.sid = phy_read.sid
   AND phy_read.statistic# = phy.statistic#
   AND phy.name = 'physical reads'
   AND cons_get.value + block_get.value > 0
ORDER BY 1

/* 查询确定Shared Pool Memory的利用率 */
/* 60%-80%利用是最佳的 */
SELECT (used / value) * 100 SHARED_POOL_USAGE_RATIO
  FROM v$parameter p,
       (SELECT SUM(bytes) used
          FROM v$sgastat
         WHERE pool = 'shared pool'
           AND name <> 'free memory')
WHERE p.name = 'shared_pool_size'


/* 共享池空闲率 */
/* 40%-100% 分配过度, 10%-20% 分配最佳*/
SELECT (s.bytes / p.value)* 100 shared_pool_free_ratio
  FROM v$parameter p, v$sgastat s
WHERE s.pool = 'shared pool'
   AND s.name = 'free memory'
   AND p.name = 'shared_pool_size'

/* Library Cache Reloads*/
/* 如果reload的值大于1%就得增加共享池的大小 */
SELECT SUM(pins) "Executions", SUM(reloads) "Cache Misses",
       SUM(reloads) / SUM(pins)
  FROM v$librarycache

/* 查询排序的情况 */
SELECT *
  FROM v$sysstat
WHERE name LIKE '%sorts%'

/* 查询磁盘排序和内存排序的比率(应该低于5%)*/
SELECT disk.value "Disk", mem.value "Mem",
       (disk.value / mem.value) * 100 "Ratio"
  FROM v$sysstat mem, v$sysstat disk
WHERE mem.name = 'sorts (memory)'
   AND disk.name = 'sorts (disk)'

/* 查询正在做的事务 */
SELECT xid, status, start_time, xidusn seg_num, r.name seg_name
  FROM v$transaction t, v$rollname r
WHERE t.xidusn = r.usn

/* 查询父表外键引用的子表 */
SELECT a.table_name 外键表, a.constraint_name 外键名, b.column_name 外键字段,
       c.table_name 主键表, a.r_constraint_name 主键名,
       c.column_name 主键字段
  FROM user_constraints a, user_cons_columns b, user_cons_columns c
WHERE a.constraint_type = 'R'
   AND a.constraint_name = b.constraint_name
   AND a.r_constraint_name = c.constraint_name
   AND LOWER(c.table_name) = '&table_name'

/* XXX 等待 XXX 用户 */
SELECT w.sid || ' wait ' || w.blocking_session
  FROM v$session a, v$session w
WHERE w.blocking_session = a.sid

/* Oracle中的一些进程信息, 便于在solaris中去kill进程 */
SELECT s.SID, s.serial#, spid AS "PROCESS OR THREAD", s.osuser, s.program,
       NVL2(s.blocking_session, 'WAIT ' || s.blocking_session,
                               'NO WAIT') "WAIT INFO",
       s.machine, s.terminal
  FROM v$process p, v$session s, v$session w
WHERE p.addr = s.paddr
   AND s.blocking_session = w.sid(+);

/* Parse */
SELECT name,value
  FROM v$sysstat
WHERE name LIKE '%parse%'

/* 监测用户执行的SQL1 */
SELECT s.*
  FROM v$sql s, dba_users u
WHERE s.parsing_user_id = u.user_id
   AND u.username = UPPER('&username');

/* 监测用户执行的SQL2 */
SELECT s.parse_calls, s.*
  FROM v$sql s, dba_users u
WHERE s.parsing_user_id = u.user_id
   AND u.username = UPPER('&username')
   AND s.module = 'JDBC Thin Client'
   --AND s.module = 'TOAD 9.0.0.160'
   AND s.sql_text LIKE 'SELECT%'

/* 查询pga命中率有关的信息 */
SELECT * FROM v$pga_target_advice

PGA的参数pga_aggregate_target

/* UNDO_TABLESPACE大小的配置 */
SELECT ((SELECT value
           FROM v$parameter
          WHERE name = 'undo_retention') *
        (SELECT MAX(undoblks)
           FROM v$undostat) *
        (SELECT value
           FROM v$parameter
          WHERE name = 'db_block_size')) / 1024 / 1024 * (1 + 0.2)|| ' MB'
         undo_tablespace_size
  FROM dual

/* 查询表空间使用情况这里的使用率还包含了回收站里的对象(不包括临时表空间)(在11g上用) */
SELECT c.ts#, c.name,  d.contents, d.extent_management, e.file_bytes, c.used,
       SUBSTR (c.used / e.file_bytes * 100, 1, 5)
  FROM (SELECT name, ts#, SUM(used) used
          FROM (SELECT a.allocated_space * (SELECT value  -- 查询db_block_size当前值
                                              FROM v$parameter
                                             WHERE name = 'db_block_size') / 1024/ 1024 used,
                        b.ts#, b.name
                  FROM v$filespace_usage a, v$tablespace b
         WHERE a.tablespace_id = b.ts#)
         GROUP BY name, ts#) c,
        dba_tablespaces d,
       (SELECT ts#, SUM(bytes) / 1024/ 1024 file_bytes
          FROM v$datafile
         GROUP BY ts#) e      
WHERE c.name = d.tablespace_name
   AND e.ts# = c.ts#
ORDER BY ts#

-- 这里还有条语句但是效率不高, 查询数据字典还是尽量查询v$或者x$开头的视图
SELECT a.a1 表空间名称, c.c2 类型, c.c3 区管理,
       b.b2 / 1024 / 1024 表空间大小m, (b.b2 - a.a2) / 1024 / 1024 已使用m,
       SUBSTR ((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
  FROM (SELECT   tablespace_name a1, SUM (NVL (BYTES, 0)) a2
            FROM dba_free_space
        GROUP BY tablespace_name) a,
       (SELECT   tablespace_name b1, SUM (BYTES) b2
            FROM dba_data_files
        GROUP BY tablespace_name) b,
       (SELECT tablespace_name c1, contents c2, extent_management c3
          FROM dba_tablespaces) c
WHERE a.a1 = b.b1 AND c.c1 = b.b1
ORDER BY 表空间名称

/* 查询使用表相关的SQL */
SELECT *
  FROM v$tempseg_usage t1, v$sql t2
WHERE t1.sql_id = t2.sql_id


/* 将常用的包缓存到SHARED_POOL中  */
BEGIN
  DBMS_SHARED_POOL.KEEP('DBMS_ALERT');
  DBMS_SHARED_POOL.KEEP('DBMS_DDL');
  DBMS_SHARED_POOL.KEEP('DBMS_DESCRIBE');
  DBMS_SHARED_POOL.KEEP('DBMS_LOCK');
  DBMS_SHARED_POOL.KEEP('DBMS_OUTPUT');
  DBMS_SHARED_POOL.KEEP('DBMS_PIPE');
  DBMS_SHARED_POOL.KEEP('DBMS_SESSION');
  DBMS_SHARED_POOL.KEEP('DBMS_SHARED_POOL');
  DBMS_SHARED_POOL.KEEP('DBMS_STANDARD');
  DBMS_SHARED_POOL.KEEP('DBMS_UTILITY');
  DBMS_SHARED_POOL.KEEP('STANDARD');
END;
/


/* 解决索引赤色 */
1, 分析索引结构
ANALYZE INDEX &index_name VALIDATE STRUCTURE
2, 查询索引是否对称, 如果比率超过20%就应该重建索引
SELECT ROUND(del_lf_rows_len / lf_rows_len * 100) || '%' BALANCE_RATIO
  FROM index_stats
WHERE name = UPPER('&index_name')
3, 重建索引
ALTER INDEX &index_name REBUILD ONLINE;

/* 定位数据库中的热点块 */
SELECT b.obj object, o.owner, o.object_name, o.object_type,
       o.status, b.dbarfil file#, b.dbablk block#, b.tch touches
  FROM x$bh b, dba_objects o
WHERE b.tch > 10
   AND o.object_id = b.obj
ORDER BY b.tch DESC

/* 字典缓存的命中率 */
SELECT parameter, gets, getmisses, usage, (gets - getmisses) / gets WHEN 0 THEN NULL
                                                                    ELSE gets END
         
  FROM v$rowcache

/* 查询回闪区的容量 */
SELECT SUBSTR (name, 1, 30) name, space_limit AS total, space_used AS used,
       space_used / space_limit AS pct_used, space_reclaimable AS reclaimable,
       number_of_files AS files
  FROM v$recovery_file_dest;
参数
db_recovery_file_dest_size --回闪区的大小


/* Oracle中对内存分配的建议 */
SELECT * FROM v$pga_target_advice
SELECT * FROM v$pga_target_advice_histogram
SELECT * FROM v$mttr_target_advice
SELECT * FROM v$px_buffer_advice
SELECT * FROM v$db_cache_advice
SELECT * FROM v$sga_target_advice
SELECT * FROM v$shared_pool_advice
SELECT * FROM v$java_pool_advice
SELECT * FROM v$streams_pool_advice

-- 查找低劣的SQL

/* RMAN */
crosscheck archivelog all;
delete expired archivelog all;

-- 查询隐含参数
SELECT a.indx, a.ksppinm, a.ksppdesc, a.ksppiflg,
       a.ksppilrmflg, b.ksppstvl, b.ksppstdf, b.ksppstvf
  FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
   AND ksppinm LIKE '\_%' ESCAPE '\'

--得到服务器ip
set serverout on
exec dbms_output.put_line(utl_inaddr.get_host_address);

-- 有关latch的
/* miss的百分比，不要超过1% */
SELECT name,
       gets, misses * 100 / DECODE (gets, 0, 1, gets) misses,
       spin_gets * 100 / DECODE (misses, 0, 1, misses) spins,
       immediate_gets igets,
       immediate_misses * 100 / DECODE (immediate_gets, 0, 1, immediate_gets) imisses
  FROM v$latch
WHERE gets <> 0
ORDER BY gets DESC

SELECT a.name, a.gets gets,
       a.misses * 100 /
       DECODE(a.gets, 0, 1, a.gets) miss,
       TO_CHAR(a.spin_gets * 100 / DECODE(a.misses, 0, 1, a.misses), '990.9') || TO_CHAR (a.sleep6 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') cspins,
       TO_CHAR (a.sleep1 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep7 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep1,
       TO_CHAR (a.sleep2 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep8 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep2,
       TO_CHAR (a.sleep3 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep9 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep3,
       TO_CHAR (a.sleep4 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep10 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep4, 
       TO_CHAR (a.sleep5 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') || TO_CHAR (a.sleep11 * 100 / DECODE (a.misses, 0, 1, a.misses), '90.9') csleep5
  FROM v$latch a
WHERE a.misses <> 0
ORDER BY 2 DESC

--DBMS_METADATA.GET_DDL
1.得到一个表或索引的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL;
select dbms_metadata.get_ddl('INDEX','PK_DEPT','SCOTT') from dual;

2.得到一个用户下的所有表，索引，存储过程的ddl
SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)
FROM USER_OBJECTS u
where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');

3.得到所有表空间的ddl语句
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;

4.得到所有创建用户的ddl
SELECT DBMS_METADATA.GET_DDL('USER',U.username)
FROM DBA_USERS U;

和处理AWR快照相关
SELECT * FROM DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
END;
/

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(56, 57);
END;
/

SQL> @?/rdbms/admin/awrrpt.sql

-- 查询无效索引
SELECT INDEX_NAME FROM USER_INDEXES WHERE STATUS <> 'VALID' AND PARTITIONED = 'NO';
SELECT INDEX_NAME, PARTITION_NAME FROM USER_IND_PARTITIONS WHERE STATUS <> 'USABLE';
-- 生成重建索引的语句
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD' FROM USER_INDEXES WHERE STATUS <> 'VALID' AND PARTITIONED = 'NO';
SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME FROM USER_IND_PARTITIONS WHERE STATUS <> 'USABLE';

-- 收集统计信息
cascade设置为true is also gather columns and index’s statistics
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'SA' , tabname=>'T_PRODUCT_SUBSCRIBE',estimate_percent=>100, degree=>1, cascade=> TRUE);

exec DBMS_STATS.SET_TABLE_STATS(ownname=> 'SA' , tabname=>'T_PRODUCT_SUBSCRIBE', numrows=>1000000);

11g上自动统计信息相关
select client_name,status from Dba_Autotask_Client;
select log_date,status from dba_scheduler_job_run_details
        where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date desc;
select job_name,state from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB' ;

BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection', OPERATION => NULL, WINDOW_NAME => NULL); END;

-- 从操作系统上杀数据库连接
ps -ef | grep LOCAL | grep -v grep | awk '{print $2}' | xargs kill -9

跟踪会话
SELECT P.PID, P.SPID
  FROM V$PROCESS P, V$SESSION S
WHERE P.ADDR = S.PADDR
   AND S.SID = 154
  
-- 使用V$PROCESS中的列SPID
ORADEBUG SETOSPID XX
-- 使用V$PROCESS中的PID
ORADEBUG SETORAPID XX
  
-- 开启跟踪
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

ORADEBUG TRACEFILE_NAME

-- 关闭跟踪
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF

tkprof C:\01\orcl_ora_652.trc C:\01\orcl_ora_652.txt aggregate=yes sys=no sort=prscnt

--
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('4ba4mf4mknjvr', 0, 'ALL'));
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('4ba4mf4mknjvr', 0, 'iostats last'));