THESE ANONYMOUS PL/SQL BLOCKS ARE VERY DANGEROUS !!!
- Clears data from all tables of logon user (data structures remains)
DECLARE
CUR INTEGER;
UC_REC SYS.USER_TABLES%ROWTYPE;
CURSOR C1 IS
SELECT * FROM USER_TABLES;
BEGIN
OPEN C1;
LOOP
BEGIN
FETCH C1 INTO UC_REC;
EXIT WHEN C1%NOTFOUND;
CUR:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CUR, 'TRUNCATE TABLE ' || UC_REC.TABLE_NAME || ' DROP
STORAGE', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(CUR);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
CLOSE C1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
- Deletes all tables with constraints of logon user
DECLARE
CUR INTEGER;
UC_REC SYS.USER_TABLES%ROWTYPE;
CURSOR C1 IS
SELECT * FROM USER_TABLES;
BEGIN
OPEN C1;
LOOP
BEGIN
FETCH C1 INTO UC_REC;
EXIT WHEN C1%NOTFOUND;
CUR:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CUR, 'DROP TABLE ' || UC_REC.TABLE_NAME || ' CASCADE
CONSTRAINTS', DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(CUR);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
CLOSE C1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Use Ctrl-C / Ctrl-V to copy / paste to SQL*Plus.
Then press / and <enter>
Regards
Robert
"Matthias Leonhardt" <i7lema_at_rz.uni-jena.de> píse v diskusním príspevku
news:3CC942D3.30907_at_rz.uni-jena.de...
> Hi,
>
> do You have any suggestions for getting a how-to of frequently used
> sql-queries of a little guide of the oracle-sql commands?
>
> I need a sql-query to clear all tables of a user.
> I have the names of it from user_tables but how can I drop all tables of
> a given user?
>
> thanks for Your help!
>
> bye,
> Matthias Leonhardt
>
Received on Fri Apr 26 2002 - 07:30:21 CDT
