Table Counts on a field accross the entire Database

Ever need to look for a value in a field across the entire DB? Have 10k+ Table that have your particular field? Here is a plsql package that I create to help in tracking where a person’s ID (emplid) shows up in the DB. Takes about 3 minutes to process 1 emplid.

 

Usage: select * from table(tablecounts_pkg.mbi_tablecounts('<<EMPLID>>') ) ;

 

 

CREATE OR REPLACE PACKAGE SYSADM.tablecounts_pkg

IS

     -- Looks for all PS tables (Joins on User_tables to make sure it is a table) and does a count on that table based on EMPLID.

   TYPE outrec_typ IS RECORD (

      var_table      VARCHAR2 (30),

      var_rowcount   NUMBER

   );

 

   TYPE outrecset IS TABLE OF outrec_typ;

 --Usage select * from table(tablecounts_pkg.tablecounts('1978614') ) ;

   FUNCTION tablecounts (emplid VARCHAR)

      RETURN outrecset PIPELINED;

    

END tablecounts_pkg;

/

 

CREATE OR REPLACE PACKAGE BODY SYSADM.tablecounts_pkg

IS

   FUNCTION tablecounts (emplid VARCHAR)

      RETURN outrecset PIPELINED

   IS

      out_rec           outrec_typ;

 

      CURSOR tablenames

      IS

         SELECT a.table_name

           FROM SYS.user_tab_cols a, user_tables b

          WHERE column_name = 'EMPLID' AND a.table_name LIKE 'PS%' AND a.table_name = b.table_name;

 

      table_row_count   NUMBER;

      stmt              VARCHAR (200);

   BEGIN

      FOR x IN tablenames

      LOOP

         stmt := 'select count(*) from ' || x.table_name || ' where emplid = ''' || emplid || '''';

 

         --DBMS_OUTPUT.put_line (stmt);

         EXECUTE IMMEDIATE stmt

                      INTO table_row_count;

 

         IF table_row_count > 0

         THEN

            out_rec.var_table := x.table_name;

            out_rec.var_rowcount := table_row_count;

            PIPE ROW (out_rec);

         END IF;

      END LOOP;

 

      RETURN;

   END;

END tablecounts_pkg;

/

Using PL/SQL to Grant Permissions to Role(s) or Person(s)

When you have a large DB and it goes through an upgrade and _ALL_ the views have to be rebuild, that means that any/all permissions / Grants to those views are gone! (*sigh*) To quickly replace the permissions here is a little PL/SQL annonymous block to pull the view names, and grant permissions. Obviously this could be a lot intellegent and flexable, but here is the idea.

DECLARE
CURSOR myviews
IS
SELECT view_name
FROM SYS.user_views
WHERE view_name LIKE '%A_Z%';
BEGIN
FOR x IN myviews
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ON ' x.view_name ' TO COOL_PROGRAMMER';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'GRANT SELECT ON SYSADM.'
x.view_name
' TO COOL_PROGRAMMER; '
SQLERRM
);
END;
END LOOP;
END;