python模块名功能明和别名_三月 | 2016 | 杭州明和科技股份有限公司官方博客

整理的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 清空共享池