ERROR: Unused TSTZ columns exist

[big]

Hi,
Installing E-Business R12.2.9

When runnig upg_tzv_check.sql.
I have :

ERROR: Unused TSTZ columns exist


In upg_tzv_check.sql it checks for:

select count (*) from dba_tab_cols c, DBA_UNUSED_COL_TABS o where c.data_type like ''%WITH TIME ZONE'.

What solution do you suggest?
Regards.

[ErmanArslansOracleBlog]

You didn't mentioned any details.. you got this error performing what? What stage of the upgrade are you at_

Yes..
It may be the following one also ->
 EXECUTE immediate 'select count (*) from dba_tab_cols c, DBA_UNUSED_COL_TABS o where c.data_type like ''%WITH TIME ZONE'' and c.owner=o.owner and c.table_name = o.table_name and c.HIDDEN_COLUMN = ''YES''' INTO V_CHECKNUM1 ;

What is your database version at the time that you were getting this error?
Did you check the following? ->

**

MOS Note 977512.1 for 11gR2
MOS Note 1509653.1 for 12c .

[big]

Thank you.

To upgrade original db (12.1.0.2) to 19c

We followed 1585343.1 and did:
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 28 10:11:53 2022

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> spool upg_tzv_check.log
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.1.0.2 .
INFO: Database RDBMS DST version is DSTv18 .
ERROR: Unused TSTZ columns exist. <========
ERROR: ORA-00904 will be seen during DBMS_DST.
ERROR: See the known issues section of
ERROR: note 977512.1 for 11gR2 or note 1509653.1 for 12c .
ERROR: for bug 14732853 .
DECLARE
*
ERROR at line 1:
ORA-20060: Stopping script - see previous message .....
ORA-06512: at line 285
I saw 977512.1: It does not talk about that script: upg_tzv_check.sql
And 1509653.1: it says only: Make sure to use version 1.9 or higher of the upg_tzv_check.sql and upg_tzv_apply.sql scripts.
But none of them show what to do if error : "Unused TSTZ columns exist" is encountered.
Here is the version:
 view upg_tzv_check.sql
Rem
Rem Copyright (c) 2013,2014 Oracle. All rights reserved.
Rem
Rem    NAME
Rem     upg_tzv_check.sql - time zone update check script for 11gR2 (and higher)
Rem  Version 1.9
Rem     published in note 1585343.1 Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database .

Regards.

[ErmanArslansOracleBlog]

Here is the example given in MOS ->

SQL> CONN / as sysdba
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Database version is 11.2.0.3 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: Doing checks for known issues ...
ERROR: Unused TSTZ columns exist.
ERROR: ORA-00904 will be seen during DBMS_DST.
ERROR: See the known issues section of
ERROR: note 977512.1 for 11gR2 or note 1509653.1 for 12c .
ERROR: for bug 14732853 .
declare
*
ERROR at line 1:
ORA-20060: Stopping script - see previous message .....
ORA-06512: at line 241

[ErmanArslansOracleBlog]

See known issues section / 6) Known Issues of the Mos Note -> Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 ) and above using DBMS_DST (Doc ID 1509653.1)

You may need to check, and ensure what those columns are. You "may" ( I m not sure yet..) need to drop those columns ..

Because, that MOS note says:

select U.OWNER, U.TABLE_NAME, U.COLUMN_NAME from dba_tab_cols u, DBA_UNUSED_COL_TABS o where u.data_type like '%WITH TIME ZONE' and u.owner=o.owner and u.table_name = o.table_name and u.HIDDEN_COLUMN = 'YES';
If there are (=above select give rows) drop the unused columns using "alter table owner.table drop unused columns;"
If the table is compressed see note 558630.1 Unable To Drop An Unused Column From A Compresed Table Due To ORA-12996


*****But don't take a direct action. you need to first check and be sure.. What are those columns. Are there any data in them What are the tables that those columns belong to? (Table_name.column_name)

[big]

Hi,
really thank you.
Then I ran
select U.OWNER, U.TABLE_NAME, U.COLUMN_NAME from dba_tab_cols u, DBA_UNUSED_COL_TABS o where u.data_type like '%WITH TIME ZONE' and u.owner=o.owner and u.table_name = o.table_name and u.HIDDEN_COLUMN = 'YES';

I found the table an column name and ran:

"alter table owner.table drop unused columns;"

Every thing is ok now.
Regards.