ORACLE系统查询语句(三)
.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');