8 Replies Latest reply: Nov 30, 2012 2:06 AM by dz_r RSS

    Quick DDL for table - grants generated 3 times

    dz_r
      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
          >
          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
            - 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
              >
              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
                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-Oracle
                  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
                    +>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
                      Joyce Scapicchio
                      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