set time on; 设置时间 alter table flashback_test enable row movement; 开启行移 select * from flashback_test as of timestamp to_timestamp('2019-2-22 09:45:43','yyyy-mm-dd hh24:mi:ss'); 查看时间点的内容 show parameter recyclebin; 查看回收站功能是否开启 select * from t11 versions between timestamp(systimestamp -interval '12' minute) and maxvalue; 查询12分组以内的改动 select versions_xid,versions_startscn,versions_endscn,id from t11 versions between timestamp(systimestamp - interval '13' minute) and maxvalue; 事务闪回查询 sqlplus sys/123456@oracle01 as sysdba 登入 select status from v$instance; 查看状态 nomount mount open select file#,checkpoint_change# from v$datafile_header; 数据文件头检查 select file#,checkpoint_change#,last_change# from v$datafile; 数据文件检查 select checkpoint_change# from v$database; 系统检查 alter database open 数据库打开 一、switchover_status概念: A 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库alter database commit to switchover to primary B 如果switchover_status为SESSION ACTIVE 就应该断开活动会话alter database commit to switchover to primary with session shutdown; C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能执行转换。 SHOW PARAMETER background_dump_dest 查看数据库的日志文件 select status from v$instance; 数据库状态 select * from v$controlfile; 查看控制文件是否正常 select * from v$log;查看日志 select * from v$logfile;查看日志文件 查看表空间物理文件的名称及大小 select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) filesize from dba_data_files order by tablespace_name; 查询表空间的碎片程度 select tablespace_name, count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name) > 10; 查看碎片程度高的表 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 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'; 查看Oracle字符集 select * from sys.props$ where name = 'NLS_CHARACTERSET'; 查看还没提交的事务 select * from v$locked_object; select * from v$transaction; 查看所有用户: select * from dba_users; select * from all_users; select * from user_users; 查看用户或角色系统权限(直接赋值给用户或角色的系统权限): select * from dba_sys_privs; select * from user_sys_privs; 查看角色(只能查看登陆用户拥有的角色)所包含的权限 sql>select * from role_sys_privs; 查看用户对象权限: select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; 查看所有角色: select * from dba_roles; 查看用户或角色所拥有的角色: select * from dba_role_privs; select * from user_role_privs; 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限) select * from V$PWFILE_USERS 比如我要查看用户 kingle的拥有的权限: SQL> select * from dba_sys_privs where grantee='kingle'; GRANTEE PRIVILEGE ADMIN_OPTION ------------------------------ ---------------------------------------- ------------ kingle CREATE TRIGGER NO kingle UNLIMITED TABLESPACE NO 比如我要查看用户 kingle的拥有的角色: SQL> select * from dba_role_privs where grantee='kingle'; GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE ------------------------------ ------------------------------ ------------ ------------ kingle DBA NO YES 查看一个用户所有的权限及角色 select privilege from dba_sys_privs where grantee='kingle' union select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='kingle' );\
确定数据库 的启动方式:
方式一 show parameter spfile
方式二
SQL> set linesize 120
SQL> col name format a10
SQL> col value format a60
SQL> select name,value from v$parameter where name='spfile';
-- 查看表空间文件
select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME='TEST03';
--查询表空间的free space
select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
--查询表空间使用率
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
查看系统的SCN号
select dbms_flashback.get_system_change_number from dual;
select checkpoint_change# from v$database;
回滚查询
1 查询数据库的的回滚段情况
select owner,segment_id,segment_name,tablespace_name,status from dba_rollback_segs;
2 查看系统回滚段基本信息
select segment_name,tablespace_name,bytes,next_extent from dba_segments where segment_type='ROLLBACK';
从上面仅仅是查询到回滚段的基本信息,要了目前各个回滚段的动态信息,还要查询V$ROLLNAME和V$ROLLSTAT视图。V$ROLLNAME视图只存放各回滚段的编号和名字,V$ROLLSTATS存放各个回滚段当前的情况信息。要想得到每个回滚段的信息,就要查询两个表的信息才能得到。如:
select s.usn,n.name,s.extents,s.optsize,s.hwmsize,s.status from v$rollstat s, v$rollname n where s.usn=n.usn
2 查看回滚段的使用情况,哪个用户正在使用回滚段的资源(当提交或回滚后资源释放):
SELECT s.username, u.name FROM v$transaction t, v$rollstat r, v$rollname u, v$session s WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn ORDER BY s.username;
3 回滚段当前活动的事物(事务提交或回滚后自动清空)
SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk FROM v$session s,v$transaction t;
4 分析 UNDO 的使用情况
SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS;
5 监控undo表空间
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT;
6 查询是否有回滚段的争用
select * from v$waitstat;
SELECT name, waits, gets, waits/gets "Ratio" FROM v$rollstat a, v$rollname b WHERE a.usn = b.usn;
7 查看回滚段的统计信息:
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn;
8 查询回滚段的事务回退率
transaction rollbacks/(transaction rollbacks+user commits)
select name,value from v$sysstat where name in('user commits','transaction rollbacks');
9 查询回滚段在使用,扩展,回缩的时候extent在循环的次数
select usn,wraps from v$rollstat;
10 查询回滚段收缩的情况
select usn,optsize,shrinks from v$rollstat;
监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4 ;
回滚段的争用情况 select name, waits, gets, waits / gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn; 查看回滚段名称及大小 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 NAME FROM v$controlfile; 查看日志文件 SELECT MEMBER FROM v$logfile; 查看前台正在发出的SQL语句 select user_name,sql_text from v$open_cursor where sid in (select sid from (select sid,serial#,username,program from v$session where status='ACTIVE')); 数据表占用空间大小情况 select segment_name, tablespace_name, bytes, blocks from user_segments where segment_type = 'TABLE' ORDER BY bytes DESC, blocks DESC; 查看表空间碎片大小 select tablespace_name,round(sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))),2) FSFI from dba_free_space group by tablespace_name order by 1; 查看表空间占用磁盘情况 select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes 字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余空间, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id; 查看表的大小,倒序排列 每张表都是作为“段”来存储的,可以通过user_segments视图查看其相应信息。 段(segments)的定义:如果创建一个堆组织表,则该表就是一个段 SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' GROUP BY SEGMENT_NAME order by MBYTESE desc; 查看表空间物理文件的名称及大小 SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; 查看Oracle 表空间使用率 SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --如果有临时表空间 SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", USED_SPACE || 'M' "USED_SPACE(M)", ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1; 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 查看Temp 表空间实际使用磁盘大小 Select f.tablespace_name, d.file_name "Tempfile name", round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB", round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024, 2) "Free MB", round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB", round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) / round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100, 2) as "Used_Rate(%)" from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p where f.tablespace_name(+) = d.tablespace_name and f.file_id(+) = d.file_id and p.file_id(+) = d.file_id; 查看session使用回滚段 SELECT r.name 回滚段名, s.sid, s.serial#, s.username 用户名, t.status, t.cr_get, t.phy_io, t.used_ublk, t.noundo, substr(s.program, 1, 78) 操作程序 FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r WHERE t.addr = s.taddr and t.xidusn = r.usn ORDER BY t.cr_get,t.phy_io; 查看当前临时表空间使用大小与正在占用临时表空间的sql语句 select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text from v$sort_usage sort, v$session sess, v$sql sql where sort.SESSION_ADDR = sess.SADDR and sql.ADDRESS = sess.SQL_ADDRESS order by blocks desc; Temp表空间上进程的查询 select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text from v$sort_usage a,v$session b,v$sqltext c where a.session_addr = b.saddr and b.sql_address = c.address order by a.tablespace,b.sid,b.serial#,c.address, c.piece; 查看SGA区剩余可用内存 select name, sgasize/1024/1024 "Allocated(M)", bytes/1024 "**空间(K)", round(bytes/sgasize*100, 2) "**空间百分比(%)" from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = 'free memory'; 监控表空间I/O比例 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; 监控SGA命中率 select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40 ; 监控 SGA 中字典缓冲区的命中率 select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses ; 监控 SGA **享缓存区的命中率,应该小于1% select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache; 监控 SGA 中重做日志缓存区的命中率,应该小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy'); 监控内存和硬盘的排序比率,最好使它小于 .10 SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)') ; 监控字典缓冲区 SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE ; 非系统用户建在SYSTEM表空间中的表 SELECT owner,table_name FROM DBA_TABLES WHERE tablespace_name in('SYSTEM','USER_DATA') AND owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC'); 性能最差的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<100; 读磁盘数超100次的sql select * from sys.v_$sqlarea where disk_reads>100; 查找消耗资源比较的sql语句 Select se.username, se.sid, su.extents, su.blocks * to_number(rtrim(p.value)) as Space, tablespace, segtype, sql_text from v$sort_usage su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size' and su.session_addr = se.saddr and s.hash_value = su.sqlhash and s.address = su.sqladdr order by se.username, se.sid; 最频繁执行的sql select * from sys.v_$sqlarea where executions>100; 查询使用CPU多的用户session select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc; 当前每个会话使用的对象数 SELECT a.sid,s.terminal,s.program,count(a.sid) FROM V$ACCESS a,V$SESSION s WHERE a.owner <> 'SYS'AND s.sid = a.sid GROUP BY a.sid,s.terminal,s.program ORDER BY count(a.sid) ; 查看数据库库对象 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; 检查角色和权限设置 根据用户名进行授权的对象级特权 select b.owner || '.' || b.table_name obj, b.privilege what_granted, b.grantable, a.username from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1, 2, 3; 根据被授权人进行授权的对象级特权 Select owner || '.' || table_name obj, privilege what_granted, grantable, grantee from sys.dba_tab_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2, 3; 根据用户名进行授予的系统级特权 select b.privilege what_granted, b.admin_option, a.username from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1, 2; 根据被授权人进行授予的系统级特权 select privilege what_granted, admin_option, grantee from sys.dba_sys_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2; 根据用户名授予的角色 select b.granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted, a.username from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1; 根据被授权人授予的角色 select granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted, grantee from sys.dba_role_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1; 用户名及已被授予的相应权限 select a.username, b.granted_role || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee UNION select a.username, b.privilege || decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee UNION select a.username, b.table_name || '-' || b.privilege || decode(grantable, 'YES', ' (With Grant Option)', null) what_granted from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1; 查询用户名及相应的配置文件、默认的表空间和临时表空间 Select username, profile, default_tablespace, temporary_tablespace, created from sys.dba_users