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?
Nothing.
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!
6 Comments
When I open the tablespaces, I just get “Tablespaces” as header for both of them. So I fastly get confused about which DB is used in what window. How did you manage to get @DB-name for the tab title?
I found out I can change the title of a worksheet, but how to change the name of a result tab?
I took that screenshot many years ago, it appears to have changed a bit…but with that tab open, you’ll see in the sqldev title bar the name of your connection, which hopefully gives you an idea of what database is being shown.
Ah, I see it, thx. That did not catch my eyes in the first place.
A color for each tab that belongs to a certain connection would be great (and of course the option to use colors for tabs or not and which color for which connection).
But thanks for your rapid reply 🙂
The colors feature was added in v4 – specify the color scheme in your connection properties. Each document/editor will have a border in that color. But not the tab itself.
Yes, that tab coloring is still missing.
And if you choose blue for example, you can no longer read the name of the connection when you click on it because choosing a color turns off the automatic change to white when the dark background color is used for showing what you have selected.
it’s not missing – we just decided not to do that. we were worried it would make SQLDev look like a christmas tree