Tom,
Thanks for the "right on target" answer. I never tried to name a MVIEW same as an existing table. I thought I'd get the "ORA-00955: name is already used by an existing object" error.
Does Oracle handles MVIEW names differently its objects namespace?

Thanks.

hi,
i have to PEL on my MV, so my PM said me to registered my MV using prebuild and created a table of the same query.
but i'm not very clear about the "ON PREBUILT TABLE" clause.
why do we use that..could u elloborate

Tom:

We have a request to copy production data (materialized view tables data) into ST/UAT instance. Those SCHEMA tables in ST/UAT are also replicated via Read-only materialized views. So we could not refresh the data with production data as it was not allowed.

So in order to refresh with production data, we created another DB schema and imported the materialized view tables (regular tables) with data. when i describe those tables :

I see "M_ROW$$" extra column in each table as the first column. Will this exta column cause any issue for any data maniuplation/queries/alters.. ?

Can i manually drop this "M_ROW$$" column (any issue later ?)

SQL> desc SITE
Name Null? Type
----------------------------------------- -------- ----------------------------
M_ROW$$ VARCHAR2(18)
ODBID NOT NULL NUMBER(11)
CREATETMSTMP NOT NULL TIMESTAMP(6)
CREATEUSER NOT NULL CHAR(8)
CREATETRAN NOT NULL CHAR(16)
LASTUPDTMSTMP NOT NULL TIMESTAMP(6)
LASTUPDUSER NOT NULL CHAR(8)
LASTUPDTRAN NOT NULL CHAR(16)
SITE_CODE NOT NULL CHAR(8)
STATE NOT NULL CHAR(2)
PROVINCE NOT NULL CHAR(3)
POSTAL_CODE NOT NULL CHAR(10)
COUNTRY_CODE NOT NULL CHAR(4)
TIME_ZONE NOT NULL CHAR(8)
CITY VARCHAR2(64)
ADDRESS_LINE1 VARCHAR2(64)
ADDRESS_LINE2 VARCHAR2(64)
ADDRESS_LINE3 VARCHAR2(64)
STOP_DATE NOT NULL DATE
COUNTY VARCHAR2(32)
SITE_NAME NOT NULL CHAR(11)
ALT_SITE_CODE NOT NULL CHAR(12)
SITE_ALIAS VARCHAR2(30)



thanks!

>> you didn't really say what you did - that looks more like dbms_redefinition than a materialized view. what were your EXACT steps.

I had to copy the production data (ONE schema, which is replicated via materialized views (not on PREBULT table) to another environment for production tickets testing.So i created another DB schema and export/imported the materialized view tables (regular tables) with data.

After import,

when i describe those tables :

I see "M_ROW$$" extra column in each table as the first column. Will this exta column cause any issue for any data maniuplation/queries/alters.. ?


<< I would not suggest using replication from production into test - that means test is not test but yet another production box. Why not just RESTORE prod on test??? Much easier, no impact on prod, verifies you can actually restore your backups.

We are not replicating production data in test. We are just export/import the materialized view tables in test environment.

Thanks!

<< does the source data therefore not have a primary key. You still have not described in detail what you did (eg: meaning: what commands did you type into the console)

Materialized views are all ROWID based. so no primary keys.

This is what i did :

a) export the entire schema (which are all replicated via materialized views)

b) created another test schema and imported the entire schema, (just as regualar tables).

<< ... We have a request to copy production data (materialized view tables data) into ST/UAT instance. Those SCHEMA tables in ST/UAT are also replicated via Read-only materialized views. ....

Since i cannot copy production data into existing st/uat (which is also replicated via read-only MVs), i created another test schema and imported the production data (as regular tables).


Do you need any additional info.

Hi Tom, I thought this might be a relevant question here, please correct me if i am wrong!

My question is that i have a set of materialized views in my database say 'D1', when i export the database and import it into another database 'D2', i see that the materialized views are getting converted to tables and they do not refresh on commit

we are using 10g Enterprise Edition Release 10.2.0.4.0

