List Materialized Views In a Refresh Group

To show the members in a materialized view refresh group, we can use the following query that takes advantage of DBA_RGROUP and DBA_RCHILD. DBA_RGROUP includes all refresh groups. DBA_RCHILD contains all the children in any refresh group.

SQL>  select r.owner, r.name gp_name, c.name mv_name 
from DBA_RCHILD c, DBA_RGROUP r 
where c.owner=r.owner and c.REFGROUP=r.REFGROUP 
order by r.owner, r.name, c.name;

OWNER                          GP_NAME                        MV_NAME
------------------------------ ------------------------------ ------------------------------
TEST_COMPS                     MV_GRP                         MV_SH_ABC
TEST_COMPS                     MV_GRP                         MV_SH_DEF
TEST_COMPS                     MV_GRP                         MV_SH_TEST

Hide PL/SQL Scripts For Function or Procedure

To protect the intellectual properties or for security reasons, we may want to hide the source PL/SQL code. To do this, we use "wrap" command to translate the PL/SQL script files into a format that can not be understood by human beings but can be processed by the database.
For example we have the following script file add_func.sql.

$ cat add_func.sql
create or replace function add2 (
  a number,
  b number
)
return number
is
begin
return a+b;
end;
/

We run wrap to convert it into a plb file that is not understandable by human beings.

oradba@bdm64-PC ~/projects_c/sql
$ wrap iname=add_func.sql

Above command generates the following file, add_func.plb. The following are its content.

create or replace function add2 wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
52 7d
bWldIYVqPP9njmOrnggm/xpHL1wwg8eZgcfLCNL+XlpZoX8JUI8JabjDpZmBMsCyJfvCkA5+
0S5E4sqxyFDKJHWpF3yctwqYsCxZtMwF+uzZPXKV7Pumreu29A==

We connect to the database and run the file add_func.plb to create the function just like running a regular PL/SQL script file.

SQL> @add_func.plb

The following queries show that the source code for this function is not readable. This is exactly what we want.

SQL> select text from user_source where name='ADD2';

TEXT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------------------------------------------------
function add2 wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
52 7d
bWldIYVqPP9njmOrnggm/xpHL1wwg8eZgcfLCNL+XlpZoX8JUI8JabjDpZmBMsCyJfvCkA5+
0S5E4sqxyFDKJHWpF3yctwqYsCxZtMwF+uzZPXKV7Pumreu29A==


SQL> select dbms_metadata.get_ddl('FUNCTION', 'ADD2') from dual;

DBMS_METADATA.GET_DDL('FUNCTION','ADD2')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FUNCTION "PROD"."ADD2" wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
52 7d
bWldIYVqPP9njmOrnggm/xpHL1wwg8eZgcfLCNL+XlpZoX8JUI8JabjDpZmBMsCyJfvCkA5+
0S5E4sqxyFDKJHWpF3yctwqYsCxZtMwF+uzZPXKV7Pumreu29A==