Home » SQL & PL/SQL » SQL & PL/SQL » droping all tables in a schema
droping all tables in a schema [message #229112] Thu, 05 April 2007 07:37 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
How can I drop all objects in a schema ?
Re: droping all tables in a schema [message #229113 is a reply to message #229112] Thu, 05 April 2007 07:41 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

drop user <username> cascade;


Create New User.

Regards
Taj
Re: droping all tables in a schema [message #229114 is a reply to message #229112] Thu, 05 April 2007 07:55 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
I want user to exsists because I created wrong tables etc now I want drop all objects in schema so that I have a freash database and I can run my script to crearte new tables ,
If I drop the user then I have create the same user with the same privilages is there any work around which will not delte the user but deletes all objects for the user ?
Re: droping all tables in a schema [message #229115 is a reply to message #229114] Thu, 05 April 2007 08:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You could make a script that selects from user_objects to delete all the objects, but to me, unless you have only one or two objects, recreating the user and granting them the privileges has to be less work (unless you don't know what privileges were given).
Re: droping all tables in a schema [message #229116 is a reply to message #229114] Thu, 05 April 2007 08:06 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

slow again.........

check these links it have been asked and answered many times.

oracle faq1

oracle faq2

oracle faq3

oracle faq4

regards
shanth

[Updated on: Thu, 05 April 2007 08:08]

Report message to a moderator

Re: droping all tables in a schema [message #229117 is a reply to message #229116] Thu, 05 April 2007 08:09 Go to previous messageGo to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Suppose I logged in with a user called test and now I want to drop this user can I do this or should I login ad sysdba ?
Re: droping all tables in a schema [message #229120 is a reply to message #229117] Thu, 05 April 2007 08:20 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

Suppose I logged in with a user called test and now I want to drop this user can I do this or should I login ad sysdba ?


Are you try Cool


[/i]
NO, you cann't.

You have to connect with "sysdba" or "drop user" privileged user.

Regards
Taj
Re: droping all tables in a schema [message #229139 is a reply to message #229120] Thu, 05 April 2007 09:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Just a DBA account would do.
Why sysdba? Sysdba is not meant for regular administrative purposes.
Re: droping all tables in a schema [message #229147 is a reply to message #229139] Thu, 05 April 2007 09:22 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Correct.

Previous Topic: Calculating Percent on running total
Next Topic: Schema Selective Copy
Goto Forum:
  


Current Time: Tue Dec 06 06:34:12 CST 2016

Total time taken to generate the page: 0.09143 seconds