Materialized views and RMAN restore: better move small mviews out of big tablespaces One horrible feature of Oracle is that all tablespaces that have materialized views (mviews) must be included in an RMAN restore, even if what you want to restore has nothing to do with those tablespaces. I told Oracle Support about this problem, or limitation or restriction. He pointed me to an old document DUPLICATE SKIP TABLESPACE failes RMAN-05586 RMAN-05587 RMAN-05588 RMAN-05589 (Doc ID 1287276.1) which simply repeats the documentation saying such tablespaces must be included, with no explanation. The document said "Version 10.2.0.4 to 11.2.0.2". I told him I still experienced this limitation in 19c, getting RMAN-05589. Then he changed the ending version "11.2.0.2" to "19.28.0.0.0" in the document. At this time, there's no indication that Oracle will change this limitation. In 2018, I posted a message "Lift the restriction that RMAN duplicate must include tablespace with materialized views" in the Ideas section of the Oracle forum https://forums.oracle.com/ords/apexds/post/lift-the-restriction-that-rman-duplicate-must-include-table-5746 back when the section existed. There's no response or comment. There's some speculation about Oracle's reasoning at https://jonathanlewis.wordpress.com/2014/03/10/duplicate-database/ (See the paragraph beginning with "One oddity about the whole MV thing".) Before the restrition is evetually lifted, we should move the mviews that are small in total size from a very big tablespace to one or a few small tablespaces so an RMAN restore can avoid including such big tablespaces, saving significant time in database restore. Here's an example of finding such mviews (assume db_block_size 8192): SQL> select a.owner, mview_name, round(b.blocks*8192/1024) mv_kb, b.tablespace_name, round(sum(bytes)/1073741824) ts_gb from dba_mviews a, dba_tables b, dba_data_files c where a.container_name=b.table_name and a.owner=b.owner and b.tablespace_name=c.tablespace_name group by a.owner, mview_name, round(b.blocks*8192/1024), b.tablespace_name order by 4,1,2; OWNER MVIEW_NAME MV_KB TABLESPACE_N TS_GB -------- ------------------ ---------- ------------ ---------- SYSADM PS_ABC_PO_KKBAL_MV 13040 MDATBL 368 SYSADM PS_ABC_KK_ACT_LGMV 160 PSDEFAULT 686 SYSADM PS_ABC_KK_TRN_LGMV 0 PSDEFAULT 686 SYSADM PS_ABC_ACCTG_L2_MV 4681496 PSMATVW 5 SYSADM PS_ABC_PI_ACCTD_VW 4040 PSMATVW 5 SYSADM PS_ABC_PI_NOSCN_VW 8160 PSMATVW 5 In the above output, we see 3 tablespaces have mviews. Two of them have such candidate mviews worth moving: MDATBL tablespace is 368 GB in size but only has one mview of 13 MB in size. PSDEFAULT is 686 GB and has two mviews, 160 KB in size. If we can move the 3 mviews to other, small tablespaces, we can avoid including MDATBL and PSDEFAULT in a future database restore. On the other hand, PSMATVW tablespace is only 5 GB and most of it is taken by one big mview. So it's better to leave that alone. The commands you use to move mviews are as follows: --check invalid objects before the move, as a "baseline" select object_name, object_type from dba_objects where owner='...' and status='INVALID' order by 1,2; --optional but recommended alter . compile [body]; ... select object_name, object_type from dba_objects where owner='...' and status='INVALID' order by 1,2; --check for unusable indexes, as a "baseline" as well select owner, index_name from dba_indexes where status='UNUSABLE' order by 1,2; --optional but recommended alter index . rebuild; alter materialized view . move tablespace ; select object_name, object_type from dba_objects where owner='...' and status='INVALID' order by 1,2; select owner, index_name from dba_indexes where status='UNUSABLE' order by 1,2; --compare with the "baseline", compile newly invalid objects and rebuild indexes just becoming unusable as a result of the above mview move 2025-09-16