I came across a weird situation when i am trying to look into the source code of a view from oracle sql developer, oracle sql developer recompiled the view , below command somehow getting fired without i am actually firing it
" Alter view view_name compile "
Please guide me how can I avoid this side effect while using oracle SQL developer
viewing source code of a view ,
No 'ALTER' or 'COMPILE' statements are executed when getting the DDL for a VIEW.
Here is exactly what SQL Developer sends to the database when asking for the DDL that represents your view (the SQL page)
Setting up the session to get the DDL from DBMS_METADATA, these are determined from your preferences where you set up how you want your EXPORTs to look.
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;
Getting the DDL for your view with any comments or triggers associated
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
)
Getting any Foreign Key constraints (yes, views can have those!)
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;
Getting any grants for said view
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
)
And that's it - no CREATE OR REPLACE or COMPILEs are sent.
Of course, you don't have to trust me, check the Log panel, we'll show you all the statements sent to the database via the JDBC driver.
Of course, you don't have to trust the application, do a database session trace, and you can see everything that's executed for your SQL Developer session.