SQL> connect test_user/test_user
Connected.
SQL> select count(*) from user_objects
2 /
COUNT(*)
----------
2
SQL> create table my_objects as select object_name, object_type, created from user_objects;
Table created.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> create materialized view my_objects on prebuilt table as select * from user_objects;
create materialized view my_objects on prebuilt table as select * from user_objects
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
SQL> create materialized view my_objects on prebuilt table as select object_name, object_type, created from user_objects;
Materialized view created.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> create sequence a_sequence start with 1 increment by 1;
create sequence a_sequence start with 1 increment by 1
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create table another_table (col_1 varchar2(5));
Table created.
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
SQL> insert into my_objects values ('A_DUMMY','TABLE',sysdate);
insert into my_objects values ('A_DUMMY','TABLE',sysdate)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL> exec dbms_mview.refresh('MY_OBJECTS','C');
PL/SQL procedure successfully completed.
SQL> select object_name, object_type, created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL>
The MV definition has to be the same as the underlying table, else I get an ORA-12060 error. Once the MV is built, any attempt at DML on the table returns an ORA-01732 error.
However, if I drop the MV, I can query the underlying table (still called "MY_OBJECTS") and execute DML on it :
SQL> drop materialized view my_objects ;
Materialized view dropped.
SQL> insert into my_objects values ('A_DUMMY','TABLE',sysdate);
1 row created.
SQL> select object_name, object_type , created from my_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS MATERIALIZED VIEW 20-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
A_DUMMY TABLE 20-MAR-09
6 rows selected.
SQL> select object_name, object_type, created from user_objects;
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ------------------- ---------
SOURCE_TB_1 TABLE 16-MAR-09
MV_2 TABLE 16-MAR-09
MY_OBJECTS TABLE 20-MAR-09
ANOTHER_TABLE TABLE 20-MAR-09
SQL>
The Table MY_OBJECTS still exists and shows the data as was present when it was refreshed as an MV, with the additional row from an INSERT as well.
However, I can no longer refresh it as a Materialized View :
SQL> exec dbms_mview.refresh('MY_OBJECTS','C');
BEGIN dbms_mview.refresh('MY_OBJECTS','C'); END;
*
ERROR at line 1:
ORA-23401: materialized view "TEST_USER"."MY_OBJECTS" does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
SQL>
Thus, an MV can be built on an underlying Table. Once the MV is built, manipulation is by way of MV Refresh and not directly on the Table. If the MV is dropped, it is only the MV definition that "goes away", the Table persists even with data.
(This could aleo be used as a way to "incrementally build a table").
.
.
20 comments:
Hemant,
One extra detail - if you create the MV with the "never refresh" option you are allowed to update the underlying table directly.
Regards
Jonathan Lewis
Jonathan,
Thanks. Just checked the documentation. I vaguely remember having read it earlier in the documentation and thinking that "NEVER REFRESH" meant "create this MV once only" (eg like a STATIC Table). But when I think of it,... using the words "STATIC Table", it does mean that it is a Table. However, the documentation doesn't say that it allows us to update the table manually.
Hemant
Hemant,
In what scenarios do you create a prebuilt table? what is the advantage of prebuilt table?
Thanks
Bhanu
Bhanu,
A few cases :
1. I want to setup an MV of a very large table in a remote database. Using the CREATE MATERIALIZED VIEW ... normally would mean that Oracle would pull all the data over SQL*Net, which also has noticeable overheads.
I might find it faster to
a. Export the table from the source
b. Compress and copy the export dump to the target
c. Import the table
Once I have the table imported, I then use CREATE MATERIALIZED VIEW .. PREBUILT ...
2. Before I came on the scene as a DBA / Consultant, the previous team had implemented replication of a table / subset by using manual / scripted methods to copy the table / data. Since the table has been copied last night and there have been no changes at the source since then, I just CREATE MATERIALIZED VIEW .. PREBUILT and then let Oracle refresh the table "automagically" for me !
Hemant
Very good post. I'm using MV but I didn't know about prebuilt table. Thanks.
Can you help me for creating the SNAPSHOT in oracle 8.0, after 8.0 which is known as MATERIALIZED VIEW. I have around multiple schemas and have created the seperate schema from where i want to fetch the records from different schema's.For that i want to create the SNAPAHOT, so can you explain in detail how to go ahead, i tried creating but data was not refreshing, so thought some where i might be wrong.
Thanks in advance.
Anonymous,
You could try the 8.0 documentation sets at
http://download.oracle.com/docs/cd/A64702_01/doc/index.htm
The Replication documentation is http://download.oracle.com/docs/cd/A64702_01/doc/index.htm
Hemant K Chitale
Hi Hemant,
On the following scenario you specified, if the table is constantly receiving updates on the source database, how will they be picked up after you complete the impdp? Do you need to use flashback_scn? if so how will you use that during the snapshot creation?
Thanks in advance
-Upendra
A few cases :
1. I want to setup an MV of a very large table in a remote database. Using the CREATE MATERIALIZED VIEW ... normally would mean that Oracle would pull all the data over SQL*Net, which also has noticeable overheads.
I might find it faster to
a. Export the table from the source
b. Compress and copy the export dump to the target
c. Import the table
Once I have the table imported, I then use CREATE MATERIALIZED VIEW .. PREBUILT ...
Upendra,
The export will be consistent as of a point in time -- irrespective of updates that may be occurring concurrently with the export.
I would have a MATERIALIZED VIEW LOG on the source table as well. So, when I CREATE the MV,the MV would "register" with the MV Log.
My MV can be a REFRESH FAST thereafter.
Hemant
Many Thanks Hemant K Chitale sir,
its help me great.
Thanks&Regards
Muhammad Abdul Halim
http://halimdba.blogspot.com/
Hi Hemant,
I'm not sure whether this post is still active. I like the idea of pre built table. I was wondering what is the technique if you want to incrementally load the target table which in this case is the pre built table. As I understand if we refresh the MV with a different set of data, the previous data will be lost in the table.
Have you done something similar. Please share if you have any ideas.
Darren,
See my latest post (dated 22-Jan-2012) : http://hemantoracledba.blogspot.com/2012/01/refreshing-mv-on-prebuilt-table.html
So following your post here. This what I did ...
So I have a complex MV.
CREATE MATERIALIZED VIEW SOI1.T$_DATA
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + interval '5' minute
WITH PRIMARY KEY
AS
SELECT DISTINCT docid,
fr.fileid,
custid,
mediadrop,
fr.mediaid,
typeid ,
fr.transactionid,
t1.parent_transactionid
FROM files_received fr,
source_values sv,
sources ds,
received mr,
transactions t1,
transactions t2
WHERE fr.fileid = sv.fileid
AND sv.datasourceid = ds.datasourceid
AND fr.mediaid = mr.mediaid
AND t1.transactionid = fr.transactionid
AND t2.transactionid = sv.transactionid;
One of the columns (custid) changed in files_received table. Then when I do a COMPLETE REFRESH on the T$_DATA it does not refresh. It does not refresh automatically too. Can anyone help ?
Anandam,
Possibly the joins do not return that particular CUSTID ?
Hemant
Hi hemant
when ever a change happens to base table
that should reflect in Materialized View
immediately
and automatically
no one will refresh that M.View to get latest information
please help me how to do this
Ranjitha,
You would define a Materialized View that is set to REFRESH ON COMMIT. Thus, every DML in the source table is immediately reflected in the MV on a commit being issued.
See http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#sthref224
Hemant K Chitale
Can you help me in creating a mview which refreshes on commit.
I am unable to create with the below select query. Can you help m in getting it out.
select deptno,job,sum(sal) from emp where hiredate > sysdate -365
group by deptno,job;
Anonymous,
You must review the documentation.
For example, please see the first restriction listed under "General Restrictions on Fast Refresh" at http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007007
It says "The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM."
A FAST Refresh must have deterministic expressions. SYSDATE is unknowable in the definition.
Hi Hemant,
We are creating a materialized view replication via SQLNet and want to avoid long refresh on FAST due to accumulation of data on the source table. However, the biggest tables we have do not have primary keys defined on the source and the vendor is not going to define them. As such we are creating the Materialized Views with ROWID. The article on Burleson stated that Fast refresh materialized views cannot be created on the prebuilt table if such table does not have a primary key?
Can you please confirm? http://www.dba-oracle.com/t_ora_12058_materialized_view_prebuilt_table.htm
THank you
Anonymous,
See the restrictions section of the ON PREBUILT TABLE clause in the documentation on the CREATE MATERIALIZED VIEW statement. You can't use an ON ROWID Materialized View.
Hemant
Post a Comment