Modify Oracle Table columns datatype:=

Bally Jassal
Bally Jassal used Ask the Experts™
on
Modify Oracle Table columns datatype:=
We have 2 Oracle 12.2.0.0 databases with the same table names.
One of the database was modified/upgraded and all the tables columns datatype in that schema is changed. The datatype is now different in these 2 databases.
I like to modify all the columns with the modified data type. I created a database link and created a new table and copied that table to this database (as below):=

From the target database (database link is removed but the syntax used is the same)
create table MOD_TAB_DATA AS
Select C.Owner, C.Table_Name, C.Column_Name, C.Data_Type, C.Char_Length
From All_Tab_Columns C, All_Tables T
Where C.Owner = T.Owner
And C.Table_Name = T.Table_Name;

On the Source:=
create table ORG_TAB_DATA AS
Select C.Owner, C.Table_Name, C.Column_Name, C.Data_Type, C.Char_Length
From All_Tab_Columns C, All_Tables T
Where C.Owner = T.Owner
And C.Table_Name = T.Table_Name;

I like to create a script which will modify the columns in the source database for all the tables
for example:=

alter table PS_GVT_POI_TBL modify(GVT_POI VARCHAR2(4 CHAR));
alter table PS_GVT_POI_TBL modify(SETID VARCHAR2(5 CHAR));
alter table PS_GVT_POI_TBL modify(EFF_STATUS VARCHAR2(1 CHAR));

MODIFIED TABLE example:=
SQL> desc psoprdefn
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ----------------------
 OPRID                                                                               NOT NULL VARCHAR2(30 CHAR)
 USERIDALIAS                                                                         NOT NULL VARCHAR2(70 CHAR)
 VERSION                                                                             NOT NULL NUMBER(38)
 OPRDEFNDESC                                                                         NOT NULL VARCHAR2(30 CHAR)
 EMPLID                                                                              NOT NULL VARCHAR2(11 CHAR)
 EMAILID                                                                             NOT NULL VARCHAR2(70 CHAR)
 OPRCLASS                                                                            NOT NULL VARCHAR2(30 CHAR)
 ROWSECCLASS                                                                         NOT NULL VARCHAR2(30 CHAR)
 OPERPSWD                                                                            NOT NULL VARCHAR2(32 CHAR)
 PTOPERPSWDV2                                                                        NOT NULL VARCHAR2(92 CHAR)
 OPERPSWDSALT                                                                        NOT NULL VARCHAR2(88 CHAR)
 ENCRYPTED                                                                           NOT NULL NUMBER(38)
 SYMBOLICID                                                                          NOT NULL VARCHAR2(8 CHAR)
 LANGUAGE_CD                                                                         NOT NULL VARCHAR2(3 CHAR)
 MULTILANG                                                                           NOT NULL NUMBER(38)
 CURRENCY_CD                                                                         NOT NULL VARCHAR2(3 CHAR)
 LASTPSWDCHANGE                                                                      NOT NULL DATE
 ACCTLOCK                                                                            NOT NULL NUMBER(38)
 PRCSPRFLCLS                                                                         NOT NULL VARCHAR2(30 CHAR)
 DEFAULTNAVHP                                                                        NOT NULL VARCHAR2(30 CHAR)
 FAILEDLOGINS                                                                        NOT NULL NUMBER(38)
 EXPENT                                                                              NOT NULL NUMBER(38)
 OPRTYPE                                                                             NOT NULL NUMBER(38)
 LASTSIGNONDTTM                                                                               TIMESTAMP(6)
 LASTUPDDTTM                                                                                  TIMESTAMP(6)
 LASTUPDOPRID                                                                        NOT NULL VARCHAR2(30 CHAR)
 PTALLOWSWITCHUSER                                                                   NOT NULL NUMBER(38)

SQL> desc PS_GVT_POI_TBL
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------------
 SETID                                                                               NOT NULL VARCHAR2(5 CHAR)
 GVT_POI                                                                             NOT NULL VARCHAR2(4 CHAR)
 EFFDT                                                                               NOT NULL DATE
 EFF_STATUS                                                                          NOT NULL VARCHAR2(1 CHAR)
 DESCR                                                                               NOT NULL VARCHAR2(30 CHAR)
 COMPANY                                                                             NOT NULL VARCHAR2(3 CHAR)
 GVT_SUB_AGENCY                                                                      NOT NULL VARCHAR2(2 CHAR)
 GVT_AUTO_SUBM_PT                                                                    NOT NULL VARCHAR2(5 CHAR)
 GVT_OPM_REG_OFC                                                                     NOT NULL VARCHAR2(1 CHAR)
 GVT_PO_NAME                                                                         NOT NULL VARCHAR2(30 CHAR)
 GVT_PO_ADDR1                                                                        NOT NULL VARCHAR2(35 CHAR)
 GVT_PO_ADDR2                                                                        NOT NULL VARCHAR2(35 CHAR)
 GVT_PO_ADDR3                                                                        NOT NULL VARCHAR2(35 CHAR)
 GVT_PO_ADDR4                                                                        NOT NULL VARCHAR2(35 CHAR)
 GVT_PO_ADDR5                                                                        NOT NULL VARCHAR2(35 CHAR)
 LOCATION                                                                            NOT NULL VARCHAR2(10 CHAR)
 GVT_ELEC_CMRCE_ID                                                                   NOT NULL VARCHAR2(35 CHAR)
 ZIP                                                                                 NOT NULL VARCHAR2(10 CHAR)
 PHONE                                                                               NOT NULL VARCHAR2(24 CHAR)

