查看表空间剩余空间的详细信息
- PL/SQL script. to determine how much contiguous
free space is available for each tablespace in the database. Oracle will
acquire space by searching first for exact fit, then next best fit, and lastly
coalesce if possible.
create table SPACE_TEMP (
TABLESPACE_NAME CHAR(30),
CONTIGUOUS_BYTES NUMBER)
/
declare
cursor query is select *
from dba_free_space
order by tablespace_name, block_id;
this_row query%rowtype;
previous_row query%rowtype;
total number;
begin
open query;
fetch query into this_row;
previous_row := this_row;
total := previous_row.bytes;
loop
fetch query into this_row;
exit when query%notfound;
if this_row.block_id = previous_row.block_id + previous_row.blocks then
total := total + previous_row.bytes;
insert into SPACE_TEMP (tablespace_name)
values (previous_row.tablespace_name);
else
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
total := this_row.bytes;
end if;
previous_row := this_row;
end loop;
insert into SPACE_TEMP values (previous_row.tablespace_name,
total);
end;
/
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' -
skip 1 center new_today skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES",
count(*) "COUNT",
CONTIGUOUS_BYTES*count(*) "TOTAL BYTES",
to_char(sysdate,'FMMonth DD, YYYY') "TODAY"
from SPACE_TEMP
where CONTIGUOUS_BYTES is not null
group by TABLESPACE_NAME, CONTIGUOUS_BYTES
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc
/
spool off
drop table SPACE_TEMP
/
exit
/
以上脚本会输出数据库中所有表空间剩余空间的详细信息，
执行完之后输入如下，如第一行的意思代表temp表空间里面3.5M的Extent有1个
A sample output:
Contiguous Extents Report
May 28, 2001
TABLESPACE NAME CONTIGUOUS BYTES COUNT TOTAL BYTES
------------------------------ ---------------- ----- ------------
TEMP 3,584,000 1 3,584,000
TEMP 1,781,760 1 1,781,760
TEMP 1,740,800 1 1,740,800
TEMP 1,536,000 1 1,536,000
TEMP 1,126,400 2 2,252,800
TEMP 716,800 1 716,800
TEMP 512,000 1 512,000
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22531473/viewspace-751844/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 关于DFS lock handle等待事件
- aix系统vg
- OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE)
- nmon 命令
- 使用 vmstat 命令确定内存使用情况
- 解决crs_stat -t看到资源为UNKNOWN的情况
- How to Deinstall Oracle Clusterware Home Manually
- Drop Table Fails With ORA-600 [15264]
- oracle ASM参数ASM_POWER_LIMIT以及其它一些参数详解
- 一次ASM磁盘空间假装耗尽 ORA-15041: DISKGROUP SPACE EXHAUSTED
