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.

Using sys.dbms_debug_vc2coll Collections

Sometimes you run across the need to perform a LIKE comparison on a value with a wildcard. Simple right?

WHERE gzrrqap_security_role LIKE 'STU_ADM_PROFESSIONAL%'

Now, what if you wanted a list of values to compare to? You can't just list them the way you're used to doing it in a static IN list. What now?

Use the sys.dbms_debug_vc2coll collection to basically perform a collection in the WHERE clause by building a table on the fly in the FROM.

SELECT
...
FROM
...
table(sys.dbms_debug_vc2coll('STU_ADM_PROFESSIONAL%','STU_ONESTOP_%')) ck_role
WHERE
 gzrrqap_security_role LIKE CK_role.COLUMN_VALUE


or a mixture of static and wildcard values:

        TABLE (sys.dbms_debug_vc2coll ('P09',
                                       'P10%',
                                       'P11%',
                                       'P12%',
                                       'P13%',
                                       'P14%')) CK_value
           WHERE  
                 ....
                 AND PROGRAM_LEVEL_2 LIKE CK_value.COLUMN_VALUE)


It's pretty handy and can solve many problems. It simply converts parameters (values) into the "table representation" - basically, the opposite to "pivot" result.