DBMS_SPACE包的使用（2）

CREATE_TABLE_COST有两种用法，因此包内进行了overload，具体的语法如下：
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);

DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);

CREATE TYPE create_table_cost_colinfo IS OBJECT (
COL_TYPE VARCHAR(200),
COL_SIZE NUMBER);

下面是关于CREATE_TABLE_COST的测试代码：
1、测试创建一个表所需的存储大小，预计该表平均行长度为100字节，10000行数据
SQL> DECLARE
2 V1 NUMBER;
3 V2 NUMBER;
4 BEGIN
5 DBMS_SPACE.CREATE_TABLE_COST('USERS', 100, 10000, 10, V1, V2);
6 DBMS_OUTPUT.PUT_LINE('V1: '||V1/1024/8||' V2: '||V2/1024/8);
7 END;
8 /
V1: 143 V2: 256 --估算出该表需要存储空间143块，所需分配空间256块
PL/SQL 过程已成功完成。

2、创建该表，并插入10000行数据
SQL> CREATE TABLE T1(C CHAR(96)); --96字节的char字段平均行长度为100字节
表已创建。

SQL> BEGIN
2 FOR I IN 1..10000 LOOP
3 INSERT INTO T1 VALUES(I);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL 过程已成功完成。

3、分析表统计信息
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
表已分析。

SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN
FROM USER_TABLES WHERE TABLE_NAME='T1';
BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ------------ -----------
180 76 100
--经检查，高水平线之前的块数180块，高水平线之后的空块数76块，总存储空间为256块，与DBMS_SPACE.CREATE_INDEX_COST计算出的总需要存储空间大小相符。

4、通过dbms_space.space_usage过程，可以进一步看到表中各个块的使用情况
declare
unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;
own dba_tables.owner%type;
tab dba_tables.table_name%type;
yesno varchar2(3);
type parts is table of dba_tab_partitions%rowtype;
partlist parts;
type cursor_ref is ref cursor;
c_cur cursor_ref;
begin
own:=upper('&owner');
tab:=upper('&table_name');
dbms_output.put_line('--------------------------------------------------------------------------------');
open c_cur for select partitioned from dba_tables
where owner=own and table_name=tab;
fetch c_cur into yesno;
close c_cur;
dbms_output.put_line('Owner: '||own);
dbms_output.put_line('Table: '||tab);
dbms_output.put_line('------------------------------------------------');
if yesno='NO' then
dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
else
open c_cur for select * from dba_tab_partitions
where table_owner=own and table_name=tab;
fetch c_cur bulk collect into partlist;
close c_cur;
for i in partlist.first .. partlist.last loop
dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line('Partition: '||partlist(i).partition_name);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
end loop;
end if;
dbms_output.put_line('--------------------------------------------------------------------------------');
end;
/

输入 owner 的值: HR
原值 22: own:=upper('&owner');
新值 22: own:=upper('HR');
输入 table_name 的值: T1
原值 23: tab:=upper('&table_name');
新值 23: tab:=upper('T1');
--------------------------------------------------------------------------------
Owner: HR
Table: T1
------------------------------------------------
unf: 0 fs1: 1 fs2: 0 fs3: 0 fs4: 39 full: 140
--------------------------------------------------------------------------------
PL/SQL 过程已成功完成。
--经查看，发现该表写满数据的块有140块，3/4满的块有39块，1/4满的块有1块，该表存储空间没有有效利用，可以看到140+39+1=180，这些均为高水平线之下的块。但与DBMS_SPACE.CREATE_INDEX_COST计算出的数据需要143块不符。

5、对表进行空间整理并重新分析
SQL> ALTER TABLE T1 MOVE;
表已更改。

SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;
表已分析。

SQL> SELECT BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN
FROM USER_TABLES WHERE TABLE_NAME='T1';
BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ------------ -----------
155 101 100
--经检查，高水平线之前的块数155，高水平线之后的空块数101，平均行长度100字节

6、通过dbms_space.space_usage过程，可以进一步看到表中各个块的使用情况
declare
unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;
own dba_tables.owner%type;
tab dba_tables.table_name%type;
yesno varchar2(3);
type parts is table of dba_tab_partitions%rowtype;
partlist parts;
type cursor_ref is ref cursor;
c_cur cursor_ref;
begin
own:=upper('&owner');
tab:=upper('&table_name');
dbms_output.put_line('--------------------------------------------------------------------------------');
open c_cur for select partitioned from dba_tables
where owner=own and table_name=tab;
fetch c_cur into yesno;
close c_cur;
dbms_output.put_line('Owner: '||own);
dbms_output.put_line('Table: '||tab);
dbms_output.put_line('------------------------------------------------');
if yesno='NO' then
dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
else
open c_cur for select * from dba_tab_partitions
where table_owner=own and table_name=tab;
fetch c_cur bulk collect into partlist;
close c_cur;
for i in partlist.first .. partlist.last loop
dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line('Partition: '||partlist(i).partition_name);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
end loop;
end if;
dbms_output.put_line('--------------------------------------------------------------------------------');
end;
/
输入 owner 的值: HR
原值 22: own:=upper('&owner');
新值 22: own:=upper('HR');
输入 table_name 的值: T1
原值 23: tab:=upper('&table_name');
新值 23: tab:=upper('T1');
--------------------------------------------------------------------------------
Owner: HR
Table: T1
------------------------------------------------
unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 143
--------------------------------------------------------------------------------
PL/SQL 过程已成功完成。
--经查看，发现该表写满数据的块有143块，与DBMS_SPACE.CREATE_INDEX_COST计算出的数据需要块数完全相同

-- review the parameters
SELECT argument_name, data_type, type_owner, type_name
FROM all_arguments
WHERE object_name = 'CREATE_TABLE_COST'
AND overload = 2

-- examine the input parameter type
SELECT text
FROM dba_source
WHERE name = 'CREATE_TABLE_COST_COLUMNS';

-- drill down further into the input parameter type
SELECT text
FROM dba_source
WHERE name = 'create_table_cost_colinfo';

set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('VARCHAR2',30),
sys.create_table_cost_colinfo('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100000,0,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Alloc Bytes: ' || TO_CHAR(ab));
END;
/

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20034375/viewspace-1021497/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20034375/viewspace-1021497/