CREATE TABLESPACE mytest DATAFILE '/data2/ora11g/xunyang1/mytest.dbf' SIZE 1024M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
create table objtest1 tablespace mytest as select * from dba_objects
1.查看一个表的段的数据块数
SELECT segment_name,segment_type,blocks
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'OBJTEST1'
/
SEGMENT_NAME SEGMENT_TYPE BLOCKS
--------------- --------------- --------
OBJTEST TABLE 256
2.查看一个表的使用的数据块
select blocks , empty_blocks , num_rows
from user_tables
where table_name = 'OBJTEST1'
/
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
182 74 13851
3.使用过的块
SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM objtest1
/
Used
----------
170
4. 数字之间的关系
DBA_SEGMENTS.blocks = user_tables.blocks + empty_blocks + 1(segment header)
5 .执行delete 操作 delete from objtest1 where object_id >1000
收集统计信息 :ANALYZE TABLE objtest1 estimate statistics
之后:
SQL> select blocks , empty_blocks , num_rows
2 from user_tables
3 where table_name = 'OBJTEST1'
4 /
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
182 88 943
SQL> SELECT COUNT (DISTINCT
2 DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
4 FROM objtest
5 /
Used
----------
12
SQL> SELECT segment_name,segment_type,blocks
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME = 'OBJTEST1'
4 /
SEGMENT_NAME SEGMENT_TYPE BLOCKS
--------------- --------------- ---------
OBJTEST TABLE 256
6.执行shrink 操作
alter table objtest1 shrink space cascade
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------ -------------- ----------
OBJTEST1 TABLE 16
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
12 4 943
Used
----------
12
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
create table objtest1 tablespace mytest as select * from dba_objects
1.查看一个表的段的数据块数
SELECT segment_name,segment_type,blocks
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'OBJTEST1'
/
SEGMENT_NAME SEGMENT_TYPE BLOCKS
--------------- --------------- --------
OBJTEST TABLE 256
2.查看一个表的使用的数据块
select blocks , empty_blocks , num_rows
from user_tables
where table_name = 'OBJTEST1'
/
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
182 74 13851
3.使用过的块
SELECT COUNT (DISTINCT
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
FROM objtest1
/
Used
----------
170
4. 数字之间的关系
DBA_SEGMENTS.blocks = user_tables.blocks + empty_blocks + 1(segment header)
5 .执行delete 操作 delete from objtest1 where object_id >1000
收集统计信息 :ANALYZE TABLE objtest1 estimate statistics
之后:
SQL> select blocks , empty_blocks , num_rows
2 from user_tables
3 where table_name = 'OBJTEST1'
4 /
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
182 88 943
SQL> SELECT COUNT (DISTINCT
2 DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3 DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
4 FROM objtest
5 /
Used
----------
12
SQL> SELECT segment_name,segment_type,blocks
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_NAME = 'OBJTEST1'
4 /
SEGMENT_NAME SEGMENT_TYPE BLOCKS
--------------- --------------- ---------
OBJTEST TABLE 256
6.执行shrink 操作
alter table objtest1 shrink space cascade
SEGMENT_NAME SEGMENT_TYPE BLOCKS
------------------ -------------- ----------
OBJTEST1 TABLE 16
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
12 4 943
Used
----------
12