OraFAQ Forum: Server Administration » Alter table multiple

Home » RDBMS Server » Server Administration » Alter table multiple

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alter table multiple [message #189956]

Mon, 28 August 2006 13:21

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

Report message to a moderator

Re: Alter table multiple [message #189957 is a reply to message #189956]

Mon, 28 August 2006 13:47

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;

Report message to a moderator

Re: Alter table multiple [message #189959 is a reply to message #189957]

Mon, 28 August 2006 13:53

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

Report message to a moderator

Re: Alter table multiple [message #189966 is a reply to message #189959]

Mon, 28 August 2006 14:20

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

Report message to a moderator

Re: Alter table multiple [message #189974 is a reply to message #189966]

Mon, 28 August 2006 15:39

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Thanks Ram

I believed that it was more difficult.

Greetings
Alex

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 10:29:19 CDT 2024