Solved

Oracle 11G Express Edition - 101

Posted on 2011-09-16
9
500 Views
Last Modified: 2012-05-12
OK.. forgive me if these are silly questions, but I'm confused.  I'm a SQL Server guy and I have a client who wants me to do some reporting from his Oracle 11G database.  Thinking like a SQL guy, I figured no big deal.   So, I downloaded the Express Edition of Oracle and installed it on one of my machines.   I clearly need a little kickstart here - or someone to tell me that I'm doing this all wrong.   I figured I could have my client back up his database and I could restore it on the Express Edition and take a look around.   Then I figured I could build my own database and do my tables and queries with data imported from his (I also need some data which is not in his Oracle DB), so I didn't mess up his database.   Apparently this isn't the way it works.

First issue - it looks to me like EVERYTHING in Oracle Express is in ONE database, differentiated by "tablespaces".   Is this true?   Does this totally destroy my plan to restore the client's database to my machine?    Secondly - I'm not crazy about the data management tools that come with Express.  I see that there is something called Oracle Enterprise Manager (which I assume is like SQL Server Management Studio), but I don't see an easy way to get it or make it work with Express.

Can someone just give me an orienting kick in the pants to get me going in the right direction - or tell me not to try to do what I'm trying to do?

Help!
0
Comment
Question by:BobSacks
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36552168
Your assumptions are close.

In Oracle a 'database' consists of the datafiles, redo, etc on disk.

An Instance is the memory and server processes.

A database consists on tablespaces that can be made up of one or more datafiles.

Objects like tables and indexes are 'owned' by db users.  These are then called schemas.

You will not be able to 'restore' your clients database unless it is also XE and properly backed up.

You can export (expdp or exp) the schema(s) or specific tables.  Then you can import them into your XE as long as you do not exceed the space limitations of XE.

If you use expdp and impdp you don't need to worry about tablespaces in XE since you can remap them.

What data exists outside the database and how is it used?

Enterprise manager is more for the DBA.  From a developer side look at SQL Developer.
0
 

Author Comment

by:BobSacks
ID: 36552421
Hmmmm... this is all pretty strange to me.   I'm approaching this from my comfort zone.  Everything I want to do is almost trivially easy in SQL Server.   I'm trying to figure out if my aversion to Oracle is just  because it is "different" and "unfamiliar" or if it is genuinely harder to do.   Moving things in and out of SQL Server (some of the data is kept in Excel - don't ask why) is easy, and SQL plays nice with MIcrosoft Office and .NET.    (What is all this crap with Oracle Client and "Instant Client"?)

Your comments and clarifications help a lot.   I need to do some more reading and playing with Oracle to make sure I just not fighting it because it's unfamilar - but my gut tells me to drop back 15 yards and punt and rethink all this.

I even have this probably insane idea where I install SQL Server Express on their server and have it suck what I need out of Oracle every night (the reporting doesn't have to be real-time).   The rest of the project would be really easy once I have the data in a form that I can use.   Is this truly crazy?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36552454
It easy to go with what you know but I think the frustration is because Oracle is unfamiliar.  Sql server is just as strange to me.  Trust me, it seems so backwards the more I learn about it with my 20 years using Oracle.

That said, if you are looking for seamless integration between Excel and Oracle, you are out of luck.  MSoft owns sql server and excel and has the option to see they plug and play nicely.

Oracle works well with .Net but not so much with Office.

As far as the client goes, MSoft has built the connectors between the products into their OS.  Anything outside the OS needs installed.  The Oracle client is just another install.  A non-MSoft program that needs to run under Windows.

Extracting the data from Oracle into sql server is always an option but do you want to make the client install software to suit you or should you install sofware to make it easy on them?

Exporting Oracle data is pretty easy.  You would get a binary file to import and then what you do with the data is up to you.  If you want to use SQl server express, who would know?
0
 

Author Comment

by:BobSacks
ID: 36552483
Yeah... my problem is that they have a LOT of data and it's an industry specfific application with the Oracle back-end.    I need a bunch of stored procedures and probably some views and another couple of tables to do what I want.  I don't want to modify their database because my changes would be wiped out when the app publisher issues any updates.    If this was SQL Server, I would have just added another database to the server instance and done my thing there.   That doesn't seem to e the way Oracle works.    I don't want any of my reporting to use any workstation-based queries because of the amount of data which would have to go across the network  in a non-client server soltuion.

Client doesn't care about how it works - just wants it to work.  Never more than a few users, so installing SQL Express would do - and it's free.

I need to think about this some more.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36552526
OH, I think I'm getting it.  You only need the data on your machine to develop the reports?

As far as wiping out your code on a new release: that can be mitigated by creating a new schema(user) that has select access to the app's schema (user).

Then it is basically the same as a sql express db.  The app can upgrade it's user and your user objects would remain.

Unless the app upgrade drops and recreates the entire database.

You will have the same issues either way if the app changes/alters core tables:  neither approach will continue to work.

If you are unfamiliar with Oracle what makes you think you need stored procedures, views and additional tables to do what you need?  You might but you might not.
0
 

Author Comment

by:BobSacks
ID: 36552577
Right.. I only need the data locally during development since I'm not on site.   I can get VPN or remote desktop access to their network, but all my cherished development tools are on my machines and I like to work "disconnected" sometimes.  My strong inclination is to stay the heck out their database and construct my own extracted data warehouse.   That's why I'm thinking the Views and other tables.   I'd like to flatten some of the normalized tables to make the reporting easier.  Ultimately I'd like to show them how to do their own reportiing, so "de-normalizing" the data makes that easier.   In SQL Server, adding another database to a server instance is a totally independent "thing" - you can mess with it, back it up, restore it, etc. without me ever worrying about messing up their mission critical app.   I was totally shocked to see that Oracle doesn't work that way.   I'm sure there is are good reasons to do it that way - but it seemed strange to me.

You're right that my proposal doesn't totally insulate me from changes in the main database.  HIstorically all their changes have been additive.  The number of tables hasn't changed, they just occasionally add more columns.   I don't have the whole story, but I think it used to be Borland Interbase.. then went to Firebird.. and finally to Oracle.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36552613
You can create a separate database on their system as well and link the two.

It is basically the same.  Just way different terminologies.

Hopefully another Expert will be along later that knows both systems and can clear it up for you.

I really only speak Oracle.
0
 

Author Comment

by:BobSacks
ID: 36552619
That's interesting....  So "real" Oracle doesn't work like Express?  i.e. you CAN have more than one database and link them?   I lke that!

 I've taken a lot of your time and I really appreciate the help you've given me.  You've earned the points and I will award them.  Like you said, I want to keep this question open for a little while to see if someone else chimes in.

Thanks again!


0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36559788
Sorry for not getting back to this until today.  I'm not very active on EE on weekends.

>> i.e. you CAN have more than one database and link them?   I lke that!

Yes.  Express is a 'special' free version that constrains certain aspects.  Number of databases/instances on a single machine is just one of them.

A fully licensed version can have as many databases as the server can handle.

Just remember SQL Server and Oracle are fierce competitors.  If one can do something, it isn't long until the other one mirrors the functionality.

Just keep that in mind in the future.  If you can do it in SQL Server, odds are you can do it in Oracle.

Just ask us!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Invalid Identifier Error 3 65
oracle query help 29 64
Export BLOB data from Oracle 10g 4 25
Oracle Syntax 8 42
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now