ADD,DELETE,MANAGE FIELDS

[maxxx]

How add, delete or change field of table?

If i want to do it i can't do it because there are constrains which don't allow me to do it.

It can be done to delete all schema and make it newerely
using scripts, but i am iteresting is there another method to make such operation?

I know that in Oracle 8i i can do it, but does anybody
know how do it on Oracle 7.3.4???

Please, Help

It is a question of all MY LIFE!!!!!

 

[MikeLacey]

Hi Max,

Yep -- this is a right pain in 7.xx

This is the way I do it -- not pretty, but it works. Please be sure to *CHECK* that the temporary table contains all of your data *before* you drop the original.... (obvious I know, but I managed to do it the wrong way around once.)

First, - take a note of the indexes and contraints associated with the table.

-- original table, I want to drop col_2
create table test_t(
col_1 number(8),
col_2 varchar2(100),
col_3 varchar2(100)
);

create table tmp_t as select col_1, col_3 from test_t;
drop table test_t;
create table test_t as select * from tmp_t;

The re-create the indexes and constraints.

Mike
michael.j.lacey@ntlworld.com

 

[carp]

Mike -
While it's still ugly, it doesn't have to be QUITE so ugly!
I might suggest one change to your routine:

Instead of

create table tmp_t as select col_1, col_3 from test_t;
drop table test_t;
create table test_t as select * from tmp_t;

try

create table tmp_t as select col_1, col_3 from test_t;
drop table test_t;
RENAME tmp_t TO test_t;

You'll not only save a lot of time (updating one row in the data dictionary instead of creating and populating a new table), but your rollback segments and redo logs will thank you!

 

[MikeLacey]

errmmm yep <embarrassed>

Have to admit that I couldn't remember the syntax of the RENAME command.....

thx Carp
Mike
michael.j.lacey@ntlworld.com