Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

How to truncate the same table in several schemas in Oracle

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
Categories: