Question:
I want to be able to display the DDL for a remote
table over a database link. The dbms_metadata does not
appear to do this. Is there a trick for displaying
remote DDL?
Answer: Here is a trick to allow
you to display the DDL for a remote table.
CREATE
OR REPLACE FUNCTION read_remote_ddl( p_type varchar2,p_name
varchar2,p_schema varchar2,p_link varchar2 default null)
RETURN CLOB
IS
c_ddl
CLOB;
v_ddl
varchar(4000);
v_link varchar2(30);
v_sql varchar(400);
v_len number;
BEGIN
c_ddl:='';
IF p_link is null
THEN
v_sql:= 'select
dbms_metadata.get_ddl('''||p_type||''','''||p_name||''','''||p_schema||''')
from dual';
execute immediate v_sql into c_ddl;
ELSE
v_sql:= 'select dbms_lob.getlength@'||p_link||'(dbms_metadata.get_ddl@'||p_link||'('''||p_type||''','''||p_name||''','''||p_schema||'''))
from dual@'||p_link;
execute immediate v_sql into v_len;
for i in 0..trunc(v_len/4000) loop
v_sql:= 'select dbms_lob.substr@'||p_link||'(dbms_metadata.get_ddl@'||p_link||'('''||p_type||''','''||p_name||''','''||p_schema||'''),4000,'||to_char(i*4000+1)||')
from dual@'||p_link;
execute immediate v_sql into v_ddl;
c_ddl:=c_ddl||v_ddl;
end loop;
END IF;
RETURN c_ddl;
END read_remote_ddl;
/
Then you can run the remote
DDL display procedure it like this to get the remote DDL:
select
table_name,
read_remote_ddl('TABLE',table_name,'SCOTT','DBLINK')
from
user_tables@dblink;
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
