ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DBMS_SPACE包的使用(2)

DBMS_SPACE包的使用(2)

原创 Linux操作系统 作者:Ora-600 时间:2009-04-29 22:47:55 0 删除 编辑

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 wner=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 wner=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 verload = 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;

/

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

请登录后发表评论 登录
全部评论

注册时间:2008-05-11

  • 博文量
    31
  • 访问量
    43912