Printable View
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!!!!!
You can change certain datatypes in Oracle 7.3.4 providing the column is null, or that data in it doesn't conflict with the new datatype - e.g char(2) to varchar(2) would be ok.
If you want to totally drop a column or alter it's datatype, then I'm afraid it's script time.
I think it's easier to disable the constraints, do whatever you want and then reenable them again, this is better then dropping all the schema,....
if you want to remove a solumn from a table do the following :
1- create a new table without constraints, and without the filed you want to remove.
2- do the following insert :
insert into new_table select field1,field2,,,,, from old_table
3- drop table old_table;
4- rename new_table to original_name;
5- create constraints and triggers on the table.
lengthy procedure but, it's the only one available to delete a column.
SURPRISE :
if you want to rename a column, there is one way to do it. I don't suggest that you do it on a production environment, do it only on development environment, I think that after going production you don't need to rename columns.
the procedure is :
select object_id
into MY_OBJ_NUMBER
from dba_objects
where object_type='TABLE'
and object_name='MY_TABLE'
and owner='ME'
select col#
MY_COL_NUMBER
from col$
where obj#=MY_OBJ_NUMBER
and name='OLD_NAME'
update col$
set name='NEW_NAME'
where col#=MY_COL_NUMBER
and obj# = MY_OBJ_NUMBER
do you beleive it ?
I didn't beleive until I tested it.