数据文件大大小及头大小
SELECT v1.file_name,v1.file_id,
num1 totle_space,
num3 free_space,
num1-num3 Used_space,
nvl(num2,0) data_space,
num1-num3-nvl(num2,0) file_head
FROM
(SELECT file_name,file_id,SUM(bytes) num1 FROM Dba_Data_Files GROUP BY file_name,file_id) v1,
(SELECT file_id,SUM(bytes) num2 FROM dba_extents GROUP BY file_id) v2,
(SELECT file_id,SUM(BYTES) num3 FROM DBA_FREE_SPACE GROUP BY file_id) v3
WHERE v1.file_id=v2.file_id(+)
AND v1.file_id=v3.file_id(+)
运行以上查询,我们可以如下信息:
Totle_pace:该数据文件的总大小,字节为单位
Free_space:该数据文件的剩于大小,字节为单位
Used_space:该数据文件的已用空间,字节为单位
Data_space:该数据文件中段数据占用空间,也就是数据空间,字节为单位
File_Head:该数据文件头部占用空间,字节为单位
如果是求hwM,可以确认到resize 回收到最大数据文件的值 用如下过程
declare
cursor c_dbfile is
select tablespace_name,file_name,file_id,bytes
from sys.dba_data_files
where status !='INVALID'
order by tablespace_name,file_id;
cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;
blocksize number(20);
filesize number(20);
extsize number(20);
begin
select value into blocksize
from v$parameter
where name = 'db_block_size';
for c_rec1 in c_dbfile loop
filesize := c_rec1.bytes;
<>
for c_rec2 in c_space(c_rec1.file_id) loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize then
filesize := (c_rec2.block_id - 1)*blocksize;
else
exit outer;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||2*blocksize/1024/1024
||' M, Actual size: '||c_rec1.bytes/1024/1024||' M');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||filesize/1024/1024
||' M, Actual size: '||c_rec1.bytes/1024/1024);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/
要是在运行下面必须先set serveroutput on
如果出现以下错误
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at line 48
则加大
set serveroutput on 1000000
如果出现以下错误则用
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 20
把数值型加大到20
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7199859/viewspace-218022/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7199859/viewspace-218022/