79 lines
2.5 KiB
SQL
79 lines
2.5 KiB
SQL
@@header
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Synonyms
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char) , this could also be passed as OWNER.OBJECT_NAME
|
|
* 2 - Synonymn name (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 18-SEP-14 Vishal Gupta Appended db_link to table_name and remove separate output column for DB_LINK
|
|
* 11-Dec-04 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE synonym_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE synonym_name="&&2"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _synonym_name NEW_VALUE synonym_name NOPRINT
|
|
|
|
set term off
|
|
|
|
SELECT DECODE(UPPER('&&owner'),'','%','&&owner') "_owner"
|
|
, DECODE(UPPER('&&synonym_name'),'','%','&&synonym_name') "_synonym_name"
|
|
FROM DUAL;
|
|
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&synonym_name'),'','%',UPPER('&&synonym_name'))
|
|
END "_synonym_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * S Y N O N Y M N L I S T
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Owner = '&&owner'
|
|
PROMPT * - Synonym Name = '&&synonym_name'
|
|
PROMPT *****************************************************************
|
|
|
|
COLUMN synonym_name FORMAT a50
|
|
COLUMN table_name FORMAT a50
|
|
COLUMN last_ddl_time HEADING "LastDDLTime" FORMAT a18
|
|
COLUMN created HEADING "Created" FORMAT a18
|
|
|
|
|
|
SELECT s.owner || '.' || s.synonym_name synonym_name
|
|
, s.table_owner || '.' || s.table_name || NVL2(s.db_link,'@'||s.db_link,'') table_name
|
|
, TO_CHAR(o.last_ddl_time,'DD-MON-YY HH24:MI:SS') last_ddl_time
|
|
, TO_CHAR(o.created,'DD-MON-YY HH24:MI:SS') created
|
|
--, s.db_link
|
|
FROM dba_synonyms s
|
|
, dba_objects o
|
|
WHERE s.owner = o.owner
|
|
AND s.synonym_name = o.object_name
|
|
AND o.object_type = 'SYNONYM'
|
|
AND o.owner like '&&owner' ESCAPE '\'
|
|
AND s.synonym_name like '&&synonym_name' ESCAPE '\'
|
|
ORDER BY s.synonym_name
|
|
, s.owner
|
|
;
|
|
|
|
|
|
@@footer
|