查询每个表占用空间大小

一旦遇到世界经济不景气或金融危机 SELECT \'select sum(bytes) FROM dba_segments WHERE owner =\'|| \'\"TESTBAR\"\'||\' AND segment_type =\'|| \'\"TABLE\"\'||\' AND segment_name = \"\'|| table_name||\'\" ;\' from user_tables;

错误的，对于oracle而言，双引号

要用单引号，可用

可以用ASCII码处理，其它特殊字符如也一样，如

insert into t values(\'i\'||chr(39)||\'m\'); -- chr(39)代表字符\'

或者用两个单引号表示一个

or insert into t values(\'I\'\'m\'); -- 两个\'\'可以表示一个\'

SELECT \'select segment_name,sum(bytes) FROM dba_segments WHERE owner =\'|| chr(39)||\'TESTBAR\'||chr(39)||\' AND segment_type =\'||chr(39)||\'TABLE\'||chr(39)||\' AND segment_name = \'|| chr(39)||table_name||chr(39)||\';\' from user_tables;

注意，对于分区表要采用\"TABLE PARTITION\"方式

SQL set linesize 300

SQL set head off

SQL spool l

exec statement

SQLspool off

处理空行问题：

采用excel的自动筛选进行空行过滤!以及计算空间总和。

实用考虑：

0 产生行一例

select \'SELECT sum(bytes)/1024/1024 Mbytes FROM user_segments WHERE segment_type = \'||chr(39)||\'TABLE\'||chr(39)||\' AND segment_name = \'||chr(39)||segment_name||chr(39)||\';\' from user_segments where segment_type=\'TABLE\';

0、查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;

1、表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments where segment_type=\'TABLE\' group by segment_name;

2、索引占用空间select segment_name ,sum(bytes)/1024/1024 from user_segments where segment_type =\'INDEX\' group by segment_name;

3、分区表TABLE PARTITION占用空间

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type=\'TABLE PARTITION\' group by segment_name;