This discussion is archived
8 Replies Latest reply: Nov 30, 2012 12:06 AM by dz_r RSS

Quick DDL for table - grants generated 3 times

dz_r Newbie
Currently Being Moderated
SQL Developer version 3.2.20.09

The table is created as follows:
create table test_quick_ddl
(
  column1 varchar2(10 char),
  column2 varchar2(10 char)
);

grant select, update, insert, delete on test_quick_ddl to scott;
And "quick DDL" generated for that table looks like:
--------------------------------------------------------
--  DDL for Table TEST_QUICK_DDL
--------------------------------------------------------

  CREATE TABLE "KTRM"."TEST_QUICK_DDL" 
   (     "COLUMN1" VARCHAR2(10 CHAR), 
     "COLUMN2" VARCHAR2(10 CHAR)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  TABLESPACE "KTRM" ;
  GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
  • 1. Re: Quick DDL for table - grants generated 3 times
    rp0428 Guru
    Currently Being Moderated
    >
    And "quick DDL" generated for that table looks like:

    --------------------------------------------------------
    -- DDL for Table TEST_QUICK_DDL
    --------------------------------------------------------

    CREATE TABLE "KTRM"."TEST_QUICK_DDL"
    (     "COLUMN1" VARCHAR2(10 CHAR),
         "COLUMN2" VARCHAR2(10 CHAR)
    ) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
    TABLESPACE "KTRM" ;
    GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
    GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
    GRANT DELETE, INSERT, SELECT, UPDATE ON "KTRM"."TEST_QUICK_DDL" TO "SCOTT";
    >
    I don't get any grants when I do that with that version on Win XP and 11g on Linux. I right click the table in the nav tree and select quick-ddl. I did the grant to another user since the table is in my SCOTT schema
    --------------------------------------------------------
    --  DDL for Table TEST_QUICK_DDL
    --------------------------------------------------------
    
      CREATE TABLE "SCOTT"."TEST_QUICK_DDL" 
       (     "COLUMN1" VARCHAR2(10 CHAR), 
         "COLUMN2" VARCHAR2(10 CHAR)
       ) SEGMENT CREATION DEFERRED 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      TABLESPACE "USERS" ;
  • 2. Re: Quick DDL for table - grants generated 3 times
    dz_r Newbie
    Currently Being Moderated
    - Workstation OS: Windows XP Professional - Service Pack 3
    - "Quick DDL" obtained in the same way: right click the table in the nav tree and select quick-ddl
    Destination - clipboard or worksheet.

    I've created table "TEST_QUICK_DDL" on schema SCOTT and granted it to user "KTRM", and now - depending on DB version - I am receiving the following results:

    - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
    --------------------------------------------------------
    --  DDL for Table TEST_QUICK_DDL
    --------------------------------------------------------
    
      CREATE TABLE "SCOTT"."TEST_QUICK_DDL" 
       (     "COLUMN1" VARCHAR2(10 CHAR), 
         "COLUMN2" VARCHAR2(10 CHAR)
       ) ;
      GRANT DELETE, INSERT, SELECT, UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    GRANT DELETE, INSERT, SELECT, UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    GRANT DELETE, INSERT, SELECT, UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    - Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
    --------------------------------------------------------
    --  DDL for Table TEST_QUICK_DDL
    --------------------------------------------------------
    
      CREATE TABLE "SCOTT"."TEST_QUICK_DDL" 
       (     "COLUMN1" VARCHAR2(10 CHAR), 
         "COLUMN2" VARCHAR2(10 CHAR)
       ) ;
      GRANT DELETE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT INSERT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT SELECT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    GRANT DELETE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT INSERT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT SELECT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    GRANT DELETE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT INSERT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT SELECT ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
     
      GRANT UPDATE ON "SCOTT"."TEST_QUICK_DDL" TO "KTRM";
    In both cases grants are generated 3 times - but in different way.
  • 3. Re: Quick DDL for table - grants generated 3 times
    rp0428 Guru
    Currently Being Moderated
    >
    In both cases grants are generated 3 times - but in different way.
    >
    I don't get the grants generated at all. Looking for a preferences setting to try to reproduce your problem.
  • 4. Re: Quick DDL for table - grants generated 3 times
    dz_r Newbie
    Currently Being Moderated
    After running SQL Developer in debug mode - after clicking "quick DDL" - the following lines of log are produced:
    INFO     1590     625     oracle.dbtools.raptor.export.ExportAPI     Export Task has completed.
    INFO     1589     31     oracle.dbtools.db.DBUtil     Prepared: SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',:NAME,:OWNER) FROM DUAL WHERE 0 !=
                          (Select COUNT(*) from all_col_privs where table_schema = :OWNER and TABLE_NAME =  :NAME)+(Select COUNT(*) from all_tab_privs where table_schema = :OWNER and TABLE_NAME = :NAME) 
    INFO     1588     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1587     94     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1586     47     oracle.dbtools.db.DBUtil     Prepared: select DBMS_METADATA.get_dependent_ddl('REF_CONSTRAINT',:NAME,:OWNER) sql from all_constraints where table_name = :NAME and owner = :OWNER and constraint_type = 'R' and rownum = 1 
    INFO     1585     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1584     625     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1583     31     oracle.dbtools.db.DBUtil     Prepared: SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',:NAME,:OWNER) FROM DUAL WHERE 0 !=
                          (Select COUNT(*) from all_col_privs where table_schema = :OWNER and TABLE_NAME =  :NAME)+(Select COUNT(*) from all_tab_privs where table_schema = :OWNER and TABLE_NAME = :NAME) 
    INFO     1582     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1581     78     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1580     31     oracle.dbtools.db.DBUtil     Prepared: select DBMS_METADATA.get_dependent_ddl('CONSTRAINT',:NAME,:OWNER) sql from all_constraints where table_name = :NAME and owner = :OWNER and constraint_type in ('C','U','P') and rownum = 1
    INFO     1579     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1578     641     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1577     0     oracle.dbtools.db.DBUtil     Prepared: SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',:NAME,:OWNER) FROM DUAL WHERE 0 !=
                          (Select COUNT(*) from all_col_privs where table_schema = :OWNER and TABLE_NAME =  :NAME)+(Select COUNT(*) from all_tab_privs where table_schema = :OWNER and TABLE_NAME = :NAME) 
    INFO     1576     109     oracle.dbtools.db.DBUtil     sys.dba_tab_privs:Not Found
    INFO     1575     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1574     141     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1573     47     oracle.dbtools.db.DBUtil     Prepared: select DBMS_METADATA.GET_DDL('TABLE',:NAME,:OWNER) from dual 
                             union all 
                             select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER ) FROM 
                                  (select table_name,owner from all_col_comments where owner = :OWNER and table_name = :NAME and comments is not null 
                                  union  
                                  select table_name,owner from sys.all_TAB_comments where owner = :OWNER and table_name = :NAME and comments is not null) 
                        
    INFO     1572     0     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1571     31     oracle.dbtools.db.DBUtil     Prepared:declare  FUNCTION ifelse (bool_in IN number)    RETURN boolean  IS  BEGIN    IF bool_in = 0    THEN      RETURN false;   ELSE      RETURN true;   END IF;  END;  begin   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', ifelse(:SEGMENTS));   dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',  ifelse(:TERMINATOR));  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',  ifelse(:PRETTY));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'SIZE_BYTE_KEYWORD',  ifelse(:BYTE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'FORCE',  ifelse(:FORCE));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA',  ifelse(:SHOWSCHEMA));  DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS', ifelse(:CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'REF_CONSTRAINTS', ifelse(:REF_CONSTRAINT));   DBMS_METADATA.SET_TRANSFORM_PARAM( dbms_metadata.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', false);  end;
    INFO     1570     31     oracle.dbtools.db.DBUtil     Prepared:Select trigger_name from all_triggers where table_owner = :OWNER and table_name = :NAME
    INFO     1569     12391     oracle.dbtools.db.DBUtil     Prepared:Select index_name from all_indexes where table_owner = :OWNER and table_name = :NAME
    Statement "Prepared: SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT',:NAME,:OWNER) FROM DUAL WHERE..." appears 3 times...
  • 5. Re: Quick DDL for table - grants generated 3 times
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    This is all very confusing. I have not checked it out in detail, but must involve all DDL generators (I believe there are at least 2 of them: Quick DLL/Export plus the one used in the object viewer SQL tab) picking up the Grant checkbox setting from:
    Tools > Preferences > Database > Utilities > Export
    That part is probably intentional, but perhaps the user may be surprised by the impact an Export preference has on Quick DDL. There is also some relationship between the number of constraints (all types or just some types?) and the number of times the grants are replicated. The fact there is duplication is an annoyance but does not really hurt anything.

    Probably dz_r has the Grant checkbox ticked and rp0428 does not.

    Regards,
    Gary
    SQL Developer Team
  • 6. Re: Quick DDL for table - grants generated 3 times
    dz_r Newbie
    Currently Being Moderated
    +>Looking for a preferences setting to try to reproduce your problem.+

    Preferences -> Database -> Utilities -> Export -> Export/View DDL Options -> Grants

    This check-box is responsible for generating / not generating grants in "quick DDL".
  • 7. Re: Quick DDL for table - grants generated 3 times
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Hi everyone,
    Thanks for your efforts in reporting/reproducing the problem.

    I am able to reproduce the problem and have identified the cause of it. Working on a fix for it now.

    Joyce Scapicchio
    SQLDeveloper Team

    Edited by: Joyce Scapicchio on Nov 29, 2012 12:57 PM
  • 8. Re: Quick DDL for table - grants generated 3 times
    dz_r Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points