adding columns to many tables - ORACLE

[Stephen Badgett]

ORACLE - adding columns to many tables..

We have a schema "srb" that has several tables in it that we need to add columns in all the tables in a schema.

example
schema SRB

TABLE A
TABLE B
TABLE C
...
TABLE ZZZ

COLUMNS TO ADD IN EACH

NAME VARCHAR2(100)
ADDRESS VARCHAR2(100)
PHONE NUMBER


I know I can add one at a time but, We have a lot of tables and need to do this without doing it manually.

Any suggestions?

[Alex Lavrov.]

yep, 2 ways:

1. write select that generates "alter table" statements, something like:
select "alter table " || table_name ...
from dict
wher ....

spool it into some file and then execute it.

2. write pl/sql procedure that executes alter table.

But I prefer the first way.

[Stephen Badgett]

I am not sure how to do that.

[Julio Yamawaki]

Hi,

I've been an Oracle DBA for many years and believe, the first way that Alex told you is the fast and best way to do this.
Also, you can make some modifications in the script generated by select..., puting some prompt´s or dbms_output.put_line... to say what your are doing, like, "alter table xxx add column xxx number;" and spooling all output of this script to verify that everything was done.

Regards

[Stephen Badgett]

I do not doubt it in any way I just do not know how to do the syntax of the statement

[Stephen Badgett]

Here it is currently -- Are we just to add the add part later?
---------------------------------

select 'alter table ' || table_name from srb_flash
where table_name is not null;
2
'ALTERTABLE'||TABLE_NAME
--------------------------------------------------------------------------------
alter table CUSTOMER
alter table DEPT
alter table EMPLOYEE
alter table NEWTBL
alter table SRB_FLASH

[Indira Aramandla · Accepted solution]

Hi Steve,

Yes creating a script with the alter table statements will be an easy solution. Here is how you do it.

SQL> set heading off
SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

This statement will prepare alter table statements for all the tables in the schema SRB and the output will look like this :-

Alter table TABLE_A add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);
Alter table TABLE_B add (name varchar2(100), address varchar2(100), phone number);

Befor you execute the above SQL statement spool to a alter_tab.sql file. Like this

SQL> set heading off

SQL>spool alter_tab.sql

SQL> select 'Alter table '||table_name|| ' add (name varchar2(100), address varchar2(100), phone number);'
from all_tables
where owner = 'SRB';

SQL> spool off

SQL>set heading on

Now you will find the sql file alter_tab.sql created in your directory. Now to execute the created alter_tab.sql do this.

SQL>spool alter_tab_output.log
SQL>@alter_tab.sql
SQL>spool off

Now check the alter_tab_output.log file for any errors.

Indira A

[Stephen Badgett]

Perfect -- thank you lots

[Stephen Badgett]

You were all help in this and I do find this to be a great solution. Thank you for the answer

Stephen