Thanks Cameron. This is really great script and saved a lots of my time. Regards Sanjay On Friday, October 25, 2013 12:51 AM, "Hodge, Cameron" <cameron.hodge@xxxxxxxx> wrote: Hiya Sanjay, Here is the code I used to this exact task. Its not the prettiest sql, but does the job. Just run as sysman. set pagesize 100000 set linesize 1000 select host_short || '.' || sid || ' (DESCRIPTION (ADDRESS = (PROTOCOL = TCP)(HOST = '|| hostname || ')(PORT = '|| port ||')) (CONNECT_DATA (SERVER = DEDICATED) '|| case when instr(service_name,'XDB') > 0 then Null else '(SERVICE_NAME = '|| service_name ||')' end || '(SID = '|| SID ||') )) ' from (SELECT --target_name, UPPER (host_name) hostname, UPPER (SUBSTR (t.host_name, 1, INSTR (t.host_name, '.') - 1)) HOST_SHORT, (SUBSTR (t.host_name, INSTR (t.host_name, '.') + 1, LENGTH (t.host_name))) DOMAIN, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'Port' AND p.target_guid = t.target_guid) port, 'sys/anything@' || host_name || ':' || (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'Port' AND p.target_guid = t.target_guid) || '/' || (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'ServiceName' AND p.target_guid = t.target_guid) || ' as sysdba' Connection_string, (SELECT tp.property_value FROM mgmt$target_properties tp WHERE tp.target_type = 'host' AND tp.property_name = 'IP_address' AND tp.target_name = t.host_name) ip, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'DBVersion' AND p.target_guid = t.target_guid) DB_Version, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'OracleHome' AND p.target_guid = t.target_guid) oh, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'ServiceName' AND p.target_guid = t.target_guid) Service_name, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'log_archive_mode' AND p.target_guid = t.target_guid) logmode, UPPER ( (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'SID' AND p.target_guid = t.target_guid)) sid, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'CPUCount' AND p.target_guid = t.target_guid) CPU, ROUND ( SYSDATE - TO_DATE ( (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'StartTime' AND p.target_guid = t.target_guid), 'YYYY-MM-DD HH24:MI:SS'), 0) Days_Uptime, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'StartTime' AND p.target_guid = t.target_guid) Uptime, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'VersionCategory' AND p.target_guid = t.target_guid) VersionCategory, (SELECT p.property_value FROM mgmt$target_properties p WHERE p.property_name = 'VersionBanner' AND p.target_guid = t.target_guid) VersionBanner, CASE WHEN (INSTR ( (SELECT UPPER (p.property_value) FROM mgmt$target_properties p WHERE p.property_name = 'VersionBanner' AND p.target_guid = t.target_guid), 'ENTERPRISE')) > 0 THEN 'Enterprise' ELSE 'Standard/Standard One' END Edition, (SELECT b.VALUE FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND a.TARGET_TYPE = 'oracle_database' AND b.name = 'control_file_record_keep_time' AND a.target_guid = t.target_guid) control_file_record_keep_time, (SELECT b.VALUE FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND a.TARGET_TYPE = 'oracle_database' AND b.name = 'optimizer_features_enable' AND a.target_guid = t.target_guid) optimizer_features_enable, (SELECT ROUND (b.VALUE / 1024 / 1024 / 1024, 2) FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_INIT_PARAMS_ECM B WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND a.TARGET_TYPE = 'oracle_database' AND b.name = 'memory_target' AND a.target_guid = t.target_guid) memory_target, (SELECT sessions_highwater FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_license_ECM B WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND TARGET_TYPE = 'oracle_database' AND a.target_guid = t.target_guid) sessions_highwater, (SELECT sessions_current FROM MGMT$ECM_VISIBLE_SNAPSHOTS A, SYSMAN.MGMT_DB_license_ECM B WHERE A.ECM_SNAPSHOT_ID = B.ECM_SNAPSHOT_ID AND TARGET_TYPE = 'oracle_database' AND a.target_guid = t.target_guid) sessions_current FROM mgmt$target t WHERE t.target_type IN ('oracle_database')) Raw_data order by host_short, sid; -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sanjay Mishra Sent: Friday, 25 October 2013 12:33 PM To: oracle-l@xxxxxxxxxxxxx Subject: Oracle Instance Name and Listener Port information Hi Does any one knows as how I can get Instance name and Listener Port configured on the database server from Oracle enterprise Manager Repository tables. I has 1200 database in one of the new client and want to create the tnsentry locally so that I can connect them. going to 100's of server with multiple database on multiple port is very time consuming process. As I had Grid control setup and so thought that if I can get the tablename which has the information then can can create tnsentry using script TIA Sanjay -- //www.freelists.org/webpage/oracle-l This email contains confidential information. The contents must not be disclosed to anyone else except with the authority of the sender. Unauthorised recipients are requested to maintain this confidentiality and immediately advise the sender of any error or misdirection in transmission. -- //www.freelists.org/webpage/oracle-l