To: Kristen Cameron 

On Tue, 25 Jan 2000, Kristen Cameron wrote:

> I am copying a database from Unix to NT.  I plan to import the data,
> but first need to set up the tablespaces and users.  I plan to write
> a script that will generate the SQL to create the tablespaces and
> users by reading the information from the DBA_ tables.  Has anyone
> done this already?  I don't want to reinvent the wheel, particularly
> since I am new to PL/SQL and it will be a long and arduous process
> to write this script!
> 
> Just to be clear, I want to read information from DBA_TABLESPACES
> and DBA_DATA_FILES and use the values to generate a CREATE
> TABLESPACE command for each tablespace in the database.  Then, using
> information from DBA_USERS and DBA_ROLE_PRIVS, I want to generate a
> CREATE USER command for each user.
> 
> Any ideas?  Thanks in advance.
> 

Kristen,

There are a few scripts that I have seen to do this. Two paths may be
chosen:

1) Use a set of scripts such as you said. They are in Kevin Loney and
Rachel Carmichael's "SQL and PL/SQL Annotated Archives". Or you can browse
around my links page (www.arikaplan.com/links.html) if you can't wait to
get the books

2) export the database without the data: "exp rows=n full=y userid=xxx/pwd"
   then use the SHOW option with import to create a file that contains all
   commands: "imp full=y show=y file=x.sql userid=xxx/pwd"

   Note that these commands will not be word-wrapped and will contain
   quotes in improper places. There is a script in perl at
   www.oracle.com/oramag/code/cod12289.html that will fix this for you. If
   your file is small enough you can do it manually with a text editor.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 345+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->


Back to Ari Kaplan's Home Page