Generate tnsnames from OEM repository

Environment: Oracle 10g, 10.2
To get tnsnames from OEM, connect to OEM repository and run the following SQL.
p3.property_value and t.target_name can be the same, or one of them can include domain name. You can choose one or both.
Script:

select p3.property_value||', '||t.target_name||' = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||p1.property_value||')(PORT = '||p2.property_value||'))) (CONNECT_DATA = (service_name = '||p3.property_value||')))'
from SYSMAN.MGMT_TARGETS t, SYSMAN.MGMT_TARGET_PROPERTIES p1, SYSMAN.MGMT_TARGET_PROPERTIES p2, SYSMAN.MGMT_TARGET_PROPERTIES p3
where upper(t.TARGET_NAME) like 'DH%' -- filter targets if you don't want all of them
and t.target_type='oracle_database'
and t.target_guid = p1.target_guid
and t.target_guid = p2.target_guid
and t.target_guid = p3.target_guid(+)
and p1.property_name='MachineName'
and p2.property_name='Port'
and p3.property_name(+)='ServiceName'
order by 1;

SQL Plus profile

When you start SQL Plus or connect, it runs the glogin.sql site profile script and then runs the login.sql user profile script.
The global site profile is $ORACLE_HOME/sqlplus/admin/glogin.sql
The user profile script is login.sql. SQL Plus looks for login.sql in the current directory, and then in the directories specified by the SQLPATH environment variable.
e.g. Following script sets up date format, SQL prompt and shows where you are connected to.

set head off feedback off
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd HH24:MI:SS';
SELECT
'user: '||SYS_CONTEXT('USERENV','CURRENT_USER')||
' inst: '|| SYS_CONTEXT('USERENV','INSTANCE_NAME')||
' db: '|| SYS_CONTEXT('USERENV','DB_NAME')||
' host: '|| SYS_CONTEXT('USERENV','SERVER_HOST') c1
FROM DUAL;
set sqlprompt "_user'@'_connect_identifier> "

set head on feedback on
prompt ___________________________________________________________________
set serveroutput on size 20000 line 200 

Oracle documentation (SQL*Plus® User's Guide and Reference Release 10.2)