declare l_string varchar2(4000); begin for tp in (select VIEW_NAME,text from dba_views) loop l_string := substr(tp.text,1,4000); if instr(l_string,'table_a')>0 then dbms_output.put_line(tp.view_name); end if; end loop; end; /
CREATE OR REPLACE PROCEDURE find IS tmp LONG; v_name VARCHAR2 (4000); CURSOR c1 IS SELECT text, view_name FROM all_views WHERE view_name LIKE 'MTL%';
BEGIN OPEN c1; LOOP FETCH c1 INTO tmp, v_name; EXIT WHEN c1%NOTFOUND; IF INSTR (tmp, 'MTL_CUSTOMER_ITEMS') > 0 and instr(tmp, 'CUSTOMER_ITEM_NUMBER') > 0 THEN DBMS_OUTPUT.put_line (v_name); END IF; END LOOP;
CLOSE c1; COMMIT; END; /这是查找所有以MTL开始的view,然后确定其有没reference 这个‘CUSTOMER_ITEM_NUMBER’。这个procedure用在有attribute要更改位置或修改类型时,用来查找相关联的view,如果是table则用 SELECT text, name FROM all_objects WHERE object_type = 'TABLE' and object_name LIKE 'MTL%'; 就可以了。
如果你会pb,在pb里顶多10行代码搞定。
l_string varchar2(4000);
begin
for tp in (select VIEW_NAME,text from dba_views) loop
l_string := substr(tp.text,1,4000);
if instr(l_string,'table_a')>0 then
dbms_output.put_line(tp.view_name);
end if;
end loop;
end;
/
执行dbms_output.put_line会出错啊!
能不能把long转换成varchar2?
至少以下试图的长度是大于4000的
ALL_DEPENDENCIES
ALL_IND_PARTITIONS
ALL_LOBS
ALL_LOB_PARTITIONS
ALL_OBJECTS
ALL_QUEUE_TABLES
ALL_QUEUE_TABLES
ALL_TABLES
ALL_TAB_COLS
ALL_TAB_PARTITIONS
ALL_TRIGGER_COLS
ALL_USTATS
DBA_MVIEWS
DBA_OBJ_AUDIT_OPTS
DBA_QUEUE_TABLES
DBA_TAB_COLS
DBA_TRIGGER_COLS
DBA_VARRAYS
INDEX_STATS
KU$_QUEUE_TABLE_VIEW
ORA_KGLR7_DEPENDENCIES
REPCAT_REPCOLUMN_BASE
STRADDLING_RS_OBJECTS
STRADDLING_TS_OBJECTS
USER_OBJ_AUDIT_OPTS
USER_QUEUE_TABLES
USER_TAB_COLS
USER_TRIGGER_COLS
USER_VARRAYS
ALL$OLAP_ENTITY_DESC_USES
DBA$OLAP2UDIM_LEVEL_ATTR_MAPS
谢谢各位拉!
IS
tmp LONG;
v_name VARCHAR2 (4000); CURSOR c1
IS
SELECT text, view_name
FROM all_views
WHERE view_name LIKE 'MTL%';
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO tmp, v_name;
EXIT WHEN c1%NOTFOUND; IF INSTR (tmp, 'MTL_CUSTOMER_ITEMS') > 0 and instr(tmp, 'CUSTOMER_ITEM_NUMBER') > 0
THEN
DBMS_OUTPUT.put_line (v_name);
END IF;
END LOOP;
CLOSE c1; COMMIT;
END;
/这是查找所有以MTL开始的view,然后确定其有没reference 这个‘CUSTOMER_ITEM_NUMBER’。这个procedure用在有attribute要更改位置或修改类型时,用来查找相关联的view,如果是table则用
SELECT text, name
FROM all_objects
WHERE object_type = 'TABLE' and object_name LIKE 'MTL%'; 就可以了。