How to Extract / Get Script for DBA_, V$ Oracle Data Dictionary Views?

advertisements

_____________________________________________________________________________________________________________________

For DBA_, V$ data dictionary views you can use DBMS_METADATA package to get the source code. See an example for DBA_SYNONYMS dictionary view.

SQL> set pages 1000

SQL> set long  10000

SQL> select dbms_metadata.get_ddl('VIEW','DBA_SYNONYMS') FROM DUAL;

DBMS_METADATA.GET_DDL('VIEW','DBA_SYNONYMS')

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

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_SYNONYMS" ("OWNER", "SYNONYM_NAME", "T

ABLE_OWNER", "TABLE_NAME", "DB_LINK") AS

  select u.name, o.name, s.owner, s.name, s.node

from sys.user$ u, sys.syn$ s, sys."_CURRENT_EDITION_OBJ" o

where o.obj# = s.obj#

  and o.type# = 5

  and o.owner# = u.user#

For V$ views you have to give V_$ as the argument to the DBMS_METADATA package

SQL> select dbms_metadata.get_ddl('VIEW','V_$LOGFILE') from dual;

DBMS_METADATA.GET_DDL('VIEW','V_$LOGFILE')

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

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$LOGFILE" ("GROUP#", "STATUS", "TYPE", "

MEMBER", "IS_RECOVERY_DEST_FILE") AS

  select "GROUP#","STATUS","TYPE","MEMBER","IS_RECOVERY_DEST_FILE" from v$logfile

SQL> select dbms_metadata.get_ddl('VIEW','GV_$LOGFILE') from dual;

DBMS_METADATA.GET_DDL('VIEW','GV_$LOGFILE')

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

  CREATE OR REPLACE FORCE VIEW "SYS"."GV_$LOGFILE" ("INST_ID", "GROUP#", "STATUS

", "TYPE", "MEMBER", "IS_RECOVERY_DEST_FILE") AS

  select "INST_ID","GROUP#","STATUS","TYPE","MEMBER","IS_RECOVERY_DEST_FILE" fro

m gv$logfile

For v$_, GV$ view definitions are stored in v$fixed_view_definition where it is a public synonym to V_$FIXED_VIEW_DEFINITION table.

SQL> SELECT view_definition FROM v$fixed_view_definition

  WHERE view_name='V$LOGFILE';

VIEW_DEFINITION

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

select  GROUP# , STATUS , TYPE , MEMBER, IS_RECOVERY_DEST_FILE from GV$LOGFILE where inst_id = USERENV('Instance')

SQL>  SELECT view_definition FROM v$fixed_view_definition

  WHERE view_name='GV$LOGFILE';

VIEW_DEFINITION

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

select inst_id,fnfno, decode(fnflg,0,'',   decode(bitand(fnflg,1),1,'INVALID',   decode(bitand(fnflg,2),2,'STALE',   dec

ode(bitand(fnflg,4),4,'DELETED',   decode(bitand(fnflg,8+32),8,'',32,'',40,'','UNKNOWN'))))), decode(bitand(fnflg,8),0,'

ONLINE','STANDBY'), fnnam, decode(bitand(fnflg, 32),0,'NO','YES') from x$kccfn where fnnam is not null and fntyp=3

From the above definition you can understand that logfile information is coming from x$kccfn table.

_____________________________________________________________________________________________________________________

Website Stats