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
LVL 1
jxbmaSoftware ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Increasing a varchar2 size can be done live:

alter table foo modify details varchar2(4000);
0
slightwv (䄆 Netminder) Commented:
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.
0
jxbmaSoftware ConsultantAuthor Commented:
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
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

slightwv (䄆 Netminder) Commented:
Again, increasing column widths can be done live without data loss.

Just try it on a test database:

create table tab1(col1 varcahr2(1));
insert into tab1 values('a');
commit;
alter table tab1 modify col1 varchar2(4000);
select * from tab1;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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));
0
slightwv (䄆 Netminder) Commented:
>>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.
0
awking00Information Technology SpecialistCommented:
>>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.
0
jxbmaSoftware ConsultantAuthor Commented:
Thanks for the quick response.

JohnB
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.