OraFAQ Forum: Server Administration » delete tables of many users at a time

Home » RDBMS Server » Server Administration » delete tables of many users at a time

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

delete tables of many users at a time [message #110914]

Fri, 11 March 2005 01:09

raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai

Junior Member

hi everybody
I had created 100 users for labtest as labtest1,labtest2,labtest3...labtest100.
now i want to delete the tables of all the 100 users at a time, and use the user ids for the next test. how to drop the tables of 100 users

Report message to a moderator

Re: delete tables of many users at a time [message #110918 is a reply to message #110914]

Fri, 11 March 2005 01:46

sridcpp
Messages: 23
Registered: February 2005
Location: India

Junior Member

declare
cursor c1 is select username from dba_users where username like 'labtest%';

sql_text varchar2(200);
uuser varchar2(50);
begin
open c1;
loop
fetch c1 into uuser;
sql_text:='Drop user ||uuser||' cascade';
execute immediate sql_text;
exit when c1%notfound;
end loop
end;

Report message to a moderator

Re: delete tables of many users at a time [message #110944 is a reply to message #110914]

Fri, 11 March 2005 05:49

sridcpp
Messages: 23
Registered: February 2005
Location: India

Junior Member

Report message to a moderator

Re: delete tables of many users at a time [message #110958 is a reply to message #110944]

Fri, 11 March 2005 08:37

raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai

Junior Member

hi
thanks for the response.
the code drops the users.
but i want to retain the users,
and the tables of the users alone has to be deleted. so that i can use the user names again.

regards
raghu r

Report message to a moderator

Re: delete tables of many users at a time [message #110960 is a reply to message #110958]

Fri, 11 March 2005 09:00

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Replace MAG% with your USER%
COMMENT OF dbms_output i you dont want (if you prefer to have it,
make sure you have set serveroutput on and dbms_output.enable('100000000') or any high value)

 1  declare
  2  cursor c1 is select owner,table_name from dba_Tables where owner like 'MAG%' order by owner;
  3  begin
  4  for mag in c1 loop
  5  exit when c1%notfound;
  6     execute immediate ('drop table '||mag.owner||'.'||mag.table_name||' cascade constraints');
  7     DBMS_OUTPUT.PUT_LINE ('drop table '||mag.owner||'.'||mag.table_name||' cascade constraints');
  8  end loop;
  9* end;
mag@mutation_mutation > /
drop table MAG.DEPT cascade constraints
drop table MAG.EMP cascade constraints

PL/SQL procedure successfully completed.

Report message to a moderator

Re: delete tables of many users at a time [message #111105 is a reply to message #110960]

Sun, 13 March 2005 22:49

raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai

Junior Member

hi
thanks for your help.
i also tried the same but i tried to create a procedure as below

CREATE OR REPLACE PROCEDURE DELTABLE is
cursor c1 is
select owner FROM dbt where OWNER like'TEST%';
sqltext varchar2(200);
begin
open c1;
open c2;
loop
fetch c1 into u;
sqltext:='drop TABLE'||u.OWNER||'.'||u.table_name;
execute immediate sqltext;
exit when c1%notfound;
end loop;
end;

Errors for PROCEDURE DELTABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/19 PLS-00201: identifier 'SYS.DBA_TABLES' must be declared
5/1 PL/SQL: SQL Statement ignored
5/8 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

5/24 PLS-00201: identifier 'SYS.DBA_TABLES' must be declared
13/1 PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/1 PL/SQL: SQL Statement ignored

u system.DBA_TABLES%ROWTYPE;

help me whats the err is about

Report message to a moderator

Re: delete tables of many users at a time [message #111140 is a reply to message #111105]

Mon, 14 March 2005 04:24

Maaher
Messages: 7065
Registered: December 2001

Senior Member

It's (most probably) a permissions issue. Your access to DBA_ views has been granted through a role. In order to use it in a procedure the grant should be done directly to the user. Look here for more info, or search the board. It's a frequent issue.

MHE

[EDIT: Typo]

[Updated on: Mon, 14 March 2005 08:28]

Report message to a moderator

Re: delete tables of many users at a time [message #111172 is a reply to message #111105]

Mon, 14 March 2005 08:09

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Maaher gave you the soltuion.

Report message to a moderator

Re: delete tables of many users at a time [message #111244 is a reply to message #111172]

Mon, 14 March 2005 23:05

raghuraja_r
Messages: 22
Registered: February 2005
Location: chennai

Junior Member

sorry for disturbing u guys again.

i had logged in as system/manager user.
is it still i need any more permission.
if so who will grant me that.

regards
raghu raja

Report message to a moderator

Re: delete tables of many users at a time [message #111285 is a reply to message #111244]

Tue, 15 March 2005 04:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Same cause/action. SYSTEM is no dictionary owner, he has been given grants through DBA role. Read my previous post.

MHE

Report message to a moderator

Previous Topic:	Newbie question
Next Topic:	Bug Report Link??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 02:30:40 CDT 2024