Home » RDBMS Server » Server Administration » DBMS_METADATA
DBMS_METADATA [message #199035] Thu, 19 October 2006 16:27 Go to next message
mrmarath
Messages: 23
Registered: July 2005
Junior Member
Hi there,
How to get DDL of all objects in a schema?
When I Query like this
"select object_type, count(*) from user_objects group by object_type;"

My output is,

FUNCTION 4
INDEX 219
JAVA CLASS 6
JAVA SOURCE 5
JOB 2
LOB 1
PACKAGE 1
PACKAGE BODY 2
PROCEDURE 31
SEQUENCE 5
SYNONYM 132
TABLE 133
TRIGGER 1
VIEW 6

14 rows selected.
(I hope I got the all the objects from that schema!).
How to get DDL for each object in that schema using DBMS_METADATA? Or any other methods to get the DDL of all objects in a schema? My requirment is to to create a new schema using this DDL definitions.
Please give some solution to this,
Thanks in advance
Rajesh
Re: DBMS_METADATA [message #199048 is a reply to message #199035] Thu, 19 October 2006 20:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>How to get DDL of all objects in a schema?
If you want to use dbms_metadata, try the method discussed here. You need to do some customization.
http://www.orafaq.com/forum/m/151577/42800/?srch=dbms_metadata#msg_151577

>> Or any other methods to get the DDL of all objects in a schema? My requirment is to to create a new schema using this DDL definitions
Easiest method is to export (with rows=n. No data is exported.) and import(with fromuser/touser optios. you will be importing only the structure) in target schema.

Re: DBMS_METADATA [message #199140 is a reply to message #199048] Fri, 20 October 2006 09:06 Go to previous messageGo to next message
mrmarath
Messages: 23
Registered: July 2005
Junior Member
Thanks Mahesh! I'll let you know the feedback

Rajesh
Re: DBMS_METADATA [message #199195 is a reply to message #199035] Sat, 21 October 2006 00:08 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

As per "Mahesh" suggestion try to import "show=y" it will not import ur dump but it show complete structure of ur dump file.
SQL> host imp scott/tiger@orcl show=y log=test.log full=y

Import: Release 10.1.0.2.0 - Production on Sat Oct 21 09:07:03 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
 "CREATE TABLE "TEST" ("SAL" NUMBER(8, 2))  PCTFREE 10 PCTUSED 40 INITRANS 1 "
 "MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "TEST"

Import terminated successfully without warnings.

SQL>


SQL> host imp scott/tiger@orcl show=y log=test.log full=y indexfile=test.sql

Import: Release 10.1.0.2.0 - Production on Sat Oct 21 10:00:53 2006

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . skipping table "TEST"

Import terminated successfully without warnings.

SQL> edit test.sql

[Updated on: Sat, 21 October 2006 01:04]

Report message to a moderator

Previous Topic: Trouble in starding oracleservicedawn
Next Topic: Capacity Planning
Goto Forum:
  


Current Time: Sat Dec 10 13:01:15 CST 2016

Total time taken to generate the page: 0.06263 seconds