OS환경 : Oracle Linux 7.5 (64bit)
DB 환경 : Oracle Database 11.2.0.4, 12.1.0.1, 19.7.0.0
쿼리 : 오라클 패치 정보를 조회할 수 있는 뷰 DBA_REGISTRY_SQLPATCH
오라클은 패치정보를 조회할 수 있는 뷰를 제공함
11g, 12c 에서 아래 두개의 SQL로 조회가 가능함
11g 패치 정보 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SQL> SET LINESIZE 400 COLUMN ACTION_TIME FORMAT A20 COLUMN ACTION FORMAT A20 COLUMN NAMESPACE FORMAT A20 COLUMN VERSION FORMAT A10 COLUMN COMMENTS FORMAT A30 COLUMN BUNDLE_SERIES FORMAT A10 SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME ,ACTION ,NAMESPACE ,VERSION ,ID ,COMMENTS ,BUNDLE_SERIES FROM DBA_REGISTRY_HISTORY ORDER BY ACTION_TIME / ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER -------------------- -------------------- -------------------- ---------- ---------- ------------------------------ ---------- 20191015 09:20:15 APPLY SERVER 11.2.0.4 0 Patchset 11.2.0.2.0 PSU |
위 뷰 대신 SYS.REGISTRY$HISTORY 를 사용하기도 한다.
12c 업그레이드시 DBA_REGISTRY_HISTORY 구조가 변경되어
DBA_REGISTRY_SQLPATCH 뷰를 사용하도록 권장하고 있음
12c 패치 정보 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> SET LINESIZE 400 COLUMN ACTION_TIME FORMAT A20 COLUMN ACTION FORMAT A10 COLUMN STATUS FORMAT A10 COLUMN DESCRIPTION FORMAT A40 COLUMN VERSION FORMAT A10 COLUMN BUNDLE_SERIES FORMAT A10 SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME ,ACTION ,STATUS ,DESCRIPTION ,VERSION ,PATCH_ID ,BUNDLE_SERIES FROM DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME / no rows selected |
패치를 하지않아서 no rows 로 나옴
11g, 12c DBA_REGISTRY_HISTORY 뷰 비교
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 11g SQL> desc DBA_REGISTRY_HISTORY Name Null? Type ----------------------------------------- -------- ---------------------------- ACTION_TIME TIMESTAMP(6) ACTION VARCHAR2(30) NAMESPACE VARCHAR2(30) VERSION VARCHAR2(30) ID NUMBER BUNDLE_SERIES VARCHAR2(30) COMMENTS VARCHAR2(255) 12c SQL> desc DBA_REGISTRY_HISTORY Name Null? Type ----------------------------------------- -------- ---------------------------- ACTION_TIME TIMESTAMP(6) ACTION VARCHAR2(30) NAMESPACE VARCHAR2(30) VERSION VARCHAR2(30) ID NUMBER COMMENTS VARCHAR2(255) |
12c 에서는 DBA_REGISTRY_HISTORY뷰에서 BUNDLE_SERIES 가 없어짐
BUNDLE_SERIES 이 DBA_REGISTRY_SQLPATCH 뷰에 포함됨
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SQL> desc DBA_REGISTRY_SQLPATCH Name Null? Type ----------------------------------------- -------- ---------------------------- PATCH_ID NOT NULL NUMBER PATCH_UID NOT NULL NUMBER VERSION NOT NULL VARCHAR2(20) FLAGS VARCHAR2(10) ACTION NOT NULL VARCHAR2(15) STATUS VARCHAR2(15) INSTALL_ID NUMBER ACTION_TIME NOT NULL TIMESTAMP(6) DESCRIPTION VARCHAR2(100) BUNDLE_SERIES VARCHAR2(30) BUNDLE_ID NUMBER BUNDLE_DATA XMLTYPE PATCH_DESCRIPTOR XMLTYPE PATCH_DIRECTORY BLOB LOGFILE VARCHAR2(500) |
19c 에서는 DBA_REGISTRY_SQLPATCH 뷰가 12c와 차이가 많이남
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> desc DBA_REGISTRY_SQLPATCH Name Null? Type ----------------------------------------------------- -------- INSTALL_ID NOT NULL NUMBER PATCH_ID NOT NULL NUMBER PATCH_UID NOT NULL NUMBER PATCH_TYPE NOT NULL VARCHAR2(10) ACTION NOT NULL VARCHAR2(15) STATUS NOT NULL VARCHAR2(25) ACTION_TIME NOT NULL TIMESTAMP(6) DESCRIPTION VARCHAR2(100) LOGFILE NOT NULL VARCHAR2(500) RU_LOGFILE VARCHAR2(500) FLAGS VARCHAR2(10) PATCH_DESCRIPTOR XMLTYPE PATCH_DIRECTORY BLOB SOURCE_VERSION VARCHAR2(15) SOURCE_BUILD_DESCRIPTION VARCHAR2(80) SOURCE_BUILD_TIMESTAMP TIMESTAMP(6) TARGET_VERSION VARCHAR2(15) TARGET_BUILD_DESCRIPTION VARCHAR2(80) TARGET_BUILD_TIMESTAMP TIMESTAMP(6) |
19c 용 DBA_REGISTRY_SQLPATCH 조회 쿼리
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL> SET LINESIZE 400 COLUMN ACTION_TIME FORMAT A20 COLUMN ACTION FORMAT A10 COLUMN STATUS FORMAT A10 COLUMN DESCRIPTION FORMAT A60 COLUMN VERSION FORMAT A10 SELECT TO_CHAR(ACTION_TIME, 'YYYYMMDD HH24:MI:SS') AS ACTION_TIME ,ACTION ,STATUS ,DESCRIPTION ,SOURCE_VERSION ,TARGET_VERSION ,PATCH_ID FROM DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME / ACTION_TIME ACTION STATUS DESCRIPTION SOURCE_VERSION TARGET_VERSION PATCH_ID -------------------- ---------- ---------- ------------------------------------------------------------ --------------- --------------- ---------- 20200602 17:09:32 APPLY SUCCESS OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 19.1.0.0.0 19.1.0.0.0 30805684 20200602 17:09:32 APPLY SUCCESS Database Release Update : 19.7.0.0.200414 (30869156) 19.1.0.0.0 19.7.0.0.0 30869156 20200602 23:52:59 ROLLBACK SUCCESS OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684) 19.3.0.0.0 19.3.0.0.0 30805684 20200602 23:52:59 ROLLBACK SUCCESS Database Release Update : 19.7.0.0.200414 (30869156) 19.7.0.0.0 19.3.0.0.0 30869156 |
'ORACLE > Sql' 카테고리의 다른 글
| undo 사용량 확인 (0) | 2018.05.25 |
|---|---|
| sql 문법 검사해주는 사이트 (0) | 2018.05.17 |
| oracle 10g 자동통계정보수집 table lock , unlock (0) | 2018.05.15 |
| 자동통계수집 job 돌고 있는지 확인 (0) | 2018.05.11 |
| index 명으로 table 찾기 (0) | 2018.05.09 |