OraFAQ Forum: SQL & PL/SQL » Database vs Tablespace

Home » SQL & PL/SQL » SQL & PL/SQL » Database vs Tablespace

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

Database vs Tablespace [message #654158]

Wed, 27 July 2016 20:50

KirkSpiritor
Messages: 4
Registered: July 2016

Junior Member

Hi everyone.

I'm a quite decent programmer, and I'm not bad with SQL, but using an Oracle server is something new to me. Most of my work is of a smaller nature using MS-Access as my database.

However, I'm being asked to assist with a project that will be using an Oracle server. The server is up and running for other purposes. It's on a WAN that connects several facilities together which each have their own LAN.

My team is exploring the possibility of setting up a new set of tables on this Oracle server. I'm taking the lead of talking to the international I.T. guys, but I'm still coming up to speed to speak their Oracle "language". I'm coming along fine, but I do have a few questions.

First, I've learned a great deal about TableSpaces, but this I.T. department is also saying that they will need to set up a separate "database" for me on the Oracle Server. To set up the TableSpace, I proposed this SQL+ statement:

CREATE TABLESPACE TheRegistry DATAFILE 'TheRegistry.dat' SIZE 20M ONLINE;

I was thinking that the 'TheRegistry.dat' file would be the actual database (possibly with some additional files that the server may create for locking and what-not.

Please provide me some help in intelligently talking to these I.T. folks. The more I can convince them that I know what's going on, the more they'll be willing to help me.

Best Regards,
Kyle

Report message to a moderator

Re: Database vs Tablespace [message #654161 is a reply to message #654158]

Thu, 28 July 2016 00:11

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

Senior Member
Account Moderator

In my opinion, I'd say that tablespace is NOT what you need. You need a "schema" , i.e. someone should create a new "user" for you using (in its simplest appearance)

create user kirkspiritor identified by some_password;

Then you'd need to be granted some privileges so that you could actually connect to that user, create tables, procedures, etc. User along with its objects make a schema.

Here's an example; everything is done by a privileged user (such as SYS):

-- list tablespaces so that you'd know which tablespaces you have in the database
select tablespace_name from dba_tablespaces;

create user kirkspiritor identified by some_password
  default tablespace users
  temporary tablespace temp
  profile default
  quota  unlimited on users;

grant create session            to kirkspiritor;
grant create table              to kirkspiritor;
grant create procedure          to kirkspiritor;
grant create sequence           to kirkspiritor;
grant create database link      to kirkspiritor;
grant create view               to kirkspiritor;
grant create trigger            to kirkspiritor;
grant create materialized view  to kirkspiritor;

So: you'd use existing tablespaces, but have your own user and do whatever you have to do there.

Report message to a moderator

Re: Database vs Tablespace [message #654167 is a reply to message #654158]

Thu, 28 July 2016 00:56

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

In an Oracle database, one tries to separate the development domain from the administration domain. A developer logs on as a user and creates objects in a schema (which is what Littlefoot is proposing). The administrator manages everything else, including the physical storage: tablespaces and datafiles. Your DBA will look after you! Just tell him what you need to do, and he will sort out how to do it.

One unrelated point: case sensitivity in identifiers. In your example,
Quote:

CREATE TABLESPACE TheRegistry DATAFILE 'TheRegistry.dat' SIZE 20M ONLINE;

SQL is case sensitive, but many client tools will do implicit case conversion in some circumstances. The name of your tablespace would be implicitly converted to upper case, but the name of the datafile (which is missing a path) would not be. It is generally considered best practice to use upper case for all identifiers to avoid confusion.

Report message to a moderator

Re: Database vs Tablespace [message #654171 is a reply to message #654158]

Thu, 28 July 2016 01:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Database Concepts

Report message to a moderator

Re: Database vs Tablespace [message #654227 is a reply to message #654158]

Thu, 28 July 2016 08:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

A big terminology difference exists between Oracle & Microsoft; starting with the word "database".
A Microsoft database is the equivalent to Oracle schema.
With Oracle a tablespace is just a logical collection of objects and tablespace is transparent during application execution.
If your colleagues want to create a new Oracle database for your application; just say THANKS & proceed.
Pick your battles wisely.

Report message to a moderator

Re: Database vs Tablespace [message #654228 is a reply to message #654171]

Thu, 28 July 2016 08:19

KirkSpiritor
Messages: 4
Registered: July 2016

Junior Member

Thank you Littlefoot and John for your great information. At corporate headquarters, we'll be sharing an Oracle server "box" with at least one other "project". I must say that, the I.T. folks at corporate are somewhat knowledgeable about their Oracle box, but they're not total experts.

Therefore, it falls on ME to tell them exactly what to do to allow our team to proceed with our development. I believe that'll involve creating a tablespace for us, and then also creating a user with relatively full rights to that tablespace so that we can proceed with creating our own tables, fields, indices, relationships, etc.

However, when we have conference calls with them, they say that our work must also be a completely separate "database". This is where I get confused. I'm starting to think that it's them that are confused, but I need to be crystal clear on this before I push the issue.

I do understand what they're after though. They just want our group's work to be as separate as possible for the other "project" that's also taking place on that Oracle server box.

Just as some additional FYI, I've installed the Oracle 11g Express server on my machine, and I've successfully logged into that as administrator, created a tablespace, created another user, logged in as that other user, created tables & fields, and added records to those fields via a front-end-stub I've written. Now, if I can just get our corporate I.T. to do their part, and expose a user and tablespace to the WAN, I can do it over the WAN rather than just my local machine.

Again, when they say it needs to be a "separate database in addition to a new tablespace", I need to have an intelligent response to that comment.

Again, thanks a million to everyone who assists me with this.

Regards

Report message to a moderator

Re: Database vs Tablespace [message #654230 is a reply to message #654228]

Thu, 28 July 2016 08:36

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 28 July 2016 08:02

Database Concepts

Report message to a moderator

Re: Database vs Tablespace [message #654232 is a reply to message #654230]

Thu, 28 July 2016 08:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Again, when they say it needs to be a "separate database in addition to a new tablespace", I need to have an intelligent response to that comment.
Say "THANKS" & proceed to deal with other unresolved issue.

Report message to a moderator

Re: Database vs Tablespace [message #654240 is a reply to message #654232]

Thu, 28 July 2016 13:03

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

Senior Member
Account Moderator

Unlike the others here, I'm just a developer with little (or, should I say, no) knowledge about DBA side of the story. Again, from my point of view: we have a database. One. There are number of different schemas in there which serve different applications. Some of them use mutual data - one reads some information from other users; another one selects data from one schema and updates information in it (once it finishes its processes). All of them happily coexist in the same database.

I don't know why those guys insist on maintaining two databases. If they are afraid that one project will affect another one (such as badly written query which makes your temporary tablespace grow and eat up all free space, all available RAM, etc.), it can happen anyway (especially as both databases will be on the same computer). Besides, are they aware of license costs?

I don't know; people who say that you should simply accept what they (the IT guys) say maybe / probably are right. However, I just think that two schemas in the same database would serve the purpose just fine. If it turns out that there are true problems with it, it is a simple task of creating a new database, export your schema here and import it over there. Even I could do that.

Report message to a moderator

Re: Database vs Tablespace [message #654243 is a reply to message #654240]

Thu, 28 July 2016 13:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I just think that two schemas in the same database would serve the purpose just fine.
I agree with statement above.
From a developer's perspective, in which database the schema resides (shared or dedicated database), does not matter.
I see little to be gained by pushing the IT guys hosting Oracle for a schema inside their existing database.

Report message to a moderator

Re: Database vs Tablespace [message #654245 is a reply to message #654228]

Thu, 28 July 2016 13:14

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Let your DBAs do it the way they want to. It's their job. Right or wrong, it isn't your problem.

There is a hierarchy of responsibility:
Developers have to do what their DBA tells them. And they don't like it.
DBAs have to do what their System Administrator tells them. And they like that even less.
System Administrators do whatever they like.
And as for the users, well, they just have to muddle along and hope that us IT blokes don't make their lives too unpleasant.

Report message to a moderator

Re: Database vs Tablespace [message #654252 is a reply to message #654228]

Thu, 28 July 2016 14:05

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Thank you Littlefoot and John for your great information. At corporate headquarters, we'll be sharing an Oracle server "box" with at least one other "project". I must say that, the I.T. folks at corporate are somewhat knowledgeable about their Oracle box, but they're not total experts.

Therefore, it falls on ME to tell them exactly what to do to allow our team to proceed with our development. I believe that'll involve creating a tablespace for us, and then also creating a user with relatively full rights to that tablespace so that we can proceed with creating our own tables, fields, indices, relationships, etc.

NO. a tablespace is just a physical file to store the data, it is still part of the original oracle database. While you can make a new tablespace that will only be used by a single user, why do it? It will not gain you anything. All you need is to make a new schema (user). The schema will be separate from the existing users of the database.

However, when we have conference calls with them, they say that our work must also be a completely separate "database". This is where I get confused. I'm starting to think that it's them that are confused, but I need to be crystal clear on this before I push the issue.

They are the ones that are confused. Unless the existing application can't have anyother schema in the database there is NO need to create a new database that will consume resources on the server. A database has to have all the oracle code running for each database and it's own memory. A shared database does not have that problem. I am running a single database with almost 60000 users and it is running on a server with 128 processors and everything works great. A seperate database is a SQL SERVER concept, NOT an oracle one and if they are oracle dba's they should know better.

I do understand what they're after though. They just want our group's work to be as separate as possible for the other "project" that's also taking place on that Oracle server box.

A separate schema with the appropriate permission will totally make each "project" invisible to the other one.

Just as some additional FYI, I've installed the Oracle 11g Express server on my machine, and I've successfully logged into that as administrator, created a tablespace, created another user, logged in as that other user, created tables & fields, and added records to those fields via a front-end-stub I've written. Now, if I can just get our corporate I.T. to do their part, and expose a user and tablespace to the WAN, I can do it over the WAN rather than just my local machine.

Again, when they say it needs to be a "separate database in addition to a new tablespace", I need to have an intelligent response to that comment.

I suspect that they are SQL SERVER admins and NOT Oracle DBA's

Again, thanks a million to everyone who assists me with this.

Regards

Report message to a moderator

Re: Database vs Tablespace [message #654263 is a reply to message #654252]

Thu, 28 July 2016 16:36

KirkSpiritor
Messages: 4
Registered: July 2016

Junior Member

@Bill B: Well, I don't know what certifications they have, but I can promise that, in terms of the knowledge they have they are not Oracle DBA's. However, they do have full rights to setting things up on this Oracle server box. That's part of the problem. At some point, we will probably need to get the Oracle folks involved to assure corporate that the "walls" between these application are setup correctly (and the Oracle folks would be happy to do that, for, of course, the $$$).

Once we get over these admin/security issues, I feel confident that I can create our tables and build front-ends to feed data into them (and also help to possibly hook MS-Access up to those tables via ODBC so that users can use the Access visual SQL builder to perform queries). It's just getting over this initial hump.

Y'all are giving me more confidence to speak to them a bit more firmly that we JUST need a new tablespace, and that thinking in terms of a separate database (Oracle box) is not the way we need to go.

I do truly appreciate the sharing of expertise.

Best to all.

Report message to a moderator

Re: Database vs Tablespace [message #654264 is a reply to message #654263]

Thu, 28 July 2016 16:43

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The nice thing about oracle is that, by default, one schema can't see another one. For example if you load a table into your oracle express database in one schema (say user1) and then build another table in a second schema (say user2), nether one will ever be able to see the other schemes objects unless given specific permission to do so. For example is user1 had a table called table1 and you issued the following command while logged in as user1

grant select on table1 to user2;

then user2 could select from table1 using the command

select * from user1.table1;

however the user2 could only select from the table because that is the only right they were given.

If user1 doesn't GRANT any rights to user2 and user2 doesn't GRANT any rights to user1 neither one will ever be able to see the others tables.

Good luck with the database admins.

[Updated on: Thu, 28 July 2016 16:44]

Report message to a moderator

Re: Database vs Tablespace [message #654265 is a reply to message #654264]

Thu, 28 July 2016 16:47

KirkSpiritor
Messages: 4
Registered: July 2016

Junior Member

And yes, I clearly need to start using that word "Schema" with them, and understand that it basically means creating a new user with certain permissions, restricted to specific tables that we'll be creating. I can't tell you how much I appreciate this discussion.

Report message to a moderator

Re: Database vs Tablespace [message #654268 is a reply to message #654265]

Fri, 29 July 2016 00:52

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Once more

Michel Cadot wrote on Thu, 28 July 2016 15:36

Michel Cadot wrote on Thu, 28 July 2016 08:02

Database Concepts

There is an overview chapter and if you have read it you'd now know all these things with more details.
In addition, this gives you an official link for your DBA to refer to and talk with the correct words.

Report message to a moderator

Re: Database vs Tablespace [message #654281 is a reply to message #654263]

Fri, 29 July 2016 05:24

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

Senior Member
Account Moderator

KirkSpiritor wrote on Thu, 28 July 2016 23:36

... we JUST need a new tablespace ...

OK then, we agree that we disagree /forum/fa/1582/0/

/forum/fa/1582/0/

but that's OK.

It is shame that those guys don't speak Oracle as their native tongue; if they did, you wouldn't have to ask such questions here. Moreover, they seem to be "Oracle DBAs" just like a lot of "DBAs" in the world (including me) whose only job is to watch Oracle administering itself /forum/fa/1950/0/

/forum/fa/1950/0/

Report message to a moderator

Re: Database vs Tablespace [message #654290 is a reply to message #654265]

Fri, 29 July 2016 07:42

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Please allow me to sum up a couple of points.
While it is true that all you need is a well defined schema, it really does not matter what database that schema is in. If the DBA's (regardless of their competence) want to make a separate database for it, what does that really matter to you? If they want to put it on a completely new server, what does that really matter to you? If they want to put it on a virtual server, what does that really matter to you? Yes, there are legitimate arguments for and against each of those options. But in the end, what does that really matter to you?

I'd approach it like this ... explain to the DBA's what you need (a schema). Explain to them your understanding of the implementation options, and make it clear that it is "my understanding". If they insist on an implementation you don't necessarily agree with, ask them to explain their reasoning, "just so I'll learn something and understand better." Don't be afraid to say "but what about <fill in the blank with whatever concern>". But keep the attitutude and approach one of "it's your call, but I'd like to better understand the pros and cons of each option." And in the end, keep in mind "what does that really matter to you?"

Report message to a moderator

Previous Topic:	Creating different worksheets in a single excel
Next Topic:	how create synonym for full database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:20:46 CDT 2024