here are my syntax's

1) for export
exp username/pass file=exp_1.dmp log=exp_1.log

2) for import
imp username/pass fromuser=Y touser=X file=exp_1.dmp log=imp_1.log

3) syntax for Intial MV creation

CREATE MATERIALIZED VIEW LOG ON ABC WITH PRIMARY KEY ,rowid;

CREATE MATERIALIZED VIEW ABC_MV
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM ABC;

ops$tkyte%ORA10GR2> drop user other cascade;

User dropped.

ops$tkyte%ORA10GR2> drop user orig cascade;

User dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant dba to orig identified by orig;

Grant succeeded.

ops$tkyte%ORA10GR2> grant dba to other identified by other;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int primary key, y int );

Table created.

ops$tkyte%ORA10GR2> CREATE MATERIALIZED VIEW LOG ON t WITH PRIMARY KEY ,rowid;

Materialized view log created.

ops$tkyte%ORA10GR2> grant all on t to orig;

Grant succeeded.

ops$tkyte%ORA10GR2> grant all on t to other;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect orig/orig
Connected.
orig%ORA10GR2>
orig%ORA10GR2> CREATE MATERIALIZED VIEW mv
  2  BUILD IMMEDIATE
  3  REFRESH FAST ON COMMIT
  4  AS SELECT * FROM ops$tkyte.t;

Materialized view created.

orig%ORA10GR2>
orig%ORA10GR2> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV                             TABLE
SYS_C007638                    INDEX
MV                             MATERIALIZED VIEW

orig%ORA10GR2> select mview_name, refresh_method, refresh_mode from user_mviews;

MVIEW_NAME                     REFRESH_ REFRES
------------------------------ -------- ------
MV                             FAST     COMMIT

orig%ORA10GR2>
orig%ORA10GR2> !exp userid=/ owner=orig

Export: Release 10.2.0.1.0 - Production on Tue Jul 29 15:28:39 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ORIG
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ORIG
About to export ORIG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ORIG's tables via Conventional Path ...
. . exporting table                             MV          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

orig%ORA10GR2>
orig%ORA10GR2> connect other/other
Connected.
other%ORA10GR2> select object_name, object_type from user_objects;

no rows selected

other%ORA10GR2> select mview_name, refresh_method, refresh_mode from user_mviews;

no rows selected

other%ORA10GR2> !imp userid=/ fromuser=orig touser=other

Import: Release 10.2.0.1.0 - Production on Tue Jul 29 15:29:04 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ORIG's objects into OTHER
. . importing table                           "MV"          0 rows imported
Import terminated successfully without warnings.

other%ORA10GR2> select object_name, object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV                             TABLE
SYS_C007639                    INDEX
MV                             MATERIALIZED VIEW

other%ORA10GR2> select mview_name, refresh_method, refresh_mode from user_mviews;

MVIEW_NAME                     REFRESH_ REFRES
------------------------------ -------- ------
MV                             FAST     COMMIT

Thanks a lot for the response here is more elaborate evidence of my problem

SQL>  drop user other cascade;
 drop user other cascade
           *
ERROR at line 1:
ORA-01918: user 'OTHER' does not exist


SQL> drop user orig cascade;
drop user orig cascade
          *
ERROR at line 1:
ORA-01918: user 'ORIG' does not exist

SQL> grant dba to orig identified by orig;

Grant succeeded.

SQL> grant dba to other identified by other;

Grant succeeded.

SQL> grant create materialized view to orig;

Grant succeeded.

SQL> grant create materialized view to other;

Grant succeeded.

connect using sqlplus orig/orig

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 31 10:30:01 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t ( x int primary key, y int );

Table created.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 X                                         NOT NULL NUMBER(38)
 Y                                                  NUMBER(38)

SQL> CREATE MATERIALIZED VIEW LOG ON t WITH PRIMARY KEY ,rowid;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW mv
  2    BUILD IMMEDIATE
  3    REFRESH FAST ON COMMIT
  4    AS SELECT * FROM t;

