[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Oracle 11G Express Edition - 101

Posted on 2011-09-16
Medium Priority
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?

Question by:BobSacks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 77

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.

Author Comment

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?
LVL 77

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?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

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.
LVL 77

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.

Author Comment

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.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 2000 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.

Author Comment

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!

LVL 77

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!

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

650 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