Package used to monitor table usage

 

Today I wrote an Oracle package to share with you.

The background is as follows: existing systems are transplanted from systems of other companies, so many tables are customized for the original company, because it is not suitable for business needs, it is not used, and no one has sorted it out for a long time. Therefore, there are many redundant tables in the system, this causes a lot of inconvenience to the maintenance of the system, so we want to see which tables are not used at all in the system. After checking these tables, we need to delete them. (There are more than 2400 tables in the current system. It is estimated that more than half of the tables are out of unused status)

The content in the package is relatively simple, that is, a table is created first to store all the tables and whether to use the status. Initially, all the tables in the system are captured, and the usage status is set to N, that is, not used.

 

Code

    --  Obtain all the tables and place them in the monitored table.  
   Procedure  Get_all_tables (v_owner  In     Varchar2  )  Is   
  Begin  
  Insert     Into  Table_usage
(
Owner
, Table_name
, Is_using
, Modify_date
)
  Select A. Owner
, A. table_name
,  '  N  '    --  The default value is N.  
  , Sysdate
  From  All_all_tables
  Where  A. Owner  =  V_owner
  And A. Status  =     '  Valid  '  ;

  Commit  ;
  End  Get_all_tables; 

 

 

 

Then, a trigger is created for each table. When an insert operation is performed on the specified table, table_usage is modified and the flag is set to y, that is, the status in use.

 

Code

  --  Create triggers for all tables for monitoring  
--  Create a table based on the conditions in the table_usage table.  
  Procedure  Create_all_triggers  Is  
  Cursor  Cur_tables  Is  
  Select Owner
, Table_name
  From  Table_usage
  Where  Is_using  =     '  N  '  ;
Strcreatesql  Varchar2  (  32767  );
  Begin 
  --  Traverse all tables and create system triggers for them to monitor usage  
    For  Rec_tables  In  Cur_tables Loop
Strcreatesql:  =     ''  ;
Strcreatesql:  =  Strcreatesql  |    '  Create or replace trigger tri_mu _  '  |  Rec_tables.owner  |  '  _  '  |  Rec_tables.table_name  |  '     '  ;
Strcreatesql: =  Strcreatesql  |     '  After insert on  '  |  Rec_tables.owner  |  '  .  '  |  Rec_tables.table_name  |  '   '  ;
Strcreatesql:  =  Strcreatesql  |     '  For each row  '  ;
Strcreatesql:  =  Strcreatesql  |     '   '  ;
Strcreatesql:  =  Strcreatesql  |     '  Begin  '  ;
Strcreatesql:  =  Strcreatesql  |     '    ' ;
Strcreatesql:  =  Strcreatesql  |     '  Update table_usage  '  ;
Strcreatesql:  =  Strcreatesql  |     '  Set is_using =  '' Y  ''     '  ;
Strcreatesql:  =  Strcreatesql  |     '  , Modify_date = sysdate  '  ;
Strcreatesql:  =  Strcreatesql  |    '  Where owner =  '''  |  Rec_tables.owner  |  '''     '  ;
Strcreatesql:  =  Strcreatesql  |     '  And table_name =  ''' |  Rec_tables.table_name  |  '''  ;  '  ;
Strcreatesql:  =  Strcreatesql  |     '    '  ;
Strcreatesql:  =  Strcreatesql |     '  End tri_mu _  '  |  Rec_tables.owner  |  '  _  '  |  Rec_tables.table_name  |  '  ;  '  ;
 Execute  Immediate strcreatesql;
  End  Loop;
  End  Create_all_triggers; 

 

 

 

In the end, you need to clear these triggers regularly. Although it will not waste too much performance, it will also be wasted, so there is a process of clearing the trigger:

 

Clear trigger

  --  To determine the table cleanup trigger in use  
 Procedure  Clear_triggers  Is  
  Cursor  Cur_used_tables  Is  
  Select  Owner
, Table_name
  From  Table_usage
  Where  A. is_using  =    '  Y  '  ;
Strcleartriggersql  Varchar2  (  32767  );
  Begin  
  For  Rec_used_table  In  Cur_used_tables Loop
Strcleartriggersql:  =    '  Drop trigger tri_mu _  '  |  Rec_used_table.owner  |  '  _  '  |  Rec_used_table.table_name  |  '  ;  '  ;
  Execute Immediate strcleartriggersql;
  End  Loop;
Exception
  When  Others  Then  
Dbms_output.put_line (  '  There is an exception -- trigger not exists --  '  );
  End  Clear_triggers; 

 

 

 

Note the following two points:

1. Because you need to create or delete a trigger in the package, you must have the permission to perform this operation.

2. I tried to add a trigger to the table_usage table. Once the flag is changed from N to Y, the trigger on the corresponding table is automatically deleted, but the trigger fails to be implemented. I have time to continue the experiment.

You are welcome to give more comments, or the Oracle built-in package already has similar features. Please tell me, huh, huh.