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'));