Home » SQL & PL/SQL » SQL & PL/SQL » About USER_TABLES error
About USER_TABLES error [message #355985] Tue, 28 October 2008 22:09 Go to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
I got a weird error happen in USER_TABLES.
My statement is like this in application

select count(1) into cnt
from user_tables
where table_name = 'something'

once in a blues moon, the cnt return me 0 where second attempt it return 1, but the table is always there.
What may cause it return 0?
Re: About USER_TABLES error [message #356002 is a reply to message #355985] Tue, 28 October 2008 23:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
* Sometimes the table is there, sometimes it is not.
* You don't use the same user each time.

You don't prove what you states so I tend to trust Oracle and not you.

Regards
Michel
Re: About USER_TABLES error [message #356007 is a reply to message #356002] Wed, 29 October 2008 00:30 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
This application is running 24/7, it always login as same user.
my application process is like this, first connect, process, disconnect after 1 min rerun connect again. When query to USER_TABLES, it return 0; the application will skip the subsequent process to exit and disconnect then after a minute it connect again, but this time query to USER_TABLES it return 1. No body is modyfying the table.
I trust on Oracle too, just hope that can find out what causing this happen.
Re: About USER_TABLES error [message #356015 is a reply to message #356007] Wed, 29 October 2008 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you still don't post the code and how you call it, we can't help.

Regards
Michel
Re: About USER_TABLES error [message #356020 is a reply to message #356007] Wed, 29 October 2008 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
No body is modyfying the table.

Well, perhaps it really is nobody, but it might be something.

Does some of the procedures create (and drop) tables dynamically? You know, using the EXECUTE IMMEDIATE? So your job checks the situation when the table is dropped, but next time it runs, the table is back there?
Re: About USER_TABLES error [message #356025 is a reply to message #355985] Wed, 29 October 2008 01:42 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

You will try as Table name must be given in Upper case.

select count(1) into cnt
from user_tables
where table_name = 'SOMETHING';
Re: About USER_TABLES error [message #356031 is a reply to message #356025] Wed, 29 October 2008 01:58 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@cvs_1984,
Hmm...
So how does the following code:
cvs_1984 wrote on Wed, 29 October 2008 12:12
You will try as Table name must be given in Upper case.

select count(1) into cnt
from user_tables
where table_name = 'SOMETHING';


Explain something like:
lsyhui wrote on Wed, 29 October 2008 08:39

once in a blues moon, the cnt return me 0 where second attempt it return 1, but the table is always there.
What may cause it return 0?



Regards,
Jo
Re: About USER_TABLES error [message #356056 is a reply to message #356031] Wed, 29 October 2008 04:12 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
joicejohn wrote on Wed, 29 October 2008 14:58
@cvs_1984,
Hmm...
So how does the following code:
cvs_1984 wrote on Wed, 29 October 2008 12:12
You will try as Table name must be given in Upper case.

select count(1) into cnt
from user_tables
where table_name = 'SOMETHING';


Explain something like:
lsyhui wrote on Wed, 29 October 2008 08:39

once in a blues moon, the cnt return me 0 where second attempt it return 1, but the table is always there.
What may cause it return 0?



Regards,
Jo


the table_name = 'something' is just giving an example.
my application is running 24/7 and every cycle it will check the table exist in USER_TABLES table, once a while the table is not exist where subsequent check it exist, but the table is always there. none of the application drop the table.
would there be any internal error happen causing this?
Re: About USER_TABLES error [message #356061 is a reply to message #356056] Wed, 29 October 2008 04:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The chances of your application dropping and re-creating the table, or the way you actually coded the check in a way that fails sometimes (by giving the wrong table name or user for example) compared to some internal error being responsible, I would estimate at about a million to one.

Why did you even code that check? What's the reason for it? If someone coded that check, he must have had SOME reason for thinking the table might not be there.
Re: About USER_TABLES error [message #356086 is a reply to message #356056] Wed, 29 October 2008 08:04 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Also note that checking for the existence of a table with COUNT(*) will not produce a NO_DATA_FOUND exception if it is not there.
Look at USER_OBJECTS at the CREATED column. It might give you an idea if the table has always been thee or not.
But as is always mentioned, there is no code, so we can't help. I believe Oracle.
Re: About USER_TABLES error [message #356241 is a reply to message #356086] Thu, 30 October 2008 01:21 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
joy_division wrote on Wed, 29 October 2008 21:04
Also note that checking for the existence of a table with COUNT(*) will not produce a NO_DATA_FOUND exception if it is not there.
Look at USER_OBJECTS at the CREATED column. It might give you an idea if the table has always been thee or not.
But as is always mentioned, there is no code, so we can't help. I believe Oracle.

I check from USER_OBJECTS table, the created date is correct, showing last time creation date. I guess my error is not come from whether the table is there or not. Due to in this sql statement i did not check the return sqlcode. I just treat if cnt return me 0, the table is not exist. This code is written by my senior.

As my application is running in a loop, connect -> process -> disconnect as 1 cycle. When sometimes the cnt return me 0, after few cycle of process, i was unable to connect to the database which return me ORA-00604 error during attempt to connect to database.

What might be causing this? I have checked on shared pool, cursor and session, it all no issues. What other possibility may causing this?
Re: About USER_TABLES error [message #356263 is a reply to message #356241] Thu, 30 October 2008 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean by 'last time it was created'?
You are aware of the fact that one should not create and drop tables at runtime in Oracle, aren't you?
This whole thing smells like one process creating the table and another process checking if that first process is done already...
Why else check for the existence of the table in the first place?
Re: About USER_TABLES error [message #356264 is a reply to message #356241] Thu, 30 October 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00604: error occurred at recursive SQL level %s
 *Cause:  An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
 *Action: If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Regards
Michel
Re: About USER_TABLES error [message #356282 is a reply to message #356263] Thu, 30 October 2008 03:15 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
Frank wrote on Thu, 30 October 2008 14:52
What do you mean by 'last time it was created'?
You are aware of the fact that one should not create and drop tables at runtime in Oracle, aren't you?
This whole thing smells like one process creating the table and another process checking if that first process is done already...
Why else check for the existence of the table in the first place?

I means the created date is correct, no one re-create it.
i know oracle will not create or drop table itself, would there be any causes that might cause me fail to attempt at first time, subsequent attempt success?

As i mention earlier, this might not be the main reason, whenever i got the error ora-00604 few cycle before the cnt will return me 0. i'm guessing some memory or cursor is being used up, so for second attempt it able to return me 1 but after some few successful cycle when attempt connect db ora-00604 occur.

what may causing ora-00604 happen? it show
An error occurred while processing a recursive SQL statement
a statement applying to internal dictionary tables).
what would be the internal dictionary tables?
Re: About USER_TABLES error [message #356286 is a reply to message #356282] Thu, 30 October 2008 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what may causing ora-00604 happen? it show
An error occurred while processing a recursive SQL statement
a statement applying to internal dictionary tables).


Michel Cadot wrote on Thu, 30 October 2008 07:53
ORA-00604: error occurred at recursive SQL level %s
 *Cause:  An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
 *Action: If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.


Quote:
a statement applying to internal dictionary tables

A recursive SQL statement may NOT be something involving dictionary, it may something you wrote.

In the end, I smell the infamous smell of "when others" without raise.

Why are you so reluctant to post the actual code and session as requested?

Regards
Michel

Re: About USER_TABLES error [message #356288 is a reply to message #356286] Thu, 30 October 2008 03:46 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
This is my main function

for (loop_i = 0 ; ; loop_i++) {
ora_connect(CONNECT_TO_REMOTE,
tns_name_gstg,
username_gstg,
password_gstg
);
if (ora_error_code != ORA_SUCCESS) {
sprintf(log_msg_gstg, "Could not connect to Oracle with oracle error code %d", ora_error_code);
log_msg(1, log_msg_gstg);
exit(EXIT_FAILURE);
}
make_general_edc_summary();
ora_disconnect();
sleep(query_time_gi);
if (loop_i == 1000)
loop_i == 0;
}

make_general_edc_summary() is the main process.
so in this function it will check the table exist.
EXEC SQL select count(1) into cnt
from user_tables
where table_name = 'SOMETHING'

where error ora-00604 will raise at ora_connect()
Re: About USER_TABLES error [message #356296 is a reply to message #356288] Thu, 30 October 2008 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
make_general_edc_summary() is the main process.
so in this function it will check the table exist.

Why don't you post it?
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Quote:
where error ora-00604 will raise at ora_connect()

Try to connect with SQL*Plus.


Regards
Michel


Re: About USER_TABLES error [message #356304 is a reply to message #356296] Thu, 30 October 2008 04:33 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
Michel Cadot wrote on Thu, 30 October 2008 17:12
Quote:
make_general_edc_summary() is the main process.
so in this function it will check the table exist.

Why don't you post it?



the code too long to post. it happen around once a month, every time when happen ora-00604, the server need to restart then the process will run smooth.
Re: About USER_TABLES error [message #356309 is a reply to message #356304] Thu, 30 October 2008 04:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
OK. Now you have completely confuzzled me.

When the problem is the ora-00604 that is happening during the CONNECT, then why did you go on and on about a funny table that is allegedly not there when the check for that table could never actually have happened, since the CONNECT already failed?
Re: About USER_TABLES error [message #356477 is a reply to message #356309] Thu, 30 October 2008 20:46 Go to previous messageGo to next message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
ThomasG wrote on Thu, 30 October 2008 17:56
OK. Now you have completely confuzzled me.

When the problem is the ora-00604 that is happening during the CONNECT, then why did you go on and on about a funny table that is allegedly not there when the check for that table could never actually have happened, since the CONNECT already failed?


Before happen ora-00604, there is always have error of table not found(cnt return 0) but second attempt cnt return 1 then after few succefull cycle ora-00604 occur, i'm not sure whether this 2 errors is related or not and because table not found happen first before ora-00604 that whay i post that first.
sorry if i confusing you, because i also blur here.
Re: About USER_TABLES error [message #356519 is a reply to message #356477] Fri, 31 October 2008 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You said:
- you get ora-604 on connect
- you get count 0 before ora-604

I conclude you try to get count before connecting.

Regards
Michel
Re: About USER_TABLES error [message #356529 is a reply to message #356519] Fri, 31 October 2008 00:29 Go to previous message
lsyhui
Messages: 10
Registered: October 2008
Junior Member
Michel Cadot wrote on Fri, 31 October 2008 13:03
You said:
- you get ora-604 on connect
- you get count 0 before ora-604

I conclude you try to get count before connecting.

Regards
Michel


I think i better put it in this way to avoid confusing.
1 full cycle of process is
Connect
main task
Disconnect
it will keep looping connect-> main task-> disconnect, connect-> main task-> disconnect, connect-> main task-> disconnect.

At cycle 1 - everything success
maybe at cycle 5 - connect success, in main task the cnt return 0 then disconnect.
At cycle 6 - everything success.
maybe until cycle 15 - during connect, error ora-604 occur. when connection fail, it will exit the app.

I'm curious, when there is always cnt return 0 after few cycle during connection ora-604 occur. I'm not sure whether this 2 error is related. Currently the solving method is restart the whole server, then no error will be happen in short period, but seems happen once a month since Sept.
Previous Topic: Procedure Calling another procedure
Next Topic: Related ON DELETE CASCADE Command
Goto Forum:
  


Current Time: Thu Dec 08 16:35:44 CST 2016

Total time taken to generate the page: 0.09915 seconds