Home » RDBMS Server » Server Administration » Alter table multiple
Alter table multiple [message #189956] Mon, 28 August 2006 13:21 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi

Somebody knows if i can do:

Alter table to multiples tables with the same name_column.

Somthing like that:

Alter table table1, table2 modify field_name datatype

I´m working with oracle database 9i release 2

Thank you very much
Greetings
Alex
Re: Alter table multiple [message #189957 is a reply to message #189956] Mon, 28 August 2006 13:47 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member


Hi,

Consider I have tables like emp1, emp2, emp3, emp4
and i want a new column emp_address to all tables
try this code

declare
type my_table is table of varchar2(20);
v_table my_table;
str varchar2(100);
ctr number := 0;
begin
select tname bulk collect into v_table FROM TAB where tname LIKE 'EMP_';
FOR R IN V_TABLE.FIRST.. V_TABLE.LAST LOOP
STR := 'ALTER TABLE '||V_TABLE(R)||
' ADD ( EMP_ADDRESS VARCHAR2(50) ) ';
EXECUTE IMMEDIATE STR;
CTR := CTR + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TABLES ALTERED ARE : '||CTR);
END;
Re: Alter table multiple [message #189959 is a reply to message #189957] Mon, 28 August 2006 13:53 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

My problem is that i have 6 tables with a field called ID_PERSONA and this is a PK in all the table.

It is varchar2(17) and i want to change it to varchar2(25) in all tables. Unfortunely the tables has a lot of data. And I would like not load data again.

Thanks rampratap
Alex
Re: Alter table multiple [message #189966 is a reply to message #189959] Mon, 28 August 2006 14:20 Go to previous messageGo to next message
rampratap
Messages: 50
Registered: November 2004
Location: New Delhi
Member

Hi Alex

you can user modify clause in place of add .

like alter table emp1 modify (empno number(5));


you can not reduce the size or u can not change the data type
but very well u can increase the size without any loss of data

Ram Pratap Singh
Re: Alter table multiple [message #189974 is a reply to message #189966] Mon, 28 August 2006 15:39 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Thanks Ram

I believed that it was more difficult.

Greetings
Alex
Previous Topic: Migrate Oracle Database 10.1 to 10.2 on Solaris 64 bit
Next Topic: writing by DBWn
Goto Forum:
  


Current Time: Tue Dec 06 08:51:22 CST 2016

Total time taken to generate the page: 0.26079 seconds