Friday, September 18, 2009

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.

4 comments:

Todd Sheetz said...

Funny thing about the remap_tablespace... I just had the opportunity to use that for the first time this week. Small world!

oraclenerd said...

heh...a couple of people emailed me privately on that thread and said the same exact thing. just one of those things you don't use often enough to remember. or you were smart enough to script it so it "just happens" all the time.

Crisatunity said...

I think I'm going to copy and paste that Little Chet overview and post as a recommendation to Big Chet's LinkedIn profile.

Joel Garry said...

They're teaching modeling in 1st grade now? Yikes!