Objectives
- After completing this lesson,you should be able to do the following:
- Use automatic segment space management
- Use manual segment space management
- Describe the use of Oracle block parameters
- Recover space from sparsely populated segments
- Describe and detect chaining and migration of Oracle blocks
- Perform index reorganization
Database Storage Hierarchy
Allocation of Extents
To avoid the disadvantages of dynamic extent allocation:
避免动态分配extent的方法有三:
- Create locally managed tablespaces.
- Size the segments appropriately.适当的对segment进行大小规划
- Monitor segments ready to extend.作为DBA来讲,要实时监控segment的使用情况,对于一些需要调整的segment,可以事先运行一些脚本来进行合理的分配.
Segment分配的时候,是按照extent来进行的,而不是按照block进行的.
Avoiding Dynamic Allocation
- To display segments with less than 10% free blocks
- blocks->已经使用过的blocks
- empty_blocks->从未使用过的blocks
- 第一次查询时,empty_blocks有可能为NULL或者为0,原因是尚未进行analyze table t compute statistics.命令.
SELECT owner,table_name,blocks,empty_blocks FROM dba_tables WHERE empty_blocks / (blocks + empty_blocks) < .1;
- To avoid dynamic allocaiton:
SQL>ALTER TABLE HR.employees ALLOCATE EXTENT; Table altered.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> alter table MARK.t ALLOCATE EXTENT; Table altered.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> alter table MARK.t ALLOCATE EXTENT (size 30M); Table altered.
Example:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLESPACE tbs1 datafile '/u01/oradata/DB202/tbs1_01.dbf' size 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT auto;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> create user mark identified by mark default tablespace tbs1; User created. SQL> grant connect,resource to mark; Grant succeeded.
conn as mark
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
[oracle@DG3 ~]$ sqlplus mark/mark SQL*Plus: Release 11.2.0.1.0 Production on Wed May 8 11:04:08 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table t(id int,name char(10)); Table created.
conn as sysdba
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT blocks,empty_blocks FROM dba_tables WHERE owner='MARK' AND table_name='T'; BLOCKS EMPTY_BLOCKS ---------- ------------
conn as mark
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> exec dbms_stats.gather_table_stats('MARK','T'); PL/SQL procedure successfully completed.
conn as sysdba
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT blocks,empty_blocks FROM dba_tables WHERE owner='MARK' AND table_name='T'; BLOCKS EMPTY_BLOCKS ---------- ------------ 0 0
执行dbms_stats.gather_table_stats后发现,empty_blokcs为0;说明使用此Procedure不能统计出真实的empty_blocks信息,必须使用analyze命令.
conn as mark
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> analyze table T compute statistics; Table analyzed.
问题出现了,在9i的环境中,执行完上述SQL CODE之后,查询相应的字典信息,均有正确数据(dba_tables,dba_segments,dba_extents),但是在oracle 11gr2中,查询上述数据字典信息,则没有数据.这是为什么?
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT blocks,empty_blocks FROM dba_tables WHERE owner='MARK' AND table_name='T'; BLOCKS EMPTY_BLOCKS ---------- ------------ 0 0 SQL> SELECT bytes,blocks,extents from dba_segments where owner='MARK' and segment_name='T'; no rows selected SQL> SELECT extent_id,block_id,bytes from dba_extents where owner='MARK' and segment_name='T'; no rows selected