dba 常用查询-白红宇

dba 常用查询

阅读量：4068 次

发布时间：2019-05-25

本文共 2326 字，大约阅读时间需要 7 分钟。

1.查看表空间

select A.tablespace_name as "TABLESPACE NAME",A.total_size AS "TOTAL SIZE", round(B.total_free_size,1) AS "TOTAL FREE SIZE",round((A.total_size-B.total_free_size),2) AS "USED SIZE",to_char(100*B.total_free_size/A.total_size,'99.99')||'%' AS "PERCENT FREE" FROM (SELECT tablespace_name,sum(bytes)/1024/1024 AS total_size from dba_data_files GROUP BY tablespace_name) A,(select tablespace_name,sum(bytes/1024/1024) AS total_free_size from dba_free_space GROUP BY tablespace_name) B where A.tablespace_name=B.tablespace_name;

2.检查chained

create table TEST.CHAINED_ROWS(

owner_name varchar2(30),

table_name varchar2(30),

cluster_name varchar2(30),

partition_name varchar2(30),

subpartition_name varchar2(30),

head_rowid rowid,

analyze_timestamp date);

select 'analyze table '||owner||'.'||table_name||' list chained rows into test.chained_rows;' from dba_tables where owner='TEST';

analyze table TEST.WORLD list chained rows into test.chained_rows;

SELECT B.owner_name AS "OWNER",

A.table_name AS "TABLE NAME",

B.row_count AS "ROW COUNT",

A.num_rows AS "TOTAL ROWS"

FROM ALL_tables A,

(SELECT B.owner_name,B.table_name,COUNT(B.head_rowid) ROW_COUNT

FROM chained_rows B

GROUP BY B.owner_name,B.table_name) B

WHERE A.table_name=B.table_name;

3.检查表空间碎片

SELECT tablespace_name AS "TABLESPACE NAME",sqrt(MAX(blocks)/SUM(blocks))*(100/sqrt((COUNT(blocks)))) AS "FSFI%" FROM dba_free_space GROUP BY tablespace_name ORDER BY 1;

FSFI:可用破碎表空间索引（Free Space Fragmentation Indx,FSFI)

数值最大值为100，表示完全没有破碎的空间，数值越低代表空间碎片越严重，通常低于30%就需要进行重整操作

4.检查索引

SELECT index_name,index_type,table_name,status from dba_indexes where status='UNSABLE';

5.检查新增失效对象

select owner OBJECT_OWNER,object_name,object_type,status from dba_objects where owner NOT IN('SYS','SYSTEM') AND status='INVALID' ORDER BY owner,object_type,object_name;

6.每月表空间增长

SELECT A.ts# AS "TABLESPACE NO",B.name AS "TABLESPACE NAME",to_char(A.creation_time,'RRRR Month') "Month",SUM(A.bytes)/1024/1024/1024 "Growth in GB" FROM sys.v_$datafile A,sys.v_$tablespace B where A.creation_time>SYSDATE-365 AND A.ts#=B.ts# GROUP BY A.ts#,B.name,to_char(A.creation_time,'RRRR Month');

7.每月数据库增长

SELECT to_char(creation_time,'RRRR Month') "Month",SUM(bytes)/1024/1024/1024 "Growth in GB" FROM sys.v_$datafile WHERE creation_time>SYSDATE-365 GROUP BY to_char(creation_time,'RRRR Month');

转载地址：http://rzhji.baihongyu.com/

你可能感兴趣的文章