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

bhd282 (benjamin.i.doyle (AT) gmail (DOT) com) wrote:
: 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

What is wrong with dbms_metadata.get_ddl itself?

It has various options to get the dll to create many types of objects, not
just tables. I find it useful to skip the attributes that aren't part of
the logic of a table.

execute
DBMS_METADATA.SET_TRANSFORM_PARAM
(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUT ES',false);

Comments embedded.

On Jan 8, 5:54*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
DATABASE is not a valid object_type for DBMS_METADATA.GET_DDL.

It won't, however, return the DDL to create a database.


David Fitzjarrell

fitzjarrell (AT) cox (DOT) net (oratune (AT) msn (DOT) com) wrote:
: Comments embedded.

: On Jan 8, 5:54=A0pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > bhd282 (benjamin.i.do... (AT) gmail (DOT) com) wrote:
: >
: > : 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. =A0 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
: >
: > What is wrong with dbms_metadata.get_ddl itself?

: DATABASE is not a valid object_type for DBMS_METADATA.GET_DDL.

My bad, I skimmed over the DATABASE keyword and in my mind read it as an
unspecific description, like saying "ddl of things in the database".

That certainly answers the question of what is wrong with dbms_metadata.

Sorry, can't help.

bhd282 schrieb:
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 \
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 \
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