Ora-01555 While Running A Query on DBA_EXTENTS, DBA_SEGMENTS and DBA_TABLES (Doc ID 340998.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6.0
This problem can occur on any platform.

Symptoms

Customer is getting ORA-01555 while running the following query:

SELECT b.owner, a.table_name, round(sum(b.bytes)/1024/1024/1024) GB,
a.partitioned
FROM dba_tables a, dba_extents b, dba_segments c
where a.owner = b.owner
and a.table_name = b.segment_name
and c.owner = b.owner
and c.segment_name = b.segment_name
and c.segment_type LIKE 'TABLE%'
GROUP BY b.owner, a.table_name, a.partitioned
HAVING round(sum(b.bytes)/1024/1024/1024) >= 10
order by 1,2;

Verified the issue by the alert log file<alert_PDM01.log>:
Fri Sep 23 14:25:33 2005
ORA-01555 caused by SQL statement below (Query Duration=1127478332 sec, SCN: 0x051d.6453a8a2):
Fri Sep 23 14:25:33 2005
SELECT b.owner, a.table_name, round(sum(b.bytes)/1024/1024/1024) GB,
a.partitioned
FROM dba_tables a, dba_extents b, dba_segments c
where a.owner = b.owner
and a.table_name = b.segment_name
and c.owner = b.owner
and c.segment_name = b.segment_name
and c.segment_type LIKE 'TABLE%'
GROUP BY b.owner, a.table_name, a.partitioned
HAVING round(sum(b.bytes)/1024/1024/1024) >= 10
order by 1,2

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms