create or replace trigger trig_copy_table_hitratio
-- after logon on hitratio.schema
before create on hitratio.schema
declare
  v_sql varchar2(400);
begin
  select sql_fulltext into v_sql from v$sql
  where sql_fulltext like '%select * from hitratio%'
  and sql_fulltext not like '%select sql_fulltext from v$sql%';
  if upper(v_sql) = 'CREATE TABLE HITRATIO_2 AS SELECT * FROM HITRATIO' then
    raise_application_error(-20001,'Copy table hitratio in schema hitratio has not been allowed!');
  end if;
exception when no_data_found then
  raise_application_error(-20002,'Not found the sql statement you want to capture!');
end;
/

09:20:11 HITRATIO@inst_name> create or replace trigger trig_copy_table_hitratio
09:21:31   2  -- after logon on hitratio.schema
09:21:31   3  before create on hitratio.schema
09:21:31   4  declare
09:21:31   5    v_sql varchar2(400);
09:21:31   6  begin
09:21:31   7    select sql_fulltext into v_sql from v$sql
09:21:31   8    where sql_fulltext like '%select * from hitratio%'
09:21:31   9    and sql_fulltext not like '%select sql_fulltext from v$sql%';
09:21:31  10    if upper(v_sql) = 'CREATE TABLE HITRATIO_2 AS SELECT * FROM HITRATIO' then
09:21:31  11      raise_application_error(-20001,'Copy table hitratio in schema hitratio has not been allowed!');
09:21:31  12    end if;
09:21:31  13  exception when no_data_found then
09:21:31  14    raise_application_error(-20002,'Not found the sql statement you want to capture!');
09:21:31  15  end;
09:21:31  16  /

Trigger created.

09:21:43 HITRATIO@inst_name> create table hitratio_2 as select * from hitratio;
create table hitratio_2 as select * from hitratio
                                         *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Copy table hitratio in schema hitratio has not been allowed!  <<==
ORA-06512: at line 8

A much better generalized version to prevent all CTAS statements with system events in place.

demo@XEPDB1> create or replace trigger no_ctas
  2  before create on demo.schema
  3  declare
  4     l_ddl long;
  5     l_sqltxt ora_name_list_t;
  6     n number;
  7  begin
  8     n := ora_sql_txt( l_sqltxt );
  9
 10     for i in 1..n
 11     loop
 12             l_ddl := l_ddl || l_sqltxt(i);
 13     end loop;
 14     l_ddl := lower(trim(l_ddl));
 15
 16     if l_ddl like '%create%table%as%select%from%' then
 17             raise_application_error(-20001,'No_more_CTAS_Please');
 18     end if;
 19  end;
 20  /

Trigger created.

demo@XEPDB1> create table emp as select * from scott.emp;
create table emp as select * from scott.emp
                                        *
ERROR at line 1:
ORA-04088: error during execution of trigger 'DEMO.NO_CTAS'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No_more_CTAS_Please
ORA-06512: at line 15


demo@XEPDB1> create table emp as select empno,ename from scott.emp;
create table emp as select empno,ename from scott.emp
                                                  *
ERROR at line 1:
ORA-04088: error during execution of trigger 'DEMO.NO_CTAS'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No_more_CTAS_Please
ORA-06512: at line 15


demo@XEPDB1> create table emp( empno number );

Table created.

demo@XEPDB1>

Thank you very much, Rajeshwaran!

Yes, prevent *all CTAS* in a specific schema is good, but I wanna just prevent a corresponding table *hitratio*. Hence I modify your code slightly to mine.

create or replace trigger no_ctas_hitratio
before create on hitratio.schema
declare
   l_ddl long;
   l_sqltxt ora_name_list_t;
   n number;
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));

   if l_ddl like '%create table%as select%from hitratio%' then
     raise_application_error(-20001,'No_CTAS_hitratio_Please.');
   end if;
end;
/

But there has two similar tables *hitratio*, *hitratio_proc* in my schema *hitratio*, how to just prevent CTAS *hitratio*?

Thanks beforehand.

Best Regards
Quanwen Zhao

