Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Wednesday, August 19, 2009

Get table and index DDL using DBMS_METADATA.GET_DDL

Prior to Oracle9i, getting table and index DDL was a time-consuming and tricky process. You could run the export utility with ROWS=NO, but the output was hard to re-use because of quoted strings. The only other option was to write complex dictionary scripts that might not work on complex objects such as IOT and nested tables.

Punching DDL from the dictionary is very useful when you are migrating a system to a new platform and you want to pre-create the objects in a new tablespace so that you can import with IGNORE=Y.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

capture index ddl before you drop.


create table indxddl
(
tbl_name varchar(32),
indx_name varchar(32),
indxddl varchar(4000)
);

create table test31(id numeric(16), name varchar2(128));


create index test31indx1 on test31(id);


insert into indxddl(tbl_name,indx_name,indxddl) values ('test31','test31indx1','create index test31indx1 on test31(id)');
INSERT INTO INDXDDL(TBL_NAME,INDX_NAME,INDXDDL) VALUES ('TEST31','TEST31INDX1','CREATE INDEX TEST31INDX1 ON TEST31(ID)');

set long 1000
select * from indxddl;

delete from indxddl;

create index test31indx2 on test31(name);

select upper(index_name) from user_indexes where upper(table_name)='TEST31'
minus
select upper(indx_name) from indxddl where upper(tbl_name)='TEST31';

SELECT DBMS_METADATA.GET_DDL('INDEX','TEST31INDX2') FROM USER_INDEXES u;

CREATE OR REPLACE procedure indexddl_proc( p_table_name varchar2)
as
cnt number;
indexddl varchar(4000);
begin
select count(*)
into cnt
from
(select upper(index_name) from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) from INDXDDL where tbl_name = 'TEST31');
if cnt>0 then
for i in
(select index_name as a from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) as a from indxddl where upper(tbl_name)= 'TEST31')
loop
indexddl:=DBMS_METADATA.GET_DDL('INDEX',i.a);
--SELECT DBMS_METADATA.GET_DDL('INDEX',i.a) into indexddl FROM USER_INDEXES u;
insert into indxddl(tbl_name,indx_name,indxddl) values (upper(p_table_name),upper(i.a),upper(indexddl));
end loop;
end if;

for x in (select table_name,index_name
from indxddl
where table_name = ltrim(rtrim(p_table_name,''''), '''' ) ) loop
execute immediate 'drop index '||x.index_name;
end loop;
execute immediate 'truncate table '||ltrim(rtrim(p_table_name,''''), '''' );
exception
when others then
raise;
end indexddl_proc;
/

execute indexddl_proc('TEST31');

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;