Home » RDBMS Server » Server Utilities » LogMiner - no records (Windows XP SP2 Oracle 10g)
LogMiner - no records [message #342731] Sun, 24 August 2008 14:05 Go to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
This is my first post so hello %)
I have small problem with LogMiner.
Oracle is running in ARCHIVELOG mode

LOG_MODE
------------
ARCHIVELOG 


I've created a dictionary file

begin EXECUTE sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
end; 


select name,value from v$parameter where name = 'utl_file_dir';

NAME VALUE
-----------------------------------------------------------------
utl_file_dir C:\LogMinerOutput 


Add Redo log file:

begin sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO01.LOG',Options=>sys.dbms_logmnr.NEW);
end;


and start logminer:

begin sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
end; 



I create test table , make some inserts, delete , updates (in my schema and in scott as well).

When I try to look for logs

SELECT username, sql_redo, sql_undo
FROM v$logmnr_contents WHERE UPPER(username) = 'SYSTEM'; 


It gives:

no rows selected


Any ideas ?


[Updated on: Sun, 24 August 2008 14:36] by Moderator

Report message to a moderator

Re: LogMiner - no records [message #342733 is a reply to message #342731] Sun, 24 August 2008 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>I create test table , make some inserts, delete , updates (in my schema and in scott as well).

I'm sorry. We don't know which schema is "my schema".

We don't know which schema did the SQL in this post.
Re: LogMiner - no records [message #342734 is a reply to message #342731] Sun, 24 August 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Log Miner can't see the statements you execute after you started it.
In addition, if you build the dictionary file BEFORE the table is created, this one will obviously not in the dictionary.

Also, you didn't say with which user you executed this (SYSTEM?).

Regards
Michel
Re: LogMiner - no records [message #342737 is a reply to message #342731] Sun, 24 August 2008 15:36 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Sorry for chaos in previous post, but I've spend about 5h working on it and effects are poor so far.

I build dictionary on system user. Changes where made in system schema and scott (all the time from system account)

I followed Michel Cadot advice and create dictionary file after I make table and execute insert,delete statements.


SELECT sql_redo
FROM  v$logmnr_contents
WHERE UPPER(username) = 'SYSTEM';


give some results but as you can see it's hard to get some information from this output:

set transaction read write;
update "SYS"."TSQ$" set "TS#" = '0', "GR
ANTOR#" = '1968', "BLOCKS" = '0', "MAXBL
OCKS" = '0', "PRIV1" = '0', "PRIV2" = '0
' where "TS#" = '0' and "GRANTOR#" = '19
76' and "BLOCKS" = '0' and "MAXBLOCKS" =
 '0' and "PRIV1" = '0' and "PRIV2" = '0'
 and ROWID = 'AAAAAKAABAAAABaAAB';


is there any option to make it more "human-readable"


Re: LogMiner - no records [message #342740 is a reply to message #342731] Sun, 24 August 2008 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_logmnr.htm#ARPLS022

also http://asktom.oracle.com has many fine coding examples.
Re: LogMiner - no records [message #342778 is a reply to message #342737] Mon, 25 August 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a recursive statement (an internal one if you want), there are few reasons you have to look at it.
Check the statement you executed with your user. Don't use SYSTEM as a test user, create your owns.

Regards
Michel
Re: LogMiner - no records [message #343246 is a reply to message #342731] Tue, 26 August 2008 14:37 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
I found information that without a dictionary, LogMiner returns internal object IDs and presents data as binary data.
But in my case I've included dictionary file.

So any other ideas about this output ?
Re: LogMiner - no records [message #343250 is a reply to message #343246] Tue, 26 August 2008 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This output is perfectly correct and use your dictionary, don't you see "SYS"."TSQ$" and other names? It is as it has been given to Oracle and executed.

Regards
Michel
Re: LogMiner - no records [message #343252 is a reply to message #342731] Tue, 26 August 2008 15:02 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
ok so why in every tutorial the output is like that :

USR    XID          SQL_REDO                        SQL_UNDO

----   ---------  ----------------------------------------------------

HR     1.11.1476  set transaction read write;

 

HR     1.11.1476  insert into "HR"."EMPLOYEES"(     delete from "HR"."EMPLOYEES"

                  "EMPLOYEE_ID","FIRST_NAME",       where "EMPLOYEE_ID" = '306'

                  "LAST_NAME","EMAIL",              and "FIRST_NAME" = 'Mohammed'

                  "PHONE_NUMBER","HIRE_DATE",       and "LAST_NAME" = 'Sami'

                  "JOB_ID","SALARY",                and "EMAIL" = 'MDSAMI'

                  "COMMISSION_PCT","MANAGER_ID",    and "PHONE_NUMBER" = '1234567890'

                  "DEPARTMENT_ID") values           and "HIRE_DATE" = TO_DATE('10-JAN-2003

                  ('306','Mohammed','Sami',         13:34:43', 'dd-mon-yyyy hh24:mi:ss')

                  'MDSAMI', '1234567890',           and "JOB_ID" = 'HR_REP' and

                  TO_DATE('10-jan-2003 13:34:43',   "SALARY" = '120000' and

                  'dd-mon-yyyy hh24:mi:ss'),         "COMMISSION_PCT" = '.05' and

                  'HR_REP','120000', '.05',         "DEPARTMENT_ID" = '10' and

                  '105','10');                      ROWID = 'AAAHSkAABAAAY6rAAO';


What should i do to get similar ?
Re: LogMiner - no records [message #343253 is a reply to message #343252] Tue, 26 August 2008 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What should i do to get similar ?

Use the same query?
Above all, don't try to understand SYS query.

Regards
Michel
Re: LogMiner - no records [message #343270 is a reply to message #342731] Tue, 26 August 2008 15:57 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Quote:
Use the same query?

no kidding is this really gonna do ?

I want to get output more in readable not 'system' style.
Re: LogMiner - no records [message #343350 is a reply to message #343270] Wed, 27 August 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It IS in readable form and not system one, you just don't see it.
Explain why you think it is in "system" form.

Regards
Michel
Re: LogMiner - no records [message #343395 is a reply to message #342731] Wed, 27 August 2008 03:20 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Ok lets analyze this one :

update "SYS"."TSQ$" set "TS#" = '0', "GR
ANTOR#" = '1968', "BLOCKS" = '0', "MAXBL
OCKS" = '0', "PRIV1" = '0', "PRIV2" = '0
' where "TS#" = '0' and "GRANTOR#" = '19
76' and "BLOCKS" = '0' and "MAXBLOCKS" =
 '0' and "PRIV1" = '0' and "PRIV2" = '0'
 and ROWID = 'AAAAAKAABAAAABaAAB';


The only thing I can see is update instruction.

update SYS.TSQ$ set TS#='0 and so on 

I don't know on which table I performed operation and what records where updated.

Re: LogMiner - no records [message #343422 is a reply to message #343395] Wed, 27 August 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know on which table I performed operation and what records where updated.

update "SYS"."TSQ$" set "TS#" = '0'...
It updates column TS# of TSQ$ table of SYS schema and so on.
AS I said don't try to understand SYS statements.
Concentrate on user statements.
Make some modification with SCOTT for example and query Log Miner view where username='SCOTT' or the like.

Regards
Michel

Re: LogMiner - no records [message #343697 is a reply to message #342731] Wed, 27 August 2008 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4635285328580
Re: LogMiner - no records [message #345689 is a reply to message #342731] Thu, 04 September 2008 08:27 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Quote:
Concentrate on user statements.
Make some modification with SCOTT for example and query Log Miner view where username='SCOTT' or the like.


Ok, so I've created using system account a user called 'smash'.
Give him the proper right to execute dbms_logmnr package, select on v$ views etc.
Then I log on as smash and do this:

create table test (x int);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);

update test 
set x=10 where x=1;

delete from test
where x=2;

begin
sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
end;

begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO01.LOG',Options=>sys.dbms_logmnr.NEW);
end;

begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO02.LOG',Options=>sys.dbms_logmnr.ADDFILE);
end;

begin
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO03.LOG',Options=>sys.dbms_logmnr.ADDFILE);
end;

begin
sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');
end;


When I execute query
select timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
and trunc(timestamp) = to_char(sysdate,'DD-MON-YY') 


I get output like this:
04-SEP-08 insert into "SYS"."OBJ$"("OBJ#","DATAOBJ 
          #","OWNER#","NAME","NAMESPACE","SUBNAME" 
          ,"TYPE#","CTIME","MTIME","STIME","STATUS 
          ","REMOTEOWNER","LINKNAME","FLAGS","OID$ 
          ","SPARE1","SPARE2","SPARE3","SPARE4","S 
          PARE5","SPARE6") values ('53128','53128'  
          ,'64','BIN$P44wVP2qQVKG8rBw/ZWprQ==$0',' 
          1',NULL,'2',TO_DATE('03-SEP-08', 'DD-MON 
          -RR'),TO_DATE('04-SEP-08', 'DD-MON-RR'), 
          TO_DATE('04-SEP-08', 'DD-MON-RR'),'1',NU 
          LL,NULL,'128',NULL,'6','2',NULL,NULL,NUL 
          L,NULL);


Are those still a SYS statements ?
If yes how to get USER statements ?
Re: LogMiner - no records [message #345693 is a reply to message #345689] Thu, 04 September 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are those still a SYS statements ?

What do you think "insert into "SYS"."OBJ$"(" is?
Add "seg_owner != 'SYS'" in your query.

You didn't commit.
Don't use the same to execute your statements and query log miner.
...

Regards
Michel
Re: LogMiner - no records [message #345745 is a reply to message #342731] Thu, 04 September 2008 12:51 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Query:

Quote:
select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents
where trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
and seg_owner!='SYS' and seg_owner!='SYSMAN';


Output:
http://img508.imageshack.us/img508/3470/logminerpz2.jpg

What now ...
Is this the most readable form ?

[Updated on: Thu, 04 September 2008 12:54]

Report message to a moderator

Re: LogMiner - no records [message #345749 is a reply to message #345745] Thu, 04 September 2008 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is not readable?

Regards
Michel
Re: LogMiner - no records [message #345757 is a reply to message #342731] Thu, 04 September 2008 13:37 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Everything ?
insert into "SMASH"."BIN$l3j+nTkqSPqVcb5
j8GM+IA==$0"("X") values ('2'); 
and so on....



And in most tutorials they get something like this :
INSERT INTO
JHUNTER.DEPT(DEPTNO,DNAME,LOC)
VALUES (50,'IT','PITTSBURGH');

[Updated on: Thu, 04 September 2008 13:38]

Report message to a moderator

Re: LogMiner - no records [message #345760 is a reply to message #345757] Thu, 04 September 2008 13:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
These are statements on objects you dropped and are now in the recycle bin.

Make a clean test if you want clean output to learn.

Regards
Michel
Re: LogMiner - no records [message #345763 is a reply to message #342731] Thu, 04 September 2008 14:00 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
And how to make this 'clean test' ?
Never heard about it..
Re: LogMiner - no records [message #345771 is a reply to message #345763] Thu, 04 September 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Test in a clean environment.

Create the table.
Switch log file.
Note the current log file.
Make your DML statements and commit.
Switch log file.
Start log miner on the previous logfile and query it.

Just that and nothing else.

Regards
Michel
Re: LogMiner - no records [message #345791 is a reply to message #342731] Thu, 04 September 2008 16:31 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Ok now output is more friendly but...
Could you tell me how to check current log file.

select * from v$logfile;


gives:

GROUP# STATUS  TYPE    MEMBER
3         ONLINE  E:\ORACLE\ORADATA\DB1\REDO03.LOG
2         ONLINE  E:\ORACLE\ORADATA\DB1\REDO02.LOG
1         ONLINE  E:\ORACLE\ORADATA\DB1\REDO01.LOG


when I alter system result is the same (3 logs online)
Maybe should I use diffrent view ?
Re: LogMiner - no records [message #345840 is a reply to message #345791] Fri, 05 September 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$log.status

Regards
Michel
Re: LogMiner - no records [message #345917 is a reply to message #342731] Fri, 05 September 2008 04:40 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Ok now I really don't know what is wrong

1.Create table ( log group 2 current)
2.Switch logfile ( log 3 is current now)
3.DML statements + commit
4.Switch logfile (log 1 is current)
5.Start log miner on the previous logfile

sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\LogMinerOutput');
sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO03.LOG',Options=>sys.dbms_logmnr.NEW);
sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');

6.Query:

select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
and trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
and seg_owner!='SYS'; 


no rows selected
Re: LogMiner - no records [message #345934 is a reply to message #345917] Fri, 05 September 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: LogMiner - no records [message #345937 is a reply to message #345917] Fri, 05 September 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
trunc(timestamp) = to_char(sysdate,'DD-MON-YY')

This is wrong, you compare a date to a string.

Regards
Michel
Re: LogMiner - no records [message #345943 is a reply to message #342731] Fri, 05 September 2008 06:34 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
SQL> show user;
USER is "SMASH"
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> create table test(x int);

Table created.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 CURRENT
         3 INACTIVE

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> delete from test where x=1;

1 row deleted.

SQL> update test set x=20 where x=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 ACTIVE
         3 CURRENT

SQL> exec sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\Log
MinerOutput');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.add_logfile( LogFileName=>'E:\ORACLE\ORADATA\DB1\REDO02.LOG',Options=>sys.
dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\LogMinerOutput\Dictionary.ora');

PL/SQL procedure successfully completed.

SQL> col sql_undo format a40 wrap;
SQL> col sql_redo format a40;
SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where username='SMASH'
  2  and trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
  3  and seg_owner!='SYS';

no rows selected

SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where
  2  trunc(timestamp) = to_char(sysdate,'DD-MON-YY')
  3  and seg_owner!='SYS' and seg_owner!='SYSMAN'; 

no rows selected

Re: LogMiner - no records [message #345945 is a reply to message #345943] Fri, 05 September 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, your last query is wrong.
In addition, Oracle 10g introduced optimization in redo log generation and so you will not see small transactions (LogMiner is not able to analyze them). See my second statement.
SQL> show user
USER is "MICHEL"
SQL> alter session set "_in_memory_undo"=false;

Session altered.

SQL> create table test (col number);

Table created.

SQL> alter system switch logfile;

System altered.

SQL> @log

Thr  Grp     Mo   Seq# Premier acces       Arc Statut Groupe    Etat Mb Membre
--- ---- ------ ------ ------------------- --- ---------------- ------- ------------------------------------
  1    1     10    653 05/09/2008 13:29:07 NO  ACTIVE           ONLINE  C:\ORACLE\ARCHIVES\MIKA\RL_G1_2.RDO
                                                                ONLINE  C:\ORACLE\BASES\MIKA\RL_G1_1.RDO
       2     10    654 05/09/2008 13:29:14 NO  ACTIVE           ONLINE  C:\ORACLE\ARCHIVES\MIKA\RL_G2_2.RDO
                                                                ONLINE  C:\ORACLE\BASES\MIKA\RL_G2_1.RDO
       3     10    655 05/09/2008 13:32:29 NO  ACTIVE           ONLINE  C:\ORACLE\ARCHIVES\MIKA\RL_G3_2.RDO
                                                                ONLINE  C:\ORACLE\BASES\MIKA\RL_G3_1.RDO
       4     10    656 05/09/2008 13:32:52 NO  CURRENT          ONLINE  C:\ORACLE\ARCHIVES\MIKA\RL_G4_2.RDO
                                                                ONLINE  C:\ORACLE\BASES\MIKA\RL_G4_1.RDO

SQL> insert into test values(0);

1 row created.

SQL> update test set col=col+1;

1 row updated.

SQL> delete test;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> exec sys.dbms_logmnr_d.build(Dictionary_FileName=>'Dictionary.ora',Dictionary_Location=>'C:\Oracle\admin\Log')
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.add_logfile( LogFileName=>'C:\ORACLE\BASES\MIKA\RL_G4_1.RDO',Options=>sys.dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_logmnr.start_logmnr(DictFileName=>'C:\Oracle\admin\Log\Dictionary.ora');

PL/SQL procedure successfully completed.

SQL> set recsep wrap
SQL> set recsepchar '-'
SQL> set recsep wrap
SQL> select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where seg_owner='MICHEL';
SEG_OWNER                        TIMESTAMP
-------------------------------- -------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
MICHEL                           05/09/2008 13:32:56
delete from "MICHEL"."TEST" where "COL" = '0' and ROWID = 'AAANLWAAEAAAARkAAA';
insert into "MICHEL"."TEST"("COL") values ('0');
----------------------------------------------------------------------------------------------------
MICHEL                           05/09/2008 13:32:56
update "MICHEL"."TEST" set "COL" = '0' where "COL" = '1' and ROWID = 'AAANLWAAEAAAARkAAA';
update "MICHEL"."TEST" set "COL" = '1' where "COL" = '0' and ROWID = 'AAANLWAAEAAAARkAAA';
----------------------------------------------------------------------------------------------------
MICHEL                           05/09/2008 13:32:56
insert into "MICHEL"."TEST"("COL") values ('1');
delete from "MICHEL"."TEST" where "COL" = '1' and ROWID = 'AAANLWAAEAAAARkAAA';
----------------------------------------------------------------------------------------------------

3 rows selected.

Regards
Michel
Re: LogMiner - no records [message #346014 is a reply to message #342731] Fri, 05 September 2008 10:32 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
Ok it seems to work.

Quote:
Oracle 10g introduced optimization in redo log generation and so you will not see small transactions (LogMiner is not able to analyze them)

So how big should the transaction be (100 record, 10000...) ?

[Updated on: Fri, 05 September 2008 11:12] by Moderator

Report message to a moderator

Re: LogMiner - no records [message #346021 is a reply to message #346014] Fri, 05 September 2008 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Internal stuff, surely depends on many things including version, patchset but not very much.
You can just try it for your database: don't disable the parameter then update 10 rows and see, restart with 20 rows and see and so on...
Post your result.

Regards
Michel
Re: LogMiner - no records [message #346513 is a reply to message #342731] Mon, 08 September 2008 14:42 Go to previous messageGo to next message
Smash
Messages: 18
Registered: August 2008
Junior Member
If someone is interested I've made some simple tests.

On my database Oracle 10 g (no patches), I put simple script

create table test (x int);

declare 
i int;
begin
i:=0;
for i in 1..50 loop
insert into test values(i);
end loop;

update test set x=x+1;
delete test ;
commit;
end;


It seems that minimal of variable 'i' is approximately 50 - that make 150 operations (insert,update,delete).

Interesting is that log miner return only 27 rows.
select seg_owner,timestamp,sql_undo,sql_redo from v$logmnr_contents where seg_owner='SMASH';

Re: LogMiner - no records [message #346527 is a reply to message #346513] Mon, 08 September 2008 15:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, I think it may depend on operation (row) size.

Quote:
Interesting is that log miner return only 27 rows

This is because other operations are flushed in vector array that Log Miner is unable to read and so skips, instead of being recorded in stand alone change vector.

Regards
Michel

[Updated on: Mon, 08 September 2008 15:12]

Report message to a moderator

Previous Topic: error during dump import: ORA-24170 when drop user
Next Topic: Import using IMPDP
Goto Forum:
  


Current Time: Sat Dec 10 12:30:53 CST 2016

Total time taken to generate the page: 0.08063 seconds