EXADATA 셀 서버 추가시 고려 사항
Exadata V2-2에서 Cell 1개 추가시 작업 시 Risk 혹은 사전 고려사항 1) 기존 장비에 대한 upgrade가 필요함 추가되는 Cell이 X3-2로
더 읽기
글쓴이: haisins
UTF8 AL32UTF8 차이
Unicode Database [ ASCII-Based platforms ] UTF8 : Unicode Version – 2.1 ~ 3.0 지원 RDBMS 8.0 ~ 9I Unicode
더 읽기
오랫동안 수행했던 DML 세션을 강제 취소 (세션 KILL) 했을 경우 …
오랫동안 수행했던 DML 세션을 Kill 취소 했을 경우 사용한 Undo 릴리즈 하는 남은 시간 확인 하는 방법 수행 시간이 긴
더 읽기
이기종 마이그레이션 TOOL
종합결론 ISPIRER 은 수동으로 하는 SCRIPT 작업을 통한 이관과 시간차이가 거의 나지 않습니다. (1달 데모 사용) 또한 컨버트 스크립트
더 읽기
LISTAGG Function 사용법
1 목적 점차적으로 10g 에서 11g로 버전업이 되고 있는 시장에서 Oracle 11g에서 새롭게 소개된 기능을 살펴보고자 한다. SQL문을 사용하다보면
더 읽기
DBV Utility를 이용한 File Check
1 테스트 환경 n 서버 : Sun Enterprise E3500 (2048M) n 운영체제 : Solaris 9 n 오라클 : Oracle 10.2.0.4 Enterprise
더 읽기
Oracle Secure File 활용
1 테스트환경 n 서버 : IBM,8202-E4B (8192M) n 운영체제 : Aix 6.1 TL 06 n 오라클 : Oracle
더 읽기
Oracle Gateway 이기종 DB Link
1 Gateway 개요 n 이기종 Database 간의 통신을 위해서 오라클에서 제공하는 제품이다. n 이기종 시스템 간의 SQL 변환, 데이터의
더 읽기
오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리
오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 |
prompt Oracle Database Option Usage prompt _______________________________________________________ prompt COL "Host Name" FORMAT A30; COL "Option/Management Pack" FORMAT A60; COL "Used" FORMAT A5; with features as( select a OPTIONS, b NAME from ( select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual union all select 'Advanced Compression', 'HeapCompression' from dual union all select 'Advanced Compression', 'Backup BZIP2 Compression' from dual union all select 'Advanced Compression', 'Backup DEFAULT Compression' from dual union all select 'Advanced Compression', 'Backup HIGH Compression' from dual union all select 'Advanced Compression', 'Backup LOW Compression' from dual union all select 'Advanced Compression', 'Backup MEDIUM Compression' from dual union all select 'Advanced Compression', 'Backup ZLIB, Compression' from dual union all select 'Advanced Compression', 'SecureFile Compression (user)' from dual union all select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual union all select 'Advanced Compression', 'Data Guard' from dual union all select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual union all select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual union all select 'Advanced Security', 'ASO native encryption and checksumming' from dual union all select 'Advanced Security', 'Transparent Data Encryption' from dual union all select 'Advanced Security', 'Encrypted Tablespaces' from dual union all select 'Advanced Security', 'Backup Encryption' from dual union all select 'Advanced Security', 'SecureFile Encryption (user)' from dual union all select 'Change Management Pack', 'Change Management Pack (GC)' from dual union all select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual union all select 'Data Mining', 'Data Mining' from dual union all select 'Diagnostic Pack', 'Diagnostic Pack' from dual union all select 'Diagnostic Pack', 'ADDM' from dual union all select 'Diagnostic Pack', 'AWR Baseline' from dual union all select 'Diagnostic Pack', 'AWR Baseline Template' from dual union all select 'Diagnostic Pack', 'AWR Report' from dual union all select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual union all select 'Diagnostic Pack', 'Baseline Static Computations' from dual union all select 'Tuning Pack', 'Tuning Pack' from dual union all select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual union all select 'Tuning Pack', 'SQL Tuning Advisor' from dual union all select 'Tuning Pack', 'SQL Access Advisor' from dual union all select 'Tuning Pack', 'SQL Profile' from dual union all select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual union all select 'Database Vault', 'Oracle Database Vault' from dual union all select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual union all select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual union all select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual union all select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual union all select 'Exadata', 'Exadata' from dual union all select 'Label Security', 'Label Security' from dual union all select 'OLAP', 'OLAP - Analytic Workspaces' from dual union all select 'Partitioning', 'Partitioning (user)' from dual union all select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual union all select 'Real Application Testing', 'Database Replay: Workload Capture' from dual union all select 'Real Application Testing', 'Database Replay: Workload Replay' from dual union all select 'Real Application Testing', 'SQL Performance Analyzer' from dual union all select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual union all select 'Total Recall', 'Flashback Data Archive' from dual ) ) select t.o "Option/Management Pack", t.u "Used", d.DBID "DBID", d.name "DB Name", i.version "DB Version", i.host_name "Host Name", to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time" from (select OPTIONS o, DECODE(sum(num),0,'NO','YES') u from ( select f.OPTIONS OPTIONS, case when f_stat.name is null then 0 when ( ( f_stat.currently_used = 'TRUE' and f_stat.detected_usages > 0 and (sysdate - f_stat.last_usage_date) < 366 and f_stat.total_samples > 0 ) or (f_stat.detected_usages > 0 and (sysdate - f_stat.last_usage_date) < 366 and f_stat.total_samples > 0) ) and ( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') or (f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0') ) then 1 else 0 end num from features f, sys.dba_feature_usage_statistics f_stat where f.name = f_stat.name(+) ) group by options) t, v$instance i, v$database d order by 2 desc,1 ; prompt prompt prompt Oracle Database Usage Options Details prompt _______________________________________________________ prompt COL "Option/Management Pack" FORMAT A60; COL "Used" FORMAT A5; COL "Feature being Used" FORMAT A50; COL "Currently Used" FORMAT A14; COL "Last Usage Date" FORMAT A18; COL "Last Sample Date" FORMAT A18; COL "Host Name" FORMAT A30; with features as( select a OPTIONS, b NAME from ( select 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual union all select 'Advanced Compression', 'HeapCompression' from dual union all select 'Advanced Compression', 'Backup BZIP2 Compression' from dual union all select 'Advanced Compression', 'Backup DEFAULT Compression' from dual union all select 'Advanced Compression', 'Backup HIGH Compression' from dual union all select 'Advanced Compression', 'Backup LOW Compression' from dual union all select 'Advanced Compression', 'Backup MEDIUM Compression' from dual union all select 'Advanced Compression', 'Backup ZLIB, Compression' from dual union all select 'Advanced Compression', 'SecureFile Compression (user)' from dual union all select 'Advanced Compression', 'SecureFile Deduplication (user)' from dual union all select 'Advanced Compression', 'Data Guard' from dual union all select 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual union all select 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual union all select 'Advanced Security', 'ASO native encryption and checksumming' from dual union all select 'Advanced Security', 'Transparent Data Encryption' from dual union all select 'Advanced Security', 'Encrypted Tablespaces' from dual union all select 'Advanced Security', 'Backup Encryption' from dual union all select 'Advanced Security', 'SecureFile Encryption (user)' from dual union all select 'Change Management Pack (GC)', 'Change Management Pack (GC)' from dual union all select 'Data Masking Pack', 'Data Masking Pack (GC)' from dual union all select 'Data Mining', 'Data Mining' from dual union all select 'Diagnostic Pack', 'Diagnostic Pack' from dual union all select 'Diagnostic Pack', 'ADDM' from dual union all select 'Diagnostic Pack', 'AWR Baseline' from dual union all select 'Diagnostic Pack', 'AWR Baseline Template' from dual union all select 'Diagnostic Pack', 'AWR Report' from dual union all select 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual union all select 'Diagnostic Pack', 'Baseline Static Computations' from dual union all select 'Tuning Pack', 'Tuning Pack' from dual union all select 'Tuning Pack', 'Real-Time SQL Monitoring' from dual union all select 'Tuning Pack', 'SQL Tuning Advisor' from dual union all select 'Tuning Pack', 'SQL Access Advisor' from dual union all select 'Tuning Pack', 'SQL Profile' from dual union all select 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual union all select 'Database Vault', 'Oracle Database Vault' from dual union all select 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual union all select 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual union all select 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual union all select 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual union all select 'Exadata', 'Exadata' from dual union all select 'Label Security', 'Label Security' from dual union all select 'OLAP', 'OLAP - Analytic Workspaces' from dual union all select 'Partitioning', 'Partitioning (user)' from dual union all select 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual union all select 'Real Application Testing', 'Database Replay: Workload Capture' from dual union all select 'Real Application Testing', 'Database Replay: Workload Replay' from dual union all select 'Real Application Testing', 'SQL Performance Analyzer' from dual union all select 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual union all select 'Total Recall', 'Flashback Data Archive' from dual ) ) select t.o "Option/Management Pack", t.u "Used", t.n "Feature being Used", t.v "Version", t.cu "Currently Used", t.du "Detected Usage", t.lud "Last Usage Date", t.ts "Total Samples", t.lsd "Last Sample Date", d.DBID "DBID", d.name "DB Name", i.version "Curr DB Version", i.host_name "Host Name", to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') "ReportGen Time" from ( select f.OPTIONS o, 'YES' u, f_stat.version v, case when f_stat.name in ('Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') then 'Data Pump Compression' when f_stat.name in ('Data Guard') then 'Data Guard Network Compression' else f_stat.name end n, f_stat.CURRENTLY_USED cu, (f_stat.DETECTED_USAGES) du, to_char(f_stat.LAST_USAGE_DATE, 'DD-MON-YY HH24:MI:SS') lud, (f_stat.TOTAL_SAMPLES) ts, to_char(f_stat.LAST_SAMPLE_DATE, 'DD-MON-YY HH24:MI:SS') lsd from features f, sys.dba_feature_usage_statistics f_stat where f.name = f_stat.name and ( (f_stat.currently_used = 'TRUE' and f_stat.detected_usages > 0 and (sysdate - f_stat.last_usage_date) < 366 and f_stat.total_samples > 0 ) or (f_stat.detected_usages > 0 and (sysdate - f_stat.last_usage_date) < 366 and f_stat.total_samples > 0) ) and ( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') or (f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and f_stat.feature_info is not null and trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0') ) ) t, v$instance i, v$database d order by t.o,t.n,t.v ; |
오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는
더 읽기
clob select 시 hugeclob 으로 표시 될 때 내용 보는 방법
HUGE CLOB 데이터타입은 일반 쿼리로 불러올 경우 HUGE CLOB 의 내용이 보이지 않는다. 때문에 아래와 같이 SELECT DBMS_LOB.SUBSTR( 컬럼, 500) FROM TABLE
더 읽기