CODEWITHSUNDEEP
sqloracle-databaseplsqldisk-partitioning
sarat
sarat

Reputation: 41

Concatenating SQL select statements with in operator for ASM Disk Groups

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?

Upvotes: 2

Views: 408

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

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;

Upvotes: 1

Peter
Peter

Reputation: 1196

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;

Upvotes: 0

Related Questions