Random Things: Volume #8 Things I've Learned Recently

ALTER SESSION SET SMTP_OUT_SERVER = 'mailserver';

Our discussion centered around our (in)ability to send out large inline HTML emails externally (reports). SMTP_OUT_SERVER was set, but there was code setting the mail server (UTL_SMTP) which was just a tad confusing. I tried, and failed, 3 times to change the mail server only to, finally, realize that it was set at the database level.

Looking at the Reference manual for 10.2, I couldn't find mention of that parameter being modifiable either by ALTER SESSION or ALTER SYSTEM. Then one of my more DBA oriented colleagues looked into V$PARAMETER and found that it was modifiable at both SYSTEM and SESSION levels.

Ran the test and it worked. Nice.

IMPDP and REMAP_TABLESPACE
I asked for and received a database export (object only) from production so that I could test my upcoming migration script to make sure I had everything in order. The intent was to install it on my local sandbox and run it until I got it right (yeah for FLASHBACK DATABASE!).

Using imp initially, it failed. Here was my first opportunity to use Data Pump.

Ran the basic commands:

c:\temp>impdp cjustice/testing@testing dumpfile=prod.dmp full=y

I received back about 65,000 errors. Scanning through the output, I realized that I didn't have the same tablespaces as production.

I wonder if there is a way to remap the tablespace to USERS?

Documentation to the rescue. Utilities --> Data Pump Import --> REMAP_TABLESPACE

So I tried that, and it failed again.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39046: Metadata remap REMAP_TABLESPACE has already been specified.

So then I tried with commas separating the tablespaces, that didn't work. Then spaces. Nope. Finally, I turned to the oracle-l mailing list. I've been participating there for the last couple of months. Much of it is more DBA centric, but I do get to participate on occasion. There are very smart and capable people on there...plus a willingness to help.

So as not to embarass myself, I tried to ask the question in an intelligent way.

Within seconds of sending it, I began to get responses. Then a phone call. Then an IM.

As to my problem, I had reversed the order of the tablespaces. All I needed to do was switch them and I was off. Got everything loaded only to realize I didn't have all the necessary objects to compile.

Closer To Home
I talk about Kate a lot here and once in awhile Little Chet. He doesn't get nearly the ink that Kate does though.

On Wednesday Kris had a parent-teacher conference and he received glowing reviews from his teacher.



If you are interested, you can read the whole thing here.

Have a great weekend.

Labels: LC, oradb, random

  ¶ 9/18/2009 06:50:00 AM