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

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



http://anuj-singh.blogspot.com/2020_12_26_archive.html



 pwd
/home/oracle
statsinfo.sql


 cat statsinfo.sql
set linesize 300
col OWNER                       for a20
col OBJECT_NAME                 for a28
col PARTITION_NAME              for a20
col SUBPARTITION_NAME   		for a20
col GLOBAL_STATS                for a15
col USER_STATS                  for a15
col TOTAL_COUNT                 for a35
col STALE_STATS                 for a15

SELECT  'Total Number of Stale Tables: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, TABLE_NAME OBJECT_NAME, 'TABLE' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM    DBA_TAB_STATISTICS
WHERE   STATTYPE_LOCKED IS NULL
AND (STALE_STATS IS NULL OR STALE_STATS='YES')
AND OWNER NOT IN ('GSMADMIN_INTERNAL','ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND OWNER NOT LIKE 'FLOW%'
UNION ALL
SELECT  'Total Number of Stale Indexes: '||COUNT(*) OVER() TOTAL_COUNT, OWNER, INDEX_NAME OBJECT_NAME, 'INDEX' OBJECT_TYPE, PARTITION_NAME, SUBPARTITION_NAME, LAST_ANALYZED, GLOBAL_STATS, USER_STATS, STATTYPE_LOCKED, STALE_STATS
FROM    DBA_IND_STATISTICS
WHERE   STATTYPE_LOCKED IS NULL
AND (STALE_STATS IS NULL OR STALE_STATS='YES')
AND OWNER NOT IN ('GSMADMIN_INTERNAL','ANONYMOUS', 'CTXSYS', 'DBSNMP', 'EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS', 'SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB' )
AND OWNER NOT LIKE 'FLOW%'
/




# Exclude  PDB$SEED PDB9 containers (-C, --excl_con).
-b" option is the prefix for the log file names.


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -C 'PDB$SEED anujv' -b statsinfo statsinfo.sql



 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -C 'PDB$SEED anujv' -b statsinfo statsinfo.sql
catcon: ALL catcon-related output will be written to [/home/oracle/statsinfo_catcon_26043.lst]
catcon: See [/home/oracle/statsinfo*.log] files for output generated by scripts
catcon: See [/home/oracle/statsinfo_*.lst] files for spool files, if any
catcon.pl: completed successfully



cat statsinfo0.log

TOTAL_COUNT                         OWNER                OBJECT_NAME                  OBJEC PARTITION_NAME       SUBPARTITION_NAME    LAST_ANALYZED  GLOBAL_STATS    USER_STATS      STATT STALE_STATS
----------------------------------- -------------------- ---------------------------- ----- -------------------- -------------------- -------------- --------------- --------------- ----- ---------------
Total Number of Stale Tables: 35    AUDSYS               AUD$UNIFIED                  TABLE                                          31-08-21 01:00 YES      NO                    YES
Total Number of Stale Tables: 35    DVSYS                DV$CMDCONTEXT                TABLE                                         NO       NO
Total Number of Stale Tables: 35    ORDDATA              ORDDCM_CT_PRED_TMP           TABLE                                         NO       NO
Total Number of Stale Tables: 35    ORDDATA              ORDDCM_RT_PREF_PARAMS_TMP    TABLE                                         NO       NO
Total Number of Stale Tables: 35    ORDDATA              ORDDCM_ANON_ATTRS_TMP        TABLE                                         NO       NO
Total Number of Stale Tables: 35    ORDDATA              ORDDCM_ANON_RULES_TMP        TABLE                                         NO       NO
Total Number of Stale Tables: 35    ORDDATA              ORDDCM_STORED_TAGS_TMP       TABLE                                         NO       NO



=======================================



pwd
/home/oracle   <<<< location 

cat stats.sql  <<<<< script !!!!
exec dbms_stats.gather_schema_stats('SYS');
exec dbms_stats.gather_schema_stats('SYSTEM');


# Exclude  PDB$SEED PDB9 containers (-C, --excl_con).
-b" option is the prefix for the log file names.

 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -e -C 'PDB$SEED anujv' -b gatherstats stats.sql
catcon: ALL catcon-related output will be written to [/home/oracle/gatherstats_catcon_12613.lst]
catcon: See [/home/oracle/gatherstats*.log] files for output generated by scripts
catcon: See [/home/oracle/gatherstats_*.lst] files for spool files, if any


cat /home/oracle/gatherstats_catcon_12613.lst
catcon: See [/home/oracle/gatherstats*.log] files for output generated by scripts
catcon: See [/home/oracle/gatherstats_*.lst] files for spool files, if any
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

catcon version: /st_rdbms_12.2.0.1.0dbbp/2
        catconInit: start logging catcon output at 2022-11-15 03:15:56

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!






cat gatherstats0.log

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 15 03:15:57 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

idle sqlplus> Connected.
primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

primary:sys@vihcdbd8-vihcdbd8 sqlplus>
ALTER SYSTEM KILL SESSION '394,26285' force timeout 0 -- process 12665
/

primary:sys@vihcdbd8-vihcdbd8 sqlplus> primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

primary:sys@vihcdbd8-vihcdbd8 sqlplus> primary:sys@vihcdbd8-vihcdbd8 sqlplus>
primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

primary:sys@vihcdbd8-vihcdbd8 sqlplus>
NOW_CONNECTED_TO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
==== Current Container = CDB$ROOT Id = 1 ====

primary:sys@vihcdbd8-vihcdbd8 sqlplus>
NOW_CONNECTED_TO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
==== Current Container = CDB$ROOT Id = 1 ====

primary:sys@vihcdbd8-vihcdbd8 sqlplus>   2
Session altered.

CATCONSECTION
--------------------------
==== CATCON EXEC ROOT ====

SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:CDB$ROOT Id:1 22-11-15 03:15:57 Proc:0 ====

SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:CDB$ROOT Id:1 22-11-15 03:15:57 Proc:0 ====

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL> SQL> exec dbms_stats.gather_schema_stats('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('SYSTEM');

PL/SQL procedure successfully completed.

SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:CDB$ROOT Id:1 22-11-15 03:19:05 Proc:0 ====

SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:CDB$ROOT Id:1 22-11-15 03:19:05 Proc:0 ====

SQL> SQL>
SQL>   2
Session altered.

SQL> SQL>
SQL>   2
Session altered.


Session altered.

SQL>
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = PDB9 Id = 3 ====

SQL>
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = PDB9 Id = 3 ====

SQL> SQL>   2
CATCONSECTION
-----------------------------------
==== CATCON EXEC IN CONTAINERS ====

SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:PDB9 Id:3 22-11-15 03:19:05 Proc:0 ====

SQL>
BEGIN_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:PDB9 Id:3 22-11-15 03:19:05 Proc:0 ====

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL> SQL> exec dbms_stats.gather_schema_stats('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('SYSTEM');

PL/SQL procedure successfully completed.

SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:PDB9 Id:3 22-11-15 03:21:46 Proc:0 ====

SQL>
END_RUNNING
--------------------------------------------------------------------------------
==== @stats.sql Container:PDB9 Id:3 22-11-15 03:21:46 Proc:0 ====

SQL> SQL>
SQL>   2
Session altered.

SQL> SQL>
SQL> ========== PROCESS ENDED ==========
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@ibrac02 ~]$





=============================================================================================================================



via plsql !!!!




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


https://carlos-sierra.net/2017/07/03/how-to-execute-some-sql-in-all-pluggable-databases-pdbs/

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;