Materialized view created.

SQL> col object_name format a20
SQL>  select object_name, object_type from user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
RUPD$_T              TABLE
MV                   TABLE
T                    TABLE
SYS_C006205          INDEX
SYS_C006206          INDEX
MV                   MATERIALIZED VIEW
MLOG$_T              TABLE

7 rows selected.

SQL> set linesize 100
SQL> select mview_name, refresh_method, refresh_mode from user_mviews;

MVIEW_NAME                     REFRESH_ REFRES
------------------------------ -------- ------
MV                             FAST     COMMIT

SQL> insert into t values (1, 1);

1 row created.

SQL> insert into t values (2, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mv;

         X          Y
---------- ----------
         1          1
         2          2

SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2

SQL> exit

C:\Documents and Settings\log>exp orig/orig owner=orig

Export: Release 10.2.0.1.0 - Production on Thu Jul 31 10:42:19 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ORIG
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ORIG
About to export ORIG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ORIG's tables via Conventional Path ...
. . exporting table                        MLOG$_T          0 rows exported
. . exporting table                             MV          2 rows exported
. . exporting table                        RUPD$_T
. . exporting table                              T          2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

C:\Documents and Settings\log>imp other/other fromuser=orig touser=other

Import: Release 10.2.0.1.0 - Production on Thu Jul 31 10:46:06 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by ORIG, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table                      "MLOG$_T"          0 rows imported
. . importing table                           "MV"          2 rows imported
. . importing table                            "T"          2 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE SNAPSHOT LOG ON "ORIG"."T" WITH ROWID , PRIMARY KEY EXCLUDING NEW VA"
 "LUES USING ("MLOG$_T", (7, 'TSTORA10', 99, '2008-07-31:10:40:08', '2008-07-"
 "31:10:40:08', '2008-07-31:10:37:37', '4000-01-01:00:00:00','4000-01-01:00:0"
 "0:00','4000-01-01:00:00:00', 1, "X", '2008-07-31:10:37:37', 2, 1, 41, '2008"
 "-07-31:10:40:08', ("RUPD$_T")))"
Import terminated successfully with warnings.

C:\Documents and Settings\log>sqlplus other/other

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 31 10:47:05 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> col object_name format a20
SQL> set linesize 100
SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2

SQL> select * from mv;

         X          Y
---------- ----------
         1          1
         2          2

SQL> select object_name, object_type from user_objects;

OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
MLOG$_T              TABLE
MV                   TABLE
SYS_C006209          INDEX
RUPD$_T              TABLE
T                    TABLE
SYS_C006210          INDEX
MV                   MATERIALIZED VIEW

7 rows selected.

SQL> select mview_name, refresh_method, refresh_mode from user_mviews;

MVIEW_NAME                     REFRESH_ REFRES
------------------------------ -------- ------
MV                             FAST     COMMIT

SQL> insert into t values (3, 2);

1 row created.

SQL> insert into t values (4, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

         X          Y
---------- ----------
         1          1
         2          2
         3          2
         4          2

SQL> select * from mv;

         X          Y
---------- ----------
         1          1
         2          2

SQL>exit

So if you see once i import the materialized view to the new schema, it does not work as an MV, but just a normal table , as i tried to insert row 3, 4 in table t and it did not reflect in MV

I see an error though during import which i did not see in your example.

Thanks once again for your prompt reply, so this is what i infer from your reply, please correct me if i am wrong

MV logs are always tied to a specific schema/database, they do not move along with the other objects (so fromuser---touser does not work in this scenario).

is this a solution?

Once we get data from one(orig) schema/database to another(other) schema/database, we should rebuilt just the materialized view logs after the refresh so that MV's could be fast refreshable
i will test this scenario...and update you with the results

or do you think there is some other solution?

ok...this is the error i get when i move data from my production environment to my development environment, so then to do this we take export from the production database and import it into the development database, that is the reason i have to user from/to user.

what would be you suggestion in this case?