One of the coolest parts of my job is that I actually get to use the software that I help manage. The end-user perspective is hard to imagine sometimes, but when you ARE the end-user things become clear as day. This morning I spent quite a bit of time in DataPump and the SQL Developer Cart.
I needed to copy a schema from databaseA to databaseB. Everything’s relative, and this goes double when it comes to the concept of ‘size.’ I thought that my schema was ‘big’, so I defaulted to Data Pump for servicing this particular task. It’s multi-threaded, all server-side, and it just WORKS. Is it perfect? No, but it comes really close.
If you’re going to copy a schema between two databases, you need to consider the following:
- tablespace mapping
- available space
Mapping tablespaces is easy IF you know your tablespaces
The first time I ran through the SQL Developer wizard for the IMPORT, I skipped right past the tablespace ‘Remapping’ step. Let’s take a quick look at my scenario:
So a very nice touch here – the tablespaces listed in the drop-=down are being parsed out of the DMP files that were previously scanned in step 2 of the wizard. So, instead of being asked to consider every tablespace, we’re just looking at the tablespaces that are being used for the objects being imported.
What would really help me at this point is to see the tablespaces in all their glory in both the source and destination databases. Guess what, SQL Developer can do that!
Connections to both databases is the obvious step 1. And there’s not really a ‘trick’ here, but it does help to know how to do it
- Connect to database A and database B
- Open the DBA Panel
- Open ‘Tablespaces’ panel under ‘Storage’ for A
- Pin it!
- Open ‘Tablespaecs’ panel for B
Ok, we’re close, but not quite there. This will give us something that looks like this –
Once you click the ‘New Editor Tab Group’, you can drag the second tablespaces panel to the bottom, giving you something like this:
Now I can peruse my tablespaces to check the space reqs for moving objects
Guess what happens if your tablespace(s) fill up during a datapump job?
It will just sit there.
Now, that’s not entirely true.
So I queried V$SESSION_LONGOPS –
This is one of my favorite ‘database tricks.’ Oracle will tell me what’s going on, in this case, my data pump process for the import.
Now @neilkod laughed at me, probably because of this –
‘IMPORT_JOB_SQLDEV_681: IMPORT : 65 out of 226 MB done’
226 MB? That’s MEGA bytes, not mega-huge-terra-exa bytes. Something must be wrong! So I went to see what WAITS were hanging around on that session, using the ‘Monitor Sessions’ report under the ‘Tools’ menu.
Only one wait event – ‘wait for unread message on broadcast channel‘
Hmm, what’s that?
Crap! Out of space!
Thankfully Data Pump is forgiving enough (unlike the older and deprecated EXP) that it by default allows you to RESUME the process once more space is added.
After that, I was back to normal.
Except I had forgotten to export my views and procedures. So I fired up the CART, and 2 minutes later, I was done!