on 02-02-2012 8:06 PM
Dear All,
I am 3rd time getting following error during system Import after successfully exported while i execute SMIGR_CREATE_DDL and past the output of these file in <EXPORT_DIR>/ABAP/DB/ORA .
(DB) INFO: TPALOG created #20120202220412
(IMP) INFO: import of TPALOG completed (117599 rows) #20120202220415
DbSl Trace: Error 1452 in exec_immediate() from oci_execute_stmt(), orpc=0
DbSl Trace: ORA-1452 occurred when executing SQL stmt (parse error offset=34)
(DB) ERROR: DDL statement failed
(CREATE UNIQUE INDEX "TPALOG~0" ON "TPALOG"
( "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP" )
TABLESPACE PSAPSR3 STORAGE (INITIAL 2071552 NEXT 0000000080K MINEXTENTS 0000000001
MAXEXTENTS 2147483645 PCTINCREASE 0 ) NOLOGGING COMPUTE STATISTICS )
DbSlExecute: rc = 99
(SQL error 1452)
error message returned by DbSl:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
(DB) INFO: disconnected from DB
SQL> desc SAPSR3.TPALOG;
Name Null? Type
----------------------------------------- -------- --------------
TRTIME NOT NULL VARCHAR2(42)
TRKORR NOT NULL VARCHAR2(60)
TARSYSTEM NOT NULL VARCHAR2(30)
TRCLI NOT NULL VARCHAR2(9)
TRSTEP NOT NULL VARCHAR2(3)
ALLCLI NOT NULL VARCHAR2(3)
PROJECT NOT NULL VARCHAR2(60)
TRUSER NOT NULL VARCHAR2(36)
RETCODE NOT NULL VARCHAR2(12)
HOST NOT NULL VARCHAR2(96)
ADMIN NOT NULL VARCHAR2(36)
TPSTAT_KEY NOT NULL VARCHAR2(60)
LINESEQUENCE NOT NULL VARCHAR2(51)
SQL> select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata,
2 (SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP, ALLCLI, PROJECT, TRUSER,
RETCODE, HOST, ADMIN, TPSTAT_KEY, LINESEQUENCE FROM SAPSR3.TPALOG GROUP BY TRTIM
E, TRKORR, TARSYSTEM, TRCLI, TRSTEP, ALLCLI, PROJECT, TRUSER, RETCODE, HOST, ADM
IN, TPSTAT_KEY, LINESEQUENCE HAVING COUNT(*) > 1) keydata where
3 alldata.TRTIME=keydata.TRTIME
4 alldata.TRKORR=keydata.TRKORR
5 alldata.TARSYSTEM=keydata.TARSYSTEM
6 alldata.TRCLI=keydata.TRCLI
7 alldata.TRSTEP=keydata.TRSTEP
8 alldata.ALLCLI=keydata.ALLCLI
9 alldata.PROJECT=keydata.PROJECT
10 alldata.TRUSER=keydata.TRUSER
11 alldata.RETCODE=keydata.RETCODE
12 alldata.HOST=keydata.HOST
13 alldata.ADMIN=keydata.ADMIN
14 alldata.TPSTAT_KEY=keydata.TPSTAT_KEY
15 alldata.LINESEQUENCE=LINESEQUENCE.HOST;
alldata.TRKORR=keydata.TRKORR
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
what's issue with above command.
and
how can i resolve this issue? please guide me.
Regards,
Hello,
As mentioned in note [23237|http://service.sap.com/sap/support/notes/23237] you should use only columns TRTIME, TRKORR, TARSYSTEM, TRCLI and TRSTEP (index TPALOG~0) to identify the duplicated keys.
Regards,
Eduardo Rezende
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Eduardo,
the following command is running from last 10 minutes and executed thousand of line.
select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata,
(SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP FROM SAPSR3.TPALOG GROUP BY TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP HAVING COUNT(*) > 1) keydata where
alldata.TRTIME=keydata.TRTIME
and alldata.TRKORR=keydata.TRKORR
and alldata.TARSYSTEM=keydata.TARSYSTEM
and alldata.TRCLI=keydata.TRCLI
and alldata.TRSTEP=keydata.TRSTEP;
would you share the command to find out the TPALOG~0?
Regards
Dear ,
the output of sql command:
select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata,
(SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP FROM SAPSR3.TPALOG GROUP BY TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP HAVING COUNT(*) > 1) keydata where
alldata.TRTIME=keydata.TRTIME
and alldata.TRKORR=keydata.TRKORR
and alldata.TARSYSTEM=keydata.TARSYSTEM
and alldata.TRCLI=keydata.TRCLI
and alldata.TRSTEP=keydata.TRSTEP;
no rows selected
when i execute following select command and it gave 64763 record.
SQL> select ROWID from "SAPSR3"."TPALOG" a where a.ROWID > (select MIN(ROWID) fr
om "SAPSR3"."TPALOG" b where b.TRTIME=a.TRTIME AND b.TRKORR=a.TRKORR);
Please guide me, how can I find this Unique index(TPALOG~0) because Indexes window is empty against TPALOG table in SE11 on source system.
Regards,
Hi,
Check how many rows have a problem
select "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)
from "TPALOG"
group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"
having count(*) > 1
Depending of how many of these are affected different options to proceed are given.
Such errors can occur in very old systems when the export converts data.
Just recently had that with a DB that had CharNum fields padded with blanks.
Not possible to find out how they got into the source system before, but in fact
the keys where identical in SE16 allthough on the DB on value was zero padded and one was blankpadded.
Like:
"000123", ...
" 123", ...
SE16 and the DB export converted both to "000123" thus it was throwing duplicate keys on import.
Volker
Edited by: Volker Borowski on Feb 3, 2012 9:11 PM : Added code tags
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi Volker,
the result of mentioned query is:
SQL> select "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)
2 from SAPSR3."TPALOG"
3 group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"
4 having count(*) > 1;
no rows selected
when i try to rebuild the TPALOG~0 then got duplicate key error
SQL> alter index SAPSR3."TPALOG~0" rebuild online;
alter index SAPSR3."TPALOG~0" rebuild online
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
when i check this index(TPALOG~0) on source system then there is no exist this index against TPALOG table in SE11. When I try to create index (0) on TPALOG table then getting (Index ID 0 is reserved for the primary index)
please help me to sortout this issue.
Regards,
Uups,
so you are getting this on the SOURCE System?
I'd thought that the source system is correct and you are only having this problem on the target system....
In this case the PK Index on the source seems to be corrupted.
Your not gettig a result because the query is possibly using the incorrect index for access
which does not have the wrong keys .
Run the same query in your target system and / or run the same query in the
source system utilizing a FULL hint.
select /*+ FULL(T) */
"TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)
from SAPSR3."TPALOG" T
group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"
having count(*) > 1;
Volker
Dear All,
would anyone(Specially Volker) guide me further because again I am getting same issue during homogenous system copy.
the result of said command is on Source and Target system:
there are showing 2535 rows,
SQL> select ROWID from "SAPSR3"."TPALOG" a where a.ROWID > (select MIN(ROWID) from "SAPSR3"."TPALOG" b
where b.TRTIME=a.TRTIME
and b.TRKORR=a.TRKORR
and b.TARSYSTEM=a.TARSYSTEM
and b.TRCLI=a.TRCLI
and b.TRSTEP=a.TRSTEP);
ROWID
------------------
AAAepgAASAAB9GGAAp
AAAepgAASAAB9GGAAu
AAAepgAASAAB9GGAA0
AAAepgAASAAB9GHAAM
AAAepgAAdAABViRAAR
69492 rows selected.
now I have to delete all above rows if yes then how can i delete duplicate rows/records please help me out because I am stuck due to this issue?
Regards,
Hello,
that is quit a lot of rows, and I would first assume that the Primary Key index "0" is not in place at all. Can you crosscheck the DB object in SE14 if the PK is probably missing.
How do you decide which rows to delete? Ok, if all datafields are identical, it is simple: you just eliminate the rows. But what if that is different?!? Right now I have no idea how to approach such a number of rows.
I'd first check if this are duplicate keys with same data fields. I'd try something like
(no system acces right now, so only the idea, not the real values...)
create table tpalog_check_tab as select
key_fields, datafiled1 || datafield2 || datafield 3 .... as concatenated_datafield
from tpalog;
This will create a temporary table with the same keys, but a single datafield which is a concatenation of all datafields of tpalog. You could the check this table with
select TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP,
count(distinct concatenated_datafield) from SAPSR3.tpalog_check_tab
group by TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP
having count(distinct concatenated_datafield) >1;
This will retrieve keys with DIFFERENT datafield content.
After this we might have a better basis to check what is defective.
BTW, I will be on vacation the next days with only limited Interent access, but I am confident, that the other guys will help you as well.
Volker
Hi Abdul,
I recently had the same problem, this is the solution that SAP Support gave me and it worked:
1. Backup the content of table TPALOG;
2. Delete all its entries;
3. Modify the ENDTIME in TPALOGHDR table to a point in time before the
first timestamp in ALOG. This will force STMS to read the ALOG files
again and updates the table TPALOG.
They explained that the STMS looks in the table TPALOGHDR to check if the time frame for which
the import history is called is between STARTTIME and ENDTIME. If it is, it will use the entries in table TPALOG.If not, it will read
the ALOGxxxx files on DIR_TRANS/log and update table TPALOG.
You will find attatched the exact steps I followed.
Best Regards,
Luis Clemente
Hi,
Please check line 15
Thanks,
Salman
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.