1 2 3 4 Previous Next 47 Replies Latest reply: Feb 28, 2012 1:36 AM by Pavel RSS

    DDL operators on v$-views

    Pavel
      Hello!


      How to see DDL operators on v$-views (WITH FIXED TABLES) ?

      I'm trying to use DBMS_METADATA.GET_DDL, but do't see references it
      Pardon me if I am not correct,
      I'm using * 10.2.0.5*

      Regards,
      Paul
        • 1. Re: DDL operators on v$-views
          sb92075
          Paul wrote:
          Hello!


          How to see DDL operators on v$-views (WITH FIXED TABLES) ?

          I'm trying to use DBMS_METADATA.GET_DDL, but do't see references it
          Pardon me if I am not correct,
          I'm using * 10.2.0.5*

          Regards,
          Paul
          I do not understand what you desire/expect?

          SQL> DESC V$SESSION
          • 2. Re: DDL operators on v$-views
            rp0428
            Works for me
             select dbms_metadata.get_ddl('VIEW','V_$DATAFILE') from dual;
            Returns a CLOB with the view definition in it.

            But if you want the real view it comes from GV$DATAFILE so use
            SELECT view_definition FROM
             v$fixed_view_definition
             WHERE view_name='V$DATAFILE';
            and it gets it data from GV$DATAFILE so query it
            SELECT view_definition FROM
             v$fixed_view_definition
             WHERE view_name='GV$DATAFILE';
            • 3. Re: DDL operators on v$-views
              Aman....
              There is no need for it. Can you tell us that why you want to do it in anyways?

              Aman....
              • 4. Re: DDL operators on v$-views
                CKPT
                Paul wrote:
                Hello!


                How to see DDL operators on v$-views (WITH FIXED TABLES) ?

                I'm trying to use DBMS_METADATA.GET_DDL, but do't see references it
                Pardon me if I am not correct,
                I'm using * 10.2.0.5*

                Regards,
                Paul
                Here is example

                use v_$datfile instead of V$datafile
                SQL>  select dbms_metadata.get_ddl('VIEW','V$DATAFILE','SYS') from dual
                
                  2  SQL>
                SQL>
                SQL> set long 90000000
                SQL> select dbms_metadata.get_ddl('VIEW','V$DATAFILE','SYS') from dual;
                ERROR:
                ORA-31603: object "V$DATAFILE" of type VIEW not found in schema "SYS"
                ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
                ORA-06512: at "SYS.DBMS_METADATA", line 3241
                ORA-06512: at "SYS.DBMS_METADATA", line 4812
                ORA-06512: at line 1
                
                
                
                no rows selected
                
                SQL> select object_name,object_type ,owner from dba_objects where object_name like 'V_$DATAFILE';
                V_$DATAFILE                    VIEW                SYS
                
                SQL>
                
                
                
                SQL> select dbms_metadata.get_ddl('VIEW','V_$DATAFILE','SYS')  from dual;
                
                  CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATAFILE" ("FILE#", "CREATION_CHANGE#",
                 "CREATION_TIME", "TS#", "RFILE#", "STATUS", "ENABLED", "CHECKPOINT_CHANGE#", "C
                HECKPOINT_TIME", "UNRECOVERABLE_CHANGE#", "UNRECOVERABLE_TIME", "LAST_CHANGE#",
                "LAST_TIME", "OFFLINE_CHANGE#", "ONLINE_CHANGE#", "ONLINE_TIME", "BYTES", "BLOCK
                S", "CREATE_BYTES", "BLOCK_SIZE", "NAME", "PLUGGED_IN", "BLOCK1_OFFSET", "AUX_NA
                ME", "FIRST_NONLOGGED_SCN", "FIRST_NONLOGGED_TIME", "FOREIGN_DBID", "FOREIGN_CRE
                ATION_CHANGE#", "FOREIGN_CREATION_TIME", "PLUGGED_READONLY", "PLUGIN_CHANGE#", "
                PLUGIN_RESETLOGS_CHANGE#", "PLUGIN_RESETLOGS_TIME") AS
                  select "FILE#","CREATION_CHANGE#","CREATION_TIME","TS#","RFILE#","STATUS","ENA
                BLED","CHECKPOINT_CHANGE#","CHECKPOINT_TIME","UNRECOVERABLE_CHANGE#","UNRECOVERA
                BLE_TIME","LAST_CHANGE#","LAST_TIME","OFFLINE_CHANGE#","ONLINE_CHANGE#","ONLINE_
                TIME","BYTES","BLOCKS","CREATE_BYTES","BLOCK_SIZE","NAME","PLUGGED_IN","BLOCK1_O
                FFSET","AUX_NAME","FIRST_NONLOGGED_SCN","FIRST_NONLOGGED_TIME","FOREIGN_DBID","F
                OREIGN_CREATION_CHANGE#","FOREIGN_CREATION_TIME","PLUGGED_READONLY","PLUGIN_CHAN
                GE#","PLUGIN_RESETLOGS_CHANGE#","PLUGIN_RESETLOGS_TIME" from v$datafile
                
                
                
                SQL>
                • 5. Re: DDL operators on v$-views
                  Pavel
                  Hello!

                  Thanks, Sir!

                  Best regards,
                  Paul
                  • 6. Re: DDL operators on v$-views
                    Pavel
                    Hello Aman!

                    I still have problems with view v$SQL_SHARED_MEMORY, Sir,
                    do't returning rows

                    Best regards,
                    Paul
                    • 7. Re: DDL operators on v$-views
                      rp0428
                      >
                      use v_$datfile instead of V$datafile
                      >
                      Yes but that just refers to itself - see the last line of your post
                      >
                      from v$datafile
                      >
                      You need to use v$fixed_view_definition like I showed above - DBMS_METADATA doesn't work the same for the sys views.

                      v$fixed_view_definition will show the actual system tables - try my last query above
                      • 8. Re: DDL operators on v$-views
                        sb92075
                        Paul wrote:
                        Hello Aman!

                        I still have problems with view v$SQL_SHARED_MEMORY, Sir,
                        do't returning rows

                        Best regards,
                        Paul
                        stop wasting time on your snipe hunt!
                        http://en.wikipedia.org/wiki/Snipe_hunt
                          1* select dbms_metadata.get_ddl('VIEW','V_$SQL_SHARED_MEMORY') from dual
                        SQL> /
                        
                        DBMS_METADATA.GET_DDL('VIEW','V_$SQL_SHARED_MEMORY')
                        --------------------------------------------------------------------------------
                        
                          CREATE OR REPLACE FORCE VIEW "SYS"."V_$SQL_SHARED_MEMORY" ("SQL_TEXT", "SQL_FU
                        LLTEXT", "HASH_VALUE", "SQL_ID", "HEAP_DESC", "STRUCTURE", "FUNCTION", "CHUNK_CO
                        M", "CHUNK_PTR", "CHUNK_SIZE", "ALLOC_CLASS", "CHUNK_TYPE", "SUBHEAP_DESC") AS
                          select "SQL_TEXT","SQL_FULLTEXT","HASH_VALUE","SQL_ID","HEAP_DESC","STRUCTURE"
                        ,"FUNCTION","CHUNK_COM","CHUNK_PTR","CHUNK_SIZE","ALLOC_CLASS","CHUNK_TYPE","SUB
                        HEAP_DESC" from v$sql_shared_memory
                        • 9. Re: DDL operators on v$-views
                          rp0428
                          >
                          I still have problems with view v$SQL_SHARED_MEMORY
                          >
                          Did you read my last two posts?
                          These work
                          SELECT view_definition FROM
                           v$fixed_view_definition
                           WHERE view_name='V$SQL_SHARED_MEMORY'
                           
                          SELECT view_definition FROM
                           v$fixed_view_definition
                           WHERE view_name='GV$SQL_SHARED_MEMORY'
                          • 10. Re: DDL operators on v$-views
                            CKPT
                            use v_$datfile instead of V$datafile
                            Yes but that just refers to itself - see the last line of your post
                            from v$datafile
                            Thanks for the information you provided :)
                            Yes, its showing from self object, "PLUGIN_RESETLOGS_TIME" from v$datafile. which doesnt make sense to know the exact source.
                            You need to use v$fixed_view_definition like I showed above - DBMS_METADATA doesn't work the same for the sys views.

                            v$fixed_view_definition will show the actual system tables - try my last query above
                            yeah even these are fetching from from GV$DATAFILE
                            Again v$fixed_view_definition describing the definitions of views.



                            If we see an example from DBA_DATA_FILES(dictionary views) , they been used even other views including x$ tables too,
                            But in case of v$views its referring to the same view again, where we starting. :)
                            SQL> select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES','SYS') from dual;
                            
                              CREATE OR REPLACE FORCE VIEW "SYS"."DBA_DATA_FILES" ("FILE_NAME", "FILE_ID", "
                            TABLESPACE_NAME", "BYTES", "BLOCKS", "STATUS", "RELATIVE_FNO", "AUTOEXTENSIBLE",
                             "MAXBYTES", "MAXBLOCKS", "INCREMENT_BY", "USER_BYTES", "USER_BLOCKS", "ONLINE_S
                            TATUS") AS
                              select v.name, f.file#, ts.name,
                                   ts.blocksize * f.blocks, f.blocks,
                                   decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
                                   f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
                                   ts.blocksize * f.maxextend, f.maxextend, f.inc,
                                   ts.blocksize * (f.blocks - 1), f.blocks - 1,
                                   decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
                                     decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
                            from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
                            where v.file# = f.file#
                              and f.spare1 is NULL
                              and f.ts# = ts.ts#
                              and fe.fenum = f.file#
                            union all
                            select
                                   v.name,f.file#, ts.name,
                                   decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
                                   decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
                                   decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
                                   f.relfile#,
                                   decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
                                   decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
                                   decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
                                   decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
                                   decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
                                   decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
                                   decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
                                     decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
                            from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
                            where v.file# = f.file#
                              and f.spare1 is NOT NULL
                              and v.file# = hc.ktfbhcafno
                              and hc.ktfbhctsn = ts.ts#
                              and fe.fenum = f.file#
                            Edited by: CKPT on Feb 24, 2012 9:04 AM
                            • 11. Re: DDL operators on v$-views
                              rp0428
                              select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES','SYS') from dual;
                              Thanks for that. That's a new one for me. Always wondered why it didn't didn't work for the V$'s though.
                              • 12. Re: DDL operators on v$-views
                                CKPT
                                rp0428 wrote:
                                select dbms_metadata.get_ddl('VIEW','DBA_DATA_FILES','SYS') from dual;
                                Thanks for that. That's a new one for me. Always wondered why it didn't didn't work for the V$'s though.
                                My assumption on views.
                                Any/all v$ views can be accessible even in mount status. Then why should use again BASE tables? ;-)
                                • 13. Re: DDL operators on v$-views
                                  rp0428
                                  >
                                  why should use again BASE tables
                                  >
                                  Mostly I agree. But, for better or worse, I've been getting deeper and deeper into some of Richard Foote's stuff and Jonathan Lewis (latest Oracle Core ... book) and some of those queries are strictly on the base tables. Jonathan is pretty good about explaining the acronyms when he can (e.g. x$ksmfsv: kernel services - memory - fixed SGA variables) and I find it handy to try to find out which system views use a table that he queries directly since the view usually puts a more readable name to the columns.

                                  But I sure avoid them when I can.
                                  • 14. Re: DDL operators on v$-views
                                    CKPT
                                    rp0428 wrote:
                                    >
                                    why should use again BASE tables
                                    >
                                    Mostly I agree. But, for better or worse, I've been getting deeper and deeper into some of Richard Foote's stuff and Jonathan Lewis (latest Oracle Core ... book) and some of those queries are strictly on the base tables. Jonathan is pretty good about explaining the acronyms when he can (e.g. x$ksmfsv: kernel services - memory - fixed SGA variables) and I find it handy to try to find out which system views use a table that he queries directly since the view usually puts a more readable name to the columns.

                                    But I sure avoid them when I can.
                                    Seen lot of articles in Sir JL blog, But not easy to understand these x$ tables AFAIK.
                                    Need some inputs from experts again. :)
                                    1 2 3 4 Previous Next