Re: Multiple "databases" in Oracle...from a SQL Server Admin view..HELP

Thanks for the most comprehensive yet concise answer to this issue I've seen in these groups.. I have wanted one to give to folks ( especially converts from SqlServer) , and, if you saw my reply to this poster, mine was not very good...

As usual, good job Howard ;-)

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>
>"Trevis" <trevisc_at_yahoo.com> wrote in message
>news:30e0b07d.0209300541.3a5cd298_at_posting.google.com...
>> Hello all you Oracle Gurus!
>>
>> I'm really confused about setting up more than one database in oracle.
>> I've read multiple posts about "Instances" and "Schemas" and i'm more
>> confused now then ever.
>
>OK, some definitions to help you on your way. An instance is the set of
>memory structures and processes through which access to the database is
>gained, and where all the processing of a select (or other SQL) statement
>takes place.
>
>A database is the set of physical files on disk where data and transactional
>information is permanently stored. There is a minimum of 4 files which make
>up an Oracle database: a control file, a datafile and two online redo logs.
>
>A schema is just a collection of objects (tables, indexes, clusters and so
>on) owned by a user. It doesn't physically exist, but is a logical grouping
>of objects by owner. Oracle does have a physical grouping structure, too,
>called the tablespace. That collects a group of objects by their physical
>placement on disk.
>
>The SQL Server equivalents are notoriously hard to pin down, because (after
>all) they are two completely different systems. But a SQL Server database is
>more like a schema or tablespace than anything else: you use SQL Server
>databases to break a complete SQL Server server down into smaller pieces.
>Precisely what a tablespace does (physically) or a schema does (logically).
>
>An 'instance' of SQL Server is (as I understand it) the entire system: you
>install the software onto your Windows box, and you get a whole set of
>memory and process structures, together with some starter 'databases' (like
>"master"). That's what we'd call an Oracle Server (the instance+a database).
>What Oracle calls a database, SQL Server doesn't have a name for, I think:
>it's actually the collection of all your 'databases' put together. And SQL
>Server doesn't have a name (as far as I know) for what we call an
>Instance -there's no identifiable name in SQL Server for the bunch of
>processes and memory structures needed to make SQL Server work.
>
>>Here is what I want to accomplish. I want to
>> set up a box that I can use for testing VB applications against.
>> There are multiple applications that use different databases(tables).
>> In SQL server I just create a new "database" and create an odbc
>> connection that uses that database's tables as its default.
>
>Well, with the above definitions in mind, the equivalent in Oracle is to
>have one database, but multiple schemas (and potentially, multiple
>tablespaces if you want the two sets of data to be physically separated from
>each other, as well as logically).
>
>>How do I
>> accomplish this in Oracle 8.1.7? I've tried creating 2 databases but
>> it seems like the first "Instance" that loads hogs up all the
>> resources and even though I see the second database I can't access it.
>
>As I say, you only need one database, but two schemas or tablespaces. The
>subtler problem here is how to get two different Instances running on one
>box (which I don't think actually is what you are after) -you'll have to
>delve into the depths of the Oracle init.ora file, and start modifying some
>of the memory parameters there so that your Instance doesn't behave as such
>a resource hog.
>
>The reason I don't think you want to go down this route is that if you have
>two separate Oracle databases, you'll always have to establish two
>completely separate ODBC links to the databases to use both sets of data. If
>you use a single database, you need only create two users (which is how you
>create schemas):
>
>create user app1 identified by app1 default tablespace APP1 temporary
>tablespace temp quota unlimited on APP1;
>
>and
>
>create user app2 identified by app2 default tablespace APP2 temporary
>tablespace temp quota unlimited on APP2;
>
>(Of course, those two tablespaces need to exist beforehand, so a "create
>tablespace APP1 datafile 'C:\wherever\app1.dbf' size 100m;" and a "create
>tablespace APP2 datafile 'C:\wherever\app2.dbf' size 100m;" would be needed
>first).
>
>Now you make a single ODBC connection to the database, and -depending on who
>you connect as- the relevant set of data will be visible. Were you to do
>something like this:
>
>create user superone identified by password;
>connect app1/app1
>grant select on APP1TABLE1 to superone;
>grant select on APP1TABLE2 to superone;
>connect app2/app2
>grant select on APP2TABLE1 to superone;
>grant select on APP2TABLE2 to superone;
>
>...and so on, then the single connection could be made as user SUPERONE, and
>both sets of application tables would be simultaneously visible. You
>couldn't do that with separate databases.
>
>> I'm sure i'm missing something really simple because I will be the
>> only one accessing the data and I don't care about performance I just
>> care about having different databases on one machine. Please post any
>> friendly advice. My company won't pay for training but I could
>> purchase books if there are any that would help.
>
>It's a shame they won't pay for any training, because I'd say that 5 days
>spent on the DBA1 Fundamentals course would pay enormous dividends.
>Converting from one database terminology to another is notoriously
>difficult. You get loonies saying "well, they're all relational databases,
>aren't they?, so they all work fundamentally the same way" -which has a
>grain of truth in it, but hides real and fundamental differences. When the
>word "database" means two completely different things in two products, its a
>fair bet that making the transition is not a walk in the park.
>
>If you really can't persuade them to spring for a single 5-day course, shame
>on them, and hard luck to you. Try reading a couple of excellent books, such
>as (ahem) 'Beginning Oracle Programming' or 'Expert One-on-One' (both
>published by Wrox).
>
>Otherwise, try not to translate SQL Server into a one-to-one correspondence
>to Oracle. I've been there (the other way round) and it fundamentally
>doesn't work. You really need to try to approach each product independently,
>treat it on its own terms, and get inside the mindset of each one as though
>it were the only product out there.
>
>Best of luck
>Regards
>HJR
>
>>
>> Thank you!
>>
>> T
>

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Tue Oct 01 2002 - 10:08:09 CDT