1 2 Previous Next 15 Replies Latest reply: Jul 23, 2013 6:38 PM by rp0428 RSS

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

    GTS (DBA)

      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

          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

            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)

              $ 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)

                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

                  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

                    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)

                      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

                        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

                          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)

                            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....

                              Ans1: Yes

                              Ans2: Yes


                              Aman....

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

                                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....

                                  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)

                                    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