搞个 sql 脚本实现set echo off set verify offprompt prompt specify OWNER of the table define owner = &1 prompt prompt specify NAME of the table define tablename = &2column object_type format a12 column owner format a10 column object_name format a25 column tablespace_name format a10 column partitioned format a3select 'TABLE' object_type, owner,table_name object_name, tablespace_name,partitioned, space_used,space_allocated from dba_tables,TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF(owner,table_name,'TABLE',1)) where owner=upper('&owner') and table_name=upper('&tablename') UNION ALL select index_type||' INDEX' object_type, owner,index_name, tablespace_name,partitioned, space_used,space_allocated from dba_indexes,TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF(owner,index_name,'INDEX',null)) where table_name=upper('&tablename') and table_owner=upper('&owner');undefine owner undefine tablename
上一个脚本方便在 sqlplus 中显示,并且可以查询表、索引实际分配和使用的空间(这个是 dba_segments 查不到的)。既然不满意,看看下面这个是否可以。with tb as( select table_name, owner, tablespace_name, partitioned, (select sum(blocks) from dba_segments where segment_name=t.table_name group by segment_name) blocks, (case when exists (select 1 from dba_constraints where table_name=t.table_name and owner=t.owner and constraint_type='P') then 'Y' else 'N' end) pk, (case when exists (select 1 from dba_constraints where table_name=t.table_name and owner=t.owner and constraint_type='F') then 'Y' else 'N' end) fk from dba_tables t ), ix as( select index_name, index_type, tablespace_name, table_owner, table_name, (select sum(blocks) from dba_segments where segment_name=i.index_name group by segment_name) blocks from dba_indexes i ) select tb.table_name, tb.owner, tb.tablespace_name, tb.partitioned, tb.blocks table_allocate_blocks, tb.pk, tb.fk, ix.index_name, ix.index_type, ix.tablespace_name index_tablespace_name, ix.blocks index_allocate_blocks from tb, ix where tb.owner=ix.table_owner and tb.table_name=ix.table_name and tb.owner=upper('&owner') and tb.table_name=upper('&tablename');
改一下,最后的 tb 和 ix 关联用 left join,避免没有索引会返回空集。
昨天晚上做出来了,但是没有考虑性能问题。select aa.table_name,aa.users,aa.users_tablespace,aa.isPartition,aa.table_bytes,aa.index_name,aa.indexs_tablespace,sum(bb.bytes) / 1024 / 1024 index_bytes from ( select a.segment_name table_name, a.owner users, a.tablespace_name users_tablespace, a.isPartition, a.table_bytes, b.index_name index_name, b.tablespace_name indexs_tablespace from (select owner, isPartition, tablespace_name, segment_name, sum(bytes) / 1024 / 1024 table_bytes from (select owner, decode(partition_name, null, 1, 0) isPartition, tablespace_name, segment_name, bytes from dba_extents where owner = '&owner' and segment_type in ('TABLE PARTITION', 'TABLE')) group by owner, isPartition, tablespace_name, segment_name) a, (select * from dba_indexes where owner = '&owner') b where a.segment_name = b.table_name(+) ) aa, ( select owner,decode(partition_name,null,1,0) isPartition,tablespace_name,segment_name, bytes from dba_extents where owner = '&owner' and segment_type in ('INDEX PARTITION','INDEX') ) bb where aa.index_name = bb.segment_name group by aa.table_name,aa.users,aa.users_tablespace,aa.isPartition,aa.table_bytes,aa.index_name,aa.indexs_tablespace order by index_bytes
DBA_TABLES是数据字典表
set verify offprompt
prompt specify OWNER of the table
define owner = &1
prompt
prompt specify NAME of the table
define tablename = &2column object_type format a12
column owner format a10
column object_name format a25
column tablespace_name format a10
column partitioned format a3select 'TABLE' object_type,
owner,table_name object_name,
tablespace_name,partitioned,
space_used,space_allocated
from dba_tables,TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF(owner,table_name,'TABLE',1))
where owner=upper('&owner') and table_name=upper('&tablename')
UNION ALL
select index_type||' INDEX' object_type,
owner,index_name,
tablespace_name,partitioned,
space_used,space_allocated
from dba_indexes,TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF(owner,index_name,'INDEX',null))
where table_name=upper('&tablename') and table_owner=upper('&owner');undefine owner
undefine tablename
哥们我要的是 表 -- 索引 这样的结果呀。你这样的结果用dba_segment就可以查出来了,不需要UNION。
好的,我正在做。java3344520有时间帮我看看。
select table_name, owner, tablespace_name, partitioned,
(select sum(blocks) from dba_segments where segment_name=t.table_name group by segment_name) blocks,
(case when exists (select 1 from dba_constraints
where table_name=t.table_name and owner=t.owner and constraint_type='P')
then 'Y' else 'N' end) pk,
(case when exists (select 1 from dba_constraints
where table_name=t.table_name and owner=t.owner and constraint_type='F')
then 'Y' else 'N' end) fk
from dba_tables t
),
ix as(
select index_name, index_type, tablespace_name, table_owner, table_name,
(select sum(blocks) from dba_segments where segment_name=i.index_name group by segment_name) blocks
from dba_indexes i
)
select tb.table_name, tb.owner, tb.tablespace_name, tb.partitioned, tb.blocks table_allocate_blocks, tb.pk, tb.fk,
ix.index_name, ix.index_type, ix.tablespace_name index_tablespace_name, ix.blocks index_allocate_blocks
from tb, ix
where tb.owner=ix.table_owner and tb.table_name=ix.table_name
and tb.owner=upper('&owner') and tb.table_name=upper('&tablename');
(
select a.segment_name table_name,
a.owner users,
a.tablespace_name users_tablespace,
a.isPartition,
a.table_bytes,
b.index_name index_name,
b.tablespace_name indexs_tablespace
from (select owner,
isPartition,
tablespace_name,
segment_name,
sum(bytes) / 1024 / 1024 table_bytes
from (select owner,
decode(partition_name, null, 1, 0) isPartition,
tablespace_name,
segment_name,
bytes
from dba_extents
where owner = '&owner'
and segment_type in ('TABLE PARTITION', 'TABLE'))
group by owner, isPartition, tablespace_name, segment_name) a,
(select * from dba_indexes where owner = '&owner') b
where a.segment_name = b.table_name(+)
) aa,
(
select owner,decode(partition_name,null,1,0) isPartition,tablespace_name,segment_name, bytes
from dba_extents
where owner = '&owner' and segment_type in ('INDEX PARTITION','INDEX')
) bb
where aa.index_name = bb.segment_name
group by aa.table_name,aa.users,aa.users_tablespace,aa.isPartition,aa.table_bytes,aa.index_name,aa.indexs_tablespace
order by index_bytes