Concatenating SQL select statements with in operator for ASM Disk Groups

Question

Apologies if this is a stupid question but I can't get my head around this. I often get request from client to drop few disks from Diskgroup.

So In this process - I want to generate a dynamic sql from v$asm_disk, v$asm_diskgroup.

SQL:

select d.path, d.FAILGROUP,d.os_mb/1024 GB
from v$asm_disk d, v$asm_diskgroup dg
where d.group_number = dg.group_number and dg.name='Test_archive'
order by DISK_GROUP_NAME;

The dynamic sql OUTPUT should look like below :

ALTER DISKGROUP Test_archive DROP DISK mnc1, mnc2, mnc3;

Could you please let me know how to achive the above output?

Barbaros Özhan · Answer 1 · 2018-11-04T08:43:46.360

1

You may use such a mechanism as below

( but handle with care for this critical and dangerous operation, before using execute immediate just message the results by dbms_output.put_line ) :

SQL> set serveroutput on;
SQL> declare
  v_command varchar2(5000);
begin
    for c in 
    ( 
      select failgroup, listagg(name, ',') within group(order by name) as disks
        from (select d.failgroup, dg.name
                from v$asm_disk d
                join v$asm_diskgroup dg
                  on (d.group_number = dg.group_number)
               where dg.name = 'Test_archive'
               group by d.failgroup, dg.name)
       group by failgroup 
    )
    loop
     begin      
         v_command := 'ALTER DISKGROUP  '||c.failgroup||' DROP DISK '||c.disks||';';
         dbms_output.put_line( v_command );
        --execute immediate v_command; --firstly comment this line out to see
                                       --whether the result as you want, then
                                       --open for the operation.
      exception when others then 
        begin
         dbms_output.put_line( sqlerrm );
        end; 
     end;  
    end loop;
end;

edited Nov 04 '18 at 08:43

answered Nov 03 '18 at 19:55

Barbaros Özhan

59,113
10
31
55

Thank you for the scrpit - But above pl/sql block will give output which i posted above? Also i want to remove "Execute immediate" as it is dangerous before verifiy. – sarat Nov 04 '18 at 04:26
@sarat welcome. Yes, Before your operation see the command by using just `dbms_output.put_line` and commenting out the line of `execute immediate v_command;`. – Barbaros Özhan Nov 04 '18 at 07:25
I am getting the output as below ALTER DISKGROUP Test_archive DROP DISK mnc1" ALTER DISKGROUP Test_archive DROP DISK mnc2" But I need output as below ALTER DISKGROUP Test_archive DROP DISK mnc1, mnc2, mnc3; It means all the disks should be associated in same command, not as separate commands. – sarat Nov 04 '18 at 07:40
@sarat now, I fixed little issues, the result for the select statement should be as you want. – Barbaros Özhan Nov 04 '18 at 07:54

score 0 · Answer 2 · answered Nov 03 '18 at 19:28

Does this do the trick for you? Let me know if you want anything explained:

select 
       'ALTER DISKGROUP Test_archive DROP DISK'
    || chr(10)
    || listagg(dg_name,','||chr(10)) within group (order by dg_name)
    ||';'
from
-- your query here, placeholder with test data:
(select 'mnc1' dg_name from dual union all select 'mnc2' from dual union all select 'mnc3' from dual)
;

Output:

ALTER DISKGROUP Test_archive DROP DISK
mnc1,
mnc2,
mnc3;

Concatenating SQL select statements with in operator for ASM Disk Groups

2 Answers2