oracle自动段管理ASSM笔记
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle自动段管理ASSM笔记，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5693字，纯文字阅读大概需要9分钟。
内容图文
CREATE TABLESPACE demo
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 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】