Tuesday, December 29, 2015
Notice the second line with Jruby lib is red. This is because the JRuby is not installed (or cannot be found) on your machine.
I found the solution to this in Heli's book on SQL Developer Data Modeler, but thought I could add the easy way to solve this on your Mac. You need to download an extension to your JDK installation. A jar-file can be downloaded from http://jruby.org/download (There are two main versions, I decided to go for the the 1.X one). Look for the jar-file, currently I downloaded jruby-complete-1.7.23.jar.
You need to figure out where to put this jar file. In SQL Developer Data Modeler, check your java.library.path setting by going to Help -> About. Then click on the Properties tab. There are a lot of them, so in stead of scrolling down, start writing in the search field "java.l" as shown:
In my case, one of the directories in the search path is /Users/oisene/Library/Java/Extensions. It did not exist on my system, so I created it and copied the downloaded Jruby jar file into this directory. After a restart of SQL Developer Data Modeler you can go to Tools -> Design Rules and Transformations -> Libraries to verify that you no have the error message and the line with Jruby lib is black.
Monday, November 23, 2015
ORA-01105: mount is incompatible with mounts by other instances ORA-19808: recovery destination parameter mismatch
When this happened to me I ran this command on both instances:
show parameter recovery
it showed there was a mismatch for the parameter db_recovery_file_dest_size. Probably I forgot to add "sid=*" to the alter system command one time when I increased the limit. Since one instance was up I could update the parameter for both instances with:
alter system set db_recovery_file_dest_size=700G sid='*';
The instance that was started must be shut down and started again.
This means that this parameter has to be the same across the instances during startup, but Oracle allows you to set them different with alter system.
As shown here you can correct parameters in spfile for an instance that won't start from a running instance. This is a favourite feature, and whenever I have to change memory parameters that requires restart of the instance, I keep one instance running so I can correct the settings from there if needed.
Sunday, November 8, 2015
Regarding inspiration I think that the attention given to SQL and PL/SQL is awesome. This is much more than the code itself. It is about the ability to do more and more complex analysis for each new version of the database. You can do it with minimal amount of code, code that executes very close to the data.
It is also about Application Express that is a quick and responsive web interface for your data; web pages generated in the database with the ability to manipulate and report on the data. I am a DBA that keeps telling other DBAs that if they can choose APEX for a project they should do so. Anything else will be more complex with more layers (even with Oracle REST Data Service as a proxy). It is built on technology the DBA understands and can control.
In the era of Big Data and Cloud computing I think the most fascinating stuff is data mining. It has been around for years, but thanks to an renewed focus, data mining algorithms are cool again. Oracle Advanced Analytics is more than complex algorithms. You need to explore the data, and the more you know SQL in general, and analytical functions in particular, the better you are prepared for such a project.
I'm getting the impression that beside the Oracle database, preferably running on Exadata, I need nothing more than perhaps a couple of tiny applications servers in front, and network, of course.
Friday, October 30, 2015
The biggest conference for Oracle users in the Nordics is not far away. Be there as a speaker and attendee. Sure you have a war story or another experience worth sharing. Last year OUGN received 250 abstracts, please, do it again!
Follow this link to submit your abstracts:
Deadline is November 15.
See you on the ship (which is a boat with other boats on it, according to Millsap)!
Monday, September 28, 2015
In fact they have asked us to vote on our developer heroes, and they will award them during a big celebration at Oracle Open World. Sure you have met one SQL guru, or a PL/SQL programmer, or someone really concerned about database design? Since I am a DBA I think that we should support our database developers since they are doing their part to make sure that the database, and database application that we will support later is as good as possible.
It starts with a good design, then hopefully as much as possible of the hard work will be done in SQL or PL/SQL close to the data, and finally presented beautifully in a web application done in APEX, possibly delivered through Oracle REST Data Services to make it extra secure.
What are the alternatives? A bunch of coders that starts out without any planning or previous knowledge of the database (because it is just a persistent storage they don't want to relate to). They also want to do as much as possible in their own app far away, so they happily offload half the database to do so, and blame it on the network if it does not perform. The end result is a a mess that does not perform or scale, but you get to manage it from release until eternity.
Here is what you need to give something back before it is too late:
Sunday, September 27, 2015
create type str_set as table of varchar2(30); / create or replace function a_tables return str_set pipelined is l_str varchar2(30); begin for l_str in (select table_name from user_tables) loop pipe row(l_str.table_name); end loop; return; end; / grant execute on a_tables to B;
Then user B can see the list of A's table with:
select * from table(a.a_tables);
Trying to solve this with a view in schema A that selects on USER_TABLES does not work, but prove me wrong, please.
Monday, July 27, 2015
select owner,index_name,last_analyzed from dba_indexes where index_type='DOMAIN';
select owner,table_name from dba_tables where table_name like 'MDXT_%';
select sdo_index_owner, index_name, sdo_index_table from mdsys.all_sdo_index_info;
Sunday, July 5, 2015
select p.spid from v$session s join v$process p on(s.paddr=p.addr) where s.sid=42;
But if you kill a session with 'alter system kill session ...' the link between these views are broken because the value in v$session.addr changes. In order to look for these orphan processes run this query:
select spid, program from v$process where program!= 'PSEUDO' and addr not in (select paddr from v$session) and addr not in (select paddr from v$bgprocess) and addr not in (select paddr from v$shared_server);
You may check with OS tools like ps on Linux to see that these are indeed dead processes or with strace to see what they are doing and eventually kill them.
Saturday, May 16, 2015
In most programming languages there are a similar message for a common situation; when you refer to an object that does not exist, quite often because you wrote the name wrong. What I found a bit remarkable when I heard about it first time was that ORA-942 may mean two things, either that the table or view does indeed not exist, or you do not have access to it. An example of the latter is when some user has created a table in her schema, and you try to query it, but you have not been granted a privilege to do so. Instead of you receiving some error message like "access denied", Oracle responds with the same error message; "table or view does not exist", even if it does exist. It may have added "for you" to make it more correct. I think there is a good reason why this is so, and I think it has to do with optimisation.
If you enable sql trace, you can see Oracle runs queries against obj$ and objauth$.
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
The first checks for the object's existence, the second looks for privileges granted on the object. By the way, the results from these queries may be cached so you won't always see these in the trace file. Exactly what is going on behind the scene here is unknown to me, and I also think that the implementation of this has changed during the years with different versions of the database. But if you look at the second query, that fact that no row is returned may come from either the fact that no privileges have been given, or that the table does not exist. So, instead of going an extra round in the dictionary to see if the table exists since no privileges exist on it, Oracle simply returns the same error message "table or view does not exist".
It is probably a good security practise to not inform unprivileged users about the existence of objects they have no access to, but I thought it was a smart optimisation by Oracle back then, because I was used to see error messages like "access denied". The way they have done this saves extra work. By know I think I have lost the attention of everyone except my best oracle nerd friends, so I thought I could sneak in a confession here at the end. Some years ago I started to practise ORA-942 in my own life. You know in those situations where people suspect you know some secret and start asking away about stuff you are not allowed to pass on? Instead of me having the burden of creating excuses or explaining why I can't tell, I simply answer "I don't know". That saves me a lot of extra work and I don't consider it a worse practice than mining other people's brain.
Have a nice weekend!
Wednesday, March 4, 2015
Monday, March 2, 2015
Now, there may be a reason why this is not well documented and better known.
Saturday, January 31, 2015
- Have a fire drill once in a while to test your routines and skills.
- Get some clear policies in place like, how much data are you allowed to lose, and how much time (downtime) do you have to recover your database.
- One course covers one subject, but a conference covers many, during one intensive conference you can get inputs and learning in many fields.
- Problems you may have at work can be discussed by others at the conference. Chances are that what you are going through is interesting and valuable input to others and you are likely not the first one to go through this.
- Make friends and discover that you are not alone. Getting friends and contacts outside your pool is possibly the best part of it. Meeting people IRL and being in touch on many channels is priceless to me.
- Many conferences are run by the community, meaning that a demand for a profit margin is lower at conferences than at a course delivered by a company. It translates to more learning for the money than at most other places.