Convert oracle column value into rows(and the reverse, rows to col)

Sometime I need to convert a col value(separated with some specific character(like :)) into rows:

Image that you have a column feeded by an Apex LOV, when user choose multiple values from the LOV, the return string will be a colon separated string. Later on you may need to convert this string into multiple rows so that you can put them back into some of your sql queries.

Here is the sql I used  for this:

  select server_name||'' aa from
  (
    with t as (select 'AA:NN:CC:DD:WW' server_list from dual)
         SELECT EXTRACT(column_value,'/e/text()') server_name from t x,
         TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<ROW><e>'||REPLACE(server_list,':','</e><e>')||'</e></ROW>'),'//e')))
  )

Replace the sql in red with your own table and columns.
If your separator is something else other than colon, you can simply replace the colon in the sql with your separator.

Here  is the reverse operation(convert rows to columns):

select 
   rtrim (xmlagg (xmlelement (e, wwid||'@xyz.com'|| ',')).extract ('//text()'), ',') email
from 
   majorevent_admin
where role='Admin'

where majorevent_admin is the table, one of the Col is people's WWID. another col is Role.

Exadata ASM usage

Here is the sql I used to get the ExaData ASM usage:

  select 'Exadata', round(TOTAL_MB/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024) total_GB,
      round(usable_FILE_mb/1024) usable_free_GB,
      round((HOT_USED_MB+COLD_USED_MB)/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024)
      data_use_GB,    
      round(REQUIRED_MIRROR_FREE_MB/decode(type,''NORMAL'',2,''HIGH'',3,1)/1024)
      Req_free_redun_GB,
      ROUND(((HOT_USED_MB+COLD_USED_MB)/total_MB)*100) perc,sysdate
      from v$asm_diskgroup where total_mb != 0'

Since we have multiple databases in several of our ExaData machines, I will need to write a script to run this against all of our databases.

ExaData/Exalogic resource usage(CPU/MEM/Storage) from OEM

ExaLogic/ExaData  CPU/Mem/Storage Usage (By Node) data:

Here is the sql I used to get the Exa Node CPU/Mem/Storage usage data from our OEM repo. We have several ExaData/ExaLogic machines in house and I was asked to get these data automatically, took me a while to get here.

select decode(Instr(Entity_Name,'exad'),0,'ExaLogic','ExaData') Catelog,substr(Entity_Name,1,9) Machine,Entity_Name node_name,
case Metric_Column_Label
       When 'CPU Utilization (%)' Then 'CPU'
       When 'Physical Memory Utilization (%)' Then 'Memory'
       When 'Memory Utilization (%)' then 'Memory'
       --when 'Total Disk Utilized (%) (across all local filesystems)' then 'Local FS'
        else 'Storage'
end case,
COLLECTION_TIME,round(Avg_Value,2) Avg_Usage
From sysman.GC$metric_Values_Daily
Where Entity_Type In( 'oracle_vm_server','host')
         And ((Substr(Entity_Name,4,4) ='exal'
         And Instr(Entity_Name,'-elc')=0) or Substr(Entity_Name,4,4) ='exad')
         And Metric_Column_Label in('CPU Utilization (%)',
                'Physical Memory Utilization (%)','Memory Utilization (%)',
                 'Used Space (%)')
--,'Total Disk Utilized (%) (across all local filesystems)')
and collection_time>=sysdate-15  and round(Avg_Value,2)>0;

Several notes:

  1. The above sql doesn't return Exadata ASM usages, I couldn't find those data anywhere in OEM and I now believe OEM doesn't have them. I ended up go to our Database instance directly to get them

  2. The ExaLogic Node Storage data is a little difference with the data showing in our Ops Center,  The difference is minor, don't know why but someone from Oracle told me that OEM data is just fine.

Exalogic capacity Planning

Exalogic capacity Planning: how to get the # of cpu/memory of your vServers in Exalogic:

We have several Oracle Exalogic/Exadata machines in house, one of my recent project is to get the cpu/memory number allocated to each vServer we created in all our Exalogic machines. I know these data are somewhere available in our OEM repo but I couldn't find any official document. Took me a while to figure it out but in the end, it's pretty cool considering I can pull all these data into one place(I create an Apex report for this) so they can see how our Exalogic Machines are using.

Below is the sql I was using, keep in mind that our Exalogic Node/vServer has a name convention(we name it exalxxxx and that's part of the logic of my sql, your may be difference.

CREATE OR REPLACE FORCE VIEW "EXA_CPU_MEM" ("HOST", "SERVER_NAME", "CPU", "MEM") AS
  select substr(a.host_name,1,instr(a.host_name,'-')-1) host,a.target_name
  server_name,to_char(b.cpu_count) CPU,to_char(b.alloc_mem_mb/1024) MEM
from  MGMT$VT_VM_CONFIG b,
(
    select host_name,target_name,target_guid from mgmt$target where host_name
    in
    (select target_name from mgmt$target where instr(target_name,'exal')!=0 and target_type='host')
and target_type='oracle_vm_guest'
and instr(target_name,'zsl')!=0
) a
where
a.target_guid=b.target_guid

Change Apex 5 Interactive Report Column Width

Here is how to change the column width on the Apex 5 Interactive Report:

1. Give a static id for the report: for example, in the Static ID field, say DD
2. Give a static id for the column you want to change width: for example, give it RR
3. Repeat 2 if you have multiple columns you want to change the size
3. in the page inline section, put this in

    #DD td[headers=RR]{min-width:300px}
    #DD td[headers=TT]{min-width:200px}
    #DD td[headers=YY]{min-width:200px}

 in this case, I wanted to change 3 columns

Thant's it!