SQL & PL/SQL

Hello my ODC friends and guys :-),
Here I have to say why I write this funny trigger. Last Thursday afternoon our developer college performed such operation in the prod user/schema quietly - “CTAS three number of big volume tables”. As a result the next day I found my oracle database generates a huge amount of archived logs than the previous day, which caused the space of RMAN backup disk has left only a little (17Mb). Oh my god!
Ultimately I had to ask for the storage engineer added another one disk.
Based on the unexpected incident I need take an action to prevent him from doing CTAS big table (oracle version: 11.2.0.4.0).
So now I probably need to consider some cases:

1. In order to avoid generating REDO I recommend using keyword *nologging* in CTAS operation, if not existing "nologging" in CTAS then my trigger prompts you to add it typically.
2. How to find big tables? Might from user_segments (just retrieved ranking top 3-5).
3. By trigger only to prevent those big tables from doing CTAS.
   Yes, my entire demo is as below.

-- on SYS schema:

col tablespace_name for a15

select tablespace_name
     , sum(bytes)/1024/1024/1024 size_gb
from   dba_data_files
group by tablespace_name
having tablespace_name = 'HITRATIO'
;

TABLESPACE_NAME    SIZE_GB
--------------- ----------
HITRATIO                 4

-- on HITRATIO schema:

select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
HITRATIO                       TABLE
HITRATIO_PROC                  TABLE

create table test as select * from all_objects;

begin
  for i in 1 .. 50 loop
    insert into test value select * from all_objects;
  end loop;
  commit;
end;
/

create table test2 as select * from all_objects;

begin
  for i in 1 .. 30 loop
    insert into test2 value select * from all_objects;
  end loop;
  commit;
end;
/

col segment_name for a15

select segment_name
     , sum(bytes)/1024/1024 size_mb
from   user_segments
where  segment_type = 'TABLE'
group by segment_name
order by 2
desc
;

SEGMENT_NAME       SIZE_MB
--------------- ----------
TEST                   336
TEST2                  208
HITRATIO                 2
HITRATIO_PROC            2

create or replace trigger no_ctas_big_table
before create on hitratio.schema
declare
  l_ddl long;
  l_sqltxt ora_name_list_t;
  n number;
  t_name varchar2(35);
  ctas_err EXCEPTION;
  PRAGMA EXCEPTION_INIT(ctas_err, -24381);
  cursor big_table_cur is
  select *
  from
  (
   select segment_name
        , sum(bytes)/1024/1024 size_mb
   from   user_segments
   where  segment_type = 'TABLE'
   group by segment_name
   order by 2
   desc
  )
  where rownum <=2
  ;
begin
  n := ora_sql_txt( l_sqltxt );

  for i in 1..n
  loop
    l_ddl := l_ddl || l_sqltxt(i);
  end loop;
  
  l_ddl := lower(trim(l_ddl));
  
  for v_big_table in big_table_cur loop
    if l_ddl like 'create table%as select%from%'||lower(v_big_table.segment_name)||'%'
    and l_ddl not like '%nologging%' then
      t_name := v_big_table.segment_name;
      raise ctas_err;
    else
      continue;
    end if;
  end loop;
exception
  when ctas_err then
    dbms_output.put_line('Do not allow to CTAS big table ' || t_name || ' without keyword nologging.');
end;
/

-- on HITRATIO schema:

create table new as select * from test;

Table created.

create table old as select * from test2;

Table created.

Oh, my god! Why my user-defined exception has never been captured? By the way I've also tested it on 21c also the same weird issue.

Best Regards
Quanwen Zhao