Hi
You got a hanging or spinning something – so you can’t change or lock objects in library. I think that you can’t even make DDL on these tables – because Oracle needs to lock library objects.
Is your system MTS?
Do you have lots of users?
Investigate each session and kill unknown...
...insert into t2 values( 1, 2);
insert into t2 values( 2, 3);
insert into t2 values( 8, 9);
and now the insert statement:
insert into t1
select * from t2
where not exists (select 1 from t1
where t1.a = t2.a
and t1.b = t2.b...
Hi
Because it’s not a lock on DML (othervise you would see locks in v$lock_object), you need to investigate the system.
You may try dba_locks and views for latches.
Is it the only table you can’t analyze?
Regards
Allan
Icq: 346225948
Hi
Am I not correct – that you can activate the statement (DBMS_STATS and analyze) – and the statement starts – making lots of IO and after some time it returns the message – or does the message come immediately (the time out may take "some" time).
Regards
Allan
Icq: 346225948
Hi Dima
What do you mean by ”if you have rollback issues…”?
RBS is created based on DML on rows in the table – since the changes are the same (the result is the same – only the script is different) – the RBS is the same.
I agree with you that if possible always use pure DML statements – even...
Hi
Yes and no……
Try look in the manual Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
You got v$lock, v$locked_object, v$session, v$sql and so on.
Regards
Allan
Icq: 346225948
...from car_usage cu
group by salesmanid, year
having count(*) > 6)
and c.carid = cu.carid
order by cu.salesmanid, cu.year, cu.carid;
And if you need to reduce years, then you could change the code to...
Hi
I think it’s because something locks the table as an object in library. Analyze can’t update information on object because object is locked. It’s not the Analyze who is locking but something else. And it’s not the table data – but the table object itself in library.
DBMS_STAT don’t change...
Hi
The simples answer must bee to startup several sessions. Each session takes one table.
However the reason for your request for parallel – is properly because you got performance issues. You can never make good performance then using FETCH. You might have a chance if using bulk statements...
Hi
Anything that can go wrong may go wrong.
There are lots of changes between 8i and 9i.
You code might run efficiently if not making strange programming. But one big problem is always CBO. Several of your queries may behave differently. A lot of parameters now have a huge impact on CBO –...
...I am right – then the session making analyze must use RBS because ALL rows are deleted (not committed). We can see it with trace files.
analyze /* Table still deleted completly without commit */ table big_table
compute statistics for table for all indexes for all indexed columns
call...
Hi
Why do you need to write a procedure? - is it because you are using FETCH (open cursor) instead of using DML (insert or merge with hints)?
Regards
Allan
Icq: 346225948
Hi DeepDiverBecca
Thanks for your reply.
I must start this reply by giving you the credit for trying to solve the problem. It’s was the biggest point of my replies in this thread.
And as I now realize – people in this thread almost hate my replies. I can’t (and it’s not my point) learn old...
Hi Dima
I just had to try proving that I know Oracle don’t consider the above method as a legal option. And I found one answer from Thomas Kyle on Ask Tom to clarify my opinion.
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 10 with name "RBS_SMALL"
too small...
Hi Dima
Well I did properly not express myself clearly
I agree on your last reply. But it’s not what I am writing. I break down the commit by using temp table(s). It’s almost like a chapter in a book. After each chapter I commit. I don’t need to commit then the whole book is finished. I can...
Hi Dima
You can’t find the solution as part of Oracle posted tips. Maybe on OTN in forums like this forum but Oracle do not accept the method as a solution.
But I agree it’s often that you can’t change stuff – not because you don’t want – but for some other reasons like politic, 3. part...
Hi Dima
Oh - nothing except that girls from East Europe is so lovely and alcohol is raining down all over the place – A cola is more expensive than Vodka.
No – actually after the USSR collapsed we have a change to see nice old culture like old towns in Russia and that’s fine.
But people are...
Hi TheShowMeCanuck
Don’t kill the message-boy because you don’t like the message.
Try to go through all the material on AskTom website (http://asktom.oracle.com) at Oracle – you can’t find any information about the solution.
I don’t know your system so I can’t give you a solution. However I...
No.
If Oracle has a problem with ORA-1550 they would have used energy to solve the problem – and if not Microsoft, IBM, Sybase and others would indeed be yelling the problem on all street counters.
Regards
Allan
Icq: 346225948
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.