I am creating a package to generate the DDL of objects in my schema (you parse the object name, and return a clob
with DDL), so I can generate files, and put them straight into SVN.
I am using dbms_metadata.get_ddl
, and it works great for all objects except tables/materialized views.
If I create a table as:
create table stackoverflow
( col_1 varchar2(64)
, col_2 number
, col_3 date);
create index idx_test on stackoverflow(col_1);
alter table stackoverflow add constraint ui_test unique (col_2) using index;
And generate DDL with:
begin
dbms_output.put_line(dbms_metadata.get_ddl( object_type => 'TABLE'
, name => 'STACKOVERFLOW')
);
end;
It gives us:
CREATE TABLE "TEST_SCHEMA"."STACKOVERFLOW"
( "COL_1" VARCHAR2(64),
"COL_2" NUMBER,
"COL_3" DATE,
CONSTRAINT "UI_TEST" UNIQUE ("COL_2")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS_DATA_TS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS_DATA_TS"
To take all relevant indexes we can use:
begin
dbms_output.put_line(dbms_metadata.get_dependent_ddl( object_type => 'INDEX'
, base_object_name => 'STACKOVERFLOW'));
end;
to have:
CREATE INDEX "TEST_SCHEMA"."IDX_TEST" ON "MF"."STACKOVERFLOW" ("COL_1")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS_DATA_TS"
CREATE UNIQUE INDEX "TEST_SCHEMA"."UI_TEST" ON "MF"."STACKOVERFLOW" ("COL_2")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS_DATA_TS"
I want to create a file that contains: create table, constraints, indexes, grants (to have one file with all definition needed) and using dbms_metadata
it looks impossible for me to do.
My problems with the output is:
Double quotation of the name
Schema name inside the DDL make us difficult to compile the same DDL across many schemas. To fix that we need to create some for of regex or adding something like the following can fix that:
dbms_metadata.SET_REMAP_PARAM(dbms_metadata.SESSION_TRANSFORM,'REMAP_SCHEMA','TEST_SCHEMA','');
but you have to add like 8 more lines:
hOpenOrig0 := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(hOpenOrig0,'NAME',p_object_name);
DBMS_METADATA.SET_FILTER(hOpenOrig0,'SCHEMA',get_table.owner);
tr := dbms_metadata.add_transform(hOpenOrig0, 'MODIFY');
hTransDDL := DBMS_METADATA.ADD_TRANSFORM(hOpenOrig0,'DDL');
dbms_metadata.set_remap_param(tr, name => 'REMAP_SCHEMA', old_value => user, new_value => '');
get_package_spec.ddl := DBMS_METADATA.FETCH_CLOB(hOpenOrig0);
DBMS_METADATA.CLOSE(hOpenOrig0);
There is no way of extracting constraints (that use indexes) and indexes at the same moment. You can't concatenate the output because of repeating definition of ui_test
. Yes there is option to remove the constraints from get_ddl
but we are loosing constraint/checks.
How PL/SQL Developer creates the output
-- Create table
create table STACKOVERFLOW
(
col_1 VARCHAR2(64),
col_2 NUMBER,
col_3 DATE
)
tablespace USERS_DATA_TS
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate indexes
create index IDX_TEST on STACKOVERFLOW (COL_1)
tablespace USERS_DATA_TS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table STACKOVERFLOW
add constraint UI_TEST unique (COL_2)
using index
tablespace USERS_DATA_TS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 128K
next 128K
minextents 1
maxextents unlimited
pctincrease 0
);
Does anybody know of a way to create output similar to PL/SQL Developer? I guess they created some XML parser of dbms_metadata.get_xml
) to create a more pretty version (indents, order, all in good place, ready to compile anywhere).
Of course I can play with regexes or user_indexes
but that's not the point.
ps. DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
is a strange definition what's "pretty".
dbms_metadata.get_dll gets oracle object as xml and next transform it by xslt to ddl script.
List of useful table select table_name from all_tables where table_name like 'META%'
.
METASTYLESHEET - maps a stylesheet to it's name
METAXSL$ - maps an XMLTAG to the stylesheet name - link this to the 1st table
For a table oracle uses kutable for xml to ddl for a index oracle uses kuindex ... etc.
By setting parameters you can change behavior of transformation. To find useful parameter check METAXSLPARAM$ table or search it in style sheet documents. EMIT_SCHEMA - i have found in kucommon xslt
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'EMIT_SCHEMA',false); --undocumented remove schema
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_CREATION',false); --undocumented remove segement creation
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',true);
select dbms_metadata.get_ddl( object_type => 'TABLE' , name => 'STACKOVERFLOW') from dual;