使用存储过程批量查询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;--要素类bytesfc_bytes user_segments.bytes%type;beginopen 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 DisplayFeatureClassSizeFetureClass:AAA   Size:512KBFetureClass:AAABB   Size:512KBFetureClass:AMD_ZWMD_ART   Size:512KBFetureClass:AMD_ZWMD_BND   Size:512KBFetureClass:AMD_ZWMD_CAT   Size:512KBFetureClass:AMD_ZWMD_LOG   Size:512KBFetureClass:ATLANTIC   Size:512KBFetureClass:BB   Size:14336KBFetureClass:DDD   Size:58368KBFetureClass:DLBT   Size:1024KBFetureClass:DLTB1   Size:1024KBFetureClass:DLTB2   Size:1024KBFetureClass:EXPORT   Size:512KBFetureClass:G_TEST1_NET_JUNCTIONS   Size:512KBFetureClass:HS   Size:2560KBFetureClass:KZBJBJ   Size:512KBFetureClass:LZ_FH_YT_PL   Size:154624KBFetureClass:OWNER   Size:512KBFetureClass:PLACE   Size:512KBFetureClass:POLYGON   Size:512KBFetureClass:QUXIAN   Size:512KBFetureClass:QZPOI4   Size:2048KBFetureClass:QZPOI4_1   Size:2048KBFetureClass:QZPOI4_2   Size:2048KBFetureClass:RANDOMCRIMEPOINTS_COPYROWS   Size:7168KBFetureClass:RES2_4M   Size:512KBFetureClass:RES2_4MANNO   Size:512KBFetureClass:RES2_4MANNO2   Size:512KBFetureClass:ROAD   Size:1536KBFetureClass:T1_LINE   Size:512KBFetureClass:XIAN   Size:512KBFetureClass:XZQH   Size:512KBPL/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;--IndexIDfc_indexid sde.st_geometry_index.index_id%type;--要素类索引bytesfc_indexbytes user_segments.bytes%type;test user_segments.segment_name%type;beginopen 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 onSQL> exec  DisplayFeatureClassIndexSizeFetureClass:A24   IndexSize:512KBFetureClass:A26   IndexSize:512KBFetureClass:A8   IndexSize:512KBFetureClass:AAABB   IndexSize:512KBFetureClass:AMD_ZWMD_CAT   IndexSize:512KBFetureClass:ATLANTIC   IndexSize:512KBFetureClass:DDD   IndexSize:23040KBFetureClass:DLBT   IndexSize:512KBFetureClass:DLTB1   IndexSize:512KBFetureClass:DLTB2   IndexSize:512KBFetureClass:GDB_ITEMS   IndexSize:512KBFetureClass:G_TEST1_NET_JUNCTIONS   IndexSize:512KBFetureClass:HS   IndexSize:1024KBFetureClass:KZBJBJ   IndexSize:512KBFetureClass:PLACE   IndexSize:512KBFetureClass:QUXIAN   IndexSize:512KBFetureClass:QZPOI4   IndexSize:1024KBFetureClass:QZPOI4_1   IndexSize:1024KBFetureClass:QZPOI4_2   IndexSize:1024KBFetureClass:RES2_4M   IndexSize:512KBFetureClass:RES2_4MANNO   IndexSize:512KBFetureClass:RES2_4MANNO2   IndexSize:512KBFetureClass:ROAD   IndexSize:1024KBFetureClass:T1_LINE   IndexSize:512KBFetureClass:XIAN   IndexSize:512KBFetureClass:XZQH   IndexSize:512KBPL/SQL procedure successfully completed

上面的代码仅供参考！

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