Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)
From: spremuta on 12 Sep 2006 05:34
Hi, i have this errors in an oracle 9.0.2 db, on a hp-ux system.
I have read some info about this issue, i executed this query:

SELECT segment_name, tablespace_name, bytes, blocks, extents
FROM sys.dba_segments where tablespace_name like 'UN%';

and i obtained this results:

SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS
_SYSSMU1$,UNDOTBS1,15446671360,1885580,392
_SYSSMU2$,UNDOTBS1,570548224,69647,49
_SYSSMU3$,UNDOTBS1,1812062208,221199,112
_SYSSMU4$,UNDOTBS1,814866432,99471,101
_SYSSMU5$,UNDOTBS1,620879872,75791,48
_SYSSMU6$,UNDOTBS1,520216576,63503,64
_SYSSMU7$,UNDOTBS1,335667200,40975,7
_SYSSMU8$,UNDOTBS1,478273536,58383,59
_SYSSMU9$,UNDOTBS1,679600128,82959,76
_SYSSMU10$,UNDOTBS1,1263656960,154255,105
_SYSSMU11$,UNDOTBS1,6618734592,807951,213
_SYSSMU12$,UNDOTBS1,1282531328,156559,78
_SYSSMU13$,UNDOTBS1,910286848,111119,23
_SYSSMU14$,UNDOTBS1,444719104,54287,55
_SYSSMU15$,UNDOTBS1,309452800,37775,39
_SYSSMU16$,UNDOTBS1,335667200,40975,7
_SYSSMU17$,UNDOTBS1,258072576,31503,19
_SYSSMU18$,UNDOTBS1,4806795264,586767,186
_SYSSMU19$,UNDOTBS1,1198645248,146319,141
_SYSSMU20$,UNDOTBS1,369221632,45071,11
_SYSSMU21$,UNDOTBS1,193060864,23567,25
_SYSSMU22$,UNDOTBS1,226615296,27663,22
_SYSSMU23$,UNDOTBS1,15470837760,1888530,445
_SYSSMU24$,UNDOTBS1,939646976,114703,65
_SYSSMU25$,UNDOTBS1,1090641920,133135,48
_SYSSMU26$,UNDOTBS1,67231744,8207,10
_SYSSMU27$,UNDOTBS1,1115807744,136207,19


i obtain this errors even for segment _SYSSMU6

in my $ORACLE_SID.ora file i have this options:

db_block_size = 8192
db_cache_size = 33554432
db_domain = ''
db_file_multiblock_read_count = 16
dispatchers = '(PROTOCOL=TCP) (SERVICE=ovpiXDB)'
fast_start_mttr_target = 300
hash_join_enabled = TRUE
instance_name = ovpi
java_pool_size = 117440512
job_queue_processes = 10
large_pool_size = 16777216
log_archive_dest_1 = 'LOCATION=/opt/oracle/product/9.2.0/dbs/arch'
open_cursors = 300
pga_aggregate_target = 25165824
processes = 150
query_rewrite_enabled = FALSE
remote_login_passwordfile = SHARED
shared_pool_size = 117440512
sort_area_size = 524288
star_transformation_enabled = FALSE
timed_statistics = TRUE
undo_management = AUTO
undo_retention = 10800
undo_tablespace = UNDOTBS1
user_dump_dest = /opt/oracle/admin/ovpi/udump


thanks for assistance
regards

Drain

From: sjaffarhussain@gmail.com on 12 Sep 2006 05:49
What is the value of undo_retention parameter ?

There is a link between this error and with your undo)retention value.

Jaffar

