I’m on holiday, so it’s a lot easier to tick things off my giant list at the moment. 🙂
Someone asked me about the DBMS_DATAPUMP package, which is a PL/SQL API for Data Pump. I knew I had written about it, but it turned out I only had one little example in my main Data Pump article here. I put it on my list to expand it a little with some other common examples. I finally got round to this here.
It’s by no means an exhaustive list of what the DBMS_DATAPUMP package can do, but if someone new is coming to it, it puts a bit more meat on the bones compared to what I had before.
One of the reasons that moved that up the list was I was thinking about looking at the ORDS Database API endpoints related to Data Pump, which resulted in this.
That was a rather odd experience for a few of reasons.
- There were some issues with authentication. I expected it to work with both the default administrator and an ORDS enabled schema, but it only worked with the latter. That was confusing the hell out of me for a time. I’m told it will work with both in the future…
- There are two ways to use Data Pump. There is a REST endpoint “/database/datapump/jobs/” that can perform all the actions, and there are two RPC-style endpoints “/database/datapump/export” and “/database/datapump/import”. I decided that RPC is not for me and chose to ignore it. I’m a lot happier with the REST endpoint.
- The API has extremely limited functionality at the moment.
I guess I was expecting this to feel like a wrapper over the DBMS_DATAPUMP package, but it is not like that at all. In this version it’s an API that covers a few basic tasks and completely locks you out of everything else. I can’t imagine many DBAs being able to use it, but it might be ideal for some developers if you are OK with giving them DBA credentials to the database. I wrote some of my thoughts/concerns at the bottom of the article here.
Despite my reservations, this is effectively version 1.0 of this functionality. I would suggest people try it out and feedback their requirements to the ORDS folks, so it can be improved.
On a more general note, it’s clear the ORDS Database API needs a rethink in terms of access management. It seems to be a bit of an all or nothing at the moment. I think it’s safe to say that as the APIs expand, there will be a range of people needing to use them, each with very different security constraints. I might want someone to be able to perform table or schema-level exports/imports, but I will want to control what they can do, and by giving them the access to do this, it doesn’t mean I want them to be able to run DBCA commands to create new instances as well. I think you get my point. It would seem like the access to the APIs need to respond to the underlying roles and privileges for the user, not using a separate ORDS parameter mechanism. Just my thought…
Cheers
Tim…