{"id":4162,"date":"2018-10-10T09:50:19","date_gmt":"2018-10-10T00:50:19","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=4162"},"modified":"2018-10-10T09:50:19","modified_gmt":"2018-10-10T00:50:19","slug":"%ec%98%a4%eb%9d%bc%ed%81%b4-db-%eb%82%b4%ec%97%90-%ec%98%b5%ec%85%98-%ea%b8%b0%eb%8a%a5%ec%9d%84-%ec%82%ac%ec%9a%a9%ed%95%98%eb%8a%94-%eb%82%b4%ec%97%ad%ec%9d%84-%ed%99%95%ec%9d%b8-%ed%95%98%eb%8a%94","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4162","title":{"rendered":"\uc624\ub77c\ud074 DB \ub0b4\uc5d0 \uc635\uc158 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc5ed\uc744 \ud655\uc778 \ud558\ub294 \ucffc\ub9ac"},"content":{"rendered":"<p>\uc624\ub77c\ud074 DB \ub0b4\uc5d0 \uc635\uc158 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc5ed\uc744 \ud655\uc778 \ud558\ub294 \ucffc\ub9ac<\/p>\n<pre class=\"lang:plsql decode:true\">prompt\u00a0 Oracle Database Option Usage\r\nprompt _______________________________________________________\r\nprompt\r\n\r\nCOL \"Host Name\" FORMAT A30;\r\nCOL \"Option\/Management Pack\" FORMAT A60;\r\nCOL \"Used\" FORMAT A5;\r\nwith features as(\r\nselect a OPTIONS, b NAME from\r\n(\r\nselect 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual\r\nunion all \r\nselect 'Advanced Compression', 'HeapCompression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup BZIP2 Compression' from dual\r\nunion all \r\nselect 'Advanced Compression', 'Backup DEFAULT Compression' from dual\r\nunion all \r\nselect 'Advanced Compression', 'Backup HIGH Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup LOW Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup MEDIUM Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup ZLIB, Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'SecureFile Compression (user)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'SecureFile Deduplication (user)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Data Guard' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual\r\nunion all\r\nselect 'Advanced Security', 'ASO native encryption and checksumming' from dual\r\nunion all\r\nselect 'Advanced Security', 'Transparent Data Encryption' from dual\r\nunion all\r\nselect 'Advanced Security', 'Encrypted Tablespaces' from dual\r\nunion all\r\nselect 'Advanced Security', 'Backup Encryption' from dual\r\nunion all\r\nselect 'Advanced Security', 'SecureFile Encryption (user)' from dual\r\nunion all\r\nselect 'Change Management Pack', 'Change Management Pack (GC)' from dual\r\nunion all\r\nselect 'Data Masking Pack', 'Data Masking Pack (GC)' from dual\r\nunion all\r\nselect 'Data Mining', 'Data Mining' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Diagnostic Pack' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'ADDM' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Baseline' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Baseline Template' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Report' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Baseline Static Computations' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Tuning Pack' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Real-Time SQL Monitoring' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Tuning Advisor' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Access Advisor' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Profile' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual\r\nunion all\r\nselect 'Database Vault', 'Oracle Database Vault' from dual\r\nunion all\r\nselect 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual\r\nunion all\r\nselect 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual\r\nunion all\r\nselect 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual\r\nunion all\r\nselect 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual\r\nunion all\r\nselect 'Exadata', 'Exadata' from dual\r\nunion all\r\nselect 'Label Security', 'Label Security' from dual\r\nunion all\r\nselect 'OLAP', 'OLAP - Analytic Workspaces' from dual\r\nunion all\r\nselect 'Partitioning', 'Partitioning (user)' from dual\r\nunion all\r\nselect 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual\r\nunion all\r\nselect 'Real Application Testing', 'Database Replay: Workload Capture' from dual\r\nunion all\r\nselect 'Real Application Testing', 'Database Replay: Workload Replay' from dual\r\nunion all\r\nselect 'Real Application Testing', 'SQL Performance Analyzer' from dual\r\nunion all\r\nselect 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual\r\nunion all\r\nselect 'Total Recall', 'Flashback Data Archive' from dual\r\n)\r\n)\r\nselect t.o \"Option\/Management Pack\", \r\nt.u \"Used\",\r\nd.DBID \"DBID\",\r\nd.name \"DB Name\",\r\ni.version \"DB Version\",\r\ni.host_name \"Host Name\",\r\nto_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') \"ReportGen Time\"\r\nfrom\r\n(select OPTIONS o, DECODE(sum(num),0,'NO','YES') u\r\nfrom\r\n(\r\nselect f.OPTIONS OPTIONS, case\r\nwhen f_stat.name is null then 0\r\nwhen ( ( f_stat.currently_used = 'TRUE' and\r\nf_stat.detected_usages &gt; 0 and\r\n(sysdate - f_stat.last_usage_date) &lt; 366 and\r\nf_stat.total_samples &gt; 0\r\n)\r\nor \r\n(f_stat.detected_usages &gt; 0 and \r\n(sysdate - f_stat.last_usage_date) &lt; 366 and\r\nf_stat.total_samples &gt; 0)\r\n) and \r\n( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')\r\nor\r\n(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and\r\nf_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')\r\n)\r\nthen 1\r\nelse 0\r\nend num\r\nfrom features f,\r\nsys.dba_feature_usage_statistics f_stat\r\nwhere f.name = f_stat.name(+)\r\n) group by options) t,\r\nv$instance i,\r\nv$database d\r\norder by 2 desc,1 \r\n;\r\nprompt \r\nprompt \r\nprompt Oracle Database Usage Options Details\r\nprompt _______________________________________________________\r\nprompt\r\n\r\nCOL \"Option\/Management Pack\" FORMAT A60;\r\nCOL \"Used\" FORMAT A5;\r\nCOL \"Feature being Used\" FORMAT A50;\r\nCOL \"Currently Used\" FORMAT A14;\r\nCOL \"Last Usage Date\" FORMAT A18;\r\nCOL \"Last Sample Date\" FORMAT A18;\r\nCOL \"Host Name\" FORMAT A30;\r\n\r\n\r\nwith features as(\r\nselect a OPTIONS, b NAME from\r\n(\r\nselect 'Active Data Guard' a, 'Active Data Guard - Real-Time Query on Physical Standby' b from dual\r\nunion all \r\nselect 'Advanced Compression', 'HeapCompression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup BZIP2 Compression' from dual\r\nunion all \r\nselect 'Advanced Compression', 'Backup DEFAULT Compression' from dual\r\nunion all \r\nselect 'Advanced Compression', 'Backup HIGH Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup LOW Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup MEDIUM Compression' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Backup ZLIB, Compression' from dual\r\nunion all \r\nselect 'Advanced Compression', 'SecureFile Compression (user)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'SecureFile Deduplication (user)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Data Guard' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Oracle Utility Datapump (Export)' from dual\r\nunion all\r\nselect 'Advanced Compression', 'Oracle Utility Datapump (Import)' from dual\r\nunion all\r\nselect 'Advanced Security', 'ASO native encryption and checksumming' from dual\r\nunion all\r\nselect 'Advanced Security', 'Transparent Data Encryption' from dual\r\nunion all\r\nselect 'Advanced Security', 'Encrypted Tablespaces' from dual\r\nunion all\r\nselect 'Advanced Security', 'Backup Encryption' from dual\r\nunion all\r\nselect 'Advanced Security', 'SecureFile Encryption (user)' from dual\r\nunion all\r\nselect 'Change Management Pack (GC)', 'Change Management Pack (GC)' from dual\r\nunion all\r\nselect 'Data Masking Pack', 'Data Masking Pack (GC)' from dual\r\nunion all\r\nselect 'Data Mining', 'Data Mining' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Diagnostic Pack' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'ADDM' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Baseline' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Baseline Template' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'AWR Report' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Baseline Adaptive Thresholds' from dual\r\nunion all\r\nselect 'Diagnostic Pack', 'Baseline Static Computations' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Tuning Pack' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Real-Time SQL Monitoring' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Tuning Advisor' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Access Advisor' from dual\r\nunion all\r\nselect 'Tuning Pack', 'SQL Profile' from dual\r\nunion all\r\nselect 'Tuning Pack', 'Automatic SQL Tuning Advisor' from dual\r\nunion all\r\nselect 'Database Vault', 'Oracle Database Vault' from dual\r\nunion all\r\nselect 'WebLogic Server Management Pack Enterprise Edition', 'EM AS Provisioning and Patch Automation (GC)' from dual\r\nunion all\r\nselect 'Configuration Management Pack for Oracle Database', 'EM Config Management Pack (GC)' from dual\r\nunion all\r\nselect 'Provisioning and Patch Automation Pack for Database', 'EM Database Provisioning and Patch Automation (GC)' from dual\r\nunion all\r\nselect 'Provisioning and Patch Automation Pack', 'EM Standalone Provisioning and Patch Automation Pack (GC)' from dual\r\nunion all\r\nselect 'Exadata', 'Exadata' from dual\r\nunion all\r\nselect 'Label Security', 'Label Security' from dual\r\nunion all\r\nselect 'OLAP', 'OLAP - Analytic Workspaces' from dual\r\nunion all\r\nselect 'Partitioning', 'Partitioning (user)' from dual\r\nunion all\r\nselect 'Real Application Clusters', 'Real Application Clusters (RAC)' from dual\r\nunion all\r\nselect 'Real Application Testing', 'Database Replay: Workload Capture' from dual\r\nunion all\r\nselect 'Real Application Testing', 'Database Replay: Workload Replay' from dual\r\nunion all\r\nselect 'Real Application Testing', 'SQL Performance Analyzer' from dual\r\nunion all\r\nselect 'Spatial' ,'Spatial (Not used because this does not differential usage of spatial over locator, which is free)' from dual\r\nunion all\r\nselect 'Total Recall', 'Flashback Data Archive' from dual\r\n)\r\n)\r\nselect \r\nt.o \"Option\/Management Pack\",\r\nt.u \"Used\",\r\nt.n \"Feature being Used\",\r\nt.v \"Version\",\r\nt.cu \"Currently Used\",\r\nt.du \"Detected Usage\",\r\nt.lud \"Last Usage Date\",\r\nt.ts \"Total Samples\",\r\nt.lsd \"Last Sample Date\",\r\nd.DBID \"DBID\",\r\nd.name \"DB Name\",\r\ni.version \"Curr DB Version\",\r\ni.host_name \"Host Name\",\r\nto_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') \"ReportGen Time\"\r\nfrom (\r\nselect f.OPTIONS o, \r\n'YES' u,\r\nf_stat.version v,\r\ncase when f_stat.name in ('Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') then 'Data Pump Compression'\r\nwhen f_stat.name in ('Data Guard') then 'Data Guard Network Compression'\r\nelse f_stat.name \r\nend n,\r\nf_stat.CURRENTLY_USED cu,\r\n(f_stat.DETECTED_USAGES) du,\r\nto_char(f_stat.LAST_USAGE_DATE, 'DD-MON-YY HH24:MI:SS') lud,\r\n(f_stat.TOTAL_SAMPLES) ts,\r\nto_char(f_stat.LAST_SAMPLE_DATE, 'DD-MON-YY HH24:MI:SS') lsd\r\nfrom features f,\r\nsys.dba_feature_usage_statistics f_stat\r\nwhere f.name = f_stat.name and\r\n( (f_stat.currently_used = 'TRUE' and\r\nf_stat.detected_usages &gt; 0 and\r\n(sysdate - f_stat.last_usage_date) &lt; 366 and\r\nf_stat.total_samples &gt; 0\r\n)\r\nor \r\n(f_stat.detected_usages &gt; 0 and\r\n(sysdate - f_stat.last_usage_date) &lt; 366 and \r\nf_stat.total_samples &gt; 0)\r\n) and\r\n( f_stat.name not in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)')\r\nor\r\n(f_stat.name in('Data Guard', 'Oracle Utility Datapump (Export)', 'Oracle Utility Datapump (Import)') and\r\nf_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')\r\n)\r\n) t,\r\nv$instance i,\r\nv$database d \r\norder by t.o,t.n,t.v\r\n;<\/pre>\n<p>\uc624\ub77c\ud074 DB \ub0b4\uc5d0 \uc635\uc158 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc5ed\uc744 \ud655\uc778 \ud558\ub294 \ucffc\ub9ac<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\uc624\ub77c\ud074 DB \ub0b4\uc5d0 \uc635\uc158 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\ub294 \ub0b4\uc5ed\uc744 \ud655\uc778 \ud558\ub294 \ucffc\ub9ac prompt\u00a0 Oracle Database Option Usage prompt _______________________________________________________ prompt COL &#8220;Host Name&#8221; FORMAT A30; COL &#8220;Option\/Management Pack&#8221; FORMAT A60; COL &#8220;Used&#8221; FORMAT A5; with features as( select a OPTIONS, b NAME from ( select &#8216;Active Data Guard&#8217; a, &#8216;Active Data Guard &#8211; Real-Time Query on Physical [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":4141,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[21],"tags":[1023,1024],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/08\/oracletips.jpg?fit=650%2C361","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4162"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4162"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4162\/revisions"}],"predecessor-version":[{"id":4163,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4162\/revisions\/4163"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/4141"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4162"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}