dropping a column in a production database

[yossarian]

Is this a safe operation? According to the docs, it should be. Any
contraindications?

(Oracle 10.2.0.3 on SUSE Linux)

Thank you. Kind regards, Y.

[Ed Prochak]

when was the last time you tested your backup and restore processes?

It is safe in the sense that it will do exactly what you ask. Any data
in that dropped column will be gone (forever if you do not have it in
a backup). Note that any applications that used that column will need
to be rewritten. Packages, functions and procedures dependent on that
column will become invalid and need to be rewritten. It's all standard
stuff.

HTH,
ed

[yossarian]

Ed Prochak wrote:

> It is safe in the sense that it will do exactly what you ask. Any data
> in that dropped column will be gone (forever if you do not have it in
> a backup). Note that any applications that used that column will need
> to be rewritten. Packages, functions and procedures dependent on that
> column will become invalid and need to be rewritten. It's all standard
> stuff.

OK, thank you. I was just wondering about unexpected side effects.

Kind regards, Y.

[Frank van Bortel]

What could be unexpected?

The fact that was part of a key, used for referential
integrity of your system?

The fact that is was part of a highly selective index,
the sped up the majority of your work by a factor 100?

Nah - can't be - just told you ;)

--

Regards,
Frank van Bortel

[Mark D Powell]

As Ed said, just standard stuff. As long as you modify any code that
uses the column to no longer reference it prior to the drop column
operation you should be fine. If the table is large the drop may take
a while but since the second version after the feature was introduced
Oracle auto-commits the change however the 10g SQL manual says the
default is now 512 so you might want to change the commit frequency.

HTH -- Mark D Powell --

[Bindu]

If you are not really sure, may be you can mark the column unused
first

ALTER TABLE table_name SET UNUSED (column_name);

Then test your application and if everything works as expected, drop
the column at a later time.

[gazzag]

Presumably you can try this on a test system before applying it to
production?

HTH

-g

[yossarian]

gazzag wrote:

> Presumably you can try this on a test system before applying it to
> production?

Of course I did it. The new version of the application works fine on the
test system. I also tried to EXP and IMP the table and I didn't
encountered any problem. What other tests do you suggest?

[Mark D Powell]

I would think the testing you described is good enough.

[Ruud de Koter]

Any interfaces/links to other (database) systems?

hth

Ruud.