Home » Other » Training & Certification » Oracle XE 11g & Demo Schemas (HR and OE) (Oracle XE 11g / Vista)
Oracle XE 11g & Demo Schemas (HR and OE) [message #560322] Thu, 12 July 2012 01:50 Go to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
So I've installed Oracle XE 11g in order to prepare for the OCA SQL exam. Can anyone tell me how to invoke (enable, launch) the HR and OE demo schemas? My understanding is that I installed them along with Oacle XE 11g, or that I can install them via scripts. I'd like to be able to run queries against these databases and tables.

Thanks, in advance.

Cheers,
John Schramm
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560323 is a reply to message #560322] Thu, 12 July 2012 02:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Documentation is a wonderful thing (only if you read it). Have a look at Unlocking the Sample User Account chapter in 11g XE Getting Started book.

Seriously: welcome to both Oracle world & this forum. I believe that you should bookmark Oracle documentation page as you'll most probably use it frequently. The best thing is that it is really well written and free to use (you can even download PDFs, if you wish).

Although you are on 11g, check 10g R2 documentation page - it is nicely divided into several sections. Note "Getting started" and "Most popular" ones. Try to locate the same books in 11g documentation so that you wouldn't be confused. There are million books (11g Master Book List) and it is kind of difficult to pick the right ones (especially if you are a beginner).

I suppose we'll see you around in the future. Looking forward to it!
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560585 is a reply to message #560323] Sun, 15 July 2012 12:44 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi Littlefoot:

Sorry for the late reply. And thanks for your response. I've worked help desk for a long time, and you're exactly right. The answer to a lot of questions is RTDM (Read The Damn Manual). In all honesty I was trying to just get the queries going for study, but the manual you mentioned provides some good knowledge for setting up XE 11g, so I am taking your advice and learning some basics. Thanks for setting me on the right path.

I look forward to chatting on this forum going forward. The dialouge is great and there's lots of good information. I'm happy to be here.

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560758 is a reply to message #560323] Mon, 16 July 2012 13:48 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi:

Well, I went through the document you mentioned, and a few others. What I have found is that my install did not include the demo schemas or the scripts.

Can you tell me where I can find the complete set of scripts, or the schemas themseleves?

Thanks, in advance.

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560759 is a reply to message #560758] Mon, 16 July 2012 13:54 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
I did find a document which had a link for a zip download, but it only contained three HR scripts. I'll check to see if hr_main.sql has the other scripts internally, but the document mentions quite a few other scripts to be run.

Thanks!
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560763 is a reply to message #560759] Mon, 16 July 2012 18:16 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Take a look a this thread:

https://forums.oracle.com/forums/thread.jspa?threadID=957022&tstart=41
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560786 is a reply to message #560763] Tue, 17 July 2012 02:36 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi Matthew:

Thanks for your reply. I checked the thread and these are the documents I previously found. I'm not sure what to do with the scripts, and I don't have all the scripts listed in the documentation.

I went through the Getting Started guide again on another PC. This time, when I created the hr_apex workspace, and then went into Application Express, I was pleased to see that I have the HR tables, but no OE tables. I went back to SYSTEM signon and tried to create oe_apex user, but I do not have OE.

I think part of my confusion is getting all the users and workspaces straight. We have to unlock the HR and OE users, but then we create workspaces (which are like users), and then we will see the tables (if we've created them).

Am I heading down the right path? I really appreciate your help.

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560818 is a reply to message #560786] Tue, 17 July 2012 05:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does Application Express have to do with the whole story? Are you SURE you need it? Terms you mention ("workspace", "oe_apex user" look very suspicious to me). You really do NOT need Apex to work with SQL and PL/SQL. If I'm wrong, you'll certainly say so and explain what you are really trying to do.


From your initial post, I understood that you installed 11g XE database in order to prepare for an exam. A book you have refers to some sample schemas: HR and OE. If that's so, here's a walkthrough for you. I'm connected to my 11g XE database as SYS (a note: yes, I know, "never use SYS, it is special, ..." etc. but it'll do the job just fine for now):
M:\>sqlplus sys@xe as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Uto Srp 17 11:50:03 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

Let's check users I currently have:
SQL> select * from all_users order by username;

USERNAME                          USER_ID CREATED
------------------------------ ---------- -------------------
ANONYMOUS                              35 27.08.2011 08:37:13
APEX_040000                            47 27.08.2011 08:56:17
APEX_040100                            48 25.10.2011 08:58:12
APEX_PUBLIC_USER                       45 27.08.2011 08:56:17
CTXSYS                                 32 27.08.2011 08:36:19
FLOWS_FILES                            44 27.08.2011 08:56:16
HR                                     43 27.08.2011 08:55:19
MDSYS                                  42 27.08.2011 08:41:10
OUTLN                                   9 27.08.2011 08:20:54
RADNI                                  52 25.10.2011 11:50:23
SYS                                     0 27.08.2011 08:20:52
SYSTEM                                  5 27.08.2011 08:20:52
XDB                                    34 27.08.2011 08:37:13
XS$NULL                        2147483638 27.08.2011 08:40:49

14 rows selected.

SQL>

Fine! HR is here, OE is missing. Matthew posted a link to an OTN Forums discussion where one of participants posted a link which enables you to download sample schemas. The file is named "sample_schema_scripts.zip". Download it, extract files into the same directory that is "current" when you open command prompt. In my case, it is root of the M: disk:
SQL> $dir oe*.*
 Volume in drive M is file sharing 1
 Volume Serial Number is 98A9-A7AE

 Directory of M:\

13.11.2008.  12:34             9.845 oe_cre.sql
13.11.2008.  12:42             2.338 oe_main.sql
13.11.2008.  12:11             2.508 oe_p_cat.sql
13.11.2008.  12:12            67.741 oe_p_cus.sql
13.11.2008.  12:12            42.411 oe_p_itm.sql
13.11.2008.  12:12            21.860 oe_p_ord.sql
13.11.2008.  12:13            85.879 oe_p_pi.sql
               7 File(s)        232.582 bytes
               0 Dir(s)     458.272.768 bytes free

SQL>

If you open (in any text editor) OE_MAIN.SQL, you'll see that you should connect as SYS or SYSTEM and run this "main" script (which will then call other scripts and create the OE schema). OK, let's do it. When prompted, provide SYS' password.
SQL> show user
USER is "SYS"
SQL> @oe_main
Enter value for sys_password:

<snip>

1 row created.


Commit complete.

SQL>
Let's check what we've done:
SQL> show user
USER is "OE"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CATEGORIES                     TABLE
CUSTOMERS                      TABLE
INVENTORIES                    TABLE
ORDERS                         TABLE
ORDER_ITEMS                    TABLE
PRODUCT_DESCRIPTIONS           TABLE
PRODUCT_INFORMATION            TABLE
WAREHOUSES                     TABLE

8 rows selected.

SQL> select count(*) from orders;

  COUNT(*)
----------
       105

SQL>

Seems to be OK.

Now, try to do what I did.
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560890 is a reply to message #560818] Tue, 17 July 2012 12:57 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi Littlefoot:

Thank you very much for the excellent walkthrough.

I was using Application Express for two reasons -- one, I took an Oracle SQL course and that's what we used; and two, the Oracle Express Getting Started book asked me to use Application Express to create the new workspaces (hr_apex). It sounds like a "workspace" (as opposed to a user) is only necessary for Application Express.

I am glad you said not to use Application Express. It seems like I can learn more from using SQLPlus and SQLDeveloper, as the examples in the books I am using reference these tools.

But since you mentioned it, do you not like Application Express? Or do you just like SQLPlus and SQLDeveloper better? And do you have a preference between SQLPlus and SQLDeveloper? Just curious.

I'll try the walkthrough and post again, but I think it will work fine.

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560892 is a reply to message #560890] Tue, 17 July 2012 13:14 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

John:

Application Express is a great environment for developing web-based applications. I use it all the time and have developed some highly functional applications in that environment.

Apex is a mediocre environment for learning and practicing with SQL. Schools like using it for that purpose because it is very easy for them to implement. They simply need to set up an install of XE and Application Express. For any given class, the only setup required to allow students to practice is to create the student accounts and hand them a URL. Students can even access it from their home PC.

And yes -- Application Express workspaces are similar to schemas in some fashions, but are not the same thing. Workspaces are an artifact of Apex rather than the data dictionary and can access one-or-more database schemas.

SQL*Plus is old school, simple, robust, and the lowest common denominator. SQL*Developer has a lot more capabilities, a lot more quirks, and generally is preferable for daily use.
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560897 is a reply to message #560892] Tue, 17 July 2012 13:27 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Matthew:

Thanks, this explains the difference nicely. You're right, we did sign on from home to do the labs for the class.

Can the applications you build on Apex be deployed as a self-contained application? I am guessing that your user/customer would need to have Oracle XE installed on their home system, unless they're connecting to your server over the Internet.

I'm getting ahead of myself. Smile

Thanks, again. Have a great day!

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560903 is a reply to message #560897] Tue, 17 July 2012 13:39 Go to previous messageGo to next message
matthewmorris68
Messages: 258
Registered: May 2012
Location: Orlando, FL
Senior Member

Apex requires Oracle as a back-end. It is nothing more than a set of Oracle PL/SQL packages and tables. If your customers were going to have their own XE server to run an application on, they would also need a DBA in-house that knew how to administer an Oracle database. Even cut down to the XE-level, Oracle still needs someone that knows about the care and feeding of an Oracle database server.
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560913 is a reply to message #560903] Tue, 17 July 2012 14:21 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
I see.

Thanks, again, Matthew, and Littlefoot. I executed the walkthrough just fine, and I can now connect and bring up HR and OE tables in SQLDeveloper. Your assistance has been invaluable.

Have a great day!

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #560919 is a reply to message #560913] Tue, 17 July 2012 14:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Great, I'm glad you made it!

As of me & Apex: I like it very much. As Matthew, I developed a few applications (but I'm quite sure that mine are not that sophisticated, not even close - I'm still learning and that process will take time). I just think that one (you) should FIRST learn how to properly use SQL. Then, the next step is PL/SQL - you'll use both of these in Apex all the time. OK, Apex is supposed to let newbies create "applications", but if you want to adjust something, add certain advanced features - you MUST know (PL/)SQL.

As of SQL*Plus vs. SQL Developer: both tools are used to access an Oracle database. In SQL*Plus, there are a few simple commands you'll master in a matter of hours - rest of the time, you'll learn SQL. In SQL Developer, you first have to learn how to use that GUI properly, i.e. know the consequences of "clicking here" and "clicking there". Slip of a finger and your table is history. In SQL*Plus, you need to literally TYPE that command, letter by letter: DROP TABLE EMP. You won't do that accidentally - you have to KNOW what every command you type is doing.

Therefore (although some people will disagree, but I'm quite sure that some will agree) - perhaps you'd rather use SQL*Plus first. Then, when you learn SQL, you can use SQL Developer (or any other GUI) to significantly improve your job (above all - you'll be much faster).
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561086 is a reply to message #560919] Wed, 18 July 2012 14:25 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi Littlefoot:

I am actually pretty comfortable with SQL. I took the Orcale course (Database Design and SQL Programming). Now I am just reviewing so I can take the SQL (OCA exam), and then I want to take the Admin (OCA) exam, and earn my OCA. We used APEX for the course, so I am used to that. My study book recommends SQL Developer and SQL*Plus, and says the same as you and Matthew -- that SQL*Plus is a bit old school and SQL Developer has some challenges.

It looks like it's difficult to find SQL*Plus for download. SQL Developer does have the command interface, as I agree that it is better to type the actual commands than to depend on GUI, for studying and as a matter of practice. Do you know a good place to get a download?

I do want to learn PL/SQL, but I am also trying to get that first job. It seems like someone new would be better to try for a junior admin job for some experience, and then learn application skills. Either way, I am focusing on SQL and Admin 1 right now.

Thanks, again!

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561090 is a reply to message #561086] Wed, 18 July 2012 14:32 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Plus is there - it is installed along with the database. You access it from the command prompt:
C:\>sqlplus hr/hr

SQL*Plus: Release 11.2.0.2.0 Production on Sri Srp 18 21:32:11 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL>

If you didn't install the database on your own computer but are accessing one over the network, then you should download and install Oracle Client (it contains SQL*Plus).
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561096 is a reply to message #561090] Wed, 18 July 2012 15:28 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
You're right, it's right there. In fact, when I launch "Run SQL Command Line" it comes up.


SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 18 13:25:04 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL>
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561113 is a reply to message #561096] Thu, 19 July 2012 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which is expected, you have now to connect to the database using "connect" command.

Regards
Michel
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561118 is a reply to message #561113] Thu, 19 July 2012 01:02 Go to previous messageGo to next message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Hi Michel:

Yes, and before I could connect, I had to unlock the HR and Oe users (again) and reset the passwords. This is all starting to make more sense now.

Thanks (all of you) for your help!

Cheers,
John
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561119 is a reply to message #561118] Thu, 19 July 2012 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Connect as SYSTEM then unlock the account.
If you don't know SYSTEM password, connect "/ as sysdba" and change SYSTEM password, then connect as SYSTEM.

Regards
Michel
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561142 is a reply to message #561119] Thu, 19 July 2012 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel,

I believe that he has it up and running already (at least since yesterday evening).
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561144 is a reply to message #561142] Thu, 19 July 2012 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! I then don't understand where is the problem (if there is any after all). Sad

Regards
Michel
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561145 is a reply to message #561144] Thu, 19 July 2012 01:40 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's none.
Re: Oracle XE 11g & Demo Schemas (HR and OE) [message #561155 is a reply to message #561145] Thu, 19 July 2012 02:53 Go to previous message
jtschramm
Messages: 21
Registered: June 2012
Location: Tracy, CA
Junior Member
Michel:

Many thanks for your advice, and for your offer to help, but as Littlefoot said, I do have SQL*Plus and SQL Developer up and running with HR and OE schemas.

Much appreciation again, for all your help, Littlefoot.

Cheers,
John
Previous Topic: Study Guide for 1Z0-144: Oracle Database 11g: Program with PL/SQL
Next Topic: "CIW Database Design Specialist" beneficial for Oracle SQL analyst?
Goto Forum:
  


Current Time: Fri Mar 29 03:15:11 CDT 2024