Need to create a Materialized View for a table with a long column

I am trying to create a MV for a table with a long column, having around 6.5 million rows.
This is the description of the table:-

SQL> desc ec2.lxdesc_38043c1d;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 LXOID                                              NUMBER
 LXKIND                                             NUMBER
 LXDESC                                             LONG

SQL> select count(*) from ec2.lxdesc_38043c1d;

  COUNT(*)
----------
   6560768

When i create a MV reading the lxdesc, then I get this error.

CREATE MATERIALIZED VIEW EC2LINK.EMARS
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
NEVER REFRESH
AS
SELECT t.lxtype, t.lxname, t.lxmoddate,  decode(t.lxstate, -536841470, 'Released', 'Unknown') State, a.lxdesc, b.LXVAL DesignResponsiblity, c.lxval Mass
  FROM ec2.lxbo_38043c1d t, ec2.lxdesc_38043c1d a, ec2.LXSTRING_38043C1D b, ec2.LXreal_38043c1d c
 WHERE b.lxtype='1595247718' AND c.LXTYPE = '213294734'
   AND t.lxtype='2089847660'


I cannot afford to create another table and replicate all the data into VarChar, since the data is huge..Is there any way in which i can create this MV.
Any inputs will really help

Thanks
ParadkarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ajexpertCommented:
Hi,
You cannot create MV on a table containing long column
You might have to convert to LOB.  Refer to these links
http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/statements_6002.htm
http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part4.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ParadkarAuthor Commented:
Thank you for your response,

Well....I would prefer to have another temp table to have all the Long values read into Varchar. From this table I could create the MV. The issue in this case for me is -
How would I refresh the temp table for only delta objects once it gets changed in the base table..

In this case for example:-
If I create a temp table => tempdescriptiontable with Varchar2 for lxdesc column as stated above for the table lxdesc_38043c1d and copy all the long column data into it,
how do i refresh this"tempdescriptiontable " table for the delta changes that happens on the lxdesc_38043c1d table.
The changes to this lxdesc_38043c1d  table keeps happening and i need to have the snapshot of the data at a particular point...

I cannot add any triggers to the base table lxdesc_38043c1d or make any modifications to this since its a table managed internally by ematrix application..

If it can hold only the delta objects that have been changed then I could still use it..

I am sorry the table names are complex since its a ematrix DB...
Anything that you could suggest?
0
ajexpertCommented:
You can create DBMS job to referesh the MV.
It depends on the requirement of how frequently you want to refresh MV.

Hope it helps
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ParadkarAuthor Commented:
You mean not having the MV, but having a temp table itself which acts like a Materialized View [that is refershed from the DBMS job]...

Could you please give me some pointers to the materials about creating DBMS jobs so that I could try?

Thanks!
0
ajexpertCommented:
0
ajexpertCommented:
Essentially you need to create DBMS_JOB and refresh MV based on business needs.

Hope it helps
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.