tag:blogger.com,1999:blog-55051831335616699272024-03-14T00:30:03.952+01:00Øyvind Isene's BlogStuff related to the Oracle database (and some more), but not so much about me.
This is my old blog. See my next at https://enesi.no/.Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comBlogger107125tag:blogger.com,1999:blog-5505183133561669927.post-5188281389832309672020-12-05T19:12:00.000+01:002020-12-05T19:12:32.159+01:0010 GOTO enesi.noI haven't updated this blog for a long time. If you find anything useful here, you should probably upgrade your database. I'll leave it as is, but these blog posts will be copied to my new site and new blog posts appear there as well. Check out https://enesi.no/ if you have nothing better to do.Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-77580376784952664992017-03-26T14:23:00.000+02:002017-03-26T14:39:22.641+02:00Too Easy for a Post: Install Oracle 12.2 with Vagrant If you like the simplicity of Docker, but would like to continue with Virtualbox, then Vagrant is for you. After the conference in Dublin, OUG Ireland, I got this challenge:
@OyvindIsene agreed to be my guinea pig for my @vagrantup files. That's what you get when going to a conference! ;) https://t.co/pJDisr4Mhp
— Gerald Venzl (@GeraldVenzl) March 24, 2017
Not much of a challenge, of Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-20756597168504003882017-03-05T14:53:00.000+01:002017-03-05T14:53:21.202+01:00Adding Examples to a Docker Container with Oracle database 12.2Oracle Database 12.2 became available for download last week. This weekend I've been playing with Docker and created a container with it. The whole process is so easy, thanks to the work by Gerald Venzl at Oracle. You'll find all the information you need in his blog post. Though the post is for version 12.1.0.2, the dockerfiles have already been updated for 12.2.0.1, and you can download it from Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-80685352524081817682017-02-20T11:00:00.000+01:002017-02-20T11:00:32.572+01:00Splitting a String into ElementsEvery 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short0, I figured I should make a reminder here. Of course, you'll find this on Stackoverflow as well.
There is this function in APEX, which is usually1 available for you in Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-22200869722348418942017-02-19T14:45:00.001+01:002017-02-19T14:45:07.431+01:00ORA-1722 and Regular ExpressionsYesterday I was importing some data I downloaded. When creating a new table I tried to convert two columns with latitude and longitude, stored as VARCHAR2, to numbers (in order to use them in the SDO_GEOMETRY constructor). The create table as select (CTAS) statement failed with ORA-01722. Problem is it does not tell you which line or what value is the offender. You may turn on some tracing with Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-49389270960900656832016-11-30T19:15:00.000+01:002016-11-30T19:15:05.185+01:00Delete Cascade with Recursive PL/SQLIf you need to delete all rows in a table that has parent keys for other tables' foreign keys, and the foreign keys constraints have not been defined with "on delete cascade", you can do a recursive delete with the following simple procedure.
This is typically something you will do only in a test or development database, and not in production. As always, it is a good thing to understand this Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-31775703102589351872016-10-23T16:07:00.000+02:002016-10-23T16:07:48.879+02:00Displaying Spatial Data in SQL DeveloperThere are many ways to display spatial data, but when exploring a new data set in SQL Developer I think the built in Map View is practical. For this demo I used a list of nuclear power stations in the world, found via http://freegisdata.rtwilson.com/. The Google Fusion Table can be exported to CSV format, and it includes the longitude and latitude which makes it easy to convert to Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-58237780096808468152016-10-16T17:38:00.000+02:002016-10-16T17:38:14.651+02:00Importing GPX files to Oracle database, part 2The previous post showed how to import a file in GPX-format into a table that uses the XMLType. This post shows how to import the waypoints from the GPS logger into a table with the native SDO_GEOMETRY type.
First create a table:
create table gps_log (
log_time timestamp with time zone,
waypoint sdo_geometry,
src varchar2(20));
The datatype for log_time is chosen like shown Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-3461360699242685052016-10-11T08:00:00.000+02:002016-10-11T08:00:19.768+02:00OTN Appreciation Day: A database that is reliable
As suggested by Tim Hall, aka Oracle-Base.com, this post is about my favourite feature in the Oracle world. My favourite product is the good old database, and my favourite feature is simply the fact that I don't lose data!
Never once have I lost data due to software errors, and after many hard landings the database always manages to come back online after wading through the Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comOslo, Norge59.9138688 10.75224539999999359.658993300000006 10.106798399999992 60.1687443 11.397692399999993tag:blogger.com,1999:blog-5505183133561669927.post-4857449289129669872016-10-02T18:09:00.001+02:002016-10-16T13:10:19.073+02:00Importing GPX files to Oracle database, part 1Update 2016-10-16: Found and error in procedure, see below.
In preparation for a presentation on Oracle Spatial for DBAs I decided to import some GPS-data. I have an app called GPSLogger on my Android mobile that logs GPS data to a file. The format for this is GPS Exchange Format (GPX). Since I spent some time understanding the XML side of this I decided to blog about it.
Note, Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-73468746662201662412016-09-30T21:11:00.000+02:002016-09-30T21:11:54.729+02:00Oracle Open World 2016 Afterthoughts
So I went to Oracle Open World again. For me this year it was partly vacation, networking, and inspirations for work. I went on my own, to a few sessions, spent a lot of time on Oak Table World, hanging out in the OTN Lounge, socialising, and at meet-ups.
On my way home I put down some of the takeaways:
There were a lot of clouds, but it didn’t rain.
There are a some speakers who manage Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-54522820488544767132016-07-21T20:59:00.000+02:002016-07-21T20:59:31.223+02:0024 Basic Tips on Using SQL Developer
I have been using SQL Developer since it required some goodwill to work. Some of the motivation came from an annoyance with Toad sessions in the database. I remember that I back then googled "how to block Toad users from the database".
Anyway, these days most developers around me are using SQL Developer. But quite a few are learning only the least to get their Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-53014153665451825232016-05-23T08:30:00.000+02:002016-05-23T08:30:03.685+02:00Open Session Error when connecting to Hive from SQL DeveloperThis blog post from Oracle explains well how to connect to Hive from SQL Developer. In short, you need to download the JDBC driver from Cloudera and make sure you use the correct version, version 4 and not 4.1 Three zip files are included, use the one that starts with Cloudera_HiveJDBC4_2.*.
Anyway, after adding the jar files to SQL Developer, and restarting it, the connection failed Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-86881598762061137912016-04-19T21:39:00.000+02:002016-04-20T10:55:06.107+02:00Undo old mistakes with online table redefinitionThere are times when you as a DBA wished you were involved before someone designed a data model. After release to production correcting mistakes is usually complicated and with risks.
Update 2016-04-20: Fixed errors in code.
There is an exception to this. Online table redefinition lets you change the structure of a table while everything is up and running. I have used it lately to partition Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-15133432542427778572016-02-17T20:51:00.000+01:002016-02-17T20:51:13.904+01:00SQL Developer and SSH for DBAsIn version 4.0.3 SSH was introduced in SQL Developer; here's a post from Jeff Smith about it. Now this feature has become even easier to use with SSH configuration made independent of the database connections.
DBAs typically have a jump server they connect to in order to login further to the database servers. With the new implementation (I'm using version 4.1.3 at the moment) you Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-41685103994692181962015-12-29T18:46:00.000+01:002015-12-29T18:46:10.999+01:00JRuby in SQL Developer Data Modeler on MacIn SQL Developer Data Modeler you have Libraries under the meny Tools -> Design Rules and Transformations. But when you select that one you may have seen this error message:
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 Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-56663935419871814102015-11-23T19:41:00.000+01:002015-11-23T19:41:23.029+01:00ORA-01105This post is meant to be found by people googling the error message ORA-01105 and ORA-19808 on RAC. The following error messages may be seen after starting an instance:
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 aOyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-49333307543656171852015-11-08T18:32:00.000+01:002015-11-08T18:32:04.445+01:00My take away from OOW15A big conference like Oracle Open World is an excellent opportunity to learn more. But with all the books, blog posts, and on-demand learning on Internet, I really did not have to go to OOW this year to keep myself busy learning. Of course, I learned a lot, but for me, the biggest reward is the inspiration and ideas for future projects, as well as meeting friends in the community including OracleOyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-54901929978800633952015-10-30T18:19:00.001+01:002015-10-30T18:29:18.564+01:00OUGN16 - Call for paperThe 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:
http://c4p.ougn.no/
Deadline is November 15.
See you on the ship (which is a boat with other boats on it, according to Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-85012234051785188792015-09-28T19:05:00.000+02:002015-09-28T19:43:56.050+02:00Who is your hero in the Oracle community?I have my mental list of people in the Oracle community I look up to. They are people who help through social networks, emails, presentations, books, blog posts, or in a friendly conversation. Now, Oracle has recognised that there are many such individuals in the community, people that know a lot and spend much time sharing it with others.
In fact they have asked us to vote on our developer Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-55972364998407897342015-09-27T15:24:00.000+02:002015-09-27T15:24:00.919+02:00Function to let user B see all tables of user A<!--?xml version="1.0" encoding="UTF-8" standalone="no"?-->
In case you do not want to grant a user access to data dictionary tables like DBA_TABLES, but will let user B see the list of all tables belonging to user A, you can work around it with a pipelined function in schema A:
create type str_set as table of varchar2(30);
/
create or replace function a_tables return str_set Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-64072537332855769602015-07-27T13:34:00.000+02:002015-07-27T13:34:32.378+02:00Gather statistics on spatial index
When you run dbms_stats.gather_table_stats on tables with spatial indexes or dbms_stats.gather_index_stats directly on the spatial index the routine may return without an error even if no statistics gathering took place. You can verify this by looking at the LAST_ANALYZED column in DBA_INDEXES:
select owner,index_name,last_analyzed
from dba_indexes
where index_type='DOMAIN';
Another Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-67520876674074689002015-07-05T13:15:00.000+02:002015-07-05T13:15:11.988+02:00Orphan Processes in the databaseWhen you need to find the OS-process on the database server for an oracle session (dedicated server) you can join v$session with v$process:
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 Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-86010123979837690962015-05-16T17:39:00.000+02:002015-05-16T17:39:07.773+02:00ORA-00942When I had my first Oracle DBA course some years before the start of this millennium I made a special mental note about a very common error message, ORA-00942, "table or view does not exist".
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 remarkableOyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.comtag:blogger.com,1999:blog-5505183133561669927.post-86723202547503000482015-03-04T20:35:00.000+01:002015-03-04T20:35:00.602+01:00Search and history in SQL DeveloperShould be obvious, but I just discovered that it is much easier to change preferences if you search for it in the search field instead of wandering around in the Preferences GUI looking for the place where you can set it:
And it is pretty fast, try to look for the place where you choose the OCI client, just write OCI in the field. And similarly, if you think you wrote some smart code a few Oyvind Isenehttp://www.blogger.com/profile/17143586629854631281noreply@blogger.com