Oracle DB Object - Gists

Select all invalid objects:


Recompile all invalid objects:




One of the really annoying things about Oracle PL/SQL is the way it tells you something is wrong, gives you a line number and then leaves it up to you to find the statement that caused the error. Well, this little piece of SQL gives you the exact line in error plus the lines immediately before and after it. (Acknowledgements to Ken Atkins of ARIS Consulting).

Oracle - Query DB Sessions SQL

I had an error today when trying to create a datasource in Weblogic. The error was:

ORA-27101: shared memory realm does not exist



Cause: Unable to locate shared memory realm


Action: Verify that the realm is accessible

The cause of this error was due to the number of database sessions being outnumbered by the number of Weblogic instances. We set up 7 environments, each with 50 maximum connections but only 100 sessions on the DB side.... as you can imagine we smashed the limit.

You can check the number of sessions using:

 SELECT name, value  
  
 FROM v$parameter 
  
 WHERE name = 'sessions'  

And you can check how many are currently in use using:

 SELECT COUNT(*)  
  
 FROM v$session