1BEGIN
2 FOR cur_rec IN (SELECT object_name, object_type
3 FROM user_objects
4 WHERE object_type IN
5 ('TABLE',
6 'VIEW',
7 'MATERIALIZED VIEW',
8 'PACKAGE',
9 'PROCEDURE',
10 'FUNCTION',
11 'SEQUENCE',
12 'SYNONYM',
13 'PACKAGE BODY'
14 ))
15 LOOP
16 BEGIN
17 IF cur_rec.object_type = 'TABLE'
18 THEN
19 EXECUTE IMMEDIATE 'DROP '
20 || cur_rec.object_type
21 || ' "'
22 || cur_rec.object_name
23 || '" CASCADE CONSTRAINTS';
24 ELSE
25 EXECUTE IMMEDIATE 'DROP '
26 || cur_rec.object_type
27 || ' "'
28 || cur_rec.object_name
29 || '"';
30 END IF;
31 EXCEPTION
32 WHEN OTHERS
33 THEN
34 DBMS_OUTPUT.put_line ('FAILED: DROP '
35 || cur_rec.object_type
36 || ' "'
37 || cur_rec.object_name
38 || '"'
39 );
40 END;
41 END LOOP;
42 FOR cur_rec IN (SELECT *
43 FROM all_synonyms
44 WHERE table_owner IN (SELECT USER FROM dual))
45 LOOP
46 BEGIN
47 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name;
48 END;
49 END LOOP;
50END;
51/
52
1DELETE FROM my_table; -- all rows, commit needed
2TRUNCATE TABLE my_table; -- all rows, no possible rollback
3DELETE FROM my_table WHERE my_id = 12345;
4DELETE FROM my_table WHERE my_id IN (SELECT id2 FROM my_table2);
5COMMIT; -- don't forget it...