dba常用脚本

oracle用户权限检查
1. 系统权限记录在 dba_sys_privs 视图中：
select grantee, privilege, admin_option
from dba_sys_privs
where grantee = 'USERNAME' ;
2. 角色权限记录在 dba_role_privs 视图中：
select grantee, granted_role, admin_option
from dba_role_privs
where grantee = 'USERNAME' ;
3. 对象权限记录在 dba_tab_privs 视图中，虽然视图名看起来像是表权限，但是实际包含了包，存储过程等等对象级的权限：
select grantee, owner || '.' || table_name obj_name, privilege, grantable
from dba_tab_privs
where grantee = 'USERNAME';
4. 其他还有部分权限，如表空间的quota权限，记录在 dba_ts_quota中：
select username,
tablespace_name,
bytes / 1024 / 1024 "used space (m)",
decode(decode(max_bytes, -1, 0, max_bytes) / 1024 / 1024,
0,
'unlimited',
max_bytes / 1024 / 1024) "max space(m)",
blocks used_blocks,
max_blocks
from dba_ts_quotas
where username = 'USERNAME';
select * from session_privs; 当前用户拥有的权限
SELECT * FROM DBA_SYS_PRIVS; 查询每个用户的权限
锁的检查
select b.owner, b.object_name, l.SESSION_ID, DECODE(L.LOCKED_MODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)',
3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(L.LOCKED_MODE)) MODE_HELD, s.USERNAME from dba_objects b, v$locked_object l, V$SESSION S where b.object_id = l.object_id and l.SESSION_ID = s.SID
查看一个长查询的进度
SELECT *
FROM ( select username,opname,sid,serial#,
context,b.sql_text,sofar,totalwork,
round(sofar/totalwork*100,2) "% Complete",
elapsed_seconds
from v$session_longops , v$sql b
where sql_hash_value=b.hash_value
and sql_address = address
and totalwork <> 0
)
WHERE "% Complete" <> 100 ;
查看ACTIVE SESSION的等待事件
select a.event,,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Tot"
from v$session_wait a, v$session b
where a.sid = b.sid
and b.status = 'ACTIVE'
group by a.event
-- having count(*) > 10
order by 4;
在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
通过DBMS_METADATA包得到对象的DLL语句
a.获取单个的建表和建索引的语法,其他对象类似：
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
b.获取一个SCHEMA下的所有建表的语法，以scott为例，其他对象类似：
SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, u.owner) || ';'
FROM DBA_TABLES u
where owner = 'SCOTT' ;
查看回滚段名称及大小
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 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 version FROM Product_component_version Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
查看数据库的创建日期和归档方式
Select Created, Log_Mode From V$Database;
查看还没提交的事务
select * from v$locked_object; select * from v$transaction;
查找object为哪些进程
select p.spid, s.sid, s.serial# serial_num, s.username user_name, a.type object_type, s.osuser os_user_name, a.owner, a.object object_name, decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command)) action, p.program oracle_process, s.terminal terminal, s.program program, s.status session_status from v$session s, v$access a, v$process p where s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object = 'EMP' order by s.username, s.osuser
怎样计算一个表占用的空间的大小？
select owner,
table_name,
NUM_ROWS,
BLOCKS * AAA / 1024 / 1024 "Size M",
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name = 'XXX';
注意:执行以上语句前要先对表做统计分析
select sum(a.bytes) / 1024 * 1024 "size" from dba_extents a where a.segment_name = 'GOV_FDDBR'
select a.bytes / 1024 * 1024 "size", (a.blocks * 8192) / 1024 * 1024 "da" from dba_segments a where a.segment_name = 'GOV_FDDBR'
如何查看最大会话数
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 1
processes integer 200
这里为200个用户。
select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数
如何在Oracle服务器上通过SQLPLUS查看本机IP地址
select sys_context('userenv','ip_address') from dual;
如何将表、索引移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME; ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
如何才能得知系统当前的SCN号
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
如何在字符串里加回车
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual；
如何将小表放入keep池中
alter table xxx storage(buffer_pool keep);
如何查询做比较大的排序的进程？
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, ¡¡ a.username, a.osuser, a.status ¡¡ FROM v$session a, v$sort_usage b ¡¡ WHERE a.saddr = b.session_addr ¡¡ ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
如何删除重复记录？
DELETE FROM TABLE_NAME WHERE ROWID != (SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1 = D.COL1
AND TABLE_NAME.COL2 = D.COL2)；
如何回滚段的争用情况
select name, waits, gets, waits / gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
如何监控表空间的 I/O 比例
select B.tablespace_name name,
B.file_name "file",
A.phyrds pyr,
A.phyblkrd pbr,
A.phywrts pyw,
A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
如何监控文件系统的 I/O 比例
Select substr(C.file#, 1, 2) "#",
substr(C.name, 1, 30) "Name",
C.status,
C.bytes,
D.phyrds,
D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
如何在某个用户下找所有的索引
select user_indexes.table_name,
user_indexes.index_name,
uniqueness,
column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
如何监控 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;
select sum(pinhits - reloads) / sum(pins) "hit radio", sum(reloads) / sum(pins) ¡¡¡¡"reload percent" ¡¡ 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，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts(disk)');
如何监控当前数据库谁在运行什幺SQL语句？
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by address, piece;
如何查看碎片程度高的表?
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 segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'TABLE'
group by tablespace_name, segment_name;
如何知道索引在表空间中的存储情况?
select segment_name, count(*)
from dba_extents
where segment_type = 'INDEX'
and owner = '&owner'
group by segment_name;
如何知道使用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#=11 and c.sid=a.sid and a.paddr=b.addr
order by value desc;
物理读和内存读较高SQL
SELECT t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS / t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS / t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME / t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME / t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS / t.EXECUTIONS > 500 OR
t.BUFFER_GETS / t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;
查看低效率的SQL语句
SELECT EXECUTIONS,
DISK_READS,
BUFFER_GETS,
ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,
ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0
AND BUFFER_GETS > 0
AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
ORDER BY EXECUTIONS DESC
或者
select executions exec,
loads loads,
parse_calls parse,
disk_reads reads,
buffer_gets gets,
rows_processed rows_proc,
sorts sorts,
sql_text
from v$sqlarea
order by &sortkey desc;
找出oracle中从没有使用的索引
set pages 999; set heading off; spool run_monitor.sql select 'alter index '||owner||'.'||index_name||' monitoring usage;'
from dba_indexes where owner not in ('SYS','SYSTEM','PERFSTAT') spool off;
@run_monitor
等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。
Select index_name,table_name,mon,used from v$object_usage;
在V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。不幸的是，它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。
自己编写的脚本
select c.SID,c.STATUS,s.NAME,b.VALUE from v$statname s, v$sesstat b, v$session c where s.STATISTIC# = b.STATISTIC# and b.SID = c.SID and b.sid=&sid and s.NAME in ('consistent gets','physical reads','parse count (total)','parse count (hard)')
V$sql:查询一条sql执行时间及消耗的cpu时间，被执行及被分析的次数
Parse_calls,
Executions
Cpu_time,
Elapsed_time

Alter system flush shared_pool 清空共享池

--表空间使用状态
SELECT b.tablespace_name, file_num,
round(total_bytes / 1024 / 1024 / 1024, 2) "total_space(g)",
round(free_bytes / 1024 / 1024 / 1024, 2) "free_space(g)",
round(free_bytes * 100 / total_bytes, 2) "free_per(%)",
round((total_bytes - free_bytes) * 100 / total_bytes, 2) "used_per(%)"
FROM (SELECT a.tablespace_name, COUNT(1) file_num, SUM(a.bytes) total_bytes
FROM dba_data_files a
GROUP BY a.tablespace_name) b,
(SELECT a.tablespace_name, SUM(a.bytes) free_bytes
FROM dba_free_space a
GROUP BY a.tablespace_name) c
WHERE b.tablespace_name = c.tablespace_name
ORDER BY b.tablespace_name;
--表空间的I/O情况
SELECT b.tablespace_name, SUM(a.phyrds) phyrds, SUM(a.phyblkrd) phyrds,
SUM(a.phywrts) phywrts, SUM(a.phyblkwrt) phyblkwrt
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
GROUP BY b.tablespace_name
ORDER BY b.tablespace_name;
SELECT *
FROM v$filestat;
--监控 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;
SELECT SUM(pinhits - reloads) / SUM(pins) "hit radio",
SUM(reloads) / SUM(pins) "reload percent"
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');
--监控事例的等待
SELECT event, SUM(decode(wait_time, 0, 0, 1)) prev, SUM(decode(wait_time, 0, 1, 0)) curr,
COUNT(*)
FROM v$session_wait
GROUP BY event
ORDER BY 4;
--查看数据库版本
SELECT *
FROM product_component_version;
--物理读和内存读较高SQL
SELECT t.hash_value, t.executions, t.disk_reads,
round(t.disk_reads / t.executions) AS perdiskreads, t.buffer_gets,
round(t.buffer_gets / t.executions) AS perbufferreads, t.elapsed_time,
round(t.elapsed_time / t.executions) AS perelaytime, t.cpu_time,
round(t.cpu_time / t.executions) AS percputime, t.first_load_time, t.sql_text
FROM v$sql t
WHERE (t.disk_reads / t.executions > 500 OR t.buffer_gets / t.executions > 20000)
AND t.executions > 0
ORDER BY 6 DESC;