Sometimes Oracle Database Administrators receive some strange requests like dropping or truncating the same table in several schemas. Obviously we can do such requests manually, but it would be time consuming and very hard to automate. Instead of performing such requests manually we can use a handy script.

We will start from creating two tables under two different users.

SQL> conn a/a

Connected.

SQL> create table t10(

  2  c1 int)

  3  /



Table created.



SQL> insert into t10(c1) values(10)

  2  /



1 row created.



SQL> commit

  2  /



Commit complete.



SQL> conn b/b

Connected.

SQL> create table t10(

  2  c1 int)

  3  /

SQL> commit

  2  /



Commit complete.



Table created.



SQL> insert into t10(c1) values(20)

  2  /



1 row created.



SQL> commit

  2  /



Commit complete.

Then we create a table in which we will store schema names on which we want to perform operation.

SQL> conn system/oracle

Connected.

SQL> create table schematotruncate(username varchar(32))

  2  tablespace users

  3  /



Table created.

SQL> conn system/oracle

Connected.

SQL> create table schematotruncate(username varchar(32))

  2  tablespace users

  3  /



Table created.



SQL> insert into schematotruncate(username) values('A');



1 row created.



SQL> insert into schematotruncate(username) values('B');



1 row created.

Now we can run our script, which will truncate the tables

SQL> declare

  2      cursor v_cursor is select username from schematotruncate;

  3      v_username varchar(32);

  4      v_tablename varchar(32) := 't10';

  5  begin

  6  open v_cursor;

  7  if v_cursor%isopen then

  8      loop

  9     fetch v_cursor into v_username;

 10     exit when v_cursor%notfound;

 11     execute immediate 'truncate table ' || v_username || '.' || v_tablename;

 12      end loop;

 13  end if;

 14  end;

 15  /



PL/SQL procedure successfully completed.

We use a cursor to read the table with schema names to truncate, then we use the EXECUTE IMMEDIATE instruction, which allows us to execute dynamic SQL. The trick with EXECUTE IMMEDIATE, which we used to truncate the table can be also used for other purposes for example dropping tables. You just need to replace truncate with drop.
The last thing is to check if our script was successful.

SQL> select * from a.t10

  2  /



no rows selected



SQL> select * from b.t10

  2  /



no rows selected