There are a number of ways to transfer data between Oracle Databases, one of which is to use the PL/SQL Datapump API – DBMS_DATAPUMP. If you wish to avail yourself of this utility but find the syntax a bit fiddly, you always have the option of getting SQLDeveloper to do (most of) it for you. What we’re talking about here is how to persuade the SQLDeveloper DB module to :
Create and execute a custom Datapump export job
do most of the work creating an import of a subset of the exported data
I would begin this post by saying something pithy about the latest Brexit crisis gripping the nation. However, watching any news or current affairs program at the moment leaves me feeling rather like this :
Fortunately, I had a new version of SQLDeveloper to install on my Ubuntu 16.04 laptop to take my mind off things. After installing the software, I forgot – as I almost always do – how to add a new item to the Unity Launcher, so I thought I’d write down the steps this time.
It’s been rather a trying week.
Wales beat England in the Rugby on Saturday and every Welsh person alive has been observing the ancient tradition of rubbing English noses in it ever since.
My claim to Welsh heritage by marriage have been given short-shrift by Deb, whose accent has become rather more pronounced ever since the final whistle.
All in all, the onslaught of Welsh chauvinism has left me feeling rather like this :
Until things blow over, I’ve decided to spend more time in the shed. Fortunately, the Wifi signal is still pretty good so I’ve decided to use the free time by installing APEX 18.2 into an Oracle 18c RDBMS. As I’ve got time on my hands ( celebrations are unlikely to fizzle out for a couple of months yet), I’ve decided to follow Oracle’s recommendation and configure it to run on ORDS 18.4.
Specifically, what I’ll be covering here is :
installing APEX 18c
installing ORDS 18c
configuring APEX to run on ORDS
configuring ORDS to run on HTTPS with self-signed SSL certificates
I was going to begin with some extended Brexit metaphor to illustrate the chaos and confusion that can ensue when you first encounter Oracle’s CREATE SCHEMA command.
Fortunately, the Dutch Government saved me the trouble :
Much as I’d like to believe that the Cookie Monster has finally overcome his Sesame Street type casting, I can’t help noticing that the Brexit Monster never seems to in the same room as this guy…
In Oracle, the term “schema” is used interchangeably with the term “user”. Creating a user in Oracle automatically creates a schema of the same name belonging to that user.
The process is so seamless that it’s almost impossible to tell where one begins and the other ends.
You may therefore be somewhat confused the first time you encounter Oracle’s CREATE SCHEMA command…
What with Larry the Downing Street Cat and Palmerston, his counterpart at the Foreign Office, Teddy suspects he knows the real reason for the Country’s current travails.
Here he is, doing his best Boris Johnson impression :
“No wonder Brexit’s a Cat-astrophe !”
In an attempt to distract myself from the prospect of the country being ruined by this feline consipracy, I’ve been playing with my new Raspberry Pi Model 3 B-spec.
At some point, I’m going to want to connect remotely to the Desktop on the Pi. What follows is how I can do this using VNC… Continue reading →
“Proxy Users !” exclaimed Debbie.
“I say, that’s rather harsh don’t you think ?” came the rather startled reply from her boss.
Debbie sighed. They were in the midst of a discussion on the subject of how best to deploy database changes to multiple schemas.
“I meant”, she replied with iron patience, “that we could set up a proxy user to connect as each application owner in turn. That way, we wouldn’t have to grant those CREATE ANY privileges that get auditors so worried”.
“Oh, I see”, said Mike, who didn’t.
Not for the first time, Debbie wondered whether she had been lumbered with a less competent man as her boss simply in order to imbue this post with a semblance of social realism.
“I think”, she said, “that it’s time to move on to the techie bit.”
Debbie is right, as usual…
In order to make a change in Oracle ( or any database for that matter), you need at some point to connect to the database and run some SQL.
This is relatively straightforward if you are using the schema that is – or will be – the owner of the objects you are creating or changing.
However, this may not be possible if the account is identified externally or – in more recent releases – it’s a schema only account.
So, what is the best way to setup and use an account to make such changes in other schemas ?
At this point you may well ask yourself that what – apart from gratuitous puppy pics and cheesy-snack-based puns – is the difference between that post and this.
Well, if you’re a long-time user of 11gXE and you’re looking to upgrade then you will find 18cXE a rather different proposition.
The introduction of Multitenant databases aside, 18cXE differs greatly from it’s predecessor in terms of it’s functional scope.
Wheras 11gXE was – broadly speaking – functionally equivalent to Oracle Standard Edition, the approach for 18cXE has been to shoe-horn in as many Enterprise Edition features as possible.
No doubt, this will leave you anxious to play with the new version. However, there are some “home comforts” that were present in the old version that you’ll need to configure yourself this time around.
What I’m going to go through is :
Installing 18cXE on a Red Hat compatible distro (CentOS7)
Connecting to the database and exploring the containers
Checking the TNS Listener
Manual and Automatic Startup and Shutdown of the database and listener
Setting and persisting the Oracle environment variables
Accessing Enterprise Manager Express
Installing the HR demo application in a Pluggable Database (PDB)
Configuring the firewall to allow remote access to Oracle
The steps documented here have been performed on a vanilla installation of CentOS7. As such, they should work pretty much unaltered for other Red Hat based distros based on or similar to Red Hat Enterprise Linux (RHEL) version 7.