오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리

Byhaisins

10월 10, 2018 lms, oracle lms

오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리

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 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

DB 설치 & 구축

LINUX RHEL 8.4 에서 Oracle 19c ASM 구성시 필요한 ASMLIB 패키지

11월 5, 2021 haisins

DB 설치 & 구축

ASM DISK 에서 AU size 와 DATA FILE 의 최대 크기

11월 4, 2021 haisins

DB 설치 & 구축

이기종 마이그레이션 TOOL

11월 3, 2021 haisins

13 thoughts on “오라클 DB 내에 옵션 기능을 사용하는 내역을 확인 하는 쿼리”

Johna699 말해보세요:

2019년 1월 12일, 4:27 오후

Link exchange is nothing else but it is just placing the other persons weblog link on your page at proper place and other person will also do similar in support of you. kkceddekbabd

답변하기
Johne865 말해보세요:

2019년 1월 12일, 4:27 오후

Today, while I was at work, my sister stole my apple ipad and tested to see if it can survive a 30 foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views. I know this is entirely off topic but I had to share it with someone! fkbabgddebbc

답변하기
Smithc663 말해보세요:

2019년 1월 12일, 4:28 오후

Attractive section of content. I just stumbled upon your website and in accession capital to assert that I get in fact enjoyed account your blog posts. Any way Ill be subscribing to your feeds and even I achievement you access consistently rapidly. kdeeffdccacdkeeg

답변하기
Johnk803 말해보세요:

2019년 1월 12일, 4:29 오후

I got this website from my friend who told me concerning this web site and at the moment this time I am visiting this web page and reading very informative content here. gdbgfggkcdge

답변하기
Johnf706 말해보세요:

2019년 1월 12일, 4:30 오후

I just like the helpful info you supply on your articles. I will bookmark your blog and take a look at once more here regularly. I’m somewhat sure Ill learn a lot of new stuff right right here! Best of luck for the following! cefdebfdfakf

답변하기
Johnd504 말해보세요:

2019년 1월 13일, 6:05 오후

Fantastic website. A lot of useful information here. I’m sending it to a few pals ans also sharing in delicious. And naturally, thank you in your sweat! egdcdbdkdgdd

답변하기
Johnd371 말해보세요:

2019년 1월 13일, 6:05 오후

You made some really good points there. I checked on the internet for additional information about the issue and found most people will go along with your views on this site. edkfbdgcdebg

답변하기
Smithb219 말해보세요:

2019년 1월 13일, 6:06 오후

Only wanna tell that this is very useful , Thanks for taking your time to write this. ckeckcegfdgegckd

답변하기
Johnc582 말해보세요:

2019년 1월 13일, 6:07 오후

location within my public complexes! ekcabddegfff

답변하기
Johnd602 말해보세요:

2019년 1월 13일, 6:07 오후

Fckin awesome things here. I am very glad to see your article. Thanks a lot and i’m looking forward to contact you. Will you please drop me a mail? eagedkfdaekg

답변하기
Smithc476 말해보세요:

2019년 1월 13일, 6:08 오후

I really enjoy reading on this website, it holds great articles. Don’t put too fine a point to your wit for fear it should get blunted. by Miguel de Cervantes. dffecafgdaaegeee

답변하기
Woodrow 말해보세요:

2019년 6월 15일, 7:52 오전

Hi to every body, it’s my first pay a visit of this webpage; this web site carries awesome
and genuinely excellent information for visitors.

답변하기
ThogssedEmeds 말해보세요:

2019년 12월 9일, 8:50 오전

I chisel been devoid for a instance, but now I retrieve why I utilized to drinkable this leger. Convey you, I talent try and insure rearwards pol oft. How ofttimes you update your website?

답변하기