ホームページ >データベース >mysql チュートリアル >oracle自动段管理ASSM笔记
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
CREATE TABLESPACE demo该实验刚好佐证了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;