OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Empty database (Oracle 10g, Windows 7)

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

Empty database [message #562191]

Mon, 30 July 2012 05:18

bg1984
Messages: 21
Registered: July 2012

Junior Member

Hi,
How can I delete all the objects from my database?
Regards,
BG

Report message to a moderator

Re: Empty database [message #562192 is a reply to message #562191]

Mon, 30 July 2012 05:21

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

DROP them, I suppose.

What are you, actually, trying to do? Drop whose objects? Could you - instead of dropping objects - drop users (which would also drop all their objects so that might be faster than dropping objects themselves)?

Report message to a moderator

Re: Empty database [message #562193 is a reply to message #562192]

Mon, 30 July 2012 05:22

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

DROP DATABASE then recreate?

Report message to a moderator

Re: Empty database [message #562194 is a reply to message #562192]

Mon, 30 July 2012 05:29

bg1984
Messages: 21
Registered: July 2012

Junior Member

Thanks for your reply.
Actually I exported one entire database into newly created database. Now I would like to make this database as it was before. I understand your point that dropping users will drop objects as well and it's easier, but the problem is when I had exported the database, I hadn't mentioned any schema and so all the objects are in the SYS schema I suppose. So, How can I do this please?

Report message to a moderator

Re: Empty database [message #562195 is a reply to message #562194]

Mon, 30 July 2012 05:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SYS objects are never exported nor imported.
During import the objects go to their original schema (unless explicitly specified another one on the command line).

Regards
Michel

[Edit: missing word]

[Updated on: Mon, 30 July 2012 05:42]

Report message to a moderator

Re: Empty database [message #562196 is a reply to message #562195]

Mon, 30 July 2012 05:37

bg1984
Messages: 21
Registered: July 2012

Junior Member

I see. So, I think I can drop the users containing these objects I guess. Thanks very much for your help Michel.

Report message to a moderator

Re: Empty database [message #562277 is a reply to message #562196]

Tue, 31 July 2012 02:11

bg1984
Messages: 21
Registered: July 2012

Junior Member

Hi there, continuing with my previous topic, I removed my database at last and re-created it. Now, I re-created this database with the same name and when I checked the objects by issuing "Select * from tab", surprisingly the previous objects have appeared in this database as well. Why did this happen? I would like one fresh database, but need to put the same name. Please help.

Report message to a moderator

Re: Empty database [message #562282 is a reply to message #562277]

Tue, 31 July 2012 02:34

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How did you recreate it? What steps did you perform (mention all of them)?

Report message to a moderator

Re: Empty database [message #562284 is a reply to message #562282]

Tue, 31 July 2012 02:43

bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore

Member

With which user you issued "select * from tab" ?
If its sys/system and few other default schemas, there there will be same number of objects.

Regards,
Veeresh

Report message to a moderator

Re: Empty database [message #562285 is a reply to message #562282]

Tue, 31 July 2012 02:43

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In addition with which user did you execute your select?
Are you sure these objects are not Oracle standard ones?

Regards
Michel

Report message to a moderator

Re: Empty database [message #562288 is a reply to message #562282]

Tue, 31 July 2012 02:45

bg1984
Messages: 21
Registered: July 2012

Junior Member

Thanks for your reply. I recreated the database using DBCA. I followed the normal steps as we create a simple database.

Report message to a moderator

Re: Empty database [message #562290 is a reply to message #562288]

Tue, 31 July 2012 02:48

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In case you didn't see my answer posted when you were writing yours:

Michel Cadot wrote on Tue, 31 July 2012 09:43

In addition with which user did you execute your select?
Are you sure these objects are not Oracle standard ones?

Regards
Michel

Report message to a moderator

Re: Empty database [message #562291 is a reply to message #562288]

Tue, 31 July 2012 02:50

bg1984
Messages: 21
Registered: July 2012

Junior Member

The user was sys. Yes, I am sure these objects are not the oracle standard ones as they are the ones that were created when I imported the entire database(As I mentioned earlier).

Report message to a moderator

Re: Empty database [message #562292 is a reply to message #562288]

Tue, 31 July 2012 02:51

bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore

Member

Use the following step if the time permits,

Connect as sys
1.shutdown immediate;
2.startup mount;
3.alter system enable restricted session;
4.drop database;
---------------------
5. startup nomount pfile='path to pfile'
6. create database (try manually)
7. shut immediate;
8. startup nomount pfile='path'
9. create spfile from pfile;

Report message to a moderator

Re: Empty database [message #562293 is a reply to message #562291]

Tue, 31 July 2012 02:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What did you import, exactly? If export file contains objects BEFORE you dropped them, import imported them back. Maybe I don't understand what you are saying, but ... perhaps you should really name all steps you took.

Report message to a moderator

Re: Empty database [message #562295 is a reply to message #562293]

Tue, 31 July 2012 03:01

bg1984
Messages: 21
Registered: July 2012

Junior Member

Thanks very much for replies. Let me first try to create one new database with the different name so I can check if I still get the same objects in the new one. Veeresh, I will try following your steps afterwards. Thank you guys.

Report message to a moderator

Re: Empty database [message #562298 is a reply to message #562295]

Tue, 31 July 2012 03:08

bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore

Member

One thing is not clear.. what is "I still get the same objects in the new one" ?
all the sys(oracle system) objects will be same. IT will not change, no matter how many databases you create. Smile