Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

How to execute some SQL in all Pluggable Databases (PDBs)

leave a comment »

If you are on 12c (and you should) and your database is truly multitenant, you may be in need to execute some SQL in all PDBs. OEM is awesome when it comes to executing a Job in a set of databases, and if such Job is a SQL Script then you can write it to do the same SQL in each PDB out of the set, as long as it is not a Standby. I have seen SQL Scripts doing that, making use of dynamic SQL and generating “ALTER SESSION SET CONTAINER = xxx” commands, spooled to a text file and executing such text file trusting its content. This approach works fine but is not very clean, and opens the door to some issues about the spooled file. I won’t get into the details, but one is security…

In order to avoid using a spool file with dynamic SQL, and having the parent script trust that such dynamic script is indeed available and legit, what I am proposing here is the use of oldie DBMS_SQL, and still do dynamic SQL but self contained. Look at sample code below, this script simply enables those tasks out of the Auto Task set provided by Oracle. Of course this script below assumes that someone has disabled one or more of the three tasks in some PDBs… or maybe in all of them, and that some Databases out of the farm may have the same issue… So if in this example we just want to enable all 3 tasks in all PDBs for all databases in farm, then we could schedule sample script as an OEM Job, and execute it every once in a while.

Anyways, enjoy the sample script and consider using it for other purposes. Nothing new, but just a simple and clean case of using DBMS_SQL on multitenant, while avoiding having to execute a script generated by another script and all the headaches that such action may cause when something goes wrong.


COL report_date NEW_V report_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24-MI-SS') report_date FROM DUAL;
SPO /tmp/change_all_pdbs_&&report_date..txt;

VAR v_cursor CLOB;
BEGIN
  :v_cursor := q'[
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  DBMS_OUTPUT.PUT_LINE('ENABLE DBMS_AUTO_TASK_ADMIN');
  DBMS_AUTO_TASK_ADMIN.ENABLE;
  FOR i IN (SELECT client_name, operation_name 
              FROM dba_autotask_operation 
             WHERE status = 'DISABLED'
             ORDER BY 1, 2)
  LOOP
    DBMS_OUTPUT.PUT_LINE('ENABLE CLIENT_NAME:'||i.client_name||' OPERATION:'||i.operation_name);
    DBMS_AUTO_TASK_ADMIN.ENABLE
      ( client_name => i.client_name
      , operation   => NULL
      , window_name => NULL
      );
  END LOOP;
  COMMIT;
END;
  ]';
END;
/
PRINT v_cursor;

SET SERVEROUTPUT ON
DECLARE
  l_cursor_id INTEGER;
  l_rows_processed INTEGER;
BEGIN
  l_cursor_id := DBMS_SQL.OPEN_CURSOR;
  FOR i IN (SELECT name 
              FROM v$containers 
             WHERE con_id > 2 
               AND open_mode = 'READ WRITE'
             ORDER BY 1)
  LOOP
    DBMS_OUTPUT.PUT_LINE('PDB:'||i.name); 
    DBMS_SQL.PARSE
      ( c             => l_cursor_id
      , statement     => :v_cursor
      , language_flag => DBMS_SQL.NATIVE
      , container     => i.name
      );
      l_rows_processed := DBMS_SQL.EXECUTE(c => l_cursor_id);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c => l_cursor_id);
END;
/

SPO OFF;

Advertisements

Written by Carlos Sierra

July 3, 2017 at 4:18 pm

Posted in OEM, PDB, Scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: