oracle自动段管理ASSM笔记
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle自动段管理ASSM笔记,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5693字,纯文字阅读大概需要9分钟。
内容图文
![oracle自动段管理ASSM笔记](/upload/InfoBanner/zyjiaocheng/464/d532a78c0fca4feea6653a646b709f24.jpg)
DATAFILE ‘J:\app\wufan\oradata\orcl\demo01.dbf‘
SIZE 50M
EXTENT MANAGEMENT LOCAL --一定是本地管理
SEGMENT SPACE MANAGEMENT AUTO; --ASSM管理的标志
CREATE TABLE demotab (x NUMBER)
TABLESPACE demo
STORAGE (INITIAL 1000 K); SQL> show user
USER is "SCOTT"
SQL> SELECT t.table_name,
2 t.initial_extent,
3 t.next_extent,
4 t.pct_free,
5 t.pct_used
6 FROM user_tables t
7 WHERE t.table_name = ‘DEMOTAB‘;
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_FREE PCT_USED
------------------------------ -------------- ----------- ---------- ----------
DEMOTAB 1024000 10
SQL> set serveroutput on
SQL> exec scott.show_space(‘demotab‘,‘auto‘,‘T‘,‘Y‘);
Total Blocks............................0
Total Bytes.............................0
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................0
Last Used Ext BlockId...................0
Last Used Block.........................0
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0
PL/SQL procedure successfully completed.
SQL> SELECT t.segment_name, t.extent_id, t.block_id
2 FROM dba_extents t
3 WHERE t.segment_name = ‘DEMOTAB‘;
no rows selected
SQL>
该实验刚好佐证了11g创建一个表,只是产生了一个表定义,并未分配任何空间
附show_space过程代码,觉得这段代码挺有用,记下来mark 一下:
CREATE OR REPLACE PROCEDURE show_space (p_segname_1 IN varchar2, p_space IN varchar2 DEFAULT 'MANUAL' , p_type_1 IN varchar2 DEFAULT 'TABLE' , p_analyzed IN varchar2 DEFAULT 'N' , p_owner_1 IN varchar2 DEFAULT USER ) AS p_segname VARCHAR2 (100); p_type VARCHAR2 (10); p_owner VARCHAR2 (30); l_unformatted_blocks NUMBER; l_unformatted_bytes NUMBER; l_fs1_blocks NUMBER; l_fs1_bytes NUMBER; l_fs2_blocks NUMBER; l_fs2_bytes NUMBER; l_fs3_blocks NUMBER; l_fs3_bytes NUMBER; l_fs4_blocks NUMBER; l_fs4_bytes NUMBER; l_full_blocks NUMBER; l_full_bytes NUMBER; 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 p_segname := UPPER (p_segname_1); -- rainy changed p_owner := UPPER (p_owner_1); p_type := p_type_1; IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN --rainy changed p_type := 'INDEX'; END IF; IF (p_type_1 = 't' OR p_type_1 = 'T') THEN --rainy changed p_type := 'TABLE'; END IF; IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN --rainy changed p_type := 'CLUSTER'; END IF; DBMS_SPACE.unused_space (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, 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 ); IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN DBMS_SPACE.free_blocks (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks ); p ('Free Blocks', l_free_blks); END IF; 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); /*IF the segment is analyzed */ IF p_analyzed = 'Y' THEN DBMS_SPACE.space_usage (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes, fs1_blocks => l_fs1_blocks, fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks, fs2_bytes => l_fs2_bytes, fs3_blocks => l_fs3_blocks, fs3_bytes => l_fs3_bytes, fs4_blocks => l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks => l_full_blocks, full_bytes => l_full_bytes ); DBMS_OUTPUT.put_line (RPAD (' ', 50, '*')); DBMS_OUTPUT.put_line ('The segment is analyzed'); p ('0% -- 25% free space blocks', l_fs1_blocks); p ('0% -- 25% free space bytes', l_fs1_bytes); p ('25% -- 50% free space blocks', l_fs2_blocks); p ('25% -- 50% free space bytes', l_fs2_bytes); p ('50% -- 75% free space blocks', l_fs3_blocks); p ('50% -- 75% free space bytes', l_fs3_bytes); p ('75% -- 100% free space blocks', l_fs4_blocks); p ('75% -- 100% free space bytes', l_fs4_bytes); p ('Unused Blocks', l_unformatted_blocks); p ('Unused Bytes', l_unformatted_bytes); p ('Total Blocks', l_full_blocks); p ('Total bytes', l_full_bytes); END IF; END;
oracle自动段管理ASSM笔记
标签:
本文系统来源:http://blog.csdn.net/kadwf123/article/details/51357429
内容总结
以上是互联网集市为您收集整理的oracle自动段管理ASSM笔记全部内容,希望文章能够帮你解决oracle自动段管理ASSM笔记所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。