|
안녕하세요.
Invalid 된 Synonym 중에 참조하는 Object가 있음면 재생성해주고 없으면 삭제하는 쿼리를 만드는 중입니다.
원하는 결과는 아래처럼 나오는 것인데요
| COMPAME_DDL |
TOAD_PLAN_TABLE |
INVALID |
COMPAME |
TOAD_PLAN_TABLE |
DROP SYNONYM COMPAME_DDL.TOAD_PLAN_TABLE; |
| COMPAME_DML |
TOAD_PLAN_TABLE |
INVALID |
COMPAME |
TOAD_PLAN_TABLE |
DROP SYNONYM COMPAME_DML.TOAD_PLAN_TABLE; |
| COMPAME_SEL |
TOAD_PLAN_TABLE |
INVALID |
COMPAME |
TOAD_PLAN_TABLE |
DROP SYNONYM COMPAME_SEL.TOAD_PLAN_TABLE; |
| COMPWEB |
TOAD_PLAN_TABLE |
INVALID |
COMPAME |
TOAD_PLAN_TABLE |
DROP SYNONYM COMPWEB.TOAD_PLAN_TABLE; |
| KMRMSBT_DDL |
F_GET_INST_BZ_REG_NO_01 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_01 |
CREATE OR REPLACE SYNONYM KMRMSBT_DDL.F_GET_INST_BZ_REG_NO_01 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_01; |
| KMRMSBT_DDL |
F_GET_INST_BZ_REG_NO_02 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_02 |
CREATE OR REPLACE SYNONYM KMRMSBT_DDL.F_GET_INST_BZ_REG_NO_02 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_02; |
| KMRMSBT_DML |
F_GET_INST_BZ_REG_NO_01 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_01 |
CREATE OR REPLACE SYNONYM KMRMSBT_DML.F_GET_INST_BZ_REG_NO_01 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_01; |
| KMRMSBT_DML |
F_GET_INST_BZ_REG_NO_02 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_02 |
CREATE OR REPLACE SYNONYM KMRMSBT_DML.F_GET_INST_BZ_REG_NO_02 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_02; |
| KMRMSBT_SEL |
F_GET_INST_BZ_REG_NO_01 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_01 |
CREATE OR REPLACE SYNONYM KMRMSBT_SEL.F_GET_INST_BZ_REG_NO_01 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_01; |
| KMRMSBT_SEL |
F_GET_INST_BZ_REG_NO_02 |
INVALID |
KMRMSBT |
F_GET_INST_BZ_REG_NO_02 |
CREATE OR REPLACE SYNONYM KMRMSBT_SEL.F_GET_INST_BZ_REG_NO_02 FOR KMRMSBT.F_GET_INST_BZ_REG_NO_02; |
제가 작성한 쿼리는 아래와 같습니다
,A.SYNONYM_NAME
,B.STATUS
,A.TABLE_OWNER
,A.TABLE_NAME
,CASE WHEN B.OWNER = A.TABLE_OWNER
THEN 'CREATE OR REPLACE SYNONYM ' ||A.OWNER||'.'||A.SYNONYM_NAME|| ' FOR ' ||A.OWNER||'.'||A.TABLE_NAME||';'
ELSE 'DROP SYNONYM ' ||A.OWNER||'.'||A.SYNONYM_NAME||';'
END
FROM DBA_SYNONYMS A LEFT OUTER JOIN DBA_OBJECTS B
ON A.SYNONYM_NAME = B.OBJECT_NAME
WHERE B.STATUS='INVALID'
AND B.OBJECT_TYPE='SYNONYM'
AND A.OWNER = B.OWNER
여기까진 작성을 해보았고.. 당연히 왜 안되는지는 알겠는데..
어떻게 고쳐야 할지를 모르겠네요..
도움주실분 계신가요ㅠㅠ
|