Hope this should help you, but test few more boundary conditions to this - the line 15 and 16 from trigger should do the trick for you.

demo@XEPDB1> create table hitratio as select * from all_objects;

Table created.
demo@XEPDB1> create table hitratio_proc as select * from all_objects;

Table created.
demo@XEPDB1> create or replace trigger no_ctas
  2  before create on demo.schema
  3  declare
  4     l_ddl long;
  5     l_sqltxt ora_name_list_t;
  6     n number;
  7  begin
  8     n := ora_sql_txt( l_sqltxt );
  9
 10     for i in 1..n
 11     loop
 12              l_ddl := l_ddl || l_sqltxt(i);
 13     end loop;
 14     l_ddl := lower(trim(l_ddl));
 15     if l_ddl like '%create%table%as%select%from%hitratio%'
 16                     and l_ddl not like '%hitratio_proc%'  then
 17              raise_application_error(-20001,'No_more_CTAS_Please');
 18     end if;
 19  end;
 20  /

Trigger created.
demo@XEPDB1> create table t as select * from hitratio;
create table t as select * from hitratio
                                *
ERROR at line 1:
ORA-04088: error during execution of trigger 'DEMO.NO_CTAS'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No_more_CTAS_Please
ORA-06512: at line 15

demo@XEPDB1> create table t as select object_id from hitratio where object_id > 0 ;
create table t as select object_id from hitratio where object_id > 0
                                        *
ERROR at line 1:
ORA-04088: error during execution of trigger 'DEMO.NO_CTAS'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: No_more_CTAS_Please
ORA-06512: at line 15

demo@XEPDB1> create table t as select * from hitratio_proc;

Table created.
demo@XEPDB1> create table t1 as select object_id from hitratio_proc where object_id > 0 ;

Table created.
demo@XEPDB1>

An enormous thanks to Rajeshwaran.

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.

By the way the prior trigger is just an initial idea.

So now I probably need to consider some cases:

1) How to estimate redo size before CTAS table?
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.

Hope you to guide me finish the trigger.

Best Regards
Quanwen Zhao

So now I probably need to consider some cases:

1) How to estimate redo size before CTAS table?
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.

1) ==>> In order to avoid generating REDO I recommend using keyword *nologging* in CTAS operation, if having "nologging" in CTAS then my trigger prompts you to add it typically.

2) and 3) have been performed in my new trigger.

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;
/

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?

Best Regards
Quanwen Zhao

Here is what i did.

demo@XEPDB1> select *
  2    from
  3    (
  4     select segment_name
  5          , sum(bytes)/1024/1024 size_mb
  6     from   user_segments
  7     where  segment_type = 'TABLE'
  8     group by segment_name
  9     order by 2
 10     desc
 11    )
 12    where rownum <=2
 13  /

SEGMENT_NA    SIZE_MB
---------- ----------
BIG_TABLE        1472
TEST              568

demo@XEPDB1> create or replace trigger no_ctas_big_table
  2  before create on demo.schema
  3  declare
  4     l_ddl long;
  5     l_sqltxt ora_name_list_t;
  6     n number;
  7     t_name varchar2(35);
  8     ctas_err EXCEPTION;
  9     PRAGMA EXCEPTION_INIT(ctas_err, -24381);
 10     cursor big_table_cur is
 11     select *
 12     from (
 13     select segment_name
 14             , sum(bytes)/1024/1024 size_mb
 15     from   user_segments
 16     where  segment_type = 'TABLE'
 17     group by segment_name
 18     order by 2   desc  )
 19     where rownum <=2  ;
 20  begin
 21    n := ora_sql_txt( l_sqltxt );
 22
 23    for i in 1..n
 24    loop
 25      l_ddl := l_ddl || l_sqltxt(i);
 26    end loop;
 27
 28    l_ddl := lower(trim(l_ddl));
 29
 30    for v_big_table in big_table_cur loop
 31      if l_ddl like 'create%table%as%select%from%'||lower(v_big_table.segment_name)||'%'
 32                     and l_ddl not like '%nologging%' then
 33        t_name := v_big_table.segment_name;
 34        raise ctas_err;
 35      else
 36        continue;
 37      end if;
 38    end loop;
 39  exception
 40    when ctas_err then
 41      dbms_output.put_line('Do not allow to CTAS big table ' || t_name || ' without keyword nologging.');
 42  end;
 43  /

