Home » RDBMS Server » Server Administration » Empty database (Oracle 10g, Windows 7)
Empty database [message #562191] Mon, 30 July 2012 05:18 Go to next message
bg1984
Messages: 21
Registered: July 2012
Junior Member
Hi,
How can I delete all the objects from my database?
Regards,
BG
Re: Empty database [message #562192 is a reply to message #562191] Mon, 30 July 2012 05:21 Go to previous messageGo to next message
Littlefoot
Messages: 21805
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)?
Re: Empty database [message #562193 is a reply to message #562192] Mon, 30 July 2012 05:22 Go to previous messageGo to next message
gazzag
Messages: 1116
Registered: November 2010
Location: Bedwas, UK
Senior Member
DROP DATABASE then recreate?
Re: Empty database [message #562194 is a reply to message #562192] Mon, 30 July 2012 05:29 Go to previous messageGo to next message
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?
Re: Empty database [message #562195 is a reply to message #562194] Mon, 30 July 2012 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
Re: Empty database [message #562277 is a reply to message #562196] Tue, 31 July 2012 02:11 Go to previous messageGo to next message
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.
Re: Empty database [message #562282 is a reply to message #562277] Tue, 31 July 2012 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you recreate it? What steps did you perform (mention all of them)?
Re: Empty database [message #562284 is a reply to message #562282] Tue, 31 July 2012 02:43 Go to previous messageGo to next message
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
Re: Empty database [message #562285 is a reply to message #562282] Tue, 31 July 2012 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Empty database [message #562288 is a reply to message #562282] Tue, 31 July 2012 02:45 Go to previous messageGo to next message
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.
Re: Empty database [message #562290 is a reply to message #562288] Tue, 31 July 2012 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Empty database [message #562291 is a reply to message #562288] Tue, 31 July 2012 02:50 Go to previous messageGo to next message
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).
Re: Empty database [message #562292 is a reply to message #562288] Tue, 31 July 2012 02:51 Go to previous messageGo to next message
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;
Re: Empty database [message #562293 is a reply to message #562291] Tue, 31 July 2012 02:53 Go to previous messageGo to next message
Littlefoot
Messages: 21805
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.
Re: Empty database [message #562295 is a reply to message #562293] Tue, 31 July 2012 03:01 Go to previous messageGo to next message
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.
Re: Empty database [message #562298 is a reply to message #562295] Tue, 31 July 2012 03:08 Go to previous messageGo to next message
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



Re: Empty database [message #562428 is a reply to message #562191] Wed, 01 August 2012 07:50 Go to previous messageGo to next message
fantook
Messages: 3
Registered: July 2012
Location: lahore
Junior Member
Well, what you want to ask from that empty database??
Re: Empty database [message #562435 is a reply to message #562428] Wed, 01 August 2012 07:55 Go to previous message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because he does like the objects that are in there. Razz

Regards
Michel
Previous Topic: Remove datafile from operating system after droping its tablespace
Next Topic: How to manage ASM instance
Goto Forum:
  


Current Time: Tue Mar 19 04:05:08 CDT 2024