Add column to a table for every shema in an oracle db 11g

Question

Our oracle db has various users with same schema. I would like to add a column to one table in those schemas. The same column to same table in every schema. I know that I can do that manually, like that

alter table user1.EMER_COMP add TELEFONE varchar2(20) NULL;

alter table user2.EMER_COMP add TELEFONE varchar2(20) NULL;

...

Is there an easier way to do this?

• Colin 't Hart: You can generate the SQL using a query on Oracle catalog tables. Or write a procedure that uses dynamic SQL.

Answer 1

begin
 for t in (select * from dba_tables where table_name = 'EMER_COMP')
 loop
 execute immediate 'alter table "' || t.owner || '"."' || t.table_name || '" add TELEFONE varchar2(20) NULL'; 
 end loop;
end;
/