Home » SQL & PL/SQL » SQL & PL/SQL » log miner is not displaying the result in text format (oracle,8.1.7, unix)
log miner is not displaying the result in text format [message #301533] Thu, 21 February 2008 00:08 Go to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Hi Gurus,
i have enable the log miner but the output which i am getting after firing this query is not in proper text format.
will tell you the steps which i follow:
SQL> EXECUTE dbms_logmnr_d.build(Dictionary_FileName=>'ustst.ora',Dictionary_Location=>'/usr1/oradata/pbm1/utl_file');
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
TABLE: OBJ$ recorded in LogMnr Dictionary File
TABLE: TAB$ recorded in LogMnr Dictionary File
TABLE: COL$ recorded in LogMnr Dictionary File
TABLE: SEG$ recorded in LogMnr Dictionary File
TABLE: UNDO$ recorded in LogMnr Dictionary File
TABLE: UGROUP$ recorded in LogMnr Dictionary File
TABLE: TS$ recorded in LogMnr Dictionary File
TABLE: CLU$ recorded in LogMnr Dictionary File
TABLE: IND$ recorded in LogMnr Dictionary File
TABLE: ICOL$ recorded in LogMnr Dictionary File
TABLE: LOB$ recorded in LogMnr Dictionary File
TABLE: USER$ recorded in LogMnr Dictionary File
TABLE: FILE$ recorded in LogMnr Dictionary File
TABLE: PARTOBJ$ recorded in LogMnr Dictionary File
TABLE: PARTCOL$ recorded in LogMnr Dictionary File
TABLE: TABPART$ recorded in LogMnr Dictionary File
TABLE: INDPART$ recorded in LogMnr Dictionary File
TABLE: SUBPARTCOL$ recorded in LogMnr Dictionary File
TABLE: TABSUBPART$ recorded in LogMnr Dictionary File
TABLE: INDSUBPART$ recorded in LogMnr Dictionary File
TABLE: TABCOMPART$ recorded in LogMnr Dictionary File
TABLE: INDCOMPART$ recorded in LogMnr Dictionary File
Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_LOGMNR.add_logfile (
3 options => DBMS_LOGMNR.new,
4 logfilename => '/ora1/pbm/arch/arch1_0000010839.arc');
SQL> SELECT scn, operation, sql_redo, sql_undo
2 FROM v$logmnr_contents;

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
410157400 START
set transaction read write;


410157400 INSERT
insert into "PBMADM"."PBM_TRX"("PK","ENTERED_BY","TIME_IN","TIME_OUT","TRX_REFER
ENCE","BIN_NUM","VERSION_NUM","TRANSACTION_CD","PROC_CONTROL_NUM","PHARMACY_NUM"

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
,"GROUP_NUM","CARDHOLDER_ID","PERSON_CD","DATE_OF_BIRTH","SEX_CD","RELATIONSHIP_
CD","DATE_FILLED","PATIENT_FIRST_NAME","PATIENT_LAST_NAME","EMPLOYER_NAME","RX_N
UM","NEW_REFILL_CD","METRIC_QTY","DAYS_SUPPLY","COMPOUND_CD","NDC_NUM","DAW","IN
GREDIENT_COST","PRESCRIBER_ID","DATE_WRITTEN","RX_DENIAL","USUAL_CUSTOMARY","SAL
ES_TAX","DISPENSING_FEE_SUB","PA_CODE","PA_NUM","BASIS_OF_COST_DETERMINATION","D
IAGNOSIS_CD","GROSS_AMT_DUE","DATE_OF_INJURY","CLAIM_ID_NUM","DUR_CONFLICT_CD","
DUR_INTERVENTION_CD","DUR_OUTCOME_CD","METRIC_DECIMAL_QTY","HEADER_RESPONSE_STAT

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
US","RX_RESPONSE_STATUS","PATIENT_PAY_AMT","INGREDIENT_COST_PAID","CONTRACT_FEE_
PAID","SALES_TAX_PAID","TOTAL_AMT_PAID","AUTHORIZATION_NUM","MESSAGE","ACCUMULAT
ED_DED_AMT","REMAINING_DED_AMT","REMAINING_BENEFIT_AMT","AMT_APPLIED_PERIOD_DED"
,"AMT_OF_COPAY","AMT_ATTRIB_TO_PROD_SEL","AMT_EXCEED_PERIOD_BENEFIT_MAX","BASIS_
OF_REIMB_DETERMINATION","AMT_ATTRIB_TO_SALES_TAX","DUR_RESPONSE","REJECT_CODES",
"CLAIM_PK","PHYSNET_PK","PHRNET_PK","GROUP_ID","MEMBER_PK","PHARMACY_PK","COVERA
GE_CODE","DRUG_NDC","PLNMST_PK","OTHER_PAYOR_AMT","PATIENT_PAID_AMT","OTHER_COVE

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
RAGE_CD","MESSAGE_INT","CLAIM_ID","TRANSACTION_COUNT","SOFTWARE_VENDOR","CARDHOL
DER_FIRST_NAME","CARDHOLDER_LAST_NAME","PATIENT_ID_QUALIFIER","PATIENT_ID","PREG
NANCY_IND","NUMBER_REFILLS_AUTH","UOM","PRESCRIBER_LAST_NAME","PRESCRIBER_PHONE"
,"PCT_SALES_TAX_AMT_SUB","PCT_SALES_TAX_RATE_SUB","PCT_SALES_TAX_BASIS_SUB","DIA
GNOSIS_CD2","DIAGNOSIS_CD3","DIAGNOSIS_CD4","DIAGNOSIS_CD5","OTHER_PAYER_COVERAG
E_TYPE1","OTHER_PAYER_COVERAGE_TYPE2","OTHER_PAYOR_AMT2","REASON_SERVICE_CD1","C
LINICAL_SIGNIFICANCE_CD1","OTHER_PHARMACY_IND1","PREVIOUS_DATE_FILL1","QUANTITY_

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
PREVIOUS_FILL1","DATABASE_IND1","OTHER_PRESCRIBER_IND1","DUR_FREE_TEXT_MESSAGE1"
,"REASON_SERVICE_CD2","CLINICAL_SIGNIFICANCE_CD2","OTHER_PHARMACY_IND2","PREVIOU
S_DATE_FILL2","QUANTITY_PREVIOUS_FILL2","DATABASE_IND2","OTHER_PRESCRIBER_IND2",
"DUR_FREE_TEXT_MESSAGE2","REASON_SERVICE_CD3","CLINICAL_SIGNIFICANCE_CD3","OTHER
_PHARMACY_IND3","PREVIOUS_DATE_FILL3","QUANTITY_PREVIOUS_FILL3","DATABASE_IND3",
"OTHER_PRESCRIBER_IND3","DUR_FREE_TEXT_MESSAGE3","OTHER_AMT_CLAIMED_SUB_QUAL","O
THER_AMT_CLAIMED_SUB","APPROVED_MESSAGE_CD1","APPROVED_MESSAGE_CD2","APPROVED_ME

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
SSAGE_CD3","ADDITIONAL_MESSAGE","PCT_SALES_TAX_AMT_PAID","PCT_SALES_TAX_RATE_PAI
D","PCT_SALES_TAX_BASIS_PAID","OTHER_AMT_PAID","OTHER_PAYER_AMT_RECOG","REJECTED
_FIELD_OCCURRENCE_IND","REJECT_PLNREST_PK","PRESCRIBER_ID_QUALIFIER") values (31
508954,'TRXPROC',TO_DATE('14-FEB-2008 20:15:56', 'DD-MON-YYYY HH24:MI:SS'),TO_DA
TE('14-FEB-2008 20:15:57', 'DD-MON-YYYY HH24:MI:SS'),'PY0000',NULL,'51','B1','VD
Z','1523794',NULL,'104100205499',NULL,'20051231','2','1','20080214','ANIYAH','ST
RONG',NULL,'2642010','0','0000030000','7','1','64376072830','0','00003348','BS30
SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8IAAr';
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8IAAr';

410157400 UPDATE
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8IAAs';
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8IAAs';

410157400 UPDATE
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAA';

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAA';

410157400 UPDATE
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAB';
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAB';

410157400 UPDATE
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAC';
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAC';

SCN OPERATION
---------- --------------------------------
SQL_REDO
--------------------------------------------------------------------------------
SQL_UNDO
--------------------------------------------------------------------------------

410157400 UPDATE
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAD';
update "UNKNOWN"."Objn:34244" set "Col[32]" = HEXTORAW('c118'), "Col[33]" = HEXT
ORAW('44454c54412042454e4546495453') where ROWID = 'AAAIXEAAVAAAM8JAAD';


SCN OPERATION
i have not pasted the whole output as its quite large.

Could anybody plaese explain me why???

Thanks,
Daniesh
Re: log miner is not displaying the result in text format [message #301541 is a reply to message #301533] Thu, 21 February 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the output which i am getting after firing this query is not in proper text format.

What do you mean?
If you want to prevent from the display of many headers, use "set pagesize".

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: log miner is not displaying the result in text format [message #301568 is a reply to message #301533] Thu, 21 February 2008 01:05 Go to previous messageGo to next message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Ok i will keep in mind or should i post another.
i want to say that when i fired the command
SQL> SELECT scn, operation, sql_redo, sql_undo
2 FROM v$logmnr_contents;
the output which i am getting is not in proper format. i have gone through the log miner docs which says that
There are 2 packages that you need to use DBMS_LOGMNR_D and DBMS_LOGMNR. DBMS_LOGMNR_D is not necessary, but it helps with determinings what was going on as it will translate the object ids to the actual names.
but in output i can see that objectid is not converted into object names. from my above forum, you can see that i have configue DBMS_LOGMNR_D, then also i am not getting the expected output.


Thanks,
Sheldon
Re: log miner is not displaying the result in text format [message #301588 is a reply to message #301568] Thu, 21 February 2008 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Ok i will keep in mind

So why don't you follow it in your just following query?

Quote:
i can see that objectid is not converted into object names

This is because your dictionary does not match with the log records. For instance, your object 34244 has been dropped and is no more in the dictionary.

Regards
Michel
Re: log miner is not displaying the result in text format [message #301653 is a reply to message #301533] Thu, 21 February 2008 04:36 Go to previous message
sheldon1982
Messages: 59
Registered: February 2008
Location: india
Member
Thanks Michel....
Previous Topic: Can I replace the column position?
Next Topic: PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 12:53:10 CST 2016

Total time taken to generate the page: 0.08253 seconds