Home » SQL & PL/SQL » SQL & PL/SQL » Alter Materialized View log (merged)
Alter Materialized View log (merged) [message #399028] Mon, 20 April 2009 05:35 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

It is possible to rename column of the Materialized View log like

ALTER MATERIALIZED VIEW LOG ON DOCUMENTS_UD
RENAME custodian to capture_business_unit_code;

ADD Clause works with alter materialized view log but Rename clause gives following error..

ERROR at line 2:
ORA-14155: missing PARTITION or SUBPARTITION keyword

Thanks in advance

Uday
Re: Alter Materialized View Log [message #399036 is a reply to message #399028] Mon, 20 April 2009 06:02 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


>>It is possible to rename column of the Materialized View log like

Why do you want rename column of the materialized view log??

Babu
Re: Alter Materialized View Log [message #399039 is a reply to message #399036] Mon, 20 April 2009 06:10 Go to previous messageGo to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi Babu,

Please give the query.

I am renaming the column of the table which is also added there in the materialized view log thru alter statement, and i do not want to drop and recreate the mat view log and build the indexes again.

Uday
Re: Alter Materialized View Log [message #399044 is a reply to message #399039] Mon, 20 April 2009 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bholeuday wrote on Mon, 20 April 2009 13:10
Hi Babu,

Please give the query.

I am renaming the column of the table which is also added there in the materialized view log thru alter statement, and i do not want to drop and recreate the mat view log and build the indexes again.

Uday

You have to.

Regards
Michel

Re: Alter Materialized View Log [message #399047 is a reply to message #399039] Mon, 20 April 2009 06:19 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> create table t (a int);

Table created.

SQL> create materialized view log on t with rowid;

Materialized view log created.

SQL> create materialized view mv_t refresh fast with rowid as select * from t;

Materialized view created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

SQL> desc mv_t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

SQL> desc mlog$_T
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 M_ROW$$                                            VARCHAR2(255)
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)

SQL> select * from mlog$_T;

no rows selected

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mlog$_T;

M_ROW$$
--------------------------------------------------------------------------------
SNAPTIME$ D O
--------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
AAAEBSAAJAAAAB4AAA
01-JAN-00 I N
FE


Materialized View log only contain data transfer details. Why do you want rename materialized view log column??

If you want add/rename any new column in your base table. You should re-create your materialized view not materialized view log.

What's your exact requirement??

Babu
Re: Alter Materialized View Log [message #399068 is a reply to message #399047] Mon, 20 April 2009 07:25 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
gentlebabu wrote on Mon, 20 April 2009 13:19
Materialized View log only contain data transfer details. Why do you want rename materialized view log column??



That is not entirely true:

SQL> create table t ( a int  , b date ) ;

Table created.

SQL> create materialized view log on t with rowid (b) ;

Materialized view log created.

SQL> desc mlog$_t

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 B						    DATE
 M_ROW$$					    VARCHAR2(255)
 SNAPTIME$$					    DATE
 DMLTYPE$$					    VARCHAR2(1)
 OLD_NEW$$					    VARCHAR2(1)
 CHANGE_VECTOR$$				    RAW(255)



As you can see there is a column B in the materialized view log-table.
Re: Alter Materialized View Log [message #399069 is a reply to message #399068] Mon, 20 April 2009 07:35 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> drop materialized view log on t;

Materialized view log dropped.

SQL> create materialized view log on t with rowid (a);

Materialized view log created.

SQL> desc mlog$_t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 M_ROW$$                                            VARCHAR2(255)
 SNAPTIME$$                                         DATE
 DMLTYPE$$                                          VARCHAR2(1)
 OLD_NEW$$                                          VARCHAR2(1)
 CHANGE_VECTOR$$                                    RAW(255)


Ok I agree. If you not specify column (using rowid) by default it's created by database rowid.

Babu
How to refresh Materialized View log [message #399312 is a reply to message #399028] Tue, 21 April 2009 09:56 Go to previous messageGo to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

I have a table say abc

create table abc
(
a number(4), Primary Key
b varchar2(10),
c varchar2(20)
);


then i have created mat view log

create materialized view log on abc;

Now i am adding column c (Non primary Key) to mat view log...

alter materialized view log on abc
add (c) ;


Now after some time i fire alter statement on table renaming the column c to d

alter table abc rename column c to d;

Now i want to refresh the mat view log according to the table definition.

Thanks in advance Smile



Re: Alter Materialized View log (merged) [message #399399 is a reply to message #399028] Wed, 22 April 2009 01:39 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
As far as I know your only option is to recreate the materialized view log table.
A materialized view log table is used from withing a trigger.
So renaming the column in your log table would do more harm than good.

Way to, again as far as I know, go is:
- perform a full/fast refresh on the table you're about to alter
- disable replication (one way or another)
- drop materialized view log
- rename column of your base table
- (re-)create materialized view log
- perform a full refresh
- enable replication

Maybe there are other faster or smarter solutions, but the one I described is a safer one Wink
Previous Topic: restrict any user not to select more than 100 rows by his select
Next Topic: Change date format permanently
Goto Forum:
  


Current Time: Wed Dec 07 10:56:59 CST 2016

Total time taken to generate the page: 0.09917 seconds