how to alter table columns:
Zahid Khan
create table cmpd (
cmpd_formula varchar2(20),
ca_number varchar2(10));
But now I want to change the name of the field ca_number to ca_no How do
I do this?
2.] I have the cmpd_formula field declared as a primary key. But now
I've decided that I don't want it to be the primary key. How do I make
this change?
TIA
--zahid
TurkBear
Unless you are using 8i, you cannot....
The way I would handle it is to create a new table with the column names you
want, and populate it with a select * from cmpd...[ if the same # of columns ]
you can then drop cmpd and rename your new table to cmpd...
Hope it helps,
Zahid Khan <za...@cc.gatech.edu> wrote:
-----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
http://www.newsfeeds.com The Largest Usenet Servers in the World!
------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----
Zahid Khan
TurkBear wrote:
> Unless you are using 8i, you cannot....
>
I am using 8i. Is there anyway to do it then?
--
Zahid A. Khan
CS Junior | ChemE Computer Assistant
ICQ UIN: 4103995 | Phone:(404) 892 4886
TurkBear
>
>
>TurkBear wrote:
>
>> Unless you are using 8i, you cannot....
>>
>
>I am using 8i. Is there anyway to do it then?
I was afraid you would say that....:-)
I have heard that this is possible in 8i -- Perhaps Thomas Kyte is reading
this and will reply with more info...( He knows everything.... :-) )
Sorry...
Jonathan Lewis
1: Best strategy - rename the table,
create view cmpd as select renamed column list from renamed table
Alternative:
create new_table unrecoverable as select renamed column list from
cmpd
rename cmpd to backup_cmpd
rename new_table to cmpd
Oracle 8i option:
alter table cmpd add column .....
update cmpd set new_column = old_column
alter table cmpd drop column ....
2: Alter table cmpd drop primary key;
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Zahid Khan wrote in message <37B05ED7...@cc.gatech.edu>...
Thomas Kyte
(if that email address didn't require changing)
On Wed, 11 Aug 1999 14:04:06 GMT, you wrote:
>Zahid Khan <za...@cc.gatech.edu> wrote:
>
>>
>>
>>TurkBear wrote:
>>
>>> Unless you are using 8i, you cannot....
>>>
>>
>>I am using 8i. Is there anyway to do it then?
>
> I was afraid you would say that....:-)
>
>I have heard that this is possible in 8i -- Perhaps Thomas Kyte is reading
>this and will reply with more info...( He knows everything.... :-) )
>
the original questions were:
1.] I have a table declared as :
create table cmpd (
cmpd_formula varchar2(20),
ca_number varchar2(10));
But now I want to change the name of the field ca_number to ca_no How do
I do this?
2.] I have the cmpd_formula field declared as a primary key. But now
I've decided that I don't want it to be the primary key. How do I make
this change?
1) you cannot in any version of Oracle rename a column. In Oracle8i, release
8.1 you can drop a column so I suppose the steps of:
SQL> desc cmpd
Name Null? Type
------------------------------------ -------- -------------------------
CMPD_FORMULA VARCHAR2(20)
CA_NUMBER VARCHAR2(10)
SQL>
SQL> alter table cmpd add ca_no varchar2(10);
Table altered.
SQL> update cmpd set ca_no = ca_number;
(n) rows updated.
SQL> alter table cmpd drop column ca_number;
Table altered.
SQL>
SQL> desc cmpd
Name Null? Type
------------------------------------ -------- -------------------------
CMPD_FORMULA VARCHAR2(20)
CA_NO VARCHAR2(10)
would do it. I would suggest tho that a view is a more efficient way to do this
(lots less io's on a big table).
SQL> desc cmpd
Name Null? Type
------------------------------------ -------- -------------------------
CMPD_FORMULA VARCHAR2(20)
CA_NUMBER VARCHAR2(10)
SQL>
SQL> rename cmpd to cmpd_table
2 /
Table renamed.
SQL> create or replace view cmpd ( cmpd_formula, ca_no )
2 as
3 select cmpd_formula, ca_number from cmpd_table
4 /
View created.
SQL>
SQL> desc cmpd
Name Null? Type
------------------------------------ -------- -------------------------
CMPD_FORMULA VARCHAR2(20)
CA_NO VARCHAR2(10)
2) for number 2 it is a simple matter of dropping the primary key constraint
(any release of Oracle)
SQL> create table cmpd (
2 cmpd_formula varchar2(20) primary key,
3 ca_number varchar2(10));
Table created.
SQL> REM this gets the name of the primary key constraint on our table
SQL> select constraint_name from user_constraints where table_name = 'CMPD'
2 and CONSTRAINT_TYPE = 'P'
3 /
CONSTRAINT_NAME
------------------------------
SYS_C007528
SQL> alter table cmpd drop constraint SYS_C007528;
Table altered.
>Sorry...
>
>
>
>
> -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
> http://www.newsfeeds.com The Largest Usenet Servers in the World!
>------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Rauno Seppanen
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
What do you think about updateing sys.col$ ?
You need to flush (or shutdown and restart database)
to see the change of the column. I did't once and it has worked fine.
SQL> show user
user is "SYSTEM"
SQL> desc emp
Name Null? Type
------------------------------- -------- ----
EMPNO NOT NULL NUMBER(4)
EMPLOYEE_NAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SALARY NUMBER(7,2)
COMMISSION NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc sys.obj$
Name Null? Type
------------------------------- -------- ----
OBJ# NOT NULL NUMBER
OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
NAMESPACE NOT NULL NUMBER
TYPE NOT NULL NUMBER
CTIME NOT NULL DATE
MTIME NOT NULL DATE
STIME NOT NULL DATE
STATUS NOT NULL NUMBER
REMOTEOWNER VARCHAR2(30)
LINKNAME VARCHAR2(128)
SQL> desc sys.col$
Name Null? Type
------------------------------- -------- ----
OBJ# NOT NULL NUMBER
COL# NOT NULL NUMBER
SEGCOL# NOT NULL NUMBER
SEGCOLLENGTH NOT NULL NUMBER
OFFSET NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
FIXEDSTORAGE NOT NULL NUMBER
PRECISION NUMBER
SCALE NUMBER
NULL$ NOT NULL NUMBER
DISTCNT NUMBER
LOWVAL RAW(32)
HIVAL RAW(32)
DEFLENGTH NUMBER
DEFAULT$ LONG
SPARE2 NUMBER
SPARE3 NUMBER
UPDATE sys.col$
SET name = 'COMMISSION'
WHERE obj# = (SELECT obj# FROM sys.obj$
WHERE name = 'EMP'
AND owner# = 8)
AND name = 'COMM';
1 rows updated.
SQL> commit
2 /
Commit complete.
Rauno
Thomas Kyte
(if that email address didn't require changing)
>>1) you cannot in any version of Oracle rename a column. In Oracle8i,
>release
>>8.1 you can drop a column so I suppose the steps of:
[snip]
>
>What do you think about updateing sys.col$ ?
I think it is a hugely *BAD* terrible idea. Words cannot describe how bad it
could be.
>You need to flush (or shutdown and restart database)
>to see the change of the column. I did't once and it has worked fine.
you have procedures that should be invalid (but aren't) out there. they could
and will raise internal errors (these procedures that reference this old column
*should* work but they won't and the system won't understand why)
you have views that should be invalid (but aren't) out there.... same as above.
I see lots of internal errors in your future.
you have constraints that reference this now non-existent column. they can and
will raise internal errors.
don't even want to think what this would do to replication.
who knows what hidden side effects may occur. I know of real side effects, some
above, but that is not an inclusive list.
bottom line -- do it and you are on you own.
[snip]
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Rauno Seppanen
>
it
>could be.
could
>and will raise internal errors (these procedures that reference this old
column
>*should* work but they won't and the system won't understand why)
>you have views that should be invalid (but aren't) out there.... same as
above.
>I see lots of internal errors in your future.
>you have constraints that reference this now non-existent column. they can
and
>will raise internal errors.
>don't even want to think what this would do to replication.
>who knows what hidden side effects may occur. I know of real side effects,
some
>above, but that is not an inclusive list.
>Thomas Kyte tk...@us.oracle.com
>Oracle Service Industries Reston, VA USA
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
I believe those are the reasons why this shouldn't be used in production
area but in
development stage it can be useful. If developing is well organized and you
can keep
track whose is doing what then this change is possible and sometimes useful.
It happens
that in development stage some columns are thought useful and added into
tables but later
(before production) the architechture is changed and these new columns
become useless
(and other kind of columns could be needed). But I wouldn't recommend this
to anyone else
since it isn't Oracle's way and we are using their product so there I
totally agree with you
Thomas. Just wanted a pro's opinion so thanks for your answer.
Rauno
owais...@my-deja.com
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote:
>
> 1: Best strategy - rename the table,
> create view cmpd as select renamed column list from renamed
table
>
> Alternative:
> create new_table unrecoverable as select renamed column list
from
> cmpd
> rename cmpd to backup_cmpd
> rename new_table to cmpd
>
> Oracle 8i option:
> alter table cmpd add column .....
> update cmpd set new_column = old_column
> alter table cmpd drop column ....
>
> 2: Alter table cmpd drop primary key;
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Zahid Khan wrote in message <37B05ED7...@cc.gatech.edu>...
> >
> >create table cmpd (
> >cmpd_formula varchar2(20),
> >ca_number varchar2(10));
> >
> >But now I want to change the name of the field ca_number to ca_no How
do
> >I do this?
> >
> >2.] I have the cmpd_formula field declared as a primary key. But now
> >I've decided that I don't want it to be the primary key. How do I
make
> >this change?
> >
> >--zahid
> >
>
>
create new_table unrecoverable as select renamed column list
from cmpd
rename cmpd to backup_cmpd
rename new_table to cmpd
I have a question regarding this strategy.
What if the original table cmpd is being referenced in a number of other
tables? As far as I know, the create as command does not propagate the
references. And this necessarily means that we have to manually
re-create all the references for the original cmpd table.
By the way, does Oracle have some ideological reservations against
providing a command for renaming a column...
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Jonathan Lewis
This is what makes casual restructuring
of the data very painful. You are quite right,
of course, the new table has nothing to do
with your original references. (Which is
why I prefer to do everything with views).
Since you were also asking about dropping
the primary key anyway, I had assumed that
such references would not be such a problem.
Thomas Kyte in one of his notes in this
thread pointed out the main reason why
renaming a column is not a trivial problem:
in particular, you may have written PL/SQL code
which addresses the column by name - whilst Oracle
could perhaps modify the tokenised version of the
PL/SQL, full maintenance of integrity would require
some sort of free-text search for the column name
in the PL/SQL source code stored in the database ....
The problem of changing the name of a column
is much larger, with many more side-effects, than
you might think at first.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
fumi
Rauno Seppanen <rauno.s...@icon.fi> wrote in message
news:nUjs3.268$yL6...@read2.inet.fi...
>
> What do you think about updateing sys.col$ ?
> You need to flush (or shutdown and restart database)
> to see the change of the column. I did't once and it has worked fine.
>
> SET name = 'COMMISSION'
> WHERE obj# = (SELECT obj# FROM sys.obj$
> WHERE name = 'EMP'
> AND owner# = 8)
> AND name = 'COMM';
>
> 1 rows updated.
> SQL> commit
> 2 /
> Commit complete.
I think it's a evildoing.
I've heard about it and tested for a while.
Although you can see the column name changed when you issue "describe" command,
it does NOT mean that the internal data would be consistent.
In fact, what you did is to destroy the consistency of internal structure,
and may cause a disaster.
For example, to create a trigger about the "new" column name,
it may cause a internal error when Oracle trying to compile the trigger.
IMHO, after you do it, the only possible way to correct the inconsistency is
to restart Oracle immediately.
But it's NOT recommended.
Chris Hamilton
<rauno.s...@icon.fi> wrote:
>I believe those are the reasons why this shouldn't be used in production
>area but in
>development stage it can be useful. If developing is well organized and you
>can keep
>track whose is doing what then this change is possible and sometimes useful.
I did it once on a development server just to see what would happen.
None of my procedures that referenced the altered table would work
after that, even after recompiling, shutdown, flush, etc.
Chris
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Christopher Hamilton
Oracle DBA -- Wall Street Sports
ch...@wallstreetsports.com
http://www.wallstreetsports.com/