---Invalid Synonym are valid script with Editionable
---DB object
DECLARE
l_DDL VARCHAR2 (5000);
v_DDL VARCHAR2 (5000);
CURSOR Cur
IS
SELECT EDITION_NAME,
DBMS_METADATA.get_ddl (object_type => 'SYNONYM',
name => db.object_name,
schema => 'PUBLIC')
DDL,
als.synonym_name,
REPLACE (
DBMS_METADATA.get_ddl (object_type => 'SYNONYM',
name => db.object_name,
schema => 'PUBLIC'),
'NONEDITIONABLE',
'EDITIONABLE')
new_ddl
FROM all_synonyms als, dba_objects db
WHERE als.owner = 'PUBLIC'
AND als.table_owner NOT IN ('SYS')
AND als.synonym_name = db.object_name
AND db.status = 'INVALID'
AND db.OWNER = 'PUBLIC'
AND object_type = 'SYNONYM'
AND OBJECT_NAME = :OBJECT_NAME --- comment for all record
;
BEGIN
DBMS_OUTPUT.put_line ('>>> DDL >>> ');
FOR i IN Cur
LOOP
DBMS_OUTPUT.put_line ('>>> DDL Name: ' || i.synonym_name);
COMMIT;
-------1-----------
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || i.synonym_name;
DBMS_OUTPUT.put_line (
'>>>Successfuly Drop Public Synonym: ' || i.synonym_name);
COMMIT;
END;
------2----------
BEGIN
EXECUTE IMMEDIATE REPLACE (i.new_ddl, CHR (10), '');
DBMS_OUTPUT.put_line (
'>>>Created Editionable Public Synonym : '
|| REPLACE (i.new_ddl, CHR (10), ''));
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('>>> Error : ' || SQLERRM);
END;