This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jul 23, 2013 4:38 PM by rp0428 RSS

Can we grep how x$ structures are mapped to v$ views

GTS (DBA) Journeyer
Currently Being Moderated

Hello  Everyone ;


Can we grep how x$ structures are mapped to v$ views ?


- we can grep $ORACLE_HOME/rdbms/admin directory and see how the x$ structures are mapped to v$ views

   

  REF_LINK : http://www.dba-oracle.com/t_x$_tables.htm


   Please clarify.



  • 1. Re: Can we grep how x$ structures are mapped to v$ views
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated

    Pl post OS and database versions. What business or technical issue requires you to map X$ structures to V$ views ? X$ structures are internal to Oracle and are not documented.

     

    HTH
    Srini

  • 2. Re: Can we grep how x$ structures are mapped to v$ views
    sb92075 Guru
    Currently Being Moderated

    is

    man grep

    broken for you?

     

    grep 'X\$' *sql

     

     

    When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

  • 3. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    $ cat /etc/*release

    Enterprise Linux Enterprise Linux AS release 4 (October Update 4)

    Enterprise Linux Enterprise Linux AS release 4 (October Update 4)

     

     

     

    SQL>select * from v$version;

     

     

    BANNER

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

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

    PL/SQL Release 10.2.0.1.0 - Production

    CORE    10.2.0.1.0      Production

    TNS for Linux: Version 10.2.0.1.0 - Production

    NLSRTL Version 10.2.0.1.0 - Production

  • 4. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    What business or technical issue requires you to map X$ structures to V$ views ?

                        Analyzing  - how it is mapping.  that's it.

  • 5. Re: Can we grep how x$ structures are mapped to v$ views
    sb92075 Guru
    Currently Being Moderated

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     

    What business or technical issue requires you to map X$ structures to V$ views ?

                        Analyzing  - how it is mapping.  that's it.

     

    Question & answer is a   total & complete waste of time & electrons!

  • 6. Re: Can we grep how x$ structures are mapped to v$ views
    rp0428 Guru
    Currently Being Moderated

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     

    Hello  Everyone ;


    Can we grep how x$ structures are mapped to v$ views ?


    - we can grep $ORACLE_HOME/rdbms/admin directory and see how the x$ structures are mapped to v$ views

       

      REF_LINK : http://www.dba-oracle.com/t_x$_tables.htm


       Please clarify.



    When you get things off the internet you need to consider the SOURCE and the credibility of that source.

    >

    Can we grep how x$ structures are mapped to v$ views ?
    >

    NO - because, in general, X$ structures are NOT mapped directly to V$ views; they are generally mapped to the GV$ views and those GV$ views are what is mapped to the V$ views.

     

    And why, on Earth, would you want to use grep rather than just query the info from Oracle? If you want to know the definitions of the fixed views just use the views that Oracle provides for that very purpose: V_$FIXED_TABLE and V_$FIXED_VIEW_DEFINITION.

    >

    select * from v_$FIXED_VIEW_DEFINITION

    where view_name like '%VERSION%'

    order by view_name

     

    VIEW_NAME,VIEW_DEFINITION

    GV$VERSION,select inst_id, banner from x$version

    V$VERSION,select  BANNER from GV$VERSION where inst_id = USERENV('Instance')

    >

    And many of those views are also PUBLIC SYNONYMS with exactly the same name:

    >

    select synonym_name, table_name from dba_synonyms

    where synonym_name in ('V$VERSION', 'GV$VERSION')

     

    SYNONYM_NAME,TABLE_NAME

    GV$VERSION,GV_$VERSION

    V$VERSION,V_$VERSION

    >

  • 7. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    Hello rp;

     

    SQL> conn rs/rs

    Connected.

     

    SQL> select count(*) From session_privs;

      COUNT(*)

           161

     

    SQL> select * from v_$FIXED_VIEW_DEFINITION

      2  where view_name like '%VERSION%'order by view_name;

    select * from v_$FIXED_VIEW_DEFINITION

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

    SQL> conn  /as  sysdba

    Connected.

     

    SQL> select * from v_$FIXED_VIEW_DEFINITION

      2  where view_name like '%VERSION%'order by view_name

      3  /

     

    VIEW_NAME   VIEW_DEFINITION

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

    GV$VERSION    select inst_id, banner from x$version

    V$VERSION       select  BANNER from GV$VERSION where inst_id = USERENV('Instance')

     

    If i want to query  ,  v_$FIXED_VIEW_DEFINITION  - Should i need   SYSDBA   privilege  or any other special  privilege ?

     

    Thanks rp ;

  • 8. Re: Can we grep how x$ structures are mapped to v$ views
    rp0428 Guru
    Currently Being Moderated

    The user needs the SELECT_CATALOG_ROLE or Administrator privileges

    >

     

    drop user test cascade;

     

     

     

    create user test identified by test;

    grant connect, select_catalog_role to test;

     

     

    >

    See also The Data Dictionary in the Concepts doc

    http://docs.oracle.com/cd/B28359_01/server.111/b28318/datadict.htm

  • 9. Re: Can we grep how x$ structures are mapped to v$ views
    SomeoneElse Guru
    Currently Being Moderated

    SQL> select * from v_$FIXED_VIEW_DEFINITION

      2  where view_name like '%VERSION%'order by view_name;

    select * from v_$FIXED_VIEW_DEFINITION

                  *

    ERROR at line 1:

    ORA-00942: table or view does not exist

     

    Get rid of the underscore after v.

     

    SQL> desc v_$fixed_view_definition
    ERROR:
    ORA-04043: object v_$fixed_view_definition does not exist


    SQL> desc v$fixed_view_definition
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    VIEW_NAME                                          VARCHAR2(30)
    VIEW_DEFINITION                                    VARCHAR2(4000)

  • 10. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    Thanks rp.  doubt cleared.

     

    one more doubt and final  in this thread ;

    when selecting  tab (select * tab ) ;

     

    SQL> select * from tab;

     

    TNAME                            TABTYPE  CLUSTERID

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

    ICOL$                                  TABLE            4

    CON$                                  TABLE

    UNDO$                               TABLE

    C_COBJ#                             CLUSTER

    UET$                                   TABLE            1

    IND$                                    TABLE            3

    SEG$                                    TABLE            2

    C_TS#                                 CLUSTER

    COL$                                   TABLE            5

    FET$                                    TABLE            1

    ...

    DBA_ROLES                             VIEW

    DBA_PROFILES                       VIEW

    USER_RESOURCE_LIMITS     VIEW

    USER_PASSWORD_LIMITS    VIEW

    RESOURCE_COST                    VIEW

                

    QUES 1 : marked  in blue color  are they  base tables  ??

    Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.

                

    SQL> select * from tab  where tname='ICOL$';


    TNAME                          TABTYPE  CLUSTERID

       ICOL$                          TABLE            4


    SQL> desc  ICOL$

    ....

    QUES 2 : i can describe  ICOL$  table  -   IS  this base tables  in oracle ?

     

    Thanks !


  • 11. Re: Can we grep how x$ structures are mapped to v$ views
    Aman.... Oracle ACE
    Currently Being Moderated

    Ans1: Yes

    Ans2: Yes


    Aman....

  • 12. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    Hello Aman and rp ;

     

    The X$tables are FIXED-TABLES. Oracle created V$VIEWS on the X$ TABLES to make it easier to see Oracle internal details.

     

    SQL> select synonym_name, table_name from dba_synonyms

      2  where synonym_name in ('V$VERSION', 'GV$VERSION' , 'V$SESSION');

     

    SYNONYM_NAME                   TABLE_NAME

    -------

    V$SESSION                           V_$SESSION

    V$VERSION                          V_$VERSION

    GV$VERSION                       GV_$VERSION

     

    QUESTION : Here any V$views doesn't belong X$tables. - Why ??

     

    Thanks.

  • 13. Re: Can we grep how x$ structures are mapped to v$ views
    Aman.... Oracle ACE
    Currently Being Moderated

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:

     

    Hello Aman and rp ;

     

    The X$tables are FIXED-TABLES. Oracle created V$VIEWS on the X$ TABLES to make it easier to see Oracle internal details.

     

    SQL> select synonym_name, table_name from dba_synonyms

      2  where synonym_name in ('V$VERSION', 'GV$VERSION' , 'V$SESSION');

     

    SYNONYM_NAME                   TABLE_NAME

    -------

    V$SESSION                           V_$SESSION

    V$VERSION                          V_$VERSION

    GV$VERSION                       GV_$VERSION

     

    QUESTION : Here any V$views doesn't belong X$tables. - Why ??

     

    Thanks.

    I am not sure that I understood you. The views based on X$ tables are actually named as V_$. The V$ ones are merely their synonyms. What do you mean by "v$views doesn't belong x$tables" ?Which V$ views you have found which are not made up from a X$ base table(s) ?

     

    Aman....

  • 14. Re: Can we grep how x$ structures are mapped to v$ views
    GTS (DBA) Journeyer
    Currently Being Moderated

    From following link :

     

    http://www.dba-oracle.com/t_x$_tables.htm

     

    Oracle builds v$ views upon the Oracle x$ tables

     

                i.e. " Oracle created V$VIEWS on the X$ TABLES to make it easier to see Oracle internal details."

    SQL> select synonym_name, table_name from dba_synonyms

      2  where synonym_name in ('V$VERSION', 'GV$VERSION' , 'V$SESSION');

     

    SYNONYM_NAME                   TABLE_NAME

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

    V$SESSION                           V_$SESSION

    V$VERSION                          V_$VERSION

    GV$VERSION                       GV_$VERSION

     

    above mentioned  v$views  mapped  with  following tables. ( V_$SESSION , V_$VERSION , GV_$VERSION)

     

    so i am asking ,  v$views really  merged with  x$tables  or  ( V_$SESSION , V_$VERSION , GV_$VERSION)  these  tables ?

1 2 Previous Next

Legend

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