How to generate script from database 1

[gulshan]

i have a complete database, but script used for generating that database is not available with me.(for particular user).<br>So please lem'me know how to generate the script from database. <br>One way which I know is using reverse engineering in ERwin and then generate the sql script from that. But I don't want to use any ERwin (third party) tool.<br>If you know any methos then lem'me know at <A HREF="mailto:Gulshan.wadhwa@engineer.com">Gulshan.wadhwa@engineer.com</A><br><A HREF="mailto:gulshanwadhwa@engineer.com">gulshanwadhwa@engineer.com</A><br><br>Thanx,

 

[karluk]

You can generate table and index create statements from an Oracle export by importing using the indexfile parameter.&nbsp;&nbsp;That might be enough for your needs.

 

[MikeLacey]

I didn't think about this until I saw Karl's post but exp can do the whole thing, tables, indexes, constraints, tablespaces, clusters. I think the parameter is rows=no.<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[Raji]

What you can do is, Export the Full Database and then you can use SHOW and INDEXFILE option to generate a script file for the Tables and Indexes in all the users.&nbsp;&nbsp;In case you want the script of a specified user, then specify the username in the options while IMPort.&nbsp;&nbsp;With ROWS=No, you will NOT get the script to INSERT the records in the tables.

 

[amorgulis]

How can I also re-create database triggers at the same time as they apply to the table being replicated/copied?

 

[MikeLacey]

exp does triggers as well. <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[patr1q]

I am trying to do a similar thing, recreate a database from a full export of an existing one. However, I seem to need the users setup in the new one prior to importing? This is a problem as the roles, and system & object rights of each user are very longwinded.

Is there a way of either setting the users up correctly from the export file, or generating a script from the old database to do this prior to importing?

Thanks for your help

 

[sybaseguru]

patr1q.

As colleagues pointed out above &quot;export database&quot; will provide you with all you need. The <database>.dmp file produced is a binary file. If you are working on UNIX you can easily extract the user creation and grant section to ensure that the import will work. You can try the following option that I normally use. In the example below test.dmp is the full export for a database called &quot;test&quot;. I use UNIX utility egrep with option '-a' which will treat the binary dump file as a text file

cat test.dmp | egrep -a -i 'create user|grant ' > abc.sql

cat abc.sql

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMPTS1"
CREATE USER "IBMS" IDENTIFIED BY VALUES 'B56BF24F357A5318' DEFAULT TABLESPACE "BSS_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "BSS" IDENTIFIED BY VALUES '8905450AC4CF40B7' DEFAULT TABLESPACE "BSS_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "ODYSSEY" IDENTIFIED BY VALUES '5BF8317FFD7F22D4' DEFAULT TABLESPACE "ODYSSEY_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "RATINGS" IDENTIFIED BY VALUES 'A058D77389DD3631' DEFAULT TABLESPACE "RATINGS_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "WORKFLOW" IDENTIFIED BY VALUES 'E9C4850247F1DFC4' DEFAULT TABLESPACE "WORKFLOW_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "USER1" IDENTIFIED BY VALUES 'BBE7786A584F9103' DEFAULT TABLESPACE "MAG_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "USER2" IDENTIFIED BY VALUES '1718E5DBB8F89784' DEFAULT TABLESPACE "MAG_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "USER3" IDENTIFIED BY VALUES '94152F9F5B35B103' DEFAULT TABLESPACE "MAG_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "USER4" IDENTIFIED BY VALUES '2907B1BFA9DA5091' DEFAULT TABLESPACE "MAG_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "USER5" IDENTIFIED BY VALUES '6E97FCEA92BAA4CB' DEFAULT TABLESPACE "MAG_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "SSR9" IDENTIFIED BY VALUES '273D996ED66AD4EC' DEFAULT TABLESPACE "BSS_DATA" TEMPORARY TABLESPACE "TEMP"
CREATE USER "TOAD" IDENTIFIED BY VALUES '666554011D812613' DEFAULT TABLESPACE "TOOLS" TEMPORARY TABLESPACE "TEMPTS1"
CREATE USER "DUMMY" IDENTIFIED BY VALUES '933E4BFA48C26E04' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMPTS1"
GRANT CREATE USER TO "SYS"
GRANT CREATE USER TO "WKSYS"
GRANT UNLIMITED TABLESPACE TO "SYSTEM" WITH ADMIN OPTION
GRANT UNLIMITED TABLESPACE TO "OUTLN"
GRANT EXECUTE ANY PROCEDURE TO "OUTLN"
GRANT SELECT ANY TABLE TO "SYS" WITH ADMIN OPTION
GRANT CREATE SESSION TO "RECOVERY_CATALOG_OWNER"
GRANT ALTER SESSION TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE TABLE TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE CLUSTER TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE SYNONYM TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE VIEW TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE SEQUENCE TO "RECOVERY_CATALOG_OWNER"
GRANT CREATE DATABASE LINK TO "RECOVERY_CATALOG_OWNER"

Hope this helps

 

[louisas]

sybaseguru,

Useful little trick. Thanks