| Using DBMS_METADATA API's [message #151533] |
Thu, 15 December 2005 07:05  |
fogun1
Messages: 1
Registered: December 2005
|
Junior Member |
|
|
Hi,
I wonder if anyone can help me out with the code to extract ddl of an object from a database belonging to another schema.
I have followed the mddemo supplied by Oracle - but I am having difficulty changing the user witihn the code. I would like to extract the table definition as well as the indexes, triggers , contraints and grants on the table in one go.
I know how to use the dbms_metadata.get_XXX functions.
|
|
|
|
| Re: Using DBMS_METADATA API's [message #151577 is a reply to message #151533] |
Thu, 15 December 2005 13:42  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
What exactly is the problem?
all you need to do is
add
CREATE OR REPLACE PACKAGE payroll_demo
authid current_user -- add this.
Please download the attached files.
Installdemo.sql will install the thingies.
The only changes i did apart from above are,
1.)
Login as current user (SCOTT).
switch to mddemo user ( You can ignore this. Just install the package as any dba user).
Connect again as scott.
2.) Removed the filters to display all tables.
3.) Add the schema you want extract ddl from.
get_ddl.sql will display the ddl it.
a partial output of get_ddl
In this example i logged in as SYSTEM to extract SCOTT's DDL.
system@9i > @get_ddl
Call completed.
CREATE TABLE "SCOTT"."DDL"
( "DDL" CLOB,
"SEQNO" NUMBER
) ;
CREATE UNIQUE INDEX "SCOTT"."SYS_IL0000007228C00001$$" ON "SCOTT"."DDL" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PARALLEL (DEGREE 0 INSTANCES 0) ;
[Updated on: Thu, 15 December 2005 13:43] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
