hi Guys,
How do you guys normally find the size of the view?
i got a situation here.
DB_1 has a view (created by selecting from a few tables via union..)
DB_2 has a procedure that will refresh a materialized view ( select * from <the_table_from _db_1)@DB_1_database; )
Question1:
how can i find the size of the view that is in DB_1. I don't think so i can use dba_segments to find the size as i don't find a segment_type that is = 'VIEW'
Question2: the below error was reported by apps team.
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-02063: preceding line from DB_1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at "FWSS.R_<procedures>", line 6
ORA-06512: at line 1
i tried to search for similair error on both of my database alert log but could not found it. The above error should be seen on alert logs.
i seen quite a few of the below errors from the alert log of DB_1.
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
So here is my queston, does the above error occurs on DB_1 when DB_2 Procedure invoke a refresh of its materliized view that is selecting from the view via a db link in DB_1?
I am suspecting the view is too big that is causing the temp error to apear.