How to refresh MVIEW in parallel

Refresh TYPES are COMPLETE/FAST/ON COMMIT/ON DEMAND
Couple of things you should do to make a mview to refresh in parallel:
1.) Alter the master table to make sure it allows parallelism:

alter table parallel (degree 4);
To check
select degree from dba_tables where table_name='';

2.) When you create the mview log (incase of FAST refresh mviews), create it with a parallel clause:

create materialized view log on parallel (degree 4);
OR
Incase of existing mviews:
alter materialized view log on parallel (degree 4);

3.) When you create the mview, create it with a parallel clause:

create materialized view REFRESH COMPLETE ON DEMAND as select /*+ PARALLEL (table_name,4) */ * from @dblink;
OR
create materialized view REFRESH COMPLETE ON DEMAND parallel 4 as select * from @dblink;
Or
Incase of existing mviews:
alter table parallel (degree 4);
alter materialized view parallel (degree 4);

Lastly while executing the refresh:

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'',PARALLELISM=>4);

This should make your mview refresh in parallel. With the above example the mview should run with parallel degree 4.

PS: Make sure that parallel is enabled for your database, check parameters:

PARALLEL_MAX_SERVERS
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_AUTOMATIC_TUNING

Make sure that the above parameter are set to allow any parallelism to work.
Sample values for these parameters:

SQL> show parameter PARALLEL_MAX_SERVERS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 120
SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384
SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean TRUE
SQL> show parameter PARALLEL_AUTOMATIC_TUNING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning boolean FALSE


Note:
1.) Replace with the table name.
2.) Replace with materialized view name.
3.) Replacedblink with the name of the database link.

Import Error: IMP-00032: SQL statement exceeded buffer length

While trying to import a big table with 1000s of partitions I got error:

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_PART_1" NOLOGGING COMPRESS, PARTITION "TEST_NOV_1" VALUES LESS THAN (2010112806) PCTFREE 0 PCTUSED 40...

The table was exported simiply with:

exp system/oracle file=tab.dmp tables=table_part

While run import:

imp system/oracle file=tab.dmp fromuser=scott touser=tiger

I got the above error.
I tried various combination of parameter buffer i.e.

imp system/oracle file=tab.dmp fromuser=scott touser=tiger buffer=1000000 commit=y

etc, but still got the error. So I wanted to know whats the value required for buffer.

Solution:
In the source database we need to run:

SQL> select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','table_part','SCOTT')) "SQL_LENGTH" from dual;

SQL_LENGTH
----------
3886502

The above output gives the value of buffer we need to have atleast to run the import, so I used:

imp system/o1ympic file=tabs.dmp log=tabs.log fromuser=SCOTT touser=TIGER commit=y ignore=Y buffer=44886502


This solved the problem.
Metalink Note: 846397.1