=============================
ORIGINAL TABLE
SQL>desc psoprdefn
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ---------------
 OPRID                                                                               NOT NULL VARCHAR2(30)
 VERSION                                                                             NOT NULL NUMBER(38)
 OPRDEFNDESC                                                                         NOT NULL VARCHAR2(30)
 EMPLID                                                                              NOT NULL VARCHAR2(11)
 EMAILID                                                                             NOT NULL VARCHAR2(70)
 OPRCLASS                                                                            NOT NULL VARCHAR2(30)
 ROWSECCLASS                                                                         NOT NULL VARCHAR2(30)
 OPERPSWD                                                                            NOT NULL VARCHAR2(32)
 PTOPERPSWDV2                                                                        NOT NULL VARCHAR2(92)
 OPERPSWDSALT                                                                        NOT NULL VARCHAR2(88)
 ENCRYPTED                                                                           NOT NULL NUMBER(38)
 SYMBOLICID                                                                          NOT NULL VARCHAR2(8)
 LANGUAGE_CD                                                                         NOT NULL VARCHAR2(3)
 MULTILANG                                                                           NOT NULL NUMBER(38)
 CURRENCY_CD                                                                         NOT NULL VARCHAR2(3)
 LASTPSWDCHANGE                                                                      NOT NULL DATE
 ACCTLOCK                                                                            NOT NULL NUMBER(38)
 PRCSPRFLCLS                                                                         NOT NULL VARCHAR2(30)
 DEFAULTNAVHP                                                                        NOT NULL VARCHAR2(30)
 FAILEDLOGINS                                                                        NOT NULL NUMBER(38)
 EXPENT                                                                              NOT NULL NUMBER(38)
 OPRTYPE                                                                             NOT NULL NUMBER(38)
 USERIDALIAS                                                                         NOT NULL VARCHAR2(70)
 LASTSIGNONDTTM                                                                               TIMESTAMP(6)
 LASTUPDDTTM                                                                                  TIMESTAMP(6)
 LASTUPDOPRID                                                                        NOT NULL VARCHAR2(30)
 PTALLOWSWITCHUSER                                                                   NOT NULL NUMBER(38)
SQL>desc PS_GVT_POI_TBL
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------
 GVT_POI                                                                             NOT NULL VARCHAR2(4)
 SETID                                                                               NOT NULL VARCHAR2(5)
 EFFDT                                                                               NOT NULL DATE
 EFF_STATUS                                                                          NOT NULL VARCHAR2(1)
 DESCR                                                                               NOT NULL VARCHAR2(30)
 COMPANY                                                                             NOT NULL VARCHAR2(3)
 GVT_SUB_AGENCY                                                                      NOT NULL VARCHAR2(2)
 GVT_AUTO_SUBM_PT                                                                    NOT NULL VARCHAR2(5)
 GVT_OPM_REG_OFC                                                                     NOT NULL VARCHAR2(1)
 GVT_PO_NAME                                                                         NOT NULL VARCHAR2(30)
 GVT_PO_ADDR1                                                                        NOT NULL VARCHAR2(35)
 GVT_PO_ADDR2                                                                        NOT NULL VARCHAR2(35)
 GVT_PO_ADDR3                                                                        NOT NULL VARCHAR2(35)
 GVT_PO_ADDR4                                                                        NOT NULL VARCHAR2(35)
 GVT_PO_ADDR5                                                                        NOT NULL VARCHAR2(35)
 LOCATION                                                                            NOT NULL VARCHAR2(10)
 GVT_ELEC_CMRCE_ID                                                                   NOT NULL VARCHAR2(35)
 ZIP                                                                                 NOT NULL VARCHAR2(10)
 PHONE                                                                               NOT NULL VARCHAR2(24)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
Looks like you're just interested in modifying the VARCHAR2 fields
So you can use something like this.  Modify the filter as needed to get the tables you want.

SELECT REPLACE(
           REPLACE(
               REPLACE(
                   REPLACE('ALTER TABLE "~owner~"."~table~" MODIFY("~column~" VARCHAR2(~length~ CHAR));',
                           '~owner~',
                           owner),
                   '~table~',
                   table_name),
               '~column~',
               column_name),
           '~length~',
           char_length) mod_ddl
  FROM all_tab_columns@your_db_link
 WHERE data_type = 'VARCHAR2' AND owner = 'YOUR_SCHEMA' AND table_name IN ('YOUR_TABLE1', 'YOUR_TABLE2', 'ETC');
Database Administrator
Commented:
I'm not sure that the "replace" operators are needed in this query.  And you don't need to use a database link either.   I think this simpler query in the database where you want to modify the VARCHAR2 columns should give you the commands you need:
(This assumes you want to change all VARCHAR2...[BYTE] columns to VARCHAR2...CHAR columns.)
select 'alter table '||owner||'."'||table_name||'" modify "'||column_name
	||'" VARCHAR2('||data_length||' CHAR);' "Command"
from all_tab_columns
where data_type = 'VARCHAR2'
and [whatever other filters you may need on owner, table_name, etc.]

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial