Home » SQL & PL/SQL » SQL & PL/SQL » Create Materialized view on table which containing CLOB/BLOB
Create Materialized view on table which containing CLOB/BLOB [message #218081] Tue, 06 February 2007 14:31 Go to next message
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 #218090 is a reply to message #218081] Tue, 06 February 2007 15:01 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Did you check the SQL reference? From 10.2, chapter "alter materialize view":

LOB_storage_clause

The LOB_storage_clause lets you specify the storage characteristics of a new LOB. LOB storage behaves for materialized views exactly as it does for tables. Please refer to the LOB_storage_clause (in CREATE TABLE) for information on the LOB storage parameters.

modify_LOB_storage_clause

The modify_LOB_storage_clause lets you modify the physical attributes of the LOB attribute lob_item or the LOB object attribute. Modification of LOB storage behaves for materialized views exactly as it does for tables.


Source: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2001.htm#sthref4113
Re: Create Materialized view on table which containing CLOB/BLOB [message #218561 is a reply to message #218090] Thu, 08 February 2007 11:46 Go to previous messageGo to next message
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 Go to previous message
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 Laughing . 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";

Previous Topic: How to get history of commands?
Next Topic: connecting 9i with visual studio2005
Goto Forum:
  


Current Time: Fri Dec 09 11:42:00 CST 2016

Total time taken to generate the page: 0.07999 seconds