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
;set linesize 300
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
col PDB_NAME for a20
SELECT distinct 'ACTIVE' name,
CON_ID,
CON_NAME PDB_NAME,
network_name
FROM v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT distinct 'DBA***' name,
b.CON_ID,
PDB_NAME PDB_NAME,
network_name
FROM dba_services,dba_pdbs b
where 1=1
and NETWORK_NAME is not null
and b.PDB_ID=CON_ID
order by 2
;
NAME CON_ID PDB_NAME NETWORK_NAME
------------------------------ ---------- -------------------- ------------------------------
ACTIVE 1 CDB$ROOT vihcdbd8XDB
ACTIVE 1 CDB$ROOT VIHCD8X
DBA*** 2 PDB$SEED vihcdbd8
DBA*** 2 PDB$SEED VIHCD8X
=====
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;
/
