jxbma
asked on
How do I alter a Table definition but preserve data on a live ORACLE DB?
Hi:
I'm a relative newbie to the wonderful world of ORACLE.
I've got a table definition:
=====================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(1000 BYTE) NOT NULL ENABLE
)
I want to change it to the following:
========================== ==
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(4000 BYTE) NOT NULL ENABLE
)
The table is in an ORACLE DB with live customer data. So, I need to alter the table defiinition and preserve the existing data. The most obvious way I can think of is to create a copy of the existing table (definition & data); truncate the existing table, alter the table and copy the data back.
Is there a more elegante/ORACLE standard practices way of doing this?
Thanks in advance for your help,
JohnB
I'm a relative newbie to the wonderful world of ORACLE.
I've got a table definition:
=====================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(1000 BYTE) NOT NULL ENABLE
)
I want to change it to the following:
==========================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(4000 BYTE) NOT NULL ENABLE
)
The table is in an ORACLE DB with live customer data. So, I need to alter the table defiinition and preserve the existing data. The most obvious way I can think of is to create a copy of the existing table (definition & data); truncate the existing table, alter the table and copy the data back.
Is there a more elegante/ORACLE standard practices way of doing this?
Thanks in advance for your help,
JohnB
I shoudl also add that if your database is running a multi-byte character set you might not be able to use "4000 BYTE". "2000 BYTE" might be the max.
ASKER
My mistake in the original post:
The original table definition is:
========================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"DETAILS" NVARCHAR2(1000 BYTE) NOT NULL ENABLE
)
I want to change it to the following:
========================== ==
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(4000 BYTE) NOT NULL ENABLE
)
Thanks,
JohnB
The original table definition is:
========================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"DETAILS" NVARCHAR2(1000 BYTE) NOT NULL ENABLE
)
I want to change it to the following:
==========================
TABLE "FOO"
(
"USER_ID" NUMBER NOT NULL ENABLE,
"UPDATE_DATETIME" TIMESTAMP (6),
"SOURCE" VARCHAR2(500 BYTE) NOT NULL ENABLE,
"DETAILS" VARCHAR2(4000 BYTE) NOT NULL ENABLE
)
Thanks,
JohnB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think slightwv may have missed the source modification. Just to add that and enclose in parentheses -
alter table foo modify(source varchar2(500), details varchar2(4000));
alter table foo modify(source varchar2(500), details varchar2(4000));
>>think slightwv may have missed the source modification
Didn't miss it. Just figured it was self-explainitory since both columns are varchar2, there is no problem extending both.
Didn't miss it. Just figured it was self-explainitory since both columns are varchar2, there is no problem extending both.
>>Just figured it was self-explainitory since both columns are varchar2<<
That's what I figured and it should be self_explanatory. It's just that not knowing their experience level, unfortunately some askers are looking for an exact solution to their questions and not necessarily concerned with how (and I'm not saying that's the case here, jxbma). Please, no points.
That's what I figured and it should be self_explanatory. It's just that not knowing their experience level, unfortunately some askers are looking for an exact solution to their questions and not necessarily concerned with how (and I'm not saying that's the case here, jxbma). Please, no points.
ASKER
Thanks for the quick response.
JohnB
JohnB
alter table foo modify details varchar2(4000);