使用存储过程批量查询ArcSDE要素类及索引的存储大小

查询SQL语句参考：http://blog.csdn.net/linghe301/article/details/6331329

其实这个很简单，如果想解决需要掌握三个条件

1：Oracle基础知识

2：ArcSDE表结构知识

3：PL/SQL编程

下面是创建一个批量查看存储ArcSDE要素类以及其SIZE的存储过程

create or replace procedure DisplayFeatureClassSize is
--定义查询要素类游标
type sp_fc_cursor is ref cursor;
--定义查询要素类Byte游标
type sp_fc_byte_cursor is ref cursor;
--要素类游标对象
fc_cursor sp_fc_cursor;
--要素类Byte游标对象
fc_byte_cursor sp_fc_byte_cursor;
--要素类
fc sde.table_registry.table_name%type;
--要素类bytes
fc_bytes user_segments.bytes%type;
begin
open fc_cursor for select table_name from sde.table_registry where config_keyword='DEFAULTS' order by table_name;
--循环要素类
loop
    fetch fc_cursor into fc;
    exit when fc_cursor%notfound;
         open fc_byte_cursor for select bytes/1024  from user_segments where segment_type='TABLE' and segment_name=fc;
         --循环要素类对应的bytes
           loop
           fetch fc_byte_cursor into fc_bytes;
           --如果要素类为空，那么在user_segments表里面是没有任何记录信息的
           exit when fc_byte_cursor%notfound;
           dbms_output.put_line('FetureClass：'||fc||'   Size：'||fc_bytes||'KB');
           end loop;   
end loop;
     exception
      when no_data_found then 
      dbms_output.put_line('Error');
close fc_cursor;
end;

简单的PL/SQL编程提示

--显示执行结果
set serveroutput on
--查看错误
show errors

我们查看一下执行结果

SQL> exec DisplayFeatureClassSize

FetureClass:AAA   Size:512KB
FetureClass:AAABB   Size:512KB
FetureClass:AMD_ZWMD_ART   Size:512KB
FetureClass:AMD_ZWMD_BND   Size:512KB
FetureClass:AMD_ZWMD_CAT   Size:512KB
FetureClass:AMD_ZWMD_LOG   Size:512KB
FetureClass:ATLANTIC   Size:512KB
FetureClass:BB   Size:14336KB
FetureClass:DDD   Size:58368KB
FetureClass:DLBT   Size:1024KB
FetureClass:DLTB1   Size:1024KB
FetureClass:DLTB2   Size:1024KB
FetureClass:EXPORT   Size:512KB
FetureClass:G_TEST1_NET_JUNCTIONS   Size:512KB
FetureClass:HS   Size:2560KB
FetureClass:KZBJBJ   Size:512KB
FetureClass:LZ_FH_YT_PL   Size:154624KB
FetureClass:OWNER   Size:512KB
FetureClass:PLACE   Size:512KB
FetureClass:POLYGON   Size:512KB
FetureClass:QUXIAN   Size:512KB
FetureClass:QZPOI4   Size:2048KB
FetureClass:QZPOI4_1   Size:2048KB
FetureClass:QZPOI4_2   Size:2048KB
FetureClass:RANDOMCRIMEPOINTS_COPYROWS   Size:7168KB
FetureClass:RES2_4M   Size:512KB
FetureClass:RES2_4MANNO   Size:512KB
FetureClass:RES2_4MANNO2   Size:512KB
FetureClass:ROAD   Size:1536KB
FetureClass:T1_LINE   Size:512KB
FetureClass:XIAN   Size:512KB
FetureClass:XZQH   Size:512KB

PL/SQL procedure successfully completed

说明一下：我的数据比较特殊，就是我的数据都在SDE表空间或者说SDE用户下存储，但是如果用户是在其他用户存储，可能就需要修改一下了，自己发挥吧。

下面是我们查询要素类所对应空间索引的大小

create or replace procedure DisplayFeatureClassIndexSize is
--定义查询要素类游标
type sp_fc_cursor is ref cursor;
--定义查询要素类索引Byte游标
type sp_fc_indexbyte_cursor is ref cursor;
--要素类游标对象
fc_cursor sp_fc_cursor;
--要素索引Byte游标对象
fc_indexbyte_cursor sp_fc_indexbyte_cursor;
--要素类
fc sde.st_geometry_index.table_name%type;
--IndexID
fc_indexid sde.st_geometry_index.index_id%type;
--要素类索引bytes
fc_indexbytes user_segments.bytes%type;
test user_segments.segment_name%type;
begin
open fc_cursor for select table_name,index_id from sde.st_geometry_index order by table_name;
--循环要素类
loop
    fetch fc_cursor into fc,fc_indexid;
    exit when fc_cursor%notfound;
         test:='S'||fc_indexid||'$_IX1';
         open fc_indexbyte_cursor for select bytes/1024  from user_segments where segment_type='INDEX' and segment_name=test;
         --循环要素类对应的bytes
           loop
           fetch fc_indexbyte_cursor into fc_indexbytes;
           --如果索引信息为空，那么在user_segments表里面是没有任何记录信息的
           exit when fc_indexbyte_cursor%notfound;
           dbms_output.put_line('FetureClass：'||fc||'   IndexSize：'||fc_indexbytes||'KB');
           end loop;   
end loop;
     exception
      when no_data_found then 
      dbms_output.put_line('Error');
close fc_cursor;
end;

执行查看相关结果

SQL> set serveroutput on
SQL> exec  DisplayFeatureClassIndexSize

FetureClass:A24   IndexSize:512KB
FetureClass:A26   IndexSize:512KB
FetureClass:A8   IndexSize:512KB
FetureClass:AAABB   IndexSize:512KB
FetureClass:AMD_ZWMD_CAT   IndexSize:512KB
FetureClass:ATLANTIC   IndexSize:512KB
FetureClass:DDD   IndexSize:23040KB
FetureClass:DLBT   IndexSize:512KB
FetureClass:DLTB1   IndexSize:512KB
FetureClass:DLTB2   IndexSize:512KB
FetureClass:GDB_ITEMS   IndexSize:512KB
FetureClass:G_TEST1_NET_JUNCTIONS   IndexSize:512KB
FetureClass:HS   IndexSize:1024KB
FetureClass:KZBJBJ   IndexSize:512KB
FetureClass:PLACE   IndexSize:512KB
FetureClass:QUXIAN   IndexSize:512KB
FetureClass:QZPOI4   IndexSize:1024KB
FetureClass:QZPOI4_1   IndexSize:1024KB
FetureClass:QZPOI4_2   IndexSize:1024KB
FetureClass:RES2_4M   IndexSize:512KB
FetureClass:RES2_4MANNO   IndexSize:512KB
FetureClass:RES2_4MANNO2   IndexSize:512KB
FetureClass:ROAD   IndexSize:1024KB
FetureClass:T1_LINE   IndexSize:512KB
FetureClass:XIAN   IndexSize:512KB
FetureClass:XZQH   IndexSize:512KB

PL/SQL procedure successfully completed

上面的代码仅供参考！

 -------------------------------------------------------------------------------------------------------
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
------------------------------------------------------------------------------------------------------