[oracle] 오브젝트 생성시간은 어떤 시간대(time zone)

OS: linux 7.5 DBMS: oracle 19.3

 
오라클에서 오브젝트 생성일시를 확인하려면 DBA_OBJECTS 테이블의 CREATED을 조회합니다.
여기저기 여러 곳에서 오브젝트를 생성하는 경우는 없겠지만
해당 컬럼에 들어가는 시간은 어떤 기준으로 들어가는지에 대한 테스트입니다.
 

[목차여기]

 

- DB time zone, session time zone, OS time zone 3개의 값을 비교하여 어떤 시간대를 기준으로 데이터가 들어가는지 테스트
- 생성시간 기준시간대는 OS Time Zone 시간대(sysdate, systimestamp 시간)

 

참고

oracle alert.log 시간대(time zone) 기준은 무엇인가

DB TZ: KST, SESS TZ: KST, OS TZ: UTC 세션1

툴을 통한 원격접속 환경에서 테스트 진행하였고 UTC 기준으로 생성시간이 입력되었습니다.

-- DB time zone 확인
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual 

DBTIMEZONE|SESSIONTIMEZONE|SYSDATE            |CURRENT_DATE       |
----------+---------------+-------------------+-------------------+
+09:00    |Asia/Seoul     |2024-02-17 05:29:40|2024-02-17 14:29:40|

1 row(s) fetched.

-- 현재 테이블 없음
SELECT * FROM dba_objects
WHERE object_name like 'TAB11%'


OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED|LAST_DDL_TIME|TIMESTAMP|STATUS|TEMPORARY|GENERATED|SECONDARY|NAMESPACE|EDITION_NAME|SHARING|EDITIONABLE|ORACLE_MAINTAINED|APPLICATION|DEFAULT_COLLATION|DUPLICATED|SHARDED|CREATED_APPID|CREATED_VSNID|MODIFIED_APPID|MODIFIED_VSNID|
-----+-----------+--------------+---------+--------------+-----------+-------+-------------+---------+------+---------+---------+---------+---------+------------+-------+-----------+-----------------+-----------+-----------------+----------+-------+-------------+-------------+--------------+--------------+

0 row(s) fetched.

-- tab111 생성
CREATE TABLE sk.tab111 AS SELECT * FROM dba_objects 

72794 row(s) modified.

-- tab111 생성시간 확인
SELECT * FROM dba_objects
WHERE object_name like 'TAB11%'


OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED            |LAST_DDL_TIME      |TIMESTAMP          |STATUS|TEMPORARY|GENERATED|SECONDARY|NAMESPACE|EDITION_NAME|SHARING|EDITIONABLE|ORACLE_MAINTAINED|APPLICATION|DEFAULT_COLLATION|DUPLICATED|SHARDED|CREATED_APPID|CREATED_VSNID|MODIFIED_APPID|MODIFIED_VSNID|
-----+-----------+--------------+---------+--------------+-----------+-------------------+-------------------+-------------------+------+---------+---------+---------+---------+------------+-------+-----------+-----------------+-----------+-----------------+----------+-------+-------------+-------------+--------------+--------------+
SK   |TAB111     |              |    82772|         82772|TABLE      |2024-02-17 05:31:26|2024-02-17 05:31:26|2024-02-17:05:31:26|VALID |N        |N        |N        |        1|            |NONE   |           |N                |N          |USING_NLS_COMP   |N         |N      |             |             |              |              |

1 row(s) fetched.

-- DB time zone 확인
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual 

DBTIMEZONE|SESSIONTIMEZONE|SYSDATE            |CURRENT_DATE       |
----------+---------------+-------------------+-------------------+
+09:00    |Asia/Seoul     |2024-02-17 05:31:37|2024-02-17 14:31:37|

1 row(s) fetched.

 
 

DB TZ: KST, SESS TZ: UTC, OS TZ: UTC 세션2

LOCAL에서 SQLPLUS 접속하여 테스트 진행하였고 UTC 기준으로 생성시간이 입력되었습니다.

-- DB 서버 OS time zone 확인(UTC)
[oracle@svr1 ~]$ timedatectl 
      Local time: Sat 2024-02-17 05:28:39 UTC
  Universal time: Sat 2024-02-17 05:28:39 UTC
        RTC time: Sun 2023-11-12 05:04:03
       Time zone: UTC (UTC, +0000)
     NTP enabled: no
NTP synchronized: no
 RTC in local TZ: no
      DST active: n/a

[oracle@svr1 ~]$ date
Sat Feb 17 05:28:43 UTC 2024

-- DB time zone 확인
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual ;

DBTIME SESSIONTIMEZONE		   SYSDATE	       CURRENT_DATE
------ --------------------------- ------------------- -------------------
+09:00 +00:00			   2024/02/17 05:29:02 2024/02/17 05:29:02

-- tab112 생성 
CREATE TABLE sk.tab112 AS SELECT * FROM dba_objects ;

Table created.

