[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How do I alter a Table definition but preserve data on a live ORACLE DB?

Posted on 2012-04-13
8
Medium Priority
?
643 Views
Last Modified: 2012-04-24
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
0
Comment
Question by:jxbma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37843009
Increasing a varchar2 size can be done live:

alter table foo modify details varchar2(4000);
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37843023
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
 
LVL 1

Author Comment

by:jxbma
ID: 37843094
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37843106
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
 
LVL 32

Expert Comment

by:awking00
ID: 37844251
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37844261
>>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
 
LVL 32

Expert Comment

by:awking00
ID: 37844575
>>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
 
LVL 1

Author Closing Comment

by:jxbma
ID: 37887729
Thanks for the quick response.

JohnB
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question