Copy DB User to Supp 1

[TimBiesiek]

Hi all!

I'm new to the DBA position in our company (Am actually backup DBA, but our DBA is away on leave), and am very green as to how to do stuff in Oracle... (Am taking an Oracle course next month).

I want to know how to copy a user (GMS) from our Test DB to the Support DB...

Any help appreciated!

 

[SantaMufasa]

Tim,

First, welcome to our "Happy Oracle Family" !

I want to know how to copy a user (GMS) from our Test DB to the Support DB.

I presume that means that you want to re-create the GMS user and all of its objects from TEST to SUPP, right? If so, then:


1) As a DBA on SUPP, create a new user, password, and appropriate rights and privileges for GMS. Typical code to achive this might be:

create user GMS
    identified by <GMS's password>
    default tablespace <Tablespace where you want GMS to store its toys>
    temporary tablespace <Name of your Temp tablespace>
    quota <unlimited or some specific limit> on <Default tablespace you assigned>
/
grant connect, resource to GMS
/
grant CREATE VIEW to GMS -- if SUPP is Oracle 10g
/

2) Export all of GMS's objects from TEST database. There are many different parameters that you can use for an Oracle "exp" invocation. Here is a typical invocation that I might use...at the o/s prompt of the machine upon which TEST resides, invoke Oracle's export utility:

<o/s prompt> exp buffer=15000000 compress=n grants=n feedback=1000 consistent=y file=GMS.dump log=GMS_Exp.log statistics=none owner=GMS userid=GMS/GMS@TEST

With the parameter "feedback=1000", you will see a dot (".") every time the "exp" utility outputs 1000 rows from a table. (Note: both the "exp" and the "imp" command parameters should appear on a single command line to avoid issues of continuation characters.)

3) Import GMS's objects into the SUPP database. Use some sort of o/s (COPY) command to make your "exp" dump file ("GMS.dump") accessible to the SUPP database instance, then issue some variant of the following "imp" command at the target machine's o/s prompt:

imp buffer=15000000 grants=n feedback=1000 fromuser=GMS touser=GMS file=GMS.dump log=GMS_Imp.log  userid=GMS/GMS@SUPP

(Note: You will have less risk of trouble if the GMS user on the SUPP instance has tablespace quota to tablespaces with the same names from which the source objects came. If this issue causes you trouble or anxiety, just post your questions here or in a new thread.)

Let us know how all of this goes for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[TimBiesiek]

Hi Dave,

Thanks!

So the first script to create the user will be:

create user GMS
    identified by XXXXXX
    default tablespace STOSAPPS
    temporary tablespace TEMP
    quota UNLIMITED on STOSAPPS
/
grant connect, resource to GMS

Does this look right?

The Supp DB isn't 10g...

 

[TimBiesiek]

Or do I need quotes around any of the values in that?

 

[SantaMufasa]

Tim,

Except for the missing ";" behind the GRANT (or a "/" at the beginning of the next line), your code is solid.

Keep us posted of your progress.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[TimBiesiek]

Ok, user created perfect!

Will keep you posted! Cheers!

 

[TimBiesiek]

Hi Dave,

Just trying to import now, but during the import process it has stalled on a table... the table only has 37 records in, so it's not a huge table....

Any ideas?

 

[TimBiesiek]

Well, thanks for the ideas! It worked for most of the tables, except stalled with about 10 to go. Just had to create them manually, and import the data to them. Then had to do the procedures, views, functions and triggers, but they were no worries...

Thanks a heap! Have a star!