oracle drop all schema tables

Solutions on MaxInterview for oracle drop all schema tables by the best coders in the world

showing results for - "oracle drop all schema tables"
Renata
23 Sep 2019
1BEGIN
2    FOR cur_rec IN (SELECT OBJECT_NAME, OBJECT_TYPE
3                    FROM ALL_OBJECTS		-- Choose types
4                    WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'PACKAGE',
5                                          'PROCEDURE', 'FUNCTION', 'SEQUENCE'))
6                          AND OWNER = 'owner_name'
7        LOOP
8            BEGIN
9                IF cur_rec.OBJECT_TYPE = 'TABLE'
10                THEN
11                    EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
12                        || cur_rec.OBJECT_NAME || '" CASCADE CONSTRAINTS';
13                ELSE
14                    EXECUTE IMMEDIATE 'DROP ' || cur_rec.OBJECT_TYPE || ' "'
15                        || cur_rec.OBJECT_NAME || '"';
16                END IF;
17            EXCEPTION
18                WHEN OTHERS
19                    THEN
20                        DBMS_OUTPUT.PUT_LINE('FAILED: DROP '
21                            || cur_rec.OBJECT_TYPE || ' "'
22                            || cur_rec.OBJECT_NAME || '"');
23            END;
24        END LOOP;
25END;