平时整理常用脚本--oracle

1. 表空间剩余空间

SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /
(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space
GROUP BY tablespace_name

SELECT * FROM dba_tablespace_usage_metrics;

2. 表空间数据量情况显示

SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks,
to_char(100*sum_free_blocks/sum_alloc_blocks, '99.99') || '%' AS pct_free
FROM ( SELECT tablespace_name
, sum(blocks) AS sum_alloc_blocks
FROM dba_data_files
GROUP BY tablespace_name
)
, ( SELECT tablespace_name AS fs_ts_name
, max(blocks) AS max_blocks
, count(blocks) AS count_blocks
, sum(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name

3. 表和索引分析

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);
END ;
或者
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);

4. 检查tablespace情况

SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk

5. 检查已经存在的空间extent

SELECT count(*), segment_name, segment_type, dt.tablespace_name
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dt.next_extent != dx.bytes AND dx.owner = 'ECIF'
GROUP BY segment_name, segment_type, dt.tablespace_name

6. 检查没有主键的表

SELECT table_name
FROM all_tables
WHERE owner = 'ECIF'
MINUS
SELECT table_name
FROM all_constraints
WHERE owner = 'ECIF'
AND constraint_type = 'P'

7. 检查失效的主键

SELECT owner, constraint_name, table_name, status
FROM all_constraints
WHERE owner = 'ECIF' AND status = 'DISABLED' AND constraint_type = 'P'

8. 重建某个表空间上的全部索引

SELECT 'alter index ' || index_name || ' rebuild '
, 'tablespace new_tablespace_name nologging; '
FROM all_indexes
WHERE ( tablespace_name= 'TABLESPACE_NAME') AND owner = 'ECIF'

9. 对比两个schema的不同

SELECT object_name, object_type
FROM dba_objects where owner='OWNER_NAME_1'
MINUS
SELECT object_name, object_type
FROM dba_objects where owner='OWNER_NAME_2';

10. 查看全部动态性能视图

SELECT * from V$FIXED_TABLE;

11. 查看约束

select a.constraint_name, a.constraint_type,a.*
from user_constraints a
where table_name='TABLE_NAME';

select constraint_name, column_name
from user_cons_columns
where table_name='TABLE_NAME';

12. 查看索引
user_indexes包含索引的名字,user_ind_columns包含索引的列.

13. 查看数据库启动参数：
show parameter para，v$parameter提供当前会话信息，v$system_parameter提供当前系统信息。其中isses_modifiable，issys_modifiable表示是否允许动态修改。

14. 查看进程号：

select p.spid, s.username
from v$process p , v$session s
where p.addr=s.paddr;

15. 查看数据文件：

select name, status
from v$datafile;

select *
from dba_data_files;

16. 查看数据文件状态

select d.file# f#, d.name, d.status, h.status
from v$datafile d, v$datafile_header h
where d.file#=h.file#;

17. 查看控制文件

select name
from v$controlfile;

select type, record_size, records_total, records_used
from v$controlfile_record_section
where type='DATAFILE';

18. 查看是否归档模式：

archive log list

select name, log_mode
from v$database;

select archiver
from v$instance;

19. 查看日志组：

select groups, current_group#, sequence#
from v$thread;

select group#, sequence#, bytes, members, status
from v$log;

select *
from v$logfile;

其中status为空表示正常。

20. 查看large pool

select *
from v$sgastat
where pool='large pool';

21. 查看归档位置

show parameter archive

select destination, binding, target, status
from v$archive_dest;

22. 查看归档进程

select *
from v$archive_processes;

23. 查看正在备份的数据文件

select *
from v$backup;

24. 查看需要恢复的文件

select *
from v$recover_file;

25. 查看所有归档日志文件

select *
from v$archived_log;

26. 查看恢复时要用到的日志文件

select *
from v$recovery_log;

27. 查看SGA的结构

Show sga;

select *
from v$sgastat;

28. 提取library cache的命中率

select gethitratio
from v$librarycache
where namespace='…';

29. 查看正在运行的SQL语句

select sql_text, users_executing, executions, loads
from v$sqlarea;

30. 查看library cache reload情况：

select sum(pins) "Executions", sum(reloads) "cache Misses", sum(reloads)/sum(pins)
from v$librarycache;

31. 查看大匿名块

select sql_text from v$sqlarea
where command_type=47
and length(sql_text)>500;

32. 查看当前会话的UGA区

select sum(value)||'bytes' "Total session memory"
from v$mystat, v$statname
where name='session uga memory'
and v$mystat.statistic#=v$statname.statistic#;

33. 查看所有MTS用户的UGA区：

select sum(value)||'bytes' "Total session memory"
from v$sesstat, v$statname
where name='session uga memory'
and v$sesstat.statistic#=v$statname.statistic#;

34. 查看所有用户使用的最大的UGA区：

select sum(value)||'bytes' "Total session memory"
from v$sesstat, v$statname
where name='session uga memory max'
and v$sesstat.statistic#=v$statname.statistic#;

35. 查看high-water mark以下的块数

select table_name, blocks
from dba_tables
where table_name='table_name';

36. 查看会话的I/O：

select io.block_gets, io.consistent_gets, io.physical_reads
from v$sess_io io, v$session s
where s.audsid=USERENV('SESSIONID')
and io.sid=s.sid;

37. 查看Buffer pool的命中率

select name, 1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets+consistent_gets>0;

38. 查看free list的竞争

select class, count, time
from v$waitstat
where class='segment header';

select event, total_waits
from v$system_event
where event='buffer busy waits';

buffer busy waits可在两种情况发生：1dirty queue已满，2free list竞争。

39. 查看free list竞争发生在哪个segment上

select s.segment_name, s.segment_type, s.freelists, w.wait_time,
w.seconds_in_wait, w.state
from dba_segments s, v$session_wait w
where w.event='buffer busy waits'
and w.p1=s.header_file
and w.p2=s.header_block;

40. 查看全表扫描发生的次数

select name, value
from v$sysstat
where name like '%table scan%';

41. 查看大操作的执行情况

select sid, serial#, opname,
to_char(start_time, 'HH24:MI:SS') as start_t,
(sofar/totalwork)*100 as percent_complete
from v$session_longops;

42. 查看数据文件的I/O

select phyrds, phywrts, d.name
from v$datafile d, v$filestat f
where d.file#=f.file# order by d.name;

43. 查看空闲块数少于10%的segment(blocks在high-water mark以下，empty_blocks其上)

select owner, table_name, blocks, empty_blocks
from dba_tables
where empty_blocks/(blocks+empty_blocks)<0.1 and blocks+empty_blocks!=0;

44. 查看migration和chaining

检查之前要先分析表,dbms_stats.gather_table_stats一下.
select num_rows, chain_cnt
from dba_tables
where table_name='table_name';

45. 查看表的统计信息
先分析表
select num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
from dba_tables
where owner='ECIF' and
table_name='table_name';

46. 查看索引的统计信息
先分析索引
select (del_lf_rows_len/lf_rows_len)*100 as index_usage
from index_stats;

----------------------------------------------------------------------------------
-- 确认控制文件重做日志文件 数据文件的位置和名称
-- 这些文件和初始化参数文件{init(SID).ora}是冷备份的对象
----------------------------------------------------------------------------------
select * from v$controlfile;
select * from v$logfile;
select FILE#, STATUS, ENABLED, NAME from v$datafile;

----------------------------------------------------------------------------------
--关于重做日志的信息
----------------------------------------------------------------------------------
select * from v$log;
select * from v$logfile;

----------------------------------------------------------------------------------
--关于数据文件的信息
----------------------------------------------------------------------------------

select a.TABLESPACE_NAME, a.FILE_ID, a.FILE_NAME,
a.BYTES/1024/1024 SIZE_MB, b.CREATE_BYTES/1024/1024 CREATE_MB,
a.INCREMENT_BY*b.BLOCK_SIZE/1024/1024 NEXT_MB, a.MAXBYTES/1024/1024 MAX_MB
from DBA_DATA_FILES a, v$datafile b
where a.FILE_ID = b.FILE#;

----------------------------------------------------------------------------------
-- 数据文件的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.FILE_ID, a.FILE_NAME,
min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB,
sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.FILE_ID, a.FILE_NAME;
----------------------------------------------------------------------------------
--关于表空间的信息
----------------------------------------------------------------------------------

select TABLESPACE_NAME, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB, NEXT_EXTENT/1024 NEXT_EXTENT_KB,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, LOGGING
from dba_tablespaces;

----------------------------------------------------------------------------------
-- 表空间的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.TABLESPACE_NAME,
min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME;

select a.TABLESPACE_NAME, a.FILE_NAME, sum(a.BYTES)/1024 SIZE_KB, sum(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME, a.FILE_NAME;

------------------------------------------------------------------------------------
--extent的信息:extent数为2个以上的数据段的一览表
------------------------------------------------------------------------------------
select TABLESPACE_NAME, OWNER, SEGMENT_NAME, count(*)
from dba_extents
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME
having count(*) >= 2;

select TABLESPACE_NAME, OWNER, TABLE_NAME,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
from dba_tables
where (OWNER, TABLE_NAME) in (
select OWNER, SEGMENT_NAME
from dba_extents
where SEGMENT_TYPE = 'TABLE'
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
having count(*) >= 2)
order by TABLESPACE_NAME, OWNER, TABLE_NAME;

select TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
from dba_indexes
where (OWNER, INDEX_NAME) in (
select OWNER, SEGMENT_NAME
from dba_extents
where SEGMENT_TYPE = 'INDEX'
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
having count(*) >= 2)
order by TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME;
------------------------------------------------------------------------------------
--表信息表信息
------------------------------------------------------------------------------------
break on OWNER
select OWNER, TABLE_NAME,
PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
TABLESPACE_NAME
from dba_tables -- WHERE TABLE_NAME='TABLE_NAME'
order by OWNER, TABLE_NAME;

--表信息(2)
----------------------------------------------------------------------------------
-- 为了求出NUM_ROWS,BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN的值,
-- 需要事先执行dbms_stats命令。
-- exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'ECIF', tabname=>'TABLE_NAME', estimate_percent=>35, method_opt=>'for all indexed columns size AUTO',cascade=>true, degree=>4);
----------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
NUM_ROWS, BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
TABLESPACE_NAME
from dba_tables
order by OWNER, TABLE_NAME;

------------------------------------------------------------------------------------
--索引信息:索引信息(1)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
INI_TRANS, MAX_TRANS, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE,
STATUS, TABLESPACE_NAME
from dba_indexes
order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;

------------------------------------------------------------------------------------
--索引信息(2)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY,
STATUS, TABLESPACE_NAME
from dba_indexes
order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;

------------------------------------------------------------------------------------
--关于用户的信息用户信息
------------------------------------------------------------------------------------
select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE
from dba_users
order by USERNAME;
------------------------------------------------------------------------------------
--分配给用户的权限
------------------------------------------------------------------------------------
break on GRANTEE
select * from dba_role_privs order by GRANTEE, GRANTED_ROLE;

----------------------------------------------------------------------------------
--基本信息
----------------------------------------------------------------------------------
select * from v$version;
select * from v$option;
select * from v$sga;
select * from v$instance;
select * from v$database;
col VALUE format a40
select * from v$parameter;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13159425/viewspace-1048679/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13159425/viewspace-1048679/