Trigger created.

demo@XEPDB1> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WRAPPED
demo@XEPDB1> create table t1 as select * from big_table;
<b>Do not allow to CTAS big table BIG_TABLE without keyword nologging.</b>

Table created.

demo@XEPDB1>

A lot of answers to your question in these comments, but not a solution to your problem. The devs need to be conscious of operations that are potentially dangerous. The database has limited resources, and copying large tables consumes too many of them. None of these triggers convey that information. The devs need to know that they are the problem in this scenario.

If the devs don't understand why their CREATE statement was blocked, wouldn't they just copy the large table some other way that slips past your trigger? Educate them. If that isn't enough, make them work in lower environments and remove their write permission in PROD.

As a developer, I only have read access to prod. Sure it's annoying when I need to fix something fast, but it also saves the prod environment from my ignorance. "This is a simple copy command. Surely it won't bring production to its knees" is not an unusual thought.

Hello Rajeshwaran :-),

What's version with your oracle database?

Mine is 11.2.0.4.0, but I tried my new trigger on 21.3 but still being able to CTAS a new table without *nologging*.

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 09:24:13 2021
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

10:01:22 C##TEST@ORACDB> create or replace trigger no_ctas_big_table
10:02:58   2  before create on c##test.schema
10:02:58   3  declare
10:02:58   4    l_ddl long;
10:02:58   5    l_sqltxt ora_name_list_t;
10:02:58   6    n number;
10:02:58   7    t_name varchar2(35);
10:02:58   8    ctas_err EXCEPTION;
10:02:58   9    PRAGMA EXCEPTION_INIT(ctas_err, -24381);
10:02:58  10    cursor big_table_cur is
10:02:58  11    select *
10:02:58  12    from
10:02:58  13    (
10:02:58  14     select segment_name
10:02:58  15          , sum(bytes)/1024/1024 size_mb
10:02:58  16     from   user_segments
10:02:58  17     where  segment_type = 'TABLE'
10:02:58  18     group by segment_name
10:02:58  19     order by 2
10:02:58  20     desc
10:02:58  21    )
10:02:59  22    where rownum <=2
10:02:59  23    ;
10:02:59  24  begin
10:02:59  25    n := ora_sql_txt( l_sqltxt );
10:02:59  26  
10:02:59  27    for i in 1..n
10:02:59  28    loop
10:02:59  29      l_ddl := l_ddl || l_sqltxt(i);
10:02:59  30    end loop;
10:02:59  31    
10:02:59  32    l_ddl := lower(trim(l_ddl));
10:02:59  33    
10:02:59  34    for v_big_table in big_table_cur loop
10:02:59  35      if l_ddl like 'create table%as select%from%'||lower(v_big_table.segment_name)||'%'
10:02:59  36      and l_ddl not like '%nologging%' then
10:02:59  37        t_name := v_big_table.segment_name;
10:02:59  38        raise ctas_err;
10:02:59  39      else
10:02:59  40        continue;
10:02:59  41      end if;
10:02:59  42    end loop;
10:02:59  43  exception
10:02:59  44    when ctas_err then
10:02:59  45      dbms_output.put_line('Do not allow to CTAS big table ' || t_name || ' without keyword nologging.');
10:02:59  46  end;
10:02:59  47  /

Trigger created.

10:03:16 C##TEST@ORACDB> set serveroutput on

10:03:20 C##TEST@ORACDB> create table new as select * from test;

Table created.

I did the above from 18c XE also tried it in my local 19c (19.9) and worked nicely, can you debug the code in your environment and see why it didn't raised the error ?

......
demo@XEPDB1> create table t1 as select * from big_table;
<b>Do not allow to CTAS big table BIG_TABLE without keyword nologging.</b>

Table created.     <<== But it has still finished creating table big_table in your code.
......

Perhaps using *raise_application_error* to handle exception is a good approach.