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
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来1源gaodai#ma#com搞*代#码1网9;, 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笔记
