oracle的版本:Database 10g Enterprise Edition Release 10.2.0.1.0 SQL> show parameter db_block_size NAME TYPE VALUE
db_block_size integer 8192
块大小=8K 创建一个5M的测试表空间: SQL> create tablespace jhtt
2 logging
3 datafile 'D:\ORADATA\JHTT.ora'
4 size 5m;
创建用户:
create user JH
identified by jh
default tablespace JHTT
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to JH;
grant dba to JH with admin option;
查询表空间的使用情况: SQL> select df.tablespace_name, sum(df.bytes)/ (5*1024*1024) free_space_per from dba_free_space df
where df.tablespace_name='JHTT'
group by df.tablespace_name; TABLESPACE_NAME FREE_SPACE_PER
------------------------------ --------------
JHTT 0.9875 空的表空间free为98.75% 占用1.25%
问题这个表空间里面没有任务对象, 是什么占用了1.25%的空间??? 创建表:
create table jh.tabspace_test
as
select * from dba_tables
where rownum <200 创建表后表空间空闲为96.25% , 即共占用3.75%, 减去1.25%(即减少上面提到的空的表空间占用的1.25% ), 计算出:表占用空间 2.5%
analyze table jh.TABSPACE_TEST compute statistics
for table;
select * from dba_tables t where t.table_name='TABSPACE_TEST'; 分析表后,通过查询dba_tables表, blocks=9, empty_blocks=7,共占用16个块.即 16*8*1024/ 5*1024*1024 =2.5%(刚好是表占用的空间) delete表后,表空间free不变. dba_tables 表中 blocks=9, empty_blocks=7 分析表后,表空间free同上 (analyze table tabname compute statistics for table ) alter table jh.TABSPACE_TEST deallocate unused, 同上 alter table jh.TABSPACE_TEST deallocate unused keep 0 同上 truncate table 后, 空闲空间97.5%, 占用2.5%,除去自身占用的1.25%, 实际空表占用1.25%. 因为表initial extent 64kb,即 刚好占用1.25%
我们再看看 dba_tables 表中 blocks=null, empty_blocks=null 事实上测试结果: 当我们delete表后,无论分析表还是通过语句 deallocate unused (keep 0)都无法释放表空间, 为什么deallocate unused不能释放空间??? 而truncate table则释放了表空间 另:
创建一个查询空间使用的procedure,为什么执行时报错:
begin
show_space('TABSPACE_TEST','JH','TABLE');
end; 出错提示如下图所示: 以下为存储过程:
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
as
l_free_blks number; l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ¦ ¦
p_num );
end;
begin
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks ); dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_LastUsedExtFileId,
last_used_extent_block_id => l_LastUsedExtBlockId,
last_used_block => l_last_used_block ); p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_last_used_block );
end;
另外一个关于 RAISE_APPLICATION_ERROR的疑问:
-- Created on 2008-7-12 by ADMINISTRATOR
declare
-- Local variables here
i integer;
begin
-- Test statements here
RAISE_APPLICATION_ERROR(-20000,'为什么会出错');
end;
db_block_size integer 8192
块大小=8K 创建一个5M的测试表空间: SQL> create tablespace jhtt
2 logging
3 datafile 'D:\ORADATA\JHTT.ora'
4 size 5m;
创建用户:
create user JH
identified by jh
default tablespace JHTT
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to JH;
grant dba to JH with admin option;
查询表空间的使用情况: SQL> select df.tablespace_name, sum(df.bytes)/ (5*1024*1024) free_space_per from dba_free_space df
where df.tablespace_name='JHTT'
group by df.tablespace_name; TABLESPACE_NAME FREE_SPACE_PER
------------------------------ --------------
JHTT 0.9875 空的表空间free为98.75% 占用1.25%
问题这个表空间里面没有任务对象, 是什么占用了1.25%的空间??? 创建表:
create table jh.tabspace_test
as
select * from dba_tables
where rownum <200 创建表后表空间空闲为96.25% , 即共占用3.75%, 减去1.25%(即减少上面提到的空的表空间占用的1.25% ), 计算出:表占用空间 2.5%
analyze table jh.TABSPACE_TEST compute statistics
for table;
select * from dba_tables t where t.table_name='TABSPACE_TEST'; 分析表后,通过查询dba_tables表, blocks=9, empty_blocks=7,共占用16个块.即 16*8*1024/ 5*1024*1024 =2.5%(刚好是表占用的空间) delete表后,表空间free不变. dba_tables 表中 blocks=9, empty_blocks=7 分析表后,表空间free同上 (analyze table tabname compute statistics for table ) alter table jh.TABSPACE_TEST deallocate unused, 同上 alter table jh.TABSPACE_TEST deallocate unused keep 0 同上 truncate table 后, 空闲空间97.5%, 占用2.5%,除去自身占用的1.25%, 实际空表占用1.25%. 因为表initial extent 64kb,即 刚好占用1.25%
我们再看看 dba_tables 表中 blocks=null, empty_blocks=null 事实上测试结果: 当我们delete表后,无论分析表还是通过语句 deallocate unused (keep 0)都无法释放表空间, 为什么deallocate unused不能释放空间??? 而truncate table则释放了表空间 另:
创建一个查询空间使用的procedure,为什么执行时报错:
begin
show_space('TABSPACE_TEST','JH','TABLE');
end; 出错提示如下图所示: 以下为存储过程:
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
as
l_free_blks number; l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ¦ ¦
p_num );
end;
begin
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks ); dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_LastUsedExtFileId,
last_used_extent_block_id => l_LastUsedExtBlockId,
last_used_block => l_last_used_block ); p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_last_used_block );
end;
另外一个关于 RAISE_APPLICATION_ERROR的疑问:
-- Created on 2008-7-12 by ADMINISTRATOR
declare
-- Local variables here
i integer;
begin
-- Test statements here
RAISE_APPLICATION_ERROR(-20000,'为什么会出错');
end;
关注下,期待高手来临。