dba 常用查询

系统管理员 2022-07-12 02:05 202阅读 0赞

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');