-
ORA-01555: rollback segment number 9 with name "_SYSSMU9$" too smal
Hello Everyone,
[B]When:[/B]
While exporting a 30GB table getting this error
Environment:
I have an UNDO segment of 10GB. Retention is 7200.
(1) Segment Details
SQL> select SEGMENT_NAME,TABLESPACE_NAME,SEGMENT_ID from DBA_ROLLBACK_SEGS;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
SYSTEM SYSTEM 0
_SYSSMU1$ UNDOTBS 1
_SYSSMU2$ UNDOTBS 2
_SYSSMU3$ UNDOTBS 3
_SYSSMU4$ UNDOTBS 4
_SYSSMU5$ UNDOTBS 5
_SYSSMU6$ UNDOTBS 6
_SYSSMU7$ UNDOTBS 7
_SYSSMU8$ UNDOTBS 8
_SYSSMU9$ UNDOTBS 9
_SYSSMU10$ UNDOTBS 10
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID
------------------------------ ------------------------------ ----------
_SYSSMU11$ UNDOTBS 11
_SYSSMU12$ UNDOTBS 12
_SYSSMU13$ UNDOTBS 13
_SYSSMU14$ UNDOTBS 14
_SYSSMU15$ UNDOTBS 15
_SYSSMU16$ UNDOTBS 16
_SYSSMU17$ UNDOTBS 17
18 rows selected.
(2) USAGE
(2) SQL> select tablespace_name,sum(bytes/1024/1024) from dba_segments where tablespace_name='UNDOTBS' group by tablespace_name;
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 118.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 118.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 118.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 118.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 113.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 111.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 122.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 122.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 114.992188
SQL> /
TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
UNDOTBS 114.992188
Question
Why I am getting such error even when my UNDO has enough space to handle the transactions? Anything I am missing here?
sumit
-
post the code you ran to get it, are you fetching across a commit?
-
exp userid=username/pass file=filename log=logfile_name tables=tablename compress=Y consistent=Y
sumit
-
ok, I got this from alter log
ORA-01555 caused by SQL statement below (Query Duration=11645 sec, SCN: 0x06a4.db3568f0):
Wed Apr 19 01:05:52 2006
SELECT /*+NESTED_TABLE_GET_REFS+*/ "schema_name"."table_name".* FROM "schema_name"."table_name"
Wed Apr 19 01:49:15 2006
What I say:
My Undo Rentention is 2 hrs (7200). The above query stays for 3.23 hrs. Segment usage was maximum around 140MB.
What cause this error to terminate the export? And most importantly DOES Oracle use UNDO while EXPORTING and table segment :(
sumit
-
of course it does, everything uses undo
add direct=y to the export and change undo_retention to more than the value of the longest query
-
if we look into the statement (the longest query) in alter file it is
SELECT /*+NESTED_TABLE_GET_REFS+*/ "schema_name"."table_name".* FROM "schema_name"."table_name"
In such case do u suggest me to set the retention upto 12hrs? Exp took 4hrs to comlete 9Gb+
sumit
-
undo retention should be set to higher then the single longest running query. Set your undo tablespace size appropriately
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|