DBMS_SERVICE for Single Instance Databases and status

http://anuj-singh.blogspot.com/2024/12/how-to-create-oracle-database-services.html



edit tnsnames.ora

cd $ORACLE_HOME/network/admin

anujv =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.85.129)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = anuj)
 )
  )


to Create service 

BEGIN
  DBMS_SERVICE.create_service(
    service_name => 'ANUJ',
    network_name => 'ANUJ'
  );
END;
/

====

BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'ANUJ'
  );
END;
/


COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;


COLUMN name FORMAT A30
COLUMN network_name FORMAT A30



SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;



check the status from both view ..

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT 'ACTIVE' name,
       network_name
FROM   v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT 'DBA***' name,
       network_name
FROM   dba_services
where 1=1
and NETWORK_NAME is not null
order by 2
;

=====

lsnrctl services |grep -i -A3 ANUJ




sqlplus vihaan1/vihaan1@192.168.85.129:1521/anujv

set linesize 300 pagesize 300 
col USERNAME for a20
col MACHINE for a20
col for MACHINE for a20
col PROGRAM for a35
col SERVICE_NAME for a20
col OSUSER for a25

SELECT service_name, inst_id, count(*), username, machine, program, osuser
FROM gv$session
WHERE 1=1
-- and service_name NOT IN ('SYS$BACKGROUND','SYS$USERS')
--AND inst_id=1
GROUP BY service_name, inst_id, username, machine, program, osuser
ORDER BY service_name, inst_id, username, machine, program;

BEGIN
  DBMS_SERVICE.disconnect_session(
   service_name      => 'ANUJ',
   disconnect_option => DBMS_SERVICE.immediate
  );
END;
/