 asked on Modify Oracle Table columns datatype:=
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)
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)
* Oracle PL/SQLOracle Database
Last Comment
Mark Geerlings
SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionAll of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck