Jump to content

btidba

Members
  • Content count

    169
  • Joined

  • Last visited

Community Reputation

0 Neutral

About btidba

  • Rank
    Advanced Member
  1. Hi, Please provide the link to download the latest patch to apply on Oracle server 11.2.0.4. Many Thanks and Best Regards
  2. Hi, I have a deadlock which happens time to time while running 2 jobs in parallel.Each of those 2 jobs run a procedure which calls in this order the following update procedures : ParseCustomerOrder updates A table ParseInitialWorkOrders updates B table ParseCard pour updates C table ParseEnvelopesCount updates C table EvaluateCoStatus updates A table ParsePull_FR updates D table ParseReturnedMail updates E table ParseExceptionCards updates A et F tables UpdateCardCount updates C tables The trace file alert_det.trc is attached . The deadlock occurs on a session while merging the C table (ParseCard procedure above ) and in the other session while merging in the A table (proedure ParseExceptionCards above ). In the trace file we can see in deadlock graph that it's row level lock and also it was waiting in shared mode S. Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00540014-00129b60 69 307 X 50 137 S TX-005c0016-000ad96b 50 137 X 69 307 S Also: Rows waited on: Session 307: obj - rowid = 00C603F9 - AAxgP5AAAAAAAAAAAA (dictionary objn - 12977145, file - 0, block - 0, slot - 0) Session 137: obj - rowid = 0108C35A - ABCMNaAAAAAAAAAAAA (dictionary objn - 17351514, file - 0, block - 0, slot - 0) I am a novice in the deadlock troubleshooting but I have read the Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1) to start understand/analyse the case. when I request: SELECT owner, object_name, object_type FROM dba_objects WHERE object_id in (12977145,17351514) it gives: OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE 12977145 PROD A_IDX$$_1DEE00003 INDEX 17351514 PROD C_STATUSSEQ_ID INDEX SUBPARTITION C_STATUSSEQ_ID is a bitmap index created on table C using: CREATE BITMAP INDEX C_STATUSSEQ_ID ON C (STATUSSEQ_ID) LOGGING NOPARALLEL TABLESPACE TBS PCTFREE 20 LOCAL; CREATE INDEX A_IDX$$_1DEE00003 ON A (SOURCECUSTOMERORDERID) LOGGING TABLESPACE TBS.. Knowing that C table is functionaly a child of A table but this is a dataware house DB and no foreign key are created. Could you please help to diagnose more this case and can I fix this dadlock issue ? Many thanks in advance! alert_det.trc
  3. btidba

    Write a unicode nclob into a text file

    Hi, Thanks for the feedback. You're right using Word I can see the chinese character. But still have an issue with the unicode file because I need to display it in this txt file which is used by another process .For info,my DB is under 11G and nls_characterset=WE8MSWIN1252.Now I don't use DBMS_XSLPROCESSOR.CLOB2FILE to right the unicode form my 1 byte DB I had the information that it cannot do it. I'am testing with another procedures now, let me give you all the item necessary to reproduce fromp your side: I share with you the scripts which allow to reproduce the issue dwnload them from http://www76.zippyshare.com/pd/qb74pt7U/4644/Unicode.7z .Please follow the next steps: 1- Run Install_script.sql, this will install: a- the tunicode table which contains 1 row and many various fields b- the procedure WriteUnicodeFile which read from tunicode file and store the record in nclob and then write the unicode file (uou should change the file directory I used EXPORT) ,I comment in it a call of a procedure write_nclob which do the same job as Clob2file. 2- Once installed run the 1st_data.sql to insert the 1st data in tunicode table , note: after inserting the record you should modify via your db client tool the field f68 which contains '??' and replace the value by whatever chinese caracters (let's say 網站). 3- in sql session run : exec WriteUnicodeFile; 4- Open the generated file.txt and check the chinese is correctly inserted /displayed (I got the record displayed in attached right_unicode_reco) 5- Run fill_tunicode.sql this script fill all the varchar2/nvarchar2 fields of the tunicode table up to their max size 6- Run again exec WriteUnicodeFile; 7- open the file.txt again and check the wrong interpretation of the chinese characters, see the result I got in wrong_unicode_reco image. I got the same result either using Clob2file or write_nclob procedures's calls. It seems that when the record exceeds a certain size limit it write wrong characters in the unicode file (?) Please check and advise. Thanks
  4. btidba

    Write a unicode nclob into a text file

    This is not the 1st time I access to this forum I have almost all the time posted Pl/sql questions and got responses! This is why I asked the question...
  5. btidba

    Write a unicode nclob into a text file

    Hi Don. Could you check.please my request? I'd thank you in advance ?
  6. Hi, I have to write a PL/Sql code which writes a file with the chinese caracters .I use for this the call of DBMS_XSLPROCESSOR.CLOB2FILE ,the syntax is : DBMS_XSLPROCESSOR.CLOB2FILE(v_file, DIR_EXP, fileName,871) - v_file is a NCLOB variable and before writing in the file I use DBMS_LOB.append(v_file,to_nchar(A.field)) to append a line to the NCLOB variable , where A.field is a column NVARCHAR type ( I've put a chinese value in it ) -871 is the nls character set of UTF8 but when I check in the text file I find ¿¿ instead of the chinese caracters, could you help to resolve this or if you can suggest another procedure other than DBMS_XSLPROCESSOR.CLOB2FILE which allow writing a large file with the chinese caracters? Thanks
  7. btidba

    Disable database audit

    Hi , Thank you! And if I want to disable the audit for a specific DML action on a specific table , for example I have a specific policies from the table DBA_AUDIT_POLICIES, how to disable the audit policy of TOTO_1_PKV_AUDIT ? OBJECT_SCHEMA OBJECT_NAME POLICY_OWNER POLICY_NAME POLICY_TEXT POLICY_COLUMN ENABLED SEL INS UPD DEL AUDIT_TRAIL POLICY_COLUMN_OPTIONS PROD TOTO_1 PROD TOTO_1_PKV_AUDIT PROD.CHD_AUDIT() = 1 ID YES YES NO YES YES DB ANY_COLUMNS PROD TOTO_2 PROD TOTO_2_PKV_AUDIT PROD.CHD_AUDIT() = 1 ID YES YES NO YES YES DB ANY_COLUMNS PROD TOTO_3 PROD TOTO_3_PKV_AUDIT PROD.CHD_AUDIT() = 1 ID YES YES NO YES YES DB ANY_COLUMNS PROD TOTO_4 PROD TOTO_4_PK_AUDIT PROD.CHD_AUDIT() = 1 LABE YES NO NO YES NO DB ANY_COLUMNS Thanks and Regards
  8. btidba

    Disable database audit

    Hi, I need to disable the audit definitly in a database : - How can I do it from sqlplus command line..? - ..and via Sql Developper? Many thanks
  9. btidba

    Ignore

    Hi, I have a string field (labe) containig the single cote from a table TEST that I need to parse in order to extract fields between # character : #A#BB#C#|TOT'O|TITI|TYTY when I run: declare str varchar2(1000); v_substring VARCHAR2(1000); v_pattern VARCHAR2(100):='[^#]+'; begin for l in (select labe from test) loop str:='SELECT REGEXP_SUBSTR('''||l.labe||''','''||v_pattern||''',1,'||0||') from test'; EXECUTE IMMEDIATE str into v_substring; end loop; end; I get : ORA-00907: missing right parenthesis what is the right way to parse the labe string containing the single cote and extract the data ? Many thanks
  10. Hi, Thanks all! sorry for this late response Regards
  11. Hi, I need to make some calculations on a group of rows, here a sample: table toto (Id integer, datelabe varchar2(40)) : 1,'Begin date : 24/01/2017 12:30:10' 1,'End Date : 28/01/2017 12:50:10' 2,'Begin date : 23/01/2017 10:20:00' 2,'End date : 27/01/2017 10:50:10' 3,'Begin date :24/01/2017' I need to calculate for each line (grouped by Id) the End date-Begin date. So the request should group first the rows by Id then extract the date from datelabe and do the calculation. The result should be: 1, 120(s) 2,190(s) .. Many Thanks
  12. btidba

    Tool to edit Oracle Database under Linux

    Hi, Thanks a lot every one:)! Best Regards
  13. Hi, I've just installed a database 11g under Linux Redhat 6 and need to manage it and edit its objects , usually I use Toad under Windows, is there a similar tool under Linux to access Oracle DB contents? Thanks
  14. btidba

    UTL_HTTP COMPATIBILITY

    Hi, Does the package UTL_HTTP exists in 11.2.0.3 , 11.2.0.4 and upper? Thanks
×