Re: Copy of schema only

Paulos wrote:
> I have just started a new job which was supposed to involve tuning some SQL
> & PL/SQL code on an Orarcle 9i database; however it seems that I am also
> supposed to be the DBA for this database (with the exception of backups
> which are apparently done by nameless persons at the site where the DBs are
> housed). Had I known this I would not have taken the job...I am NOT an
> Oracle DBA and never claimed to be; I find it difficult to understand how
> they could have got the match between the job spec and my CV so wrong.

I've found many jobs are quite different than advertised; often, this is a good thing. Take a positive attitude, consider it a compliment and resume enhancement, perhaps they see something in you. Or maybe they don't know from DBA's, that doesn't have to hurt you. Some places consider advanced code tuning a DBA task. Most tuning _is_ code tuning.

>
> Thats the background.
>
> The prod DB is Oracle 9i running on HP-UX (version unknown) and the dev DB
> is also Oracle 9i but running on Linux (version also unknown). The Prod DB
> is in a remote datacentre to which I have access via a VERY slow secure
> link. The dev DB is on a box in the office where I work.

uname -a is very informative on both unix.

>
> I have been asked to copy the entire Prod schema (without any data) to dev
> and I am unsure of the best method of doing it. Worse yet, my Unix knowledge
> is "20 years ago at college never used it since".

If you've been around 20 years, you must've used dos. Think of unix shell languages as super-duper dos that actually works. The big conceptual difference of the "unix way" is to have small programs that are good at certain things, that can be strung together to do complex things. Most unix at the newbie level are similar enough you can learn the basics from things like
http://www.oracle.com/technology/pub/articles/tech_dba.html#linux . hp.com has all the docs online for hp-ux, too. There's likely some transitional help online from whatever you've been using for 20 years to unix, google away.

Probably the best method for an empty schema propagation is to use the exp utility with rows=n. So you'd:

exp prod/prodspassword file=prodfile log=prodfile compress=n rows=n

Which will give you prodfile.dmp (the schema in a binary format) and prodfile.log (what-all it did). If you use FTP to move export file, be sure and use binary mode.

Then do basically the same thing with imp. See the utilities manual for details, they are creaky old utilities. Since your prod and dev boxes aren't the same, you probably want to use the imp parameter to get the commands into a text file because you probably will want to change some stuff. A big gotcha to watch out for is not to import into the system tablespace, that would be very bad and is possible if you rely on too many defaults.

There are also GUI utilities like OEM (comes with Oracle), toad, etc. which can help. There are also many scripts floating about that can be run, including unsupported ones from oracle http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=131704.1 (click on dba.admin, Script: To create a Database Schema Summary (Note:1019462.6), metalink access required, you should be sure and have metalink access).

Some other stuff you might want to know: http://www.dbaoracle.net/readme-cdos.htm

>
> I would be really grateful for any suggestions anyone can make.
>
> TIA
>
> P

jg

--
@home.com is bogus.
"I've never met a human being who would want to read 17,000 pages of
documentation, and if there was, I'd kill him to get him out of the
gene pool." - Joseph Costello