Table Stats Using Objects in a Materialized View Refresh Group

If you use Refresh Groups for your Materialized Views, it may be helpful to look at some basic table stats in a group.

This query will sum the sizes of the Table objects for each REFGROUP:

SELECT refgroup, SUM (MEG) as meg
  FROM (SELECT a.OWNER, a.SEGMENT_NAME, b.refgroup, a.BYTES / 1024 / 1024 AS MEG
          FROM dba_segments a, all_refresh_children b
         WHERE     a.segment_type = 'TABLE'
         and A.SEGMENT_NAME = B.NAME
               AND SEGMENT_NAME IN (SELECT name
                                      FROM all_refresh_children
                                     ))
   group by refgroup
   order by refgroup;


This query will return Object specifics for a single REFGROUP in question:


  SELECT a.table_name,
         a.num_rows ROW_count_FROM_STATS,
         b.col_count,
         C.MEG
    FROM dba_tables a,
         (  SELECT TABLE_NAME, COUNT (column_name) AS col_count
              FROM dba_tab_cols
             WHERE owner||TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
          GROUP BY TABLE_NAME
          ORDER BY TABLE_NAME) b,
         (  SELECT owner, table_name, TRUNC (SUM (bytes) / 1024 / 1024) Meg
              FROM (SELECT segment_name AS table_name, owner, bytes
                      FROM dba_segments
                     WHERE segment_type = 'TABLE' AND owner||SEGMENT_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
                    UNION ALL
                    SELECT i.table_name, i.owner, s.bytes
                      FROM dba_indexes i, dba_segments s
                     WHERE     s.segment_name = i.index_name
                           AND s.owner = i.owner
                           AND s.segment_type = 'INDEX'
                    UNION ALL
                    SELECT l.table_name, l.owner, s.bytes
                      FROM dba_lobs l, dba_segments s
                     WHERE     s.segment_name = l.segment_name
                           AND s.owner = l.owner
                           AND s.segment_type = 'LOBSEGMENT'
                    UNION ALL
                    SELECT l.table_name, l.owner, s.bytes
                      FROM dba_lobs l, dba_segments s
                     WHERE     s.segment_name = l.index_name
                           AND s.owner = l.owner
                           AND s.segment_type = 'LOBINDEX')
             WHERE owner||TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
          GROUP BY table_name, owner
--            HAVING SUM (bytes) / 1024 / 1024 > 5 /* Ignore really small tables */
          ORDER BY SUM (bytes) DESC) C
   WHERE     a.owner||A.TABLE_NAME IN (SELECT owner || name
                                   FROM all_refresh_children
                                  WHERE refgroup = 5)
         AND A.TABLE_NAME = b.table_name(+)
         AND A.TABLE_NAME = C.TABLE_NAME(+)
ORDER BY a.table_name;

Working with Application System Accounts (CREATE LIKE)

If you have an Application on a Oracle database that uses a standard account to access the database (and usually owns all the objects), sometimes you will need to drop that user (cascade constraints) for reasons such as:

 

• Refreshing a schema (often with Test accounts)
• Performing complete restores

 

Since the roles and object privileges are often quite involved on systems like this, it is a good idea to make copies of your user. I used to use this all the time on OEM but now this function exists in SQL*Developer also.

 

Please see this great article from Jeff Smith:

http://www.thatjeffsmith.com/archive/2015/12/trick-getting-all-the-privileges-grants-for-a-user/

 

I would usually name my user "XXXX_shell"  (like: FIN_SHELL) and after their objects were dropped, would perform this "CREATE LIKE" again back to the normal user name (like: FIN_PROD). Now you are ready to IMP your objects.