ThatJeffSmith

SQL Developer Trick: See Tablespaces from Two Databases (and DataPump Troubleshooting)

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:

  1. tablespace mapping
  2. 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:

Objects in Source Tablespace Should Go to Tablespace ___ in Destination

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 :)

  1. Connect to database A and database B
  2. Open the DBA Panel
  3. Open ‘Tablespaces’ panel under ‘Storage’ for A
  4. Pin it!
  5. Open ‘Tablespaecs’ panel for B

Ok, we’re close, but not quite there. This will give us something that looks like this –

Tablespaces for both databases, but I can only see one group at a time.

Once you click the ‘New Editor Tab Group’, you can drag the second tablespaces panel to the bottom, giving you something like this:

Two Databses, Tablespaces Over-Under View

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

How much work has been done, how much time is left?!?

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!