-- 생성시간은 동일하게 UTC 기준으로 들어감
SELECT * FROM dba_objects
WHERE object_name like 'TAB11%'
;
OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED            |LAST_DDL_TIME      |
-----+-----------+--------------+---------+--------------+-----------+-------------------+-------------------+
SK   |TAB111     |              |    82772|         82772|TABLE      |2024-02-17 05:31:26|2024-02-17 05:31:26|
SK   |TAB112     |              |    82773|         82773|TABLE      |2024-02-17 05:31:53|2024-02-17 05:31:53|

 

반응형

DB TZ: KST, SESS TZ: KST, OS TZ: KST 세션2

OS 유저의 세션환경변수에 TZ='Asia/Seoul' 을 설정하여 os time zone 변경 후
LOCAL에서 SQLPLUS 접속하여 테스트 진행하였고 KST 기준으로 생성시간이 입력되었습니다.

-- OS 유저 환경변수 설정(KST)
[oracle@svr1 ~]$ export TZ='Asia/Seoul'
[oracle@svr1 ~]

-- TZ 환경변수 설정으로 세션의 모든 time zone 이 KST로 변경되었습니다
-- DB time zone 확인
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual ;

DBTIME SESSIONTIMEZONE		   SYSDATE	       CURRENT_DATE
------ --------------------------- ------------------- -------------------
+09:00 +09:00			   2024/02/17 14:36:40 2024/02/17 14:36:40

-- tab113 테이블 생성
CREATE TABLE sk.tab113 AS SELECT * FROM dba_objects ;
Table created.

-- 생성시간이 KST 기준으로 들어감
SELECT * FROM dba_objects
WHERE object_name like 'TAB11%'
;

OWNER		 OBJECT_NAME				  SUBOBJECT_NAME														    OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE	     CREATED		 LAST_DDL_TIME	     TIMESTAMP		 STATUS  T G S	NAMESPACE EDITION_NAME											   SHARING	      E O A DEFAULT_COLLATION											 D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID
----------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- ----------------------- ------------------- ------------------- ------------------- ------- - - - ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ - - - ---------------------------------------------------------------------------------------------------- - - ------------- ------------- -------------- --------------
SK		 TAB111 				82772	       82772 TABLE		     2024/02/17 05:31:26 2024/02/17 05:31:26 2024-02-17:05:31:26 VALID	 N N N		1						   NONE 		N N USING_NLS_COMP											 N N
SK		 TAB112 				82773	       82773 TABLE		     2024/02/17 05:31:53 2024/02/17 05:31:53 2024-02-17:05:31:53 VALID	 N N N		1						   NONE 		N N USING_NLS_COMP											 N N
SK		 TAB113 				82775	       82775 TABLE		     2024/02/17 14:36:56 2024/02/17 14:36:56 2024-02-17:14:36:56 VALID	 N N N		1						   NONE 		N N USING_NLS_COMP											 N N

 

DB TZ: KST, SESS TZ: UTC, OS TZ: UTC 세션3

아무 설정없는 신규세션으로(세션2와 동일)
LOCAL에서 SQLPLUS 접속하여 테스트 진행하였고 UTC 기준으로 생성시간이 입력되었습니다.

-- TZ 환경변수 확인(신규세션으로 설정없음)
[oracle@svr1 ~]$ env | grep -i tz
LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:
[oracle@svr1 ~]$ 

-- DB Time zone 확인
SELECT dbtimezone, sessiontimezone, sysdate, current_date  FROM dual ;

DBTIME SESSIONTIMEZONE								   SYSDATE	       CURRENT_DATE
------ --------------------------------------------------------------------------- ------------------- -------------------
+09:00 +00:00									   2024/02/17 05:38:09 2024/02/17 05:38:09

-- tab114 생성 
CREATE TABLE sk.tab114 AS SELECT * FROM dba_objects ;
Table created.

-- 생성시간 UTC 기준으로 들어감
SELECT * FROM dba_objects
WHERE object_name like 'TAB11%'
;  

OWNER|OBJECT_NAME|SUBOBJECT_NAME|OBJECT_ID|DATA_OBJECT_ID|OBJECT_TYPE|CREATED            |LAST_DDL_TIME      |
-----+-----------+--------------+---------+--------------+-----------+-------------------+-------------------+
SK   |TAB111     |              |    82772|         82772|TABLE      |2024-02-17 05:31:26|2024-02-17 05:31:26| --! UTC
SK   |TAB112     |              |    82773|         82773|TABLE      |2024-02-17 05:31:53|2024-02-17 05:31:53| --! UTC
SK   |TAB113     |              |    82775|         82775|TABLE      |2024-02-17 14:36:56|2024-02-17 14:36:56| --! KST
SK   |TAB114     |              |    82776|         82776|TABLE      |2024-02-17 05:38:23|2024-02-17 05:38:23| --! UTC
 
-- os time zone: UTC 
SQL>!date
Sat Feb 17 05:39:46 UTC 2024

 exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0