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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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_li
WHERE data_type = 'VARCHAR2' AND owner = 'YOUR_SCHEMA' AND table_name IN ('YOUR_TABLE1', 'YOUR_TABLE2', 'ETC');
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_li
WHERE data_type = 'VARCHAR2' AND owner = 'YOUR_SCHEMA' AND table_name IN ('YOUR_TABLE1', 'YOUR_TABLE2', 'ETC');
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.)
(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.]
Premium Content
You need an Expert Office subscription to comment.Start Free Trial