remapping multiple tablespaces in Data Pump

Occasionally I find myself migrating Oracle databases between environments using Data Pump.

Enterprise applications tend to use multiple tablespaces for logical separation (and historical reasons). However, this isn’t possible when the target database is Autonomous Database (ADB) hosted on Oracle Cloud Infrastructure (OCI).

ADB is a managed service so DBA’s can’t create tablespaces and are limited to ‘DATA’.

However, Oracle 19c Data Pump supports wildcards for the REMAP_TABLESPACE parameter which allows multiple tablespaces from the existing application to be mapped to the ADB DATA tablespace.

Football Web Pages APEX application

In the last article, we created a simple APEX application fetching data about English football from the Football Web Pages site which provides an authenticated REST API.

However, all I really want to do is to quickly look at Kingstonian’s forthcoming fixtures for the next month. Fortunately, there is an FWP API providing that information.

Endpoint: https://football-web-pages1.p.rapidapi.com/fixtures-results.json

• Matches - The current list of matches for a competition/team
• The following parameters may be set:
  • comp - The ID of the competition (note: one of “comp” or “team” is required)
  • team - The ID of the team (note: one of “comp” or “team” is required)

We already have created an APEX report listing all the available Competitions (including the numeric ID values) so it would be useful to have a similar report listing all the Teams.

a simple APEX application using REST API

Introduction

The last article provided a quick introduction to REST APIs. Now we will use a simple REST API to develop an APEX application using a real world example.

Football Web Pages

I enjoy watching football (soccer). My local team are Kingstonian FC, a non-league team in South West London. Kingstonian play in the seventh tier of English football. Kingstonian’s players are semi-professional so the players hold down jobs and train and play part-time.

Masking sensitive fields in APEX

Data masking with APEX

Background

A common customer requirement is to mask sensitive or personally identifiable data from APEX reports.

Oracle has a ‘Data Masking and Subsetting’ product that performs this task.

However, for smaller APEX projects, the full blown data masking product might be overkill as it needs familiarity with the product and configuration. This may be time consuming and expensive.

However, we are able to use the PL/SQL package DBMS_REDACT to achieve the same result.

Agile development with Oracle APEX

Tim Hall recently made a wonderful suggestion that the Oracle community remember the much missed Joel Kallman on 11 October 2021.

My contribution doesn’t demonstrate APEX technical wizardry. Instead it’s a short story from a real-life customer project implemented using APEX. Just to avoid any potential law suits, this post isn’t about Agile development either - more how APEX can be used to quickly respond to changing customer requirements.

One particular post from Joel stuck with me about his attitude to customer service which can be encapsulated in a single line:

where are they now ?

People often write in and ask ‘Is Oracle blogging dead ?’

Well, I thought it might be interesting to revisit the list of Oracle blogs I posted way back in 2007 and see what they’re up to.

• Life After Coffee - dead (last post May 2011). The pressure of the No. 1 slot proved too much.
• The Tom Kyte Blog - frequency much reduced but Tom’s a busy man.
• Rittman Mead - now a multi-user, shiny, corporate blog.
• Oracle Scratchpad - Jonathan Lewis is still going strong.
• Eddie Awad …so is Eddie.
• Doug Burns - Our favourite Scotsman is still active and contemplating a migration away from Serendipity.
• Kevin Closson - occasional poster.
• David Aldridge - as above (when something annoys him).
• Tim Hall - still travelling the world, presenting, reading books and watching weird films.
• Jeff Hunter - resting (inactive since May 2012).
• Peter Scott - alive and well blogging under the Rittman Mead umbrella.
• Andrew Clarke - sporadic flurries of activity from Tooting.
• Chris Foot - not known at this address.
• William Robertson - still manages to exclaim ‘WTF?!’ once a year.
• Howard Rogers - still blogging from Down Under.
• Robert Vollman - resting (since August 2012).
• Andy Campbell - hijacked by spammers. Either that or he really is pimping satellite TV systems and payday loans.
• Moans Norgaard - pining for the fjords (since July 2010).
• Laurent Schneider - actively blogging about Oracle.
• Lisa Dobson - went to the trouble of getting her own domain and then neglected it (since Sept 2011). No longer a ’newbie'.
• Jeff Moss - pimping vacations in Florida.
• Beth - Data Geek Gal has been quiet on data quality (since Oct 2012).
• Steve Karam - the alchemist is busy mixing up strange concoctions involving Hadoop, Hue, Oozie and occasionally Oracle.
• Eric Emrick - nothing since March 2009.
• Alex Gorbachev - busy growing Pythian Corp.
• Robert Baillie - blogging about Agile, Extreme and project management after a 3 year lull.
• Gary Myers - moved house (still in Oz) but helpfully remembered the redirect.
• Nuno Souto - still blogging from Sydney.
• Daniel Fink - The Optimal DBA blog has been dropped with constraints cascaded - which is suboptimal.
• Ed Whalen - wrote a popular book in 2011, got rich on the proceeds and retired in the Caribbean.

Sherlock Burns and Dr. Hall investigate

‘Burns - I guess this means I can book the hotel for four nights and cancel the flights to OpenWorld then ?'

It was 10:30 am on a dull, grey overcast Monday morning in Manchester. Sherlock Burns and Doctor Timothy Hall had just emerged from a fractious, tense kick-off meeting at Tiger Telecom. Sherlock Burns and Dr. Timothy Hall had been called by the IT director at short notice to investigate a sudden and marked degradation in the performance of the production database

why can't people understand date arithmetic ?

    AND TO_NUMBER(TO_CHAR(appt_start_dt + 9/24, 'YYYYMMDDHH24MISS')) <=
    TO_NUMBER(TO_CHAR((TO_DATE(:b1, 'DD/MM/YYYY HH24:MI:SS')),
                                     'YYYYMMDDHH24MISS'))`

Please can a clever Oracle person explain, in English, the precise semantics of this WHERE clause snippet in the comments below.

I realise this construct and variants thereof have probably been discussed ad infinitum on Oracle WTF but I just don’t get it.

I don’t know whether this SQL was written by a human being or a third party ETL tool but it doesn’t matter. Currently, all ETL tools are written by humans in any case.

in praise of Doug Burns

My longest standing and all-time favourite Oracle blogger is Doug Burns of Edinburgh, near Scotland.

In addition to his sharp, analytical Oracle skills, helpful attitude and easy going manner, Doug does a great deal of unsung, unpaid work for charity (although, obviously he doesn’t like to talk about it) and is a fully paid up member of Amnesty International.

Doug is also passionate about the the causes of data privacy, security issues, the freedom of information and continues to fight an unceasing battle for the rights of the individual against the evil spectre of Google Corporation.

onwards and upwards

After over five varied and enjoyable years working for Siebel (and then Oracle) in Expert Services, I am changing jobs. However, I will continue to work for Oracle as part of the group responsible for ‘Social CRM’.

Although this was a internal transfer, my interview process was quite unusual. Instead of being forced to massage my CV into Microsoft Word format and send an email attachment to a faceless recruitment agency, I was able to publish my CV using Google Docs and the whole interview process was conducted by telephone.