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?

  • You can generate the SQL using a query on Oracle catalog tables. Or write a procedure that uses dynamic SQL. Commented May 24, 2018 at 15:33

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.