Hi,
I m hit with a ORA-00001: unique constraint (STSSYS.SYS_C0038626) violation.
Below is the query trying to insert data into table sts_allmo. Can someone let me know how to find out the duplicate records from combining below query with table sts_allmo ?
SQL> INSERT INTO sts_allmo
2 SELECT port_id,
3 'HBA Port',
4 to_char(x.port_number),
5 x.adport_alias,
6 hwcontainer.hwcontainerid,
7 'Host',
8 hwcontainer.hwcontainerserialnumber,
9 DECODE (hwcontainer.aliasname,
10 NULL, hwcontainer.hwcontainerserialnumber,
11 hwcontainer.aliasname
12 ),
13 hwcontainer.vendorname
14 FROM hwcontainer,host,sts_host_hba x
15 WHERE host.physicalserverid = hwcontainer.hwcontainerid
16 AND host.hostid = x.host_id;
INSERT INTO sts_allmo
*
ERROR at line 1:
ORA-00001: unique constraint (STSSYS.SYS_C0038626) violated
SQL> select table_name, column_name
2 from DBA_cons_columns
3 where owner='STSSYS'
4 and constraint_name='SYS_C0038626'
5 order by position;
_TABLE_NAME_ _COLUMN_NAME_
STS_ALLMO MO_ID
SQL> desc hwcontainer
Name Null? Type
----------------------------------------------------- -------- ---------------------
HWCONTAINERID NOT NULL NUMBER(38)
CONTAINERTYPE VARCHAR2(64)
HWCONTAINERSERIALNUMBER NOT NULL VARCHAR2(255)
STORAGESYSTEMID NUMBER(38)
PRODUCT VARCHAR2(64)
HARDWAREREVISION VARCHAR2(257)
MODEL VARCHAR2(128)
SOFTWAREREVISION VARCHAR2(257)
FIRMWAREREVISION VARCHAR2(257)
VENDORCODE VARCHAR2(128)
VENDORNAME VARCHAR2(64)
MODELDESCRIPTION VARCHAR2(64)
ALIASNAME VARCHAR2(128)
GENERICTYPE VARCHAR2(32)
SOURCEID NUMBER(38)
STATE NUMBER(38)
LASTUPDATETIMELCL DATE
TLLOCKNUM NUMBER(38)
ACK CHAR(1)
LASTDISCOVERYTIME DATE
DATASOURCETYPEID NUMBER
VIRTUALSTORAGEID NUMBER(38)
UNIFIEDSTORAGEID NUMBER(38)
ISPARTOFUNIFIEDSTORAGE CHAR(1)
SQL> DESC HOST
Name Null? Type
----------------------------------------------------- -------- ---------------------
PHYSICALSERVERID NOT NULL NUMBER(38)
HOSTNAME NOT NULL VARCHAR2(255)
HOSTCLUSTERID NUMBER(38)
HOSTID NOT NULL NUMBER(38)
ESXSERVERID NUMBER(38)
OPSYSID NUMBER(38)
DESCRIPTION VARCHAR2(255)
NODENAME VARCHAR2(128)
GMTOFFSET NUMBER(38)
DOMAINNAME VARCHAR2(255)
ALIASNAME VARCHAR2(255)
REALHOSTNAME VARCHAR2(255)
SYMAPIVERSION VARCHAR2(128)
USEDST CHAR(1)
CONFIGUREDMEMORY NUMBER(38)
PRIIPADDRESS VARCHAR2(64)
SECIPADDRESS VARCHAR2(64)
VENDOR VARCHAR2(64)
ACTIVITY NUMBER(38)
INSTALLEDMEMORY NUMBER(38)
AVAILABLEMEMORY NUMBER(38)
CPU NUMBER(38)
SERIALNUMBER VARCHAR2(32)
GENERICTYPE VARCHAR2(32)
STATE NUMBER(38)
SOURCEID NUMBER(38)
LASTUPDATETIMELCL DATE
TLLOCKNUM NUMBER(38)
HOSTTYPE VARCHAR2(32)
ACK CHAR(1)
ENTITYTYPECODE VARCHAR2(64)
HOSTDISPLAYNAME VARCHAR2(255)
LASTDISCOVERYTIME DATE
VMGUESTDISPLAYNAME VARCHAR2(255)
ISFULLYDISCOVERED CHAR(1)
VMGUESTSTATE VARCHAR2(64)
VMTOOLSTATE VARCHAR2(64)
VMSTATEDESCRIPTION VARCHAR2(512)
UUID VARCHAR2(512)
DATASOURCETYPEID NUMBER
SQL> DESC STS_HOST_HBA
Name Null? Type
----------------------------------------------------- -------- ---------------------
AD_ID NOT NULL NUMBER(38)
AD_NAME VARCHAR2(257)
ADAPTER_NUMBER NUMBER
HOST_ID NOT NULL NUMBER(38)
HOST_NAME VARCHAR2(255)
HOST_ALIAS VARCHAR2(255)
FIBREAD_NODEWWN VARCHAR2(64)
AD_PORTCOUNT NUMBER(38)
AD_VENDOR VARCHAR2(257)
AD_REVISION VARCHAR2(257)
AD_MODEL VARCHAR2(257)
PORT_ID NOT NULL NUMBER(38)
PORT_NUMBER NUMBER
ADPORT_ALIAS VARCHAR2(386)
PORT_WWN VARCHAR2(64)
PORT_WWNNICKNAME VARCHAR2(256)
ISCSI_NAME VARCHAR2(256)
AD_DRIVER_REV VARCHAR2(257)
HOSTHBACUSTOMFIELD1 VARCHAR2(2048)
HOSTHBACUSTOMFIELD2 VARCHAR2(2048)
HOSTHBACUSTOMFIELD3 VARCHAR2(2048)
HOSTHBACUSTOMFIELD4 VARCHAR2(2048)
HOSTHBACUSTOMFIELD5 VARCHAR2(2048)
HOSTHBACUSTOMFIELD6 VARCHAR2(2048)
HOSTHBACUSTOMFIELD7 VARCHAR2(2048)
HOSTHBACUSTOMFIELD8 VARCHAR2(2048)
HOSTHBACUSTOMFIELD9 VARCHAR2(2048)
HOSTHBACUSTOMFIELD10 VARCHAR2(2048)
SQL>
Details about table sts_allmo
SQL> DESC sts_allmo;
Name Null? Type
----------------------------------------------------- -------- --------------
MO_ID NOT NULL NUMBER(38)
MO_TYPE VARCHAR2(255)
MO_NAME VARCHAR2(4000)
MO_ALIAS VARCHAR2(4000)
CONTAINER_ID NUMBER(38)
CONTAINER_TYPE VARCHAR2(255)
CONTAINER_NAME VARCHAR2(255)
CONTAINER_ALIAS VARCHAR2(255)
CONTAINER_VENDOR VARCHAR2(255)
SQL> select count(*) from sts_allmo;
COUNT(*)
----------
4615
I tried below query but no rows returned
SQL> SELECT * FROM STS_ALLMO INTERSECT SELECT x.port_id,
2 'HBA Port',
3 to_char(x.port_number),
4 x.adport_alias,
5 hwcontainer.hwcontainerid,
6 'Host',
7 hwcontainer.hwcontainerserialnumber,
8 DECODE (hwcontainer.aliasname,
9 NULL, hwcontainer.hwcontainerserialnumber,
10 hwcontainer.aliasname
11 ),
12 hwcontainer.vendorname
13 FROM hwcontainer,host,sts_host_hba x
14 WHERE host.physicalserverid = hwcontainer.hwcontainerid
15 AND host.hostid = x.host_id;
no rows selected