Nachdem Oracle das praktische Package zum Schrumpfen von Tablespaces vorgestellt hat, sollte man das natürlich auch fleißig einsetzen.

Das folgende Skript kann im cdb$root Container ausgeführt werden und führt dann den Schrump-Vorgang auch in allen Kind-Containern aus.

set serveroutput on
DECLARE
   -- FALSE = Analyse; TRUE = tatsächlich verkleinern
   c_execute CONSTANT BOOLEAN := TRUE;
   l_cursor INTEGER;
   l_dummy  INTEGER;
   l_sql    VARCHAR2(32767);
BEGIN
   FOR c IN (
       SELECT name
       FROM   v$containers
       WHERE  name <> 'PDB$SEED'
       AND    open_mode = 'READ WRITE'
       ORDER BY con_id
   )
   LOOP
       DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== Container: ' || c.name || ' ===');
       FOR t IN (
           SELECT tablespace_name
           FROM   cdb_tablespaces
           WHERE  con_id = (
               SELECT con_id
               FROM   v$containers
               WHERE  name = c.name
           )
           AND    contents = 'PERMANENT'
           AND    status = 'ONLINE'
           --AND    tablespace_name NOT IN ('SYSTEM', 'SYSAUX')
           ORDER BY tablespace_name
       )
       LOOP
           l_cursor := NULL;
           BEGIN
               IF c_execute THEN
                   l_sql :=
                       'BEGIN DBMS_SPACE.SHRINK_TABLESPACE(''' ||
                       REPLACE(t.tablespace_name, '''', '''''') ||
                       '''); END;';
               ELSE
                   l_sql :=
                       'BEGIN DBMS_SPACE.SHRINK_TABLESPACE(''' ||
                       REPLACE(t.tablespace_name, '''', '''''') ||
                       ''', shrink_mode => ' ||
                       'DBMS_SPACE.TS_SHRINK_MODE_ANALYZE); END;';
               END IF;
               l_cursor := DBMS_SQL.OPEN_CURSOR;
               DBMS_SQL.PARSE(
                   c             => l_cursor,
                   statement     => l_sql,
                   language_flag => DBMS_SQL.NATIVE,
                   container     => c.name
               );
               l_dummy := DBMS_SQL.EXECUTE(l_cursor);
               DBMS_SQL.CLOSE_CURSOR(l_cursor);
               DBMS_OUTPUT.PUT_LINE(
                   'OK: ' || t.tablespace_name);
           EXCEPTION
               WHEN OTHERS THEN
                   IF l_cursor IS NOT NULL
                      AND DBMS_SQL.IS_OPEN(l_cursor)
                   THEN
                       DBMS_SQL.CLOSE_CURSOR(l_cursor);
                   END IF;
                   DBMS_OUTPUT.PUT_LINE(
                       'FEHLER: ' || t.tablespace_name ||
                       ' - ' || SQLERRM
                   );
           END;
       END LOOP;
   END LOOP;
END;
/