Re: replication, Materialized views, and transparent data encryption

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Tue, 25 Apr 2006 14:06:58 +0200
Message-ID: <444e11ec$0$4500$9b4e6d93@newsread2.arcor-online.net>

Frank van Bortel schrieb:
> Maxim Demenko wrote:
>

>>>The ORA-01732 could be thrown because you did not create
>>>an updateable MV.
>>
>>Yes, i know (i was not sure - "alter table modify (column encrypt)"
>>equals to data modification), but i implied , OP was asking about read
>>only MV, maybe i am wrong.

>
>
> Would that be the MV table, or the originating one?
>
> It's interesting to see you can build MV's - I would have said
> you could not, reading the manual, from tables with encryption.
>

Here what i did:

sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 25 14:05:21 2006

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

scott_at_ORA102> alter system set encryption key identified by oracle;

System altered.

scott_at_ORA102> create tablespace tde datafile '/opt/oracle/admin/@/tde.dbf' size 1M;

Tablespace created.

scott_at_ORA102> alter table emp modify(ename encrypt);

Table altered.

scott_at_ORA102> create materialized view log on emp with sequence,rowid;

Materialized view log created.

scott_at_ORA102> @mv
scott_at_ORA102> set echo on
scott_at_ORA102> create materialized view emp_mv

   2 tablespace tde
   3 refresh fast
   4 on demand
   5 with rowid
   6 as select * from emp
   7
scott_at_ORA102> /

Materialized view created.

scott_at_ORA102> select ename from emp_mv;

ENAME

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER 14 rows selected.

scott_at_ORA102> conn / as sysdba
Connected.
sys_at_ORA102> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

sys_at_ORA102> !strings /opt/oracle/admin/ORA102/tde.dbf|grep "SCOTT" SCOTT sys_at_ORA102>

Best regards

Maxim Received on Tue Apr 25 2006 - 07:06:58 CDT