Duplicate Oracle Types Issue

When I was comparing object count with PROD after the Full Schema import in DEV.
I see difference in TYPE count and when further digged in to find the cause.
It seems When TYPES are modified they are created with different version with same name but different object_id’s,  so the object count for TYPE’s was not matching with PROD in DEV.

PROD
=====
SQL> l
  1  select owner,OBJECT_TYPE,status,count(1)
  2  from dba_objects
  3  where OWNER in ('CRR')
  4  and object_name not like 'BIN$%'
  5  group by owner,OBJECT_TYPE,status
  6* order by 1,4
SQL> /

OWNER                          OBJECT_TYPE         STATUS    COUNT(1)
—————————— ——————- ——- ———-
…….
…….
CRR                            TYPE                VALID          278 ====> Miss match with DEV below  ( Actually few TYPE names are repeated)

13 rows selected.

 
DEV
======

OWNER                          OBJECT_TYPE         STATUS    COUNT(1)
—————————— ——————- ——- ———-
…….
…….
CRR                            TYPE                VALID          271   ====> 7 Types are missing , ( Actually they are not missing,But missing one are repeted ones in prod, so we are good)

13 rows selected.

Below Query shows Repeated TYPE's or OLD version of TYPE
=========================================================
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,SUBOBJECT_NAME,STATUS
  2  from dba_objects
  3  where OWNER in ('CRR')
  4  and SUBOBJECT_NAME  like '$VSN_%';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         SUBOBJECT_NAME                 STATUS
—————————— —————————— ——————- —————————— ——-
CRR                            ARY_PORTFOLIO_TRANSACTION      TYPE                $VSN_1                         VALID
CRR                            ARY_TRANSACTION_IP             TYPE                $VSN_1                         VALID
CRR                            ARY_TRANSACTION_MANDATE        TYPE                $VSN_1                         VALID
CRR                            OBJ_PORTFOLIO_TRANSACTION      TYPE                $VSN_1                         VALID
CRR                            OBJ_TRANSACTION_CLIENT         TYPE                $VSN_1                         VALID
CRR                            OBJ_TRANSACTION_IP             TYPE                $VSN_1                         VALID
CRR                            OBJ_TRANSACTION_MANDATE        TYPE                $VSN_1                         VALID

7 rows selected.

Below Query shows TYPE names that were repeated twice having different object_id ,this generally happens when a TYPE is modified it news version is also created with same name .In such scenarion original object TYPE identified by SUBOBJECT_NAME is null. Old object TYPE are identified with SUBOBJECT_NAME  having $VSN_1 or $VSN_2

================================================================================
 select OWNER,OBJECT_NAME,OBJECT_TYPE,count(1)
 from dba_objects
 where OWNER in ('CRR')
 and OBJECT_TYPE='TYPE'
 group by OWNER,OBJECT_NAME,OBJECT_TYPE
 having count(1) >1;

OWNER                          OBJECT_NAME                    OBJECT_TYPE           COUNT(1)
------------------------------ ------------------------------ ------------------- ----------
CRR                            ARY_TRANSACTION_IP             TYPE                         2
CRR                            OBJ_TRANSACTION_MANDATE        TYPE                         2
CRR                            ARY_PORTFOLIO_TRANSACTION      TYPE                         2
CRR                            OBJ_TRANSACTION_IP             TYPE                         2
CRR                            OBJ_PORTFOLIO_TRANSACTION      TYPE                         2
CRR                            OBJ_TRANSACTION_CLIENT         TYPE                         2
CRR                            ARY_TRANSACTION_MANDATE        TYPE                         2
7 rows selected.

Query 2

SQL> select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,OBJECT_TYPE,STATUS
  2  from dba_objects where OWNER in ('CRR')
  3  and OBJECT_NAME='OBJ_TRANSACTION_MANDATE';
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID OBJECT_TYPE         STATUS
------------------------------ ------------------------------ ------------------------------ ---------- ------------------- -------
CRR                            OBJ_TRANSACTION_MANDATE        $VSN_1                            1049977 TYPE                VALID
CRR                            OBJ_TRANSACTION_MANDATE                                          1157952 TYPE                VALID

SO I ran modified query in PROD to count the objects again as below and now in PROD and Dev are matching.
==============================================================================

SQL> l
  1  select owner,OBJECT_TYPE,status,count(1)
  2  from dba_objects
  3  where OWNER in ('CRR')
  4  and (SUBOBJECT_NAME not like '$VSN_%' or SUBOBJECT_NAME is null)
  5  and object_name not like 'BIN$%'
  6  group by owner,OBJECT_TYPE,status
  7* order by 1,4
SQL> /
OWNER                          OBJECT_TYPE         STATUS    COUNT(1)
------------------------------ ------------------- ------- ----------
.......
.......
CRR                            TYPE                VALID          271
13 rows selected.

http://docs.oracle.com/cd/E24693_01/appdev.11203/e17126/alter_type.htm#i2057828

The ALTER TYPE statement does one of the following to a type that was created with CREATE TYPE Statement

1) Evolves the type; that is, adds or drops member attributes or methods.
2) For more information about type evolution, see Oracle Database Object-Relational Developer’s Guide.
3) Changes the specification of the type by adding object member subprogram specifications.
4) Recompiles the specification or body of the type.
5) Resets the version of the type to 1, so that it is no longer considered to be evolved.

###############################################################
SQL> SELECT distinct Version# FROM DBA_TYPE_VERSIONS
  2  WHERE Owner = 'CRR'
  3  AND TYPE_NAME = 'OBJ_TRANSACTION_MANDATE';
  VERSION#
----------
         1
         2

###############################################################
col Text for a75 wrap
break on VERSION# on TYPE_NAME skip 2
set pages 200
set line 200
SELECT Version#,TYPE_NAME,TEXT FROM DBA_TYPE_VERSIONS
WHERE Owner = 'CRR'
AND TYPE_NAME = 'OBJ_TRANSACTION_MANDATE' order by Version#,LINE;

  VERSION# TYPE_NAME                      TEXT
---------- ------------------------------ ---------------------------------------------------------------------------
         1 OBJ_TRANSACTION_MANDATE        TYPE obj_transaction_mandate AS OBJECT
                                          (
                                            mandate_base_id        NUMBER, -- mandate information
                                            mandate_name           VARCHAR2(120),
                                            currency               obj_currency,
                                            perf_start_date        TIMESTAMP,
                                            perf_stop_date         TIMESTAMP,
                                            inception_date         TIMESTAMP,
                                            fiscal_year_begin_date DATE,
                                            is_preliminary         VARCHAR(1), --possible values are "Y" and "N"
                                            model_id               NUMBER,
                                            model_code             VARCHAR2(16),
                                            transaction_data       ary_portfolio_transaction  -- transaction data
                                          )
         2 OBJ_TRANSACTION_MANDATE        TYPE obj_transaction_mandate AS OBJECT
                                          (
                                            mandate_base_id        NUMBER, -- mandate information
                                            mandate_name           VARCHAR2(120),
                                            currency               obj_currency,
                                            perf_start_date        TIMESTAMP,
                                            perf_stop_date         TIMESTAMP,
                                            inception_date         TIMESTAMP,
                                            fiscal_year_begin_date DATE,
                                            is_preliminary         VARCHAR(1), --possible values are "Y" and "N"
                                            model_id               NUMBER,
                                            model_code             VARCHAR2(16),
                                            transaction_data       ary_portfolio_transaction  -- transaction data
                                          )
28 rows selected.

https://forums.oracle.com/forums/thread.jspa?threadID=1001591

http://sheikyerbouti.developpez.com/collections/collections.htm