Locally Managed Extents
- Create a locally managed tablespace:(以后所有的表空间管理方式都需要使用Locally managed method)
SQL>CREATE TABLESPACE user_data_1 DATAFILE '/u01/oradata/sid_name/1m_1.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
- With the Oracle database the default extent management is local.
Pros(优点) and Cons(缺点) of Large Extents
- Pros
- -Are less likely to extent dynamically
- -Deliver small performance benefit
- -Enable you to read the entire extent map with a single I/O operation
- Cons
- -Free space may not be availabe
- -Unused space
The High-Water Mark
图1
图2
高水位计算公式,图解:
The High-Water Mark
- The high-water mark is :
- -Recorded in the segment header block.
- -Set to the begining of the segment on creation
- -Increamented in five-block increments as rows are inserted
- -Reset by the TRUNCATE command
- Never reset by using DELETE statements.
可以从高水位的角度去解释TRUNCATE 与 DELETE的区别.原理上解释.
Table Statistics
Populate the table statistics using the dbms_stats package and then query the values in dba_tables:
SQL> EXECUTE dbms_stats.gather_table_stats('HR','EMPLOYEES'); PL/SQL procedure successfully completed. SQL> SELECT num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len 2 FROM dba_tables 3 WHERE owner = 'HR' 4 AND table_name = 'EMPLOYEES'; NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ---------- ---------- ----------- 107 5 0 0 0 69
执行完成analyze table employees compute statistics之后,再查询employees表的统计信息statistics;
SQL> SELECT num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len 2 FROM dba_tables 3 WHERE owner = 'HR' 4 AND table_name = 'EMPLOYEES'; NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ---------- ---------- ---------- ----------- 107 5 3 6505 0 71
Recovering Space
- Below the high-water mark:
- Use the Export and Import utilities to:(方法1-1)
- -Export the table
- -Drop or truncate the table
- -Import the table
- Or use the Alter Table Employees Move command to move the table.(方法1-2)
- Use the Export and Import utilities to:(方法1-1)
- Above the high-water mark,use the Alter Table Employees Deallocate Unused;command.
Example
关于"Above the high-water mark,use the Alter Table Employees Deallocate Unused;command."应用举例
实验背景:
用户MARK下有新创建一张表表T,表中存放了1000000条记录,总共占用了23个extent(在创建表空间时,分配的extent空间大小为1M/个);
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT bytes,blocks,extents from dba_segments where owner='MARK' and segment_name='T'; BYTES BLOCKS EXTENTS ---------- ---------- ---------- 24117248 2944 23
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> anALTER TABLE T ALLOCATE EXTENT; Table altered.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT bytes,blocks,extents from dba_segments where owner='MARK' and segment_name='T'; BYTES BLOCKS EXTENTS ---------- ---------- ---------- 25165824 3072 24
现在将多余的那个extent给recovering space.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> ALTER TABLE T DEALLOCATE UNUSED; Table altered.
![](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.
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
SQL> SELECT bytes,blocks,extents from dba_segments where owner='MARK' and segment_name='T'; BYTES BLOCKS EXTENTS ---------- ---------- ---------- 24117248 2944 23