spremuta(a)gmail.com wrote:
> Hi, i have this errors in an oracle 9.0.2 db, on a hp-ux system.
> I have read some info about this issue, i executed this query:
>
> SELECT segment_name, tablespace_name, bytes, blocks, extents
> FROM sys.dba_segments where tablespace_name like 'UN%';
>
> and i obtained this results:
>
> SEGMENT_NAME,TABLESPACE_NAME,BYTES,BLOCKS,EXTENTS
> _SYSSMU1$,UNDOTBS1,15446671360,1885580,392
> _SYSSMU2$,UNDOTBS1,570548224,69647,49
> _SYSSMU3$,UNDOTBS1,1812062208,221199,112
> _SYSSMU4$,UNDOTBS1,814866432,99471,101
> _SYSSMU5$,UNDOTBS1,620879872,75791,48
> _SYSSMU6$,UNDOTBS1,520216576,63503,64
> _SYSSMU7$,UNDOTBS1,335667200,40975,7
> _SYSSMU8$,UNDOTBS1,478273536,58383,59
> _SYSSMU9$,UNDOTBS1,679600128,82959,76
> _SYSSMU10$,UNDOTBS1,1263656960,154255,105
> _SYSSMU11$,UNDOTBS1,6618734592,807951,213
> _SYSSMU12$,UNDOTBS1,1282531328,156559,78
> _SYSSMU13$,UNDOTBS1,910286848,111119,23
> _SYSSMU14$,UNDOTBS1,444719104,54287,55
> _SYSSMU15$,UNDOTBS1,309452800,37775,39
> _SYSSMU16$,UNDOTBS1,335667200,40975,7
> _SYSSMU17$,UNDOTBS1,258072576,31503,19
> _SYSSMU18$,UNDOTBS1,4806795264,586767,186
> _SYSSMU19$,UNDOTBS1,1198645248,146319,141
> _SYSSMU20$,UNDOTBS1,369221632,45071,11
> _SYSSMU21$,UNDOTBS1,193060864,23567,25
> _SYSSMU22$,UNDOTBS1,226615296,27663,22
> _SYSSMU23$,UNDOTBS1,15470837760,1888530,445
> _SYSSMU24$,UNDOTBS1,939646976,114703,65
> _SYSSMU25$,UNDOTBS1,1090641920,133135,48
> _SYSSMU26$,UNDOTBS1,67231744,8207,10
> _SYSSMU27$,UNDOTBS1,1115807744,136207,19
>
>
> i obtain this errors even for segment _SYSSMU6
>
> in my $ORACLE_SID.ora file i have this options:
>
> db_block_size = 8192
> db_cache_size = 33554432
> db_domain = ''
> db_file_multiblock_read_count = 16
> dispatchers = '(PROTOCOL=TCP) (SERVICE=ovpiXDB)'
> fast_start_mttr_target = 300
> hash_join_enabled = TRUE
> instance_name = ovpi
> java_pool_size = 117440512
> job_queue_processes = 10
> large_pool_size = 16777216
> log_archive_dest_1 = 'LOCATION=/opt/oracle/product/9.2.0/dbs/arch'
> open_cursors = 300
> pga_aggregate_target = 25165824
> processes = 150
> query_rewrite_enabled = FALSE
> remote_login_passwordfile = SHARED
> shared_pool_size = 117440512
> sort_area_size = 524288
> star_transformation_enabled = FALSE
> timed_statistics = TRUE
> undo_management = AUTO
> undo_retention = 10800
> undo_tablespace = UNDOTBS1
> user_dump_dest = /opt/oracle/admin/ovpi/udump
>
>
> thanks for assistance
> regards
>
> Drain

From: spremuta on 12 Sep 2006 06:16

sjaffarhussain(a)gmail.com ha scritto:

> What is the value of undo_retention parameter ?
>
> There is a link between this error and with your undo)retention value.
>
> Jaffar
>


undo_retention = 10800

From: Vladimir M. Zakharychev on 12 Sep 2006 06:50

spremuta(a)gmail.com wrote:
> Hi, i have this errors in an oracle 9.0.2 db, on a hp-ux system.
> I have read some info about this issue, i executed this query:
>
> [skip...]
>
> thanks for assistance
> regards
>
> Drain

And what the application is doing when you're getting the error? Does
it happen to fetch something from a big result set in a loop and commit
inside this loop? Or does it happen that the query runs longer than
specified undo retention period (10800 seconds is 3 hours,) while
there's a lot of transactional activity in the db?

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

From: spremuta on 12 Sep 2006 08:57

Vladimir M. Zakharychev ha scritto:

> And what the application is doing when you're getting the error? Does
> it happen to fetch something from a big result set in a loop and commit
> inside this loop? Or does it happen that the query runs longer than
> specified undo retention period (10800 seconds is 3 hours,) while
> there's a lot of transactional activity in the db?
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com


Is there a fetch into a cursor declaration, in a sql script that
somethimes is long 4-6 hours....
in a table there are about 90milions of records.....

 |  Next  |  Last
Pages: 1 2 3 4
Prev: Oracle sql completed with warnings...
Next: Client Connection Error (TNS-12518 TNS-12560 TNS-00530)