Oracle check的使用,Check_oracle_health之表空间使用及处理

Check_oracle_health之表空间使用及处理

1  查看表空间情况

--查看所有表空间大小

select tablespace_name, sum(bytes) / 1024 / 1024 total_M

from dba_data_files

group by tablespace_name;

--已经使用的表空间大小

select tablespace_name, sum(bytes) / 1024 / 1024 used_M

from dba_free_space

group by tablespace_name;

--所以使用空间可以这样计算

select a.tablespace_name, total_M, free_M, total_M - free_M used_M

from (select tablespace_name, sum(bytes) / 1024 / 1024 total_M

from dba_data_files

group by tablespace_name) a,

(select tablespace_name, sum(bytes) / 1024 / 1024 free_M

from dba_free_space

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name;

2 脚本内命令

SELECT a.tablespace_name   "Tablespace",

b.status            "Status",

b.contents          "Type",

b.extent_management "Extent Mgmt",

a.bytes             bytes,

a.maxbytes          bytes_max,

c.bytes_free        bytes_free

FROM (

-- belegter und maximal verfuegbarer platz pro datafile

-- nach tablespacenamen zusammengefasst

-- => bytes

-- => maxbytes

SELECT a.tablespace_name,

SUM(a.bytes) / 1024 / 1024 bytes,

SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes)) / 1024 / 1024 / 1024 maxbytes

FROM dba_data_files a

GROUP BY tablespace_name) a,

sys.dba_tablespaces b,

(

-- freier platz pro tablespace

-- => bytes_free

SELECT a.tablespace_name, SUM(a.bytes) bytes_free

FROM dba_free_space a

GROUP BY tablespace_name) c

WHERE a.tablespace_name = c.tablespace_name(+)

AND a.tablespace_name = b.tablespace_name

UNION ALL

SELECT a.tablespace_name "Tablespace",

b.status "Status",

b.contents "Type",

b.extent_management "Extent Mgmt",

sum(a.bytes_free + a.bytes_used) bytes, -- allocated

SUM(DECODE(d.autoextensible, 'YES', d.maxbytes, 'NO', d.bytes)) bytes_max,

SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, 0)) bytes_free

FROM sys.v_$TEMP_SPACE_HEADER a,

sys.dba_tablespaces      b,

sys.v_$Temp_extent_pool  c,

dba_temp_files           d

WHERE c.file_id(+) = a.file_id

and c.tablespace_name(+) = a.tablespace_name

and d.file_id = a.file_id

and d.tablespace_name = a.tablespace_name

and b.tablespace_name = a.tablespace_name

GROUP BY a.tablespace_name,

b.status,

b.contents,

b.extent_management,

d.maxbytes

ORDER BY 1;

处理办法：

方法1：为表空间增加数据文件：

alter tablespace system add datafile '/app/oracle/oradata/testdb/system02.dbf' size 20M;

方法2：是增加表空间原有数据文件尺寸：

alter database datafile '/app/oracle/oradata/testdb/system02.dbf' resize 200M;

创建表空间：

CREATE TABLESPACE 01 DATAFILE

'/oradata/grp/01_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,

'/oradata/grp/01_02.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,

'/oradata/grp/01_03.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,

'/oradata/grp/01_04.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,

'/oradata/grp/01_05.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G,

'/oradata/grp/01_06.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE 30G

LOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

减小表空间大小：

alter database datafile '/app/oracle/oradata/testdb/system02.dbf' resize 20M;

脚本check_oracle_health 计算表空间的方式是：

如果配置了auoextend on，就会采用maxbytes作为分母进行计算。

SELECT a.tablespace_name,

SUM(a.bytes)/1024/1024/1024 bytes,

SUM(DECODE(a.autoextensible, 'YES', a.maxbytes, 'NO', a.bytes))/1024/1024/1024 maxbytes

FROM dba_data_files a

GROUP BY tablespace_name

if ($self->{bytes_max} == 0) {

$self->{percent_used} =

($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100;

$self->{real_bytes_max} = $self->{bytes};

$self->{real_bytes_free} = $self->{bytes_free};

} elsif ($self->{bytes_max} > $self->{bytes}) {

$self->{percent_used} =

($self->{bytes} - $self->{bytes_free}) / $self->{bytes_max} * 100;

$self->{real_bytes_max} = $self->{bytes_max};

$self->{real_bytes_free} = $self->{bytes_free} + ($self->{bytes_max} - $self->{bytes});

} else {

# alter tablespace USERS add datafile 'users02.dbf'

#     size 5M autoextend on next 200K maxsize 6M;

# bytes = 5M, maxbytes = 6M

# ..... data arriving...until ORA-01652: unable to extend temp segment

# bytes = 6M, maxbytes = 6M

# alter database datafile 5 resize 8M;

# bytes = 8M, maxbytes = 6M

$self->{percent_used} =

($self->{bytes} - $self->{bytes_free}) / $self->{bytes} * 100;

$self->{real_bytes_max} = $self->{bytes};

$self->{real_bytes_free} = $self->{bytes_free};

}

}

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