oracle SQL developer在尝试生成视图的源代码时是否会在内部重新编译视图

7hiiyaii  于 12个月前  发布在  Oracle
关注(0)|答案(1)|浏览(125)

我遇到了一个奇怪的情况,当我试图从oracle sql developer查看视图的源代码时,oracle sql developer重新编译了视图,下面的命令不知何故被解雇,而我实际上没有解雇它
“更改视图视图名称编译“
请指导我如何在使用Oracle SQL Developer时避免此副作用
查看视图的源代码,

qyswt5oh

qyswt5oh1#

在获取VIEW的数据库时,不执行“ALTER”或“COMPILE”语句。
下面是SQL Developer在请求表示视图的数据库时向数据库发送的内容(SQL页面)
设置会话以从DBMS_METADATA获取导出,这取决于您设置导出外观的首选项。

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,
                                     'PARTITIONING',
                                     ifelse(:partitioning)
    );
    dbms_metadata.set_transform_param(
                                     dbms_metadata.session_transform,
                                     'TABLESPACE',
                                     ifelse(:tablespace)
    );
    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,
                                     'CONSTRAINTS_AS_ALTER',
                                     ifelse(:alter)
    );
    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)
    );
end;

字符串
获取带有任何注解或关联触发器的视图的视图

select dbms_metadata.get_ddl(
    'VIEW',
    :name,
    :owner
)
  from dual
union all
select dbms_metadata.get_ddl(
    'TRIGGER',
    trigger_name,
    owner
)
  from dba_triggers
 where table_owner = :owner
   and table_name = :name
union all
select dbms_metadata.get_dependent_ddl(
    'COMMENT',
    table_name,
    owner
)
  from (
    select table_name,
           owner
      from dba_col_comments
     where owner = :owner
       and table_name = :name
       and comments is not null
    union
    select table_name,
           owner
      from sys.dba_tab_comments
     where owner = :owner
       and table_name = :name
       and comments is not null
)


获取任何外键约束(是的,视图可以有这些约束!)

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,
                                     'PARTITIONING',
                                     ifelse(:partitioning)
    );
    dbms_metadata.set_transform_param(
                                     dbms_metadata.session_transform,
                                     'TABLESPACE',
                                     ifelse(:tablespace)
    );
    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;


为上述观点获得任何赠款

select dbms_metadata.get_dependent_ddl(
    'OBJECT_GRANT',
    :name,
    :owner,
    :version
)
  from dual
 where 0 != (
    select count(*)
      from dba_col_privs
     where owner = :owner
       and table_name = :name
) + (
    select count(*)
      from dba_tab_privs
     where owner = :owner
       and table_name = :name
)


就是这样-没有发送CREATE或REPLACE或COMPILE。
当然,你不必相信我,检查日志面板,我们将向你展示所有通过JDBC驱动程序发送到数据库的语句。
当然,您不必信任应用程序,执行数据库会话跟踪,并且您可以看到为SQL Developer会话执行的所有内容。

相关问题