Oracle Database Discussions

I am working on a 10g database and somehow the service name got configured wrong.

When I connect with JDBC or remote SQLPlus, I have to use the service name "CAMAT1.healthcit.com". It needs to be changed to "CAMAT1".

I read other postings on this problem and suggested doing the following:

alter system set service_names='CAMAT1' scope=both;
alter system register;
show parameter service_names;

But 'lsnrctl services' shows:

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aow-db3)(PORT=1521)))
Services Summary...
Service "CAMAT1.healthcit.com" has 1 instance(s).
Instance "CAMAT1", status READY, has 1 handler(s) for this service...
Service "CAMAT1XDB.healthcit.com" has 1 instance(s).
Instance "CAMAT1", status READY, has 1 handler(s) for this service...
Service "CAMAT1_XPT.healthcit.com" has 1 instance(s).
Instance "CAMAT1", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

I've restarted the listener and the database and still nothing. "aow-db3" maps to the ip address of the server in the hosts file.

Another posting suggested I do the above plus:

select name, network_name from dba_services;

which shows both CAMAT1 and CAMAT1.healthcit.com

and then do:

exec dbms_service.delete_service('CAMAT1.healthcit.com');

but I get this:

ERROR at line 1:
ORA-44305: service CAMAT1.healthcit.com is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 444
ORA-06512: at "SYS.DBMS_SERVICE", line 333
ORA-06512: at line 1

I've tried stopping the listener and uncommenting the entry in the tnsnames file and still nothing.

---------------------

My listener and tnsnames files:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2/db1)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = aow-db3)(PORT = 1521))
)
)

CAMAT1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aow-db3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CAMAT1)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)