Oracle

There 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 SDO_GEOMETRY. After importing the file with SQL Developer into a table called NUCLEAR, I did this to add native Spatial data: ...

The 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 because the type used in the XML-file (xsd:dateTime) maps to this, which makes importing the time data much easier. (Have a look at the previous post to see this mapping.) ...

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 redo logs and make sure the data remains consistent. ...

Update 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, there are two versions of the GPX schema, 1.0 and 1.1. My app uses the version 1.0, if you try to import a GPX file, have a look in it to verify which schema definition it uses (Look for xsi:schemaLocation in the head of the GPX file). ...

There 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 large tables. During the process I also added compression, and moved some of the partitions to another tablespace that I have set read-only, in order to reduce backup time as well. LOBs in one table were moved to SecureFiles with deduplication. ...

In 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 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. ...

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: http://c4p.ougn.no/ Deadline is November 15. See you on the ship (which is a boat with other boats on it, according to Millsap)!

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 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. ...

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 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: ...

When 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 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. ...