dba 常用查询

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

1)
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);
2)
select 'analyze table '||owner||'.'||table_name||' list chained rows into test.chained_rows;' from dba_tables where owner='TEST';
3)
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');