Solved

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

Posted on 2012-04-13
8
632 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
  • 4
  • 2
  • 2
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Increasing a varchar2 size can be done live:

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

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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 31

Expert Comment

by:awking00
Comment Utility
>>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
Comment Utility
Thanks for the quick response.

JohnB
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now