Home » RDBMS Server » Server Administration » Basic questions on Oracle (8i/MS server 2003)
Basic questions on Oracle [message #418805] Tue, 18 August 2009 10:32 Go to next message
polsol
Messages: 7
Registered: September 2008
Location: Kuwait
Junior Member
I'm a newbie at Oracle compared to most and require some basic information. Hoping this is the right forum to answer my questions.
Having read the 10i manual (one of them) some time back I'm still confussed about the tablespaces and schema relationship. Is there a simple, concise, description of this topic anywhere?

Secondly, I was talking to a programmer who wrote our current 8i application. I was asking why he didn't split up the tables et al into different schemas - such as production, sales, finance etc. He replied that by doing so would have an adverse affect on system resources and performance. Is this correct?
From memory the 'sample' schema on the 10i 'trial version' had an 'HR' sample schema. This would indicate to me (rightly or wrongly) that splitting departments by schema. Seems the way to go for access privledges. How does one normally construct a DB? One large schema or smaller schemas (which would appear more manageable to me)? Any thoughts on the correct way to construct an Oracle DB?

Thanks in advance and excuse my Oracle ignorance!
Re: Basic questions on Oracle [message #418807 is a reply to message #418805] Tue, 18 August 2009 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts

Regards
Michel
Re: Basic questions on Oracle [message #418808 is a reply to message #418805] Tue, 18 August 2009 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm
Re: Basic questions on Oracle [message #420183 is a reply to message #418805] Thu, 27 August 2009 07:42 Go to previous messageGo to next message
arungulia
Messages: 2
Registered: August 2009
Junior Member
tablespace :
it is a logical object in oracle database which have one or more datafile. these datafile are phyical objects means we know exact location on disk. we know phyical address of datafile. schema is user in oracle. who creates table and ohter objects in database. user can have write permission on different tablespaces. like in one tablespace his tables are created, on other its indexes are created.

in big database we
Re: Basic questions on Oracle [message #420188 is a reply to message #420183] Thu, 27 August 2009 08:26 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
arungulia wrote on Thu, 27 August 2009 08:42
tablespace :
it is a logical object in oracle database which have one or more datafile. these datafile are phyical objects means we know exact location on disk. we know phyical address of datafile. schema is user in oracle. who creates table and ohter objects in database. user can have write permission on different tablespaces. like in one tablespace his tables are created, on other its indexes are created.

in big database we


Did you fall off a cliff?

Proper grammar would help us understand if you were asking a question of making a statement. If you are saying that tables and indexes should be in separate tablespaces, you are basing this on very outdated information at best, or simply incorrect information.
Re: Basic questions on Oracle [message #420209 is a reply to message #420183] Thu, 27 August 2009 09:50 Go to previous messageGo to next message
polsol
Messages: 7
Registered: September 2008
Location: Kuwait
Junior Member
Thank you Arungulia for attempting to answer my question(s).
Re: Basic questions on Oracle [message #420212 is a reply to message #418805] Thu, 27 August 2009 09:59 Go to previous messageGo to next message
polsol
Messages: 7
Registered: September 2008
Location: Kuwait
Junior Member
Thank you Michel and Black Swan for the reference.

However, whilst this reference provides details as to what Tablespaces et al are, in the first instance it doesn't explain why one would need say, more than one Tablespace.

Additionally whereas it's possible to split one schema between tablespaces, again, it doesn't say why one would want to do this.

Is there a document which provides details of what I would call 'Best Practices' which describes alternate DB 'set-ups' for different usage requirements?
Re: Basic questions on Oracle [message #420222 is a reply to message #420212] Thu, 27 August 2009 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not say that because there is no general reason.
The reasons are the same ones that yours to have more than one car in your family (putting apart the money point). These reasons are different for each family depending on where you live, where you work, where are schools and mall, is there any road, what is the traffic, you can have any number of cars you want, do you need them?

Regards
Michel
Re: Basic questions on Oracle [message #420237 is a reply to message #420222] Thu, 27 August 2009 11:35 Go to previous messageGo to next message
polsol
Messages: 7
Registered: September 2008
Location: Kuwait
Junior Member
Thanks for your reply Michel,

Following your analogy, would this be something akin to having car air conditioning in Alaska/Siberia?
From what I gather, Tablespaces are 'Bells and whistles'. Sort of saying to IBM/MS, "well we have a 'silent' whistle (sic)' and you don't"?
In other words, Tablespaces are superfluous?

My original question actually arose from a statement from our ERP vendor that using multiple schemas was detrimental to Oracle system performance - which seems a little odd to me as the 'starter' schema in 10g is called 'HR' - which would seem to indicate that splitting a DB (Tablespace) by 'departmental' schemas is not a bad thing (especially when one considers access privledges).

In reality, I'm trying to understand the Oracle 'topology' and why Oracle see fit to do things the way they do.
Re: Basic questions on Oracle [message #420238 is a reply to message #418805] Thu, 27 August 2009 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>In other words, Tablespaces are superfluous?
Can be or not.
With only a single disk volume, multiple tablespaces are superfluous.

Tablespaces are one way to "distribute" disk activity across multiple disk volumes.
Re: Basic questions on Oracle [message #420239 is a reply to message #420237] Thu, 27 August 2009 12:36 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, they are aso a way to separate data that you want to manage in different ways.

For instance, if you want to back up or purge departments at different frequencies, or if you want to back up them in different locations and so on.

Regards
Michel

[Updated on: Thu, 27 August 2009 12:38]

Report message to a moderator

Previous Topic: ORA-01555
Next Topic: tablespaces
Goto Forum:
  


Current Time: Fri Dec 09 11:45:26 CST 2016

Total time taken to generate the page: 0.14185 seconds