Thursday, August 01, 2013

Get the source code for Oracle database views and materialized view

Oracle user_views/user_mviews contains source code about the view/materialized views.
SQL> select text from user_views where view_name='V_R_SUM';
TEXT
--------------------------------------------------------------------------------

select to_char(dt_tm, 'YYYY') yy, to_char(DT_TM, 'MM') mm, to_char(DT_TM,'DD') dd, to_char(DT_TM, 'HH24') hh, to_char(DT_TM, 'MI') mi, to_char(DT_TM,'SS') ss, min(price) mi_p, avg(price) a_p, max(price) mx_p from MV_VIX3 group by to_char(dt_tm, 'YYYY'),to_char(DT_TM, 'MM'),to_char(DT_TM,'DD'),to_char(DT_TM, 'HH24'),to_char(DT_TM, 'MI'), to_char(DT_TM,'SS')

SQL> select query from user_mviews where mview_name='MV_ALL_FOR_SUM';
QUERY
--------------------------------------------------------------------------------
select
a.TXN_ROWID,
a.TRN_CUSTOMER_ACCOUNT_NUM,
a.TRN_TRANSACTION_DT daily_txn_dt,
a.TRN_TRANSACTION_AMT,
a.STAR_ROWID,
a.CUSTOMER_ID_NUMBER,
a.STAR_TRAN_DATETIME,
b.TRANSACTION_AMT frd_clm_amt, b.TRANSACTION_DT frd_clm_dt, b.clm_rowid,
c.fee, c.od_rowid
from
tbl_m_txn_star_final a,
tbl_m_txn_star_clm b,
mv_m_txn_star_od c
where a.txn_rowid=b.txn_rowid(+) and
a.txn_rowid=c.txn_rowid(+)

It is very helpful as we can always find out the logic that is used to created the view. This is one of the advantage of using views over tables.

1 comment:

Unknown said...

In a press briefing after his talk, Mark Hurd Oracle has been spending the past several years rewiring almost all of its existing legacy software so that there are cloud versions available on subscription basics. Oracle will spend on building new data centers or how many new facilities it needs to compete with cloud computing giants….