oracle check
环境变量：set linesize 200 pagesize 10000 time on timing on
col owner for a10
col segment_name for a30
col object_name for a30
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
alter session set nls_language=american;
查看表空间使用情况：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "使用百分比",
F.TOTAL_BYTES "空闲空间(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDER BY 2 DESC;
******************************************8
col tbs_name for a30
col used_per for a10
col tbs_size for 9999999999
SELECT UPPER(F.TABLESPACE_NAME) tbs_name,
D.TOT_GROOTTE_MB tbs_size,
D.TOT_GROOTTE_MB-F.TOTAL_BYTES tbs_used,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') used_per,
F.TOTAL_BYTES free_space
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDER BY 2 DESC;
查看临里表空间：
col "大小 M" for a15
col "使用率%" for a15
col "名字" for a15
col"使用(M)" for a15
SELECT d.status "状态",d.tablespace_name "名字",d.contents "类型",d.extent_management "管理方式",
TO_CHAR(NVL(a.bytes/1024/1024,0),'99,999,990.900') "大小 M",
NVL(t.bytes,0)/1024/1024 || '/' || NVL(a.bytes/1024/1024,0) "使用(M)",
TO_CHAR(NVL(t.bytes/a.bytes*100,0),'990.00') "使用率%"
FROM sys.dba_tablespaces d,
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name=a.tablespace_name(+)
AND d.tablespace_name=t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
查看所有数据和临里文件大小：
set linesize 200
set pagesize 1000
set time on
set timing on
col owner for a10
col segment_name for a30
col index_name for a30
col table_name for a30
col colum_name for a30
col name for a60
col member for a50
col file_name for a50
col tablespace_name for a30
col ftime for a20
col username for a20
col default_tablespace for a40
col temporary_tablespace for a40
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
show parameter instance_name
host hostname
select * from v$version;
select sum(bytes)/1024/1024 datafile_size_M from dba_data_files;
select sum(bytes)/1024/1024 tempfile_size_M from dba_temp_files;
select count(*) tablespace_count from dba_tablespaces;
select count(1) datafile_count from dba_data_files;
select count(1) tempfile_count from dba_temp_files;
select count(1) from v$controlfile;
select * from gv$log;
select * from gv$logfile;
archive log list;
archive log list
select count(*) from v$session;
select * from v$controlfile;
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') ftime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes
from v$log_history
where first_time > sysdate - 1 order by first_time;
select * from dba_role_privs where granted_role='DBA';
select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace='SYSTEM';
select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;
select * from dba_role_privs where granted_role='DBA';
select username,default_tablespace,temporary_tablespace from dba_users where default_tablespace='SYSTEM';
select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;
表空间使用情况：
col "tbs_name" for a20
col "tbs_used%" for a10
SELECT UPPER(F.TABLESPACE_NAME) "tbs_name",
D.TOT_GROOTTE_MB "tbs_size(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES "tbs_used(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "tbs_used%",
F.TOTAL_BYTES "tbs_free(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDER BY 2 DESC;
临时表空音使用情况：
col "tbs_size(M)" for a15
col "tbs_used%" for a15
col "tbs_name" for a15
col"tbs_used(M)" for a15
SELECT d.status "tbs_status",d.tablespace_name "tbs_name",d.contents "tbs_type",d.extent_management "tbs_management_type",
TO_CHAR(NVL(a.bytes/1024/1024,0),'99,999,990.900') "tbs_size(M)",
NVL(t.bytes,0)/1024/1024 || '/' || NVL(a.bytes/1024/1024,0) "tbs_used(M)",
TO_CHAR(NVL(t.bytes/a.bytes*100,0),'990.00') "tbs_used%"
FROM sys.dba_tablespaces d,
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name=a.tablespace_name(+)
AND d.tablespace_name=t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
查看索引的情况：
col owner format a20
col OBJECT_NAME format a30
select owner,object_name,OBJECT_TYPE,status from dba_objects where status='INVALID';
select owner,index_name,tablespace_name from dba_indexes where owner'SYS' and owner'SYSTEM';
查找空表间使用率超过80%表空间：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99') "使用百分比",
F.TOTAL_BYTES "空闲空间(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(BYTES)/1024/1024) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME AND ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2)>80
ORDER BY 2 DESC;
#############################################################################
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
FROM V$SQLAREA
WHERE buffer_gets > 10000000OR disk_reads > 1000000
;(没太明白意思)
页:
[1]