今天客户要导出几张表,环境是11grac11.2.0.4.
导出之前,查询数据量
select sum(bytes/1024/10240 from dba_segments where segment_name='***';
领导说这个不准,缺少lob字段的查询。给出语句如下:
SELECT
(SELECT NVL(SUM(S.BYTES),0) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('LC0019999') AND
(S.SEGMENT_NAME = UPPER('ZWPZGS'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('LC0019999') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ZWPZGS') AND L.OWNER = UPPER('LC0019999'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('LC0019999') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ZWPZGS') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LC0019999')))
"TOTAL TABLE SIZE"
FROM DUAL;
从上面的查询语句块中发现有个dba_lobsd的字典表。
其实确认我select from dba_segments查询对不对只需要desc 该表,看是否有lob字段类型(blob、clob、binary等)当然如果有,还要查下该lob字段里面是否有数据。
导出之前,查询数据量
select sum(bytes/1024/10240 from dba_segments where segment_name='***';
领导说这个不准,缺少lob字段的查询。给出语句如下:
SELECT
(SELECT NVL(SUM(S.BYTES),0) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('LC0019999') AND
(S.SEGMENT_NAME = UPPER('ZWPZGS'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('LC0019999') AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('ZWPZGS') AND L.OWNER = UPPER('LC0019999'))) +
(SELECT NVL(SUM(S.BYTES),0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('LC0019999') AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('ZWPZGS') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('LC0019999')))
"TOTAL TABLE SIZE"
FROM DUAL;
从上面的查询语句块中发现有个dba_lobsd的字典表。
其实确认我select from dba_segments查询对不对只需要desc 该表,看是否有lob字段类型(blob、clob、binary等)当然如果有,还要查下该lob字段里面是否有数据。