| Create Materialized view on table which containing CLOB/BLOB [message #218081] |
Tue, 06 February 2007 14:31  |
Ksaravan
Messages: 17
Registered: January 2007
Location: Portland
|
Junior Member |
|
|
Hi,
I want to create mview on a table which contains LOB columns, im able to create just select * from the table. But how to use LOB_STORAGE_parameters and how to alter matview to change LOB storage parameters..I didnt get any one example from internet. Internet i m sending this to question to experts group.
Please help me.
Thanks
KSarava
|
|
|
|
|
|
| Re: Create Materialized view on table which containing CLOB/BLOB [message #218561 is a reply to message #218090] |
Thu, 08 February 2007 11:46  |
Ksaravan
Messages: 17
Registered: January 2007
Location: Portland
|
Junior Member |
|
|
SQL> desc test_img
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL VARCHAR2(15)
PHOTO1 BLOB
PHOTO2 BLOB
MINUTIAE BLOB
LFQUALITY NUMBER(4)
SQL> create materialized view TEST_IMG_MV
2 TABLESPACE USERS
3 BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND
4 AS SELECT * from TEST_IMG LOB(PHOTO1) STORE AS (TABLESPACE TEST_T01 STORAGE (INITIAL 6144 NEXT 6144)
5 CHUNK 4000
6 NOCHACHE LOGGING);
AS SELECT * from TEST_IMG LOB(PHOTO1) STORE AS (TABLESPACE TEST_T01 STORAGE (INITIAL 6144 NEXT 6144)
*
ERROR at line 4:
ORA-00933: SQL command not properly ended
What is the correct syntax?
create table is fine. But mat view will work, same table space it will look ( if i used diff one for LOB column in table mat view also look same tablespace or not?
Please helpe me with correct syntax. no where i can find out example with proper syntax..
|
|
|
|
| Re: Create Materialized view on table which containing CLOB/BLOB [message #412796 is a reply to message #218561] |
Sun, 12 July 2009 00:54 |
denissun
Messages: 3
Registered: July 2009
|
Junior Member |
|
|
I google to try to see if MV can be created on table with LOB type data. This led me to this old post  . Though it is old, I want to provide an answer specific to the syntax, I think the belowing will work:
CREATE MATERIALIZED VIEW "myname"."TEST_IMG_MV"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("PHOTO1") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("PHOTO2") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("MINUTIAE") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "TEST_IMG"."COL1" "COL1","TEST_IMG"."PHOTO1" "PHOTO1","TEST_IMG"."PHOTO2" "PHOTO2","TEST_IMG"."MINUTIAE" "MINUTIAE","TEST_IMG"."LFQULITY" "LFQULITY" FROM "TEST_IMG" "TEST_IMG";
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
