Oracle Application Technology

Recently while working on some upgrade activity, I faced an interesting scenario. I am supposed to create a sequence in Oracle database (11.2.0.4).

SQL>CREATE SEQUENCE "XX_VTX_INV_TAX_LINES_S"
MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1
START WITH 79002 CACHE 1000 NOORDER NOCYCLE;

Error at line 1:
ORA-955: name is already used by an existing object
Elapsed: 00:00:00.73
SQL>

I queried DBA_OBJECTS but didn’t find the object there. “adop -cleanup” phase was all completed. Database recyclebin if OFF but still went ahead and purged “recyclebin”. Still it did not let me create the sequence.

Later on while examining the “adop -cleanup” script, I came across the package ‘ad_zd_sys.drop_covered_object’. It asks few different parameters like

Object_owner
object_name
object_type
Edition_name

In order to get all these details, I ran a query

SQL> select * from dba_objects_ae where object_name like '%XX_VTX_INV_TAX_LINES_S%' and object_type <> 'NON-EXISTENT'
SQL>

and this fetched me a record with all the values needed to execute package – ad_zd_sys.drop_covered_object.
Logged into database as sys and executed the package

SQL> exec sys.ad_zd_sys.drop_covered_object('XX_VTX', 'XX_VTX_INV_TAX_LINES_S', 'SEQUENCE', 'V_20170715_2200');

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.90
SQL>

After this I again ran the select statement “select * from dba_objects_ae where object_name like ‘%XX_VTX_INV_TAX_LINES_S%’ and object_type ‘NON-EXISTENT'” and it did not return any records.

I went ahead and fired the “Create sequence..” statement and this time sequence got created without any error.

This error wasted a lot of time and effort in actual upgrade task but thankfully made me to learn another new thing.

Hope this help. Happy learning and keep reading.

-Anand M