Oracle空表的分配segment

 

 

1、查询相关参数deferred_segment_creation

select * from v$parameter where name='deferred_segment_creation'

如果该参数的value为true，则系统在空表创建时不会该分配其segment
如果该参数的value为false，则系统在空表创建时会该分配其segment

2、修改相关参数deferred_segment_creation

alter system set deferred_segment_creation=false scope=both;

如果参数值已经为false，则不用修改

3、查询空表

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

将查询结果导出并粘贴执行便可对已存在的空表分配segment

alter table table_name allocate extent;

4、查询其分配的segment

select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name;

select table_name from
(select table_name from user_tables where num_rows =0) tb_null
where not exists (select segment_name from user_extents tb_not_null where tb_null.table_name = tb_not_null.segment_name group by segment_name);

其中segment_name为表名

亦可将空表插入一行在回滚，也会分配segment

转自：https://www.cnblogs.com/Yuvee/p/5509393.html