oracle 的一些基础查询

select status,T.* from user_indexes T
where table_name='T_ADMIN_DEALER' --查询表是否有了索引 

select username,default_tablespace from dba_users;--查看用户和默认表空间的关系
select view_name from user_views --查看视图名字

select * from user_source; --查询所有函数和存储过程

select * from all_users;--查询所有用户

select * from v$Session; --查看当前用户连接

SELECT * FROM USER_ROLE_PRIVS;--查看用户角色

select * from session_privs; --查看当前用户权限

SELECT * FROM DBA_ROLE_PRIVS;--查看所有用户所拥有的角色

select * from dba_roles; --查看所有角色

SELECT NAME FROM V$DATABASE; --查看数据库名

select * from v$recovery_file_dest; -- 归档日志存放的位置

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;  --查看表空间的名称和大小

SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name; --查看表空间物理文件的名称及大小

SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name; --查看回滚段名称及大小 （select segment_name,status from dba_rollback_segs;）

SELECT NAME FROM v$controlfile; -- 查看控制文件 

SELECT MEMBER FROM v$logfile; --查看日志文件

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name; --查看表空间使用情况

SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;--查看数据库对象

SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';--查看数据库版本

SELECT created, log_mode, log_mode FROM v$database;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name；--查看数据库的创建日期和归档方式 

管理用户、角色和权限的DBA视图：
DBA_USERS         提供用户的信息
DBA_ROLES         显示数据库中所有角色
DBA_COL_PRIVS     显示列级对象授权
DBA_ROLE_PRIVS    显示用户及其角色
DBA_SYS_PRIVS     显示被授予系统权限的用户
DBA_TAB_PRIVS     显示用户及他们在表上的权限
ROLE_ROLE_PRIVS   显示授予角色的角色
ROLE_SYS_PRIVS    显示授予角色的系统权限
ROLE_TAB_PRIVS    显示授予角色的表权限
SESSION_PRIVS     显示允许用户使用的权限
SESSION_ROLES     显示当前允许用户使用的角色

SELECT NAME FROM V$DATABASE; --查看数据库名

select instance_name,host_name,startup_time,status,database_status from v$instance; --检查Oracle实例状态

select group#,status,type,member from v$logfile; --检查Oracle在线日志状态

 

select resource_name,
max_utilization,
initial_allocation,
limit_value
from v$resource_limit;--检查Oracle初始化文件中相关参数值

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
from v$session_wait
where event not like 'SQL%'
and event not like 'rdbms%';--检查数据库的等待事件

SELECT SQL_TEXT
FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM <= 5;--Disk Read最高的SQL语句的获取

SELECT *
FROM (SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;--最差的10条sql语句

SELECT *
FROM (SELECT *
FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL%'
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM <= 5;--等待时间最多的5个系统等待事件的获取


SELECT USERNAME,
SID,
OPNAME,
ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,
TIME_REMAINING,
SQL_TEXT
FROM V$SESSION_LONGOPS, V$SQL
WHERE TIME_REMAINING <> 0
AND SQL_ADDRESS = ADDRESS
AND SQL_HASH_VALUE = HASH_VALUE;--检查运行很久的SQL

SELECT P.PID PID,
S.SID SID,
P.SPID SPID,
S.USERNAME USERNAME,
S.OSUSER OSNAME,
P.SERIAL# S_#,
P.TERMINAL,
P.PROGRAM PROGRAM,
P.BACKGROUND,
S.STATUS,
RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM V$PROCESS P,
V$SESSION S,
V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%';--检查消耗CPU最高的进程

SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);--检查碎片程度高的表

SELECT DF.TABLESPACE_NAME NAME,
DF.FILE_NAME "FILE",
F.PHYRDS PYR,
F.PHYBLKRD PBR,
F.PHYWRTS PYW,
F.PHYBLKWRT PBW
FROM V$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;--检查表空间的I/O比例

SELECT SUBSTR(A.FILE#, 1, 2) "#",
SUBSTR(A.NAME, 1, 30) "NAME",
A.STATUS,
A.BYTES,
B.PHYRDS,
B.PHYWRTS
FROM V$DATAFILE A, V$FILESTAT B
WHERE A.FILE# = B.FILE#;-- 检查文件系统的I/O比例

select sid,
serial#,
username,
SCHEMANAME,
osuser,
MACHINE,
terminal,
PROGRAM,
owner,
object_name,
object_type,
o.object_id
from dba_objects o, v$locked_object l, v$session s
where o.object_id = l.object_id
and s.sid = l.session_id;--检查死锁及处理

SELECT a.VALUE + b.VALUE logical_reads,
c.VALUE phys_reads,
round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.NAME = 'db block gets'
AND b.NAME = 'consistent gets'
AND c.NAME = 'physical reads';--检查缓冲区命中率（如果命中率低于90%则需加大数据库参数db_cache_size。）

select sum(pinhits) / sum(pins) * 100 from v$librarycache; --检查共享池命中率（如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。）

select name,value from v$sysstat where name like '%sort%'; --检查排序区（如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。)

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries'); --检查日志缓冲区(如果redo buffer allocation retries/redo entries超过1%，则需要增大log_buffer。)



select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent
from (select tablespace_name, sum(bytes) total
from dba_free_space
group by tablespace_name) A,
(select tablespace_name, sum(bytes) total
from dba_data_files
group by tablespace_name) B
where A.tablespace_name = B.tablespace_name;--监控数据量的增长情况(根据本周每天的检查情况找到空间扩展很快的数据库对象,并采取相应的措施：

--删除历史数据
移动规定数据库中至少保留6个月的历史数据，所以以前的历史数据可以考虑备份然后进行清除以便释放其所占的资源空间。

---扩表空间

alter tablespace <tablespace_name> add datafile ‘<file>’ size <size> autoextend off;

注意：在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作，都会造成Oracle数据库控制文件的变化，DBA应及进行控制文件的备份，备份方法是：

执行SQL语句：

alter database backup controlfile to '/home/backup/control.bak';
或：

alter database backup controlfile to trace;

这样，会在USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的SQL命令。)

select index_name, table_name, tablespace_name, status
From dba_indexes
Where owner = 'CTAIS2'
And status <> 'VALID';--检查失效的索引(注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild，如：

alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME; )

SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLE'
and constraint_type = 'P';--检查不起作用的约束(如有失效约束则启用，如：

alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;)

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';--检查无效的trigger(如有失效触发器则启用，如：

alter Trigger TRIGGER_NAME Enable; )