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

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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google