Home » SQL & PL/SQL » SQL & PL/SQL » Which is better Option for Extracting the OBJECT DDL ? (Oracle 10.2.0.1.0 , Linux , SQL Plus)
Which is better Option for Extracting the OBJECT DDL ? [message #366387] Sat, 13 December 2008 04:21 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We are in the process of optimization of our product at schema level based on the Functionality (5-7 Modules). Right now All the objects are in 2 Schemas. Object list Includes TABLES , VIEWS , PROCEDURES , FUNCTIONS ,PACKAGES , TRIGGERS, TYPES , JOBS etc.

We expects to get the list of all objects from all modules ( some objects are used commonly by more than one Module) . Each objects will have just Access privileges across module-schemas and access+execute privileges for Common objects. More over , there will be one APPS schema having the synonyms of modular-schemas (with access+execute privileges )and will be accessed by Application and interface. This is the high level plan.

Now , all the objects are in One specific Schema and we need to seggrgeate the Objects Create scripts based on Modules from this scehma ( As per the list from Module leaders) . For that we need to generate the Separate object scripts . Seperate file for each object , that too modulewise separation. And these object create script SHOULD NOT refer TABLESPACE or Starage parametres .

What is the Best method to generate Module wise , Object Create script with reference to Tablespace and storage parametre ? It is possible to spool the files separately for each objects using SQL*PLUS ? Or Is UTL_FILE is a better way of doing it ? Or Does DBMS_METADATA.GET_DDL is having more advantage over UTL_FILE ? But main disadvantage I see with DBMS_METADATA.GET_DDL is that it creates tables with tablespace , storage parameter and with enabled constraints.

(This genarate script is One Time task).

In long run , we want to automate the Schema and object creation at one short . SO I also invite the openion / suggestion from the experts on the above mentioned high level plan.

Smile
Rajuvan.

[Updated on: Sat, 13 December 2008 04:29]

Report message to a moderator

Re: Which is better Option for Extracting the OBJECT DDL ? [message #368002 is a reply to message #366387] Sat, 13 December 2008 07:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Does DBMS_METADATA.GET_DDL is having more advantage over UTL_FILE
UTL_FILE is used to write to or append an OS file.
DBMS_METADATA is used to extract the DDL.
Functionally, they are different and nothing to compare.
Quote:

Now , all the objects are in One specific Schema and we need to seggrgeate the Objects Create scripts based on Modules from this scehma ( As per the list from Module leaders) . For that we need to generate the Separate object scripts . Seperate file for each object , that too modulewise separation. And these object create script SHOULD NOT refer TABLESPACE or Starage parametres .


All these can be done with DBMS_METADATA (DDL with or without storage parameters).
If there is a defined method to identify the the modules, something like
the tables like PO* are to do with Purchasing or HR* denotes HR related tables, it is just a matter of scripting.
And yes, each file can get into separate file.
You just need to do little more scripting.
>>But main disadvantage I see with DBMS_METADATA.GET_DDL is that it creates tables with tablespace , storage parameter and with enabled constraints.
Not true.
It can be easily disabled.
Please search the forum. Many examples exist.

[Updated on: Sat, 13 December 2008 08:02]

Report message to a moderator

Re: Which is better Option for Extracting the OBJECT DDL ? [message #368452 is a reply to message #366387] Sat, 13 December 2008 07:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can get any fancy you want with more scripting.
chum > @somescript
Enter value for table_name: EMP


  CREATE TABLE "DBADMIN"."EMP"
   (    "EMPNO" NUMBER,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9) NOT NULL ENABLE,
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         PRIMARY KEY ("EMPNO") ENABLE,
         FOREIGN KEY ("DEPTNO")
          REFERENCES "DBADMIN"."DEPT" ("DEPTNO") ENABLE
   )
 ;

chum > get somescript.sql
  1  set long 500000
  2  set linesize 1000
  3  SET HEAD off
  4  set trimspool on
  5  set verify off
  6  set feed off;
  7  column XXXX format a300
  8  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
  9  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
 10  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
 11  sELECT DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME)||';' FROM user_tables D where table_name='&table_name';
 12* set head on;
icon9.gif  Re: Which is better Option for Extracting the OBJECT DDL ? [message #369131 is a reply to message #368002] Sat, 13 December 2008 08:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Forgot to mention that you can disable the constraints too and generate DDL for the rest separately.
But, I would prefer the too keep referencing ones on the least.

chum > @somescript
Enter value for table_name: EMP


  CREATE TABLE "DBADMIN"."EMP"
   (    "EMPNO" NUMBER,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   )
 ;

chum > get somescript
  1  set long 500000
  2  set linesize 1000
  3  SET HEAD off
  4  set trimspool on
  5  set verify off
  6  set feed off;
  7  column XXXX format a300
  8  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
  9  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
 10  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',false);
 11  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
 12  exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
 13  sELECT DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME)||';' FROM user_tables D where table_name='&table_name';
 14* set head on;
Re: Which is better Option for Extracting the OBJECT DDL ? [message #369132 is a reply to message #366387] Sat, 13 December 2008 08:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Thanks Mahesh for Fast and brilliant reply.

Quote:
Does DBMS_METADATA.GET_DDL is having more advantage over UTL_FILE


Actually I planned to use UTL_FILE to generate the file rather than DBMS_METADATA.GET_DDL + Direct spooling . Thats what the confusion came Smile

Quote:
If there is a defined method to identify the the modules, something like
the tables like PO* are to do with Purchasing or HR* denotes HR related tables, it is just a matter of scripting.
And yes, each file can get into separate file


Unfortunately , we dont have any naming convension with respect to modules. Its purely a manual task. By the way, is it possible to spool the tables/objects separately with file name as objectnamme using spool command ? I hope , you mean this by using UTL_FILE.

And finally for the script. Thanks for spending the time for me. Its nice work . I was not sure about the use of dbms_metadata.set_transform_param.

And Even we need the referential constraints to be applied as last step , As it is not easy to get the referential order in big database like ours where there are about 2500 tables are there Smile

Thanks Mahesh Once again.

Smile
Rajuvan



Re: Which is better Option for Extracting the OBJECT DDL ? [message #369633 is a reply to message #369132] Sat, 13 December 2008 08:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> By the way, is it possible to spool the tables/objects separately with file name as objectnamme using spool command ? I hope , you mean this by using UTL_FILE
Again, your statement is little ambiguous.
As you know better, spool and UTL are totally different.
UTL is server side and has more control (write mode/append mode).

All you have to do is use sql to
* generate sql*plus commands like spool followed by direct call to dbms_metadata
or
* write a stored procedure that will use UTL_FILE and dbms_metadata to write to file (file name as input. filename is the object_name from user_tables or whereever or manually coded/called. use same object_name in dbms_metadata).

if the above is repetitive task, I would just write a package/store procedure.

[Updated on: Sat, 13 December 2008 08:37]

Report message to a moderator

Re: Which is better Option for Extracting the OBJECT DDL ? [message #371754 is a reply to message #369132] Sat, 13 December 2008 09:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Here you go. An example using UTL_FILE.
My pl/sql coding skills are barely minimal and there is a lot of room for improvement.
#
# the code
#
oracle@chum#cat getmetadata
sqlplus -s dbadmin/guessmeifyoucan<<EOF
DECLARE
ddlholder varchar2(3000);
BEGIN
DBMS_OUTPUT.ENABLE('10000000');
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
for mag in (    select  object_name,object_type 
                from    user_objects 
                where object_type in ('TABLE') -- add rest objecttypes
                order by object_type desc ,object_name desc )
loop
                if mag.object_type='TABLE' then
                sELECT DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME)||';' into ddlholder FROM user_tables D where table_name=mag.object_name;
                 writemeta(mag.object_name||'.sql',ddlholder,'W');
                end if;
end loop;
end;
/
exit;

oracle@chum#

oracle@chum#cat writemeta.sql
--
-- that will make use of UTL_FILE
--
CREATE OR REPLACE PROCEDURE writemeta
(
fname   IN VARCHAR2 ,
message IN VARCHAR2 ,
mo      IN VARCHAR2
)
IS
log_file  UTL_FILE.FILE_TYPE;         -- file handle for the log file
BEGIN
        log_file := UTL_FILE.FOPEN('ORAMETA',fname,mo); -- ORAMETA is the directory. Must be uppercase.
        UTL_FILE.PUTF(log_file,'%sn',message);
        UTL_FILE.FCLOSE(log_file);
EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('no_data_found');
WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('other stuff');
END;

oracle@chum#
--
-- Execute the script
--
oracle@chum#getmetadata

PL/SQL procedure successfully completed.

--
-- check for the file
---
oracle@chum#ls -lart
total 10
drwxr-x---  62 oracle   dba         1536 Dec 13 09:44 ..
drwxr-xr-x   2 oracle   dba          512 Dec 13 10:20 .
-rw-r--r--   1 oracle   dba          215 Dec 13 10:22 EMP.sql
-rw-r--r--   1 oracle   dba          116 Dec 13 10:22 DEPT.sql
Re: Which is better Option for Extracting the OBJECT DDL ? [message #375805 is a reply to message #366387] Sun, 14 December 2008 22:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Thanx Mahesh Once again for spending your valuable time.

As this is One time Activity I will go for First method ( Genarate SQLPLUS Commenad with DBMS_METADATA + SPOOL )

I will get back to this thread with details once its done .

Smile
Rajuvan.
Re: Which is better Option for Extracting the OBJECT DDL ? [message #375887 is a reply to message #366387] Mon, 15 December 2008 04:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

We are trying sort the issue. Presently facing some issues as follows.

1. GET_DDL fetches the DDL with deafault schema / Source schema. (SCOTT in the following example). Is it possible to ignore / replace with different/target schema name ?(something like MICHEL.EMP) . It seems there is no such option with dbms_metadata.set_transform_param .

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP') FROM DUAL;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) ;
  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE

;
  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;




2. The output from DBMS_METADATA.GET_DDL forms irregular format in spooled file as follows with (SET LONG 500000 ;SET LINESIZE 2000).While normal DBMS_OUTPUT forms regular format .

ie ,
SELECT DBMS_METADATA.GET_DDL('TABLE','XXXX') FROM DUAL ;
forms irregular format like ,

Quote:
ALTER TABLE "SCHEM_NAME"."SCHEDULE_DTLS" ADD CONSTRAINT "SCHDL_
DTLS#BF_AF_FLG$CK" CHECK (BEFORE_AFTER_FLAG_V IN ('B','A') ) ENABLE;
ALTER TABLE "SCHEM_NAME"."SCHEDULE_DTLS" ADD CONSTRAINT "SCHDL_
DTLS#AGNT$CK" CHECK (COLLECTION_AGENT_V IN ('Y','N') ) ENABLE;



So it fetched error while execution of spooled file.

Any suggestions ?


[Updated on: Mon, 15 December 2008 04:32]

Report message to a moderator

Re: Which is better Option for Extracting the OBJECT DDL ? [message #375972 is a reply to message #375887] Mon, 15 December 2008 09:11 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You answered your own question. Smile
There is no default option. But a simple replace would do the job.

chum > SELECT replace(DBMS_METADATA.GET_DDL('TABLE','EMP'),'"DBADMIN".','"SOMEBODY".') FROM DUAL;

REPLACE(DBMS_METADATA.GET_DDL('TABLE','EMP'),'"DBADMIN".','"SOMEBODY".')
--------------------------------------------------------------------------------

  CREATE TABLE "SOMEBODY"."EMP"
   (	"EMPNO" NUMBER,
	"ENAME" VARCHAR2(10),


>> forms irregular format like ,
Did you try the same format settings as in somescript.sql?
I cannot reproduce your case. Spool just works right.
It is important to use a column alias in sql to generate the ddl.
I used "xxx" (seems it was left out in the sample I posted. Sorry about that) as alias and then formatted the output with sql*plus format command.
Check this
http://www.orafaq.com/forum/t/77332/0/
Re: Which is better Option for Extracting the OBJECT DDL ? [message #375978 is a reply to message #366387] Mon, 15 December 2008 09:34 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hats off to the Master !! Lemme take a bow !!! ./fa/1578/0/

I am so silly sometimes . Even forgets the very minute principle ./fa/3518/0/ . I didn't even notice the Column Formatting in SQL*PLUS. ( I remember , I had even adviced some of Orafaq user to do the same earlier . But forgot to do with my case )

Before your reply , I was writing a function to Replace the oldschema with newschema using DBMS_LOB Packages instead of just Replace Confused


Smile
Rajuvan.



[Updated on: Mon, 15 December 2008 09:36]

Report message to a moderator

Previous Topic: ORA-06533: Subscript beyond count
Next Topic: Insert pdf
Goto Forum:
  


Current Time: Mon Dec 05 04:43:47 CST 2016

Total time taken to generate the page: 0.08223 seconds