[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

java.sql.SQLException: ORA-00942: table or view does not exist

Posted on 2011-10-18
23
Medium Priority
?
1,346 Views
Last Modified: 2012-05-12
Hi,

I opened  the DB Console for a database and clicked on the Performance tab and see no activity , just blank. But on the very top where is says -- Host: Average Runnable Processes -- Loading .... and just sits there. No Average Acitive Sessions or anything else. The user says that there is data feeding into the database.  I also clicked on the "Top Activity " link and then i see " ORA-00942: table or view does not exist " error on the top of the page , and the rest is blank.  Nothing on the Instance activity. All this makes me think either the DB Console is not working correctly or there is something wrong with the database? All this started as the user complained that the system (application) is really slow.

Any suggestions will be highly appreciated.
regards!
0
Comment
Question by:crishna1
  • 13
  • 10
23 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36987773
What username did you log into dbconsole with?

Try sys and connect 'as sysdba'.

If that fails, restart dbconsole.

Do you have any tuning sql scripts around in the event you need to troubleshoot sometihng outside the GUI?
0
 

Author Comment

by:crishna1
ID: 36987921
as SYSTEM. i just tried it as SYS after your note and it is the same.
0
 

Author Comment

by:crishna1
ID: 36987954
slightwv, sorry i did not read the entire post from you. I tries as SYS , the issues is the same. Will restart the dbconsole and try again, will let you know.

I do not have any tuning sql scripts.as of now i dont even know if the performance is even database related or not.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:crishna1
ID: 36987978
same issue, after restarting DB Console. How to troubleshoot this? rebuilding the DB Console? if so , any instructions plz.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36988288
You can drop and recreate everytihng but I would probably hold off.

If the database is having 'problems' I would dig into those first.

Check the alert log for possible issues.

If you decide to rebuild/install dbconsole the steps are everywhere.

Here is the first link I found:
http://www.akadia.com/services/ora_dbconsole.html

There is a 'recreate' option as well but I don't like to use it.

You will need to double check the syntax but I believe it is:
emca -config dbconsole db -repos drop
0
 

Author Comment

by:crishna1
ID: 36988308
thank you!  appreciate it.

Will check the alert log and let you know. alos , i agree with the idea of creating the db console instead of re-creating it.

regards!
0
 

Author Comment

by:crishna1
ID: 36988414
below is what i found in the alert log-- there are a bunch of these. At the bottom are the SGA , PGA allocations for this database.

ORA-04030: out of process memory when trying to allocate 169004 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 1049100 bytes (pga heap,kllcqgf:kllsltba)
 </txt>
ORA-20011: Approximate NDV failed: ORA-04030: out of process memory when trying to allocate 131088 bytes

---
SGA
---

Current Allocation
      
Automatic Shared Memory Management      Enabled      
Total SGA Size (MB)      1624      
SGA Component      Current Allocation (MB)
Shared Pool       968
Buffer Cache       624
Large Pool       8
Java Pool       8
Other       16

----
PGA
-----

Aggregate PGA Target (B)      
Current PGA Allocated (KB)      51713
Maximum PGA Allocated (KB)      74885
      (since startup)
Cache Hit Percentage (%)      100

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36988474
Those memory settings look a tad low.

From a sqlplus prompt post the results of:
SQL> show parameter sga
SQL> show parameter pga

Also post how much free memory the server currently has.



0
 

Author Comment

by:crishna1
ID: 36988577
The server in total has 8 GB. I see 5 GB being free , after a reboot couple of hours back.
But now it is showing only 1.6 GB FREE, and about 4.6 GB in Cached. That's not right , is'nt it? The memory seems to be getting cached and not released?

below is from SQl Plus. Thanks once again!
---------

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Product
With the Partitioning, OLAP, Data Mining and Real Application Testi

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1624M
sga_target                           big integer 0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------
pga_aggregate_target                 big integer 0
SQL>
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36988605
Missed 11g.

one more:  show parameter memory

>>The memory seems to be getting cached and not released?

Maybe, maybe not.  You need to find out what process(s) are using the memory.  Then check with the sys admins/developers/??? on what is using it all.

Unless it is Oracle, then it's the DBAs job to figure it out with help from the application folks.
0
 

Author Comment

by:crishna1
ID: 36988662
I looked in the task manager on that server. Oracle and Java are the to memory consumers with 721 and 160 MB respectively, which is not much correct?

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 2704M
memory_target                        big integer 2704M
shared_memory_address                integer     0
SQL>
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36988853
>>which is not much correct?

not sure.  But something seems to be using the memory.

The slowness could be Oracle swapping to disk which is a bad thing to have happen.

>>memory_max_target ...

Oracle can only use a max of 2,704 Meg.  The ORA-04030 means something is trying to use more than is available.

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams139.htm#REFRN10284

Figure out the correct size for your system and increase the memory parameters.
0
 

Author Comment

by:crishna1
ID: 36989820
coming back to the original problem, you think this could be the cause the DB Console is not working? any suggestion on DB Console? shall i drop and create?

also, i just re-booted the machine as the avialable memory (shown in the task manager) , came down to 50 MB from 5000 MB.

i tried to open SQL Developer , and expand the Tables, got the "out of process memory" error, even though 5 GB is avialble at the OS level.

I finally was able to get hold of the actual user of the application had a chat with him. Apparently , the application (which i just came to know ) is also running on the same server. This application itseems , is automated and every hour it goes out and pulls data from some other source and writes it into the database\schema in dicussion here. This process is suppouosed to finish in 30 seconds and now it is taking about 5 minutes, hence the compliant about performance.

i am sharing this just in case if there is anything else you want me to check. There is 8 GB RAM in total available on the server, but FREE drops from 5 GB to less than 50 MB after  couple of hours from restarting the server. You already know how much was allocated to the database. any recommendations at this point?

thank you very much for being patient and walking me thru this.

regards!
0
 

Author Comment

by:crishna1
ID: 36989841
also, once i shut down the application on the server , i was able to open the SQL Developer without "out of process memory" error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36989885
>> from 5 GB to less than 50 MB after  couple of hours from restarting the server.

If this is a custom app either this is normal or it has a pretty bad memory leak.

If this is a new app, maybe the server is under sized.

Not sure why a memory issue would cause an ora-00942.  If you don't need the repository history, drop and recreate the repository.  It is pretty straight forward.
0
 

Author Comment

by:crishna1
ID: 36989917
Thank you!

Yes this is a custom app.

Besides, that the memory now allocated to the database is OK? or would you increase it? i guess the answer is based on whatever the pplication requires?

also, i just came to know that, there were no issues couple of months back, where teh database was Oracle 9.0.7 and the application was on its previous version. They recently installed a newer version of the pplication and installed Oracle 11g and copied the database from 9i to 11g server, since then this slower performance. apparently something was not done right.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36989953
Oracle will take as much memory as you will give it.  You need to monitor to see if giving it more memory will actually benefit the app.

The advisors that come with ADDM and AWR are veery good if you are licensed for them.

Statspack is good and is free.

Once you fix the ora-942 with the repository it sounds like you ave a lot of monitoring/tuning in the immediate future.

11g should blow 9i out of the water in performance!
0
 

Author Comment

by:crishna1
ID: 36993969
ok, thank you very much!
I will drop and create the DB Console. once that is done i guess i can see where the memory leak is, which SQL might be causing it, correct?

i am also thinking of closing this thread and opening another one for memory leak since you already helped me on this and its only fair to award more points thru another question.


regards!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36994066
Since the leak is different from what is asked it should be a new question.  You can ask a 'related' question to this one.

The leak, if it is a leak, can be caused by the app itself and have nothing to do with SQL.
0
 

Author Comment

by:crishna1
ID: 36994150
sure, will open a new question. im not sure how to open a related question, i will look around.

the bottom line is there is performance issues. so i thought i will see what is going on using DB Console  and ran into that DB Console issues, and you know the rest from there.

I am seeking for  advice on how to tackle this whole scenario, somethink like.

1) get Db Console working
2) moniteor the database using DB Console
etc etc...would you please lay out the steps on how you would approach this issues. thanks again!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36994175
If you are licensed for ADDM and AWR, this is your best shot at performance tuning.

I'm not a big GUI user so I'm really not sure what is available in dbConsole.

First you need to make sure it is Oracle actually using the memory.  If it isn't dbConsole can't really show you much.

for related questions, I think it is somewhere in the ask a new question link.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36994192
Found it:  After you close out the question down at the bottom in the comments box you should see:  You can also ask a related question.
0
 

Author Closing Comment

by:crishna1
ID: 36994303
Will be opening a related question.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question