This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Sep 28, 2013 10:29 AM by JohnWatson RSS

X$  tables  Mapped with V_$ Views

975791 Explorer
Currently Being Moderated

Hi,

 

How X$  tables are mapped with V_$ views and  GV_ views?

I need X$ table of  V_$LOGMNR_CONTENTS and  X$ table with other Views?

 

Br,

Raj

  • 1. Re: X$  tables  Mapped with V_$ Views
    rp0428 Guru
    Currently Being Moderated
    How X$  tables are mapped with V_$ views and  GV_ views?

    I need X$ table of  V_$LOGMNR_CONTENTS and  X$ table with other Views?

    Just query the view definitions from the data dictionary and look at them.

  • 2. Re: X$  tables  Mapped with V_$ Views
    sb92075 Guru
    Currently Being Moderated

    >I need X$ table of  V_$LOGMNR_CONTENTS and  X$ table with other Views?

    Why?

    I disagree that you need these details.

     

  • 3. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated

    Hi,

     

    I tried but confusing......

     

    select DBMS_METADATA.GET_DDL('SYNONYM','V$LOGMNR_CONTENTS','PUBLIC')  from dual;

     

    CREATE OR REPLACE PUBLIC SYNONYM "V$LOGMNR_CONTENTS" FOR "SYS"."V_$LOGMNR_CONTENTS"

     

    select DBMS_METADATA.GET_DDL('VIEW','V_$LOGMNR_CONTENTS','SYS')  from dual;

     

    CREATE OR REPLACE FORCE VIEW "SYS"."V_$LOGMNR_CONTENTS" ("SCN", "START_SCN", "
    COMMIT_SCN", "TIMESTAMP", "START_TIMESTAMP", "COMMIT_TIMESTAMP", "XIDUSN", "XIDS
    LT", "XIDSQN", "XID", "PXIDUSN", "PXIDSLT", "PXIDSQN", "PXID", "TX_NAME", "OPERA
    TION", "OPERATION_CODE", "ROLLBACK", "SEG_OWNER", "SEG_NAME", "TABLE_NAME", "SEG
    _TYPE", "SEG_TYPE_NAME", "TABLE_SPACE", "ROW_ID", "USERNAME", "OS_USERNAME", "MA
    CHINE_NAME", "AUDIT_SESSIONID", "SESSION#", "SERIAL#", "SESSION_INFO", "THREAD#"
    , "SEQUENCE#", "RBASQN", "RBABLK", "RBABYTE", "UBAFIL", "UBABLK", "UBAREC", "UBA
    SQN", "ABS_FILE#", "REL_FILE#", "DATA_BLK#", "DATA_OBJ#", "DATA_OBJV#", "DATA_OB
    JD#", "SQL_REDO", "SQL_UNDO", "RS_ID", "SSN", "CSF", "INFO", "STATUS", "REDO_VAL
    UE", "UNDO_VALUE", "SAFE_RESUME_SCN", "CSCN", "OBJECT_ID", "EDITION_NAME", "CLIE
    NT_ID") AS
      select "SCN","START_SCN","COMMIT_SCN","TIMESTAMP","START_TIMESTAMP","COMMIT_TI
    MESTAMP","XIDUSN","XIDSLT","XIDSQN","XID","PXIDUSN","PXIDSLT","PXIDSQN","PXID","
    TX_NAME","OPERATION","OPERATION_CODE","ROLLBACK","SEG_OWNER","SEG_NAME","TABLE_N
    AME","SEG_TYPE","SEG_TYPE_NAME","TABLE_SPACE","ROW_ID","USERNAME","OS_USERNAME",
    "MACHINE_NAME","AUDIT_SESSIONID","SESSION#","SERIAL#","SESSION_INFO","THREAD#","
    SEQUENCE#","RBASQN","RBABLK","RBABYTE","UBAFIL","UBABLK","UBAREC","UBASQN","ABS_
    FILE#","REL_FILE#","DATA_BLK#","DATA_OBJ#","DATA_OBJV#","DATA_OBJD#","SQL_REDO",
    "SQL_UNDO","RS_ID","SSN","CSF","INFO","STATUS","REDO_VALUE","UNDO_VALUE","SAFE_R
    ESUME_SCN","CSCN","OBJECT_ID","EDITION_NAME","CLIENT_ID" from V_$LOGMNR_CONTENTS

     

    Why does V_$LOGMNR_CONTENTS contains V_$LOGMNR_CONTENTS?

     

    Br,

    Raj

  • 4. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated

    Hi,

     

    WHY

    Some one executed Alter table on production.We don't have supplemental log,no audit.

    We tried to use logminer,but no use.

    If any X$table which related to log miner,we can navigate and check the possiblity to find session info

     

     

    Br,

    Raj

  • 5. Re: X$  tables  Mapped with V_$ Views
    EdStevens Guru
    Currently Being Moderated

    975791 wrote:

     

    Hi,

     

    WHY

    Some one executed Alter table on production.We don't have supplemental log,no audit.

    We tried to use logminer,but no use.

    If any X$table which related to log miner,we can navigate and check the possiblity to find session info

     

     

    Br,

    Raj

     

    Perhaps you should be asking why using logminer was "no use" instead of trying to figure out un-documented data dictionary objects.  If you would explain (better, use copy and paste to show) what you did with log miner and what you got back from it, we could provide more help.  Unfortunately"but no use" is not an actionable error message.

  • 6. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated

    Hi,

     

    Thanks for your reply.

     

    We moved a particulat archive log to test machine.Then We performed the below steps.

     

    1,BEGIN
      DBMS_LOGMNR_D.build (
        dictionary_filename => 'm.ora',
        dictionary_location => '/u01/app/oracle/11.2.0.3/product/rdbms/');
    END;

     

    2,BEGIN
      DBMS_LOGMNR.add_logfile (
        options     => DBMS_LOGMNR.addfile,
        logfilename => '/u01/app/oracle/11.2.0.3/product/rdbms/arch_1_155785_774551139.arc');
    END;
    /

    3,BEGIN
      DBMS_LOGMNR.start_logmnr (
        dictfilename => '/u01/app/oracle/11.2.0.3/product/rdbms/');
    END;
    /

    4,select USERNAME,OS_USERNAME,MACHINE_NAME,AUDIT_SESSIONID,SESSION_INFO,CLIENT_ID from v$logmnr_Contents where SQL_REDO like 'ALTER%';

     

    We got the output UNKNOWN and null.

     

    Br,

    Raj

  • 7. Re: X$  tables  Mapped with V_$ Views
    jgarry Guru
    Currently Being Moderated

    Sounds like you didn't turn on the supplemental logging that would put that information in.

  • 8. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated

    Hi,

     

    Yes,We didn't enable  supplemental logging.

    And

    Any other way to find it?

     

    Br,

    Raj

  • 9. Re: X$  tables  Mapped with V_$ Views
    sb92075 Guru
    Currently Being Moderated

    975791 wrote:

     

    Hi,

     

    Yes,We didn't enable  supplemental logging.

    And

    Any other way to find it?

     

    Br,

    Raj

     

     

     

    why does it matter who issued the ALTER?

    You can't unring the bell.

     

    ask everyone who has the requisite privilege, if  they issued the ALTER statement?

     

    see  who was logged into the DB when LAST_DDL for the table was updated.

  • 10. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated


    Hi,

     

    Thanks for your reply.

    why does it matter who issued the ALTER?

    It leads business loss.We tried to find.....

     

    ask everyone who has the requisite privilege, if  they issued the ALTER statement?

    We couldn't ask everyone bcz around 200 users are there.

     

    see  who was logged into the DB when LAST_DDL for the table was updated.

    Diffclut to find

     

    Br,

    Raj

  • 11. Re: X$  tables  Mapped with V_$ Views
    sb92075 Guru
    Currently Being Moderated

    975791 wrote:

     


    Hi,

     

    Thanks for your reply.

    why does it matter who issued the ALTER?

    It leads business loss.We tried to find.....

     

    ask everyone who has the requisite privilege, if  they issued the ALTER statement?

    We couldn't ask everyone bcz around 200 users are there.

     

    see  who was logged into the DB when LAST_DDL for the table was updated.

    Diffclut to find

     

    Br,

    Raj

     

    having 200 folks who can issue ALTER TABLE is an example of management incompetence.

     

    Tell your boss the following,

     

    We have met the enemy & they is US!

  • 12. Re: X$  tables  Mapped with V_$ Views
    Aman.... Oracle ACE
    Currently Being Moderated

    Didn't you ask the same in this thread?

    To get SESSION information using logminer

     

    X$ tables are not going to give you something which is not present. If you didn't get any information from the standard process that only means that the information is simply not available. So may be, now its the time for you to set up proper auditing and supplemental logging. And if 200 users can issue a DDL, you are having a bigger problem in your hands than this missing logging information, as Sb has pointed out.

     

    Aman....

  • 13. Re: X$  tables  Mapped with V_$ Views
    975791 Explorer
    Currently Being Moderated


    Hi,

     

    Thanks  for your mail.....

    Sorry for the late reply.

     

    200 Sessions are connected on that time.not users.

  • 14. Re: X$  tables  Mapped with V_$ Views
    Aman.... Oracle ACE
    Currently Being Moderated

    Either ways, the information can't be fetched if it's not there.

     

    Aman....

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points