리스너에 time zone을 설정하면 해당 리스너로 접속하는 세션은 리스너에서 설정한 시간대가 적용되는 테스트 입니다.(sysdate 값 확인)

    리스너 설정

    리스너에 UTC 타임존 설정을 추가합니다
    
[ora19ent@svr ~]$ cat /ora19ent/product/network/admin/listener.ora
# listener.ora Network Configuration File: /ora19ent/product/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
	(DESCRIPTION =
  	(ADDRESS = (PROTOCOL = TCP)(HOST = svr)(PORT = 1522)))
)
 
SID_LIST_LISTENER =
  (SID_LIST =
	(SID_DESC =
  	(ORACLE_HOME = /ora19ent/product)
  	(SID_NAME = ent19)
  	(ENVS='TZ=UTC')
   )
  )

    리스너 재기동 후 설정확인

    
[ora19ent@svr ~]$ lsnrctl
 
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-FEB-2024 16:23:22
 
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
 
Welcome to LSNRCTL, type "help" for information.
 
LSNRCTL> set displaymode verbose
Service display mode is VERBOSE
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1522)))
Services Summary...
Service "ent19" has 1 instance(s).
  Instance "ent19", status UNKNOWN, has 1 handler(s) for this service...
	Handler(s):
  	"DEDICATED" established:4 refused:0
     	LOCAL SERVER
         (ADDRESS=(PROTOCOL=beq)(PROGRAM=/ora19ent/product/bin/oracle)(ENVS='TZ=UTC,ORACLE_HOME=/ora19ent/product,ORACLE_SID=ent19')(ARGV0=oracleent19)(ARGS='(LOCAL=NO)'))
The command completed successfully
LSNRCTL> exit
[ora19ent@svr ~]$

    리스너 통하여 접속 후 확인

    SYSDATE의 값이 리스너에서 설정한 UTC 시간대로 확인됩니다
    
[ora19ent@svr ~]$ sqlplus system/oracle@ent19
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual ;
 
DBTIME SESSIONTIMEZONE                       	SYSDATE           	CURRENT_DATE
------ ----------------------------------- ------------------- -------------------
+00:00 +09:00                               	2024/02/16 07:16:36 2024/02/16 16:16:36

    리스너 통하지 않고 local 접속하여 확인

    리스너를 통하지 않고 접속 시 DB의 시간대(UTC+9)와 동일한 결과를 보여줍니다 
    
[ora19ent@svr ~]$ ss
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual ;
 
DBTIME SESSIONTIMEZONE                       	SYSDATE           	CURRENT_DATE
------ ----------------------------------- ------------------- -------------------
+00:00 +09:00                               	2024/02/16 16:10:37 2024/02/16 16:10:37