Re: Recreating the CREATE DATABASE DDLs

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 10 Jan 2009 23:18:33 +0100
Message-ID: <49691EB9.90602_at_gmail.com>

bhd282 schrieb:

> Is it possible to regenerate database DDLs as one can do with
> dbms_metadata.get_ddl? or with some scripts that can pull the
> information from the dictionaries?
> I have the SQL saved of the original CREATE DATABASE and ALTER
> DATABASE statements when using DBCA.   I'd like to be able to see the
> exact definition of the database without resorting to saving every
> ALTER DATABASE statement that I execute.
> (I've found a useful SQL statement online, that will generate the
> CREATE TABLESPACE statements.)
> -b

You may be interested in database templates. For example, assumed i have a database named ORA102.world with user sys identified by oracle and i want get a set of scripts ( which cover all the installed options as well) from this database for future reuse. Then the following command:

dbca -silent \
> -createTemplateFromDB \
> -sourceDB ora102 \
> -sysDBAUserName sys \
> -sysDBAPassword oracle \
> -templateName ora102.dbt \
> -maintainFileLocations true

Creating a template from the database

10% complete
20% complete
30% complete
40% complete
50% complete
60% complete
70% complete
80% complete
90% complete

100% complete
Look at the log file
"/opt/oracle/product/10gR2/cfgtoollogs/dbca/silent2.log" for further details.

creates for me a template ( which is actually an xml file under location $ORACLE_HOME/assistants/dbca/templates/ora102.dbt - respectively your template name - containing all the needed information). Such template can be already used for database creation, but if desired, one can create a set of scripts from this template:

dbca -silent \
> -generateScripts \
> -templateName ora102.dbt \
> -gdbName ora102.world \
> -scriptDest /home/oracle/scripts
Database creation script generation

1% complete
4% complete
5% complete
8% complete
9% complete
10% complete
12% complete
14% complete
15% complete
17% complete
18% complete
20% complete
27% complete
33% complete
39% complete
41% complete
42% complete
45% complete
46% complete
48% complete
50% complete
51% complete
55% complete
58% complete
59% complete
70% complete
75% complete
83% complete
85% complete
87% complete

91% complete
95% complete
100% complete
Look at the log file "/home/oracle/scripts/ora102.log" for further details.

After that, looking in the directory /home/oracle/scripts (or any specified directory) one can recognize a familiar bunch of files:

ls -1 /home/oracle/scripts/
context.sql
CreateDBCatalog.sql
CreateDBFiles.sql
CreateDB.sql
cwmlite.sql
emRepository.sql
init.ora
interMedia.sql
JServer.sql
odm.sql

ora102.log
ora102.sh
ora102.sql

ordinst.sql
postDBCreation.sql
spatial.sql
xdb_protocol.sql

Best regards

Maxim Received on Sat Jan 10 2009 - 23:18:33 CET