SqlDbx Forum

garbuya

Member

Bug in generating scripts

Version 4.10 Pro.
Oracle 11gV1
I need to move the whole schema to another database
So, I'm generating scripts for all objects.
Options "Use DBMS_METADATA" and "SQL*Plus Compatible scripting" are checked
The goal is to generate all scripts and then execute it all at once.
In my case it is over 2000 objects.

Here are some problems:
1. Scripts for code (functions, packages, procedures, types, triggers) do not have GRANT statements.
2. Scripts for tables have consistent errors - redundant DDL and missing ";" on some DDL.
3. Some scripts for VIEW do not have GRANT statements.

This is an example for one table:

-------- Good ----------
DROP TABLE BATCHUSR.JOBS_EXECUTION_LOGS CASCADE CONSTRAINTS;

-------- Good ----------
CREATE TABLE "BATCHUSR"."JOBS_EXECUTION_LOGS"
("JOB_NAME" VARCHAR2(100) NOT NULL ENABLE,
  "LINE_NBR" NUMBER NOT NULL ENABLE,
  "TXT" VARCHAR2(4000),
CONSTRAINT "JOBS_EXECUTION_LOGS_PK" PRIMARY KEY ("JOB_NAME", "LINE_NBR")
  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)
  TABLESPACE "BTCH_IDX"  ENABLE
) 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)
  TABLESPACE "BTCH_DATA";

-------- Bad. 1) No ";"
--------        2) this constraint was just created above, so DDL fails
--------
ALTER TABLE "BATCHUSR"."JOBS_EXECUTION_LOGS" ADD CONSTRAINT "JOBS_EXECUTION_LOGS_PK" PRIMARY KEY ("JOB_NAME", "LINE_NBR")
  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)
  TABLESPACE "BTCH_IDX"  ENABLE

-------- Bad. 1) No ";"
--------         2) this constraint was just created above, so why modify?
--------
ALTER TABLE "BATCHUSR"."JOBS_EXECUTION_LOGS" MODIFY ("JOB_NAME" NOT NULL ENABLE)

-------- Good, ";" is here. Bad. this constraint was just created above, so why modify? --------
ALTER TABLE "BATCHUSR"."JOBS_EXECUTION_LOGS" MODIFY ("LINE_NBR" NOT NULL ENABLE);

-------- Bad. This index was just created above, so DDL fails ----------
  CREATE UNIQUE INDEX "BATCHUSR"."JOBS_EXECUTION_LOGS_PK" ON "BATCHUSR"."JOBS_EXECUTION_LOGS" ("JOB_NAME", "LINE_NBR")
  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)
  TABLESPACE "BTCH_IDX";

   -------- Bad. No ";"  ----------
GRANT DELETE ON "BATCHUSR"."JOBS_EXECUTION_LOGS" TO "ONCALUSR"
GRANT INSERT ON "BATCHUSR"."JOBS_EXECUTION_LOGS" TO "ONCALUSR"
GRANT SELECT ON "BATCHUSR"."JOBS_EXECUTION_LOGS" TO "ONCALUSR"

  -------- Good , ";" is here ----------
  GRANT UPDATE ON "BATCHUSR"."JOBS_EXECUTION_LOGS" TO "ONCALUSR";

Last edited by garbuya (2015-08-06 15:36:13)

garbuya

Member

Re: Bug in generating scripts

Any comments on this post?

sqldbxhelp

Administrator

Re: Bug in generating scripts

This is the issue with how Oracle DBMS_METADATA package works.
In a next release we will address some of issues you mention above.