Solved

How to install sample Oracle schemas (and tablespace) on Windows 7 ?

Posted on 2014-07-30
19
2,201 Views
Last Modified: 2014-08-01
I'm trying to understand what I have in my Windows installation vs. this "Sample Schemas" link, plus I want to create my own custom schema and not sure about tablespace setup.

Here's what is in my "demo\schema" folder:
demo\schema folder
The documentation refers to a master script (mksample.sql) which I don't seem to have.

Plus the documentation refers to the following schemas, which are slightly different than what's in my "demo\schema" folder (I don't have PM or IX schema). And I have the Business_Intelligence schema that's not in the documentation.
sample schemas per documentation
Plus, I don't have "hr_main.sql" as referenced in the documentation. I only have one HR file, "hr_code.sql", attached. So where are the HR tables ? data ?

So I need a little help in understanding this. Also, if I got the schema's set up, where's the data ? I don't see where is the data.

---------
In regard to setting up a custom schema, I see this command:
CREATE USER smithj
  IDENTIFIED BY pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M on tbs_perm_01;

but I assume I first have to set up the tablespace(s) ? I've never done this . . .
hr-code.sql
0
Comment
Question by:Alaska Cowboy
[X]
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
  • 9
  • 8
  • 2
19 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230063
Look in the folders themselves.

For example: HR_MAIN shouldbe under human_resources
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 150 total points
ID: 40230084
You might need to download them separately depending on what database product you downloaded and installed.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

Oracle Database 11g Release 2 Examples
        Download       win32_11gR2_examples.zip (565,154,740 bytes)

Contains examples of how to use the Oracle Database. Download if you are new to Oracle and want to try some of the examples presented in the Documentation
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 150 total points
ID: 40230090
forgot this:

>>but I assume I first have to set up the tablespace(s) ? I've never done this . . .

No.  You get what you need out of the box when a database is created.  You only need to create new ones if you want specific objects in specific tablesapces.

for example: separating tables from indexes, large tables from small tables, etc...
0
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230126
>>Look in the folders themselves. For example: HR_MAIN should be under human_resources
-- that's what I thought, but I only have one file, "hr_code.sql", in the human_resources folder

>>You might need to download them separately
-- ok, I'll download and review

>> You get [the tablespaces that] you need out of the box when a database is created.  You only need to create new ones if you want specific objects in specific tablespaces.
-- So then this is all I need to do: "CREATE USER smithj IDENTIFIED BY pwd4smithj" ?
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 40230236
>>So then this is all I need to do: "CREATE USER smithj IDENTIFIED BY pwd4smithj" ?

Yes, if what you want to do is create a customer user account named "smithj".  The "create user..." command in Oracle (like the "create table..." command and many other SQL commands) would allow you to specify additional options for this user.  But, if you don't specify these other options, Oracle will simply use default values for them.

This user account can become a schema, if you create one or more tables or other objects as this user, or for this user.

Be aware that one of the advantages of Oracle is that it is very tunable and configurable.  This allows Oracle databases to perform very efficiently on a huge range of: servers, storage systems, applications, number of users, etc.  A disavantage of Oracle is that unless you spend time configuring and tuning the database for your server and storage hardware, type of application, number of users, number of records, etc., the performance you get from Oracle may be disappointing, as you start to add data, especially if you depend on mostly default values.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40230241
Oops!  I intended that first sentence to read: "Yes, if what you want to do is create a custom [not customer] user account named ..."
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230340
markgeer,

ok, sounds good, thanks for the insight. At my day job I do development and the DBA keeps everything straight :-) But now I'll expand my knowledge base with my home Windows installation.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230493
>>-- So then this is all I need to do: "CREATE USER smithj IDENTIFIED BY pwd4smithj" ?

Not for the sample schemas.  They create their own specific schemas.

You only need to create a new one if you want to create your own objects.

After you create the user you need to grant permission for them to do things.

For users that need to create objects this is typically the resource role so for smithj:
grant create session, resource to smithj;
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230496
slightwv,

>>You might need to download them separately --> Oracle Database 11g Release 2 Examples, Download       win32_11gR2_examples.zip (565,154,740 bytes)
-- I downloaded this but it still says "Before you can use the Oracle Database Examples, you must install the Sample Schemas in an Oracle Database", and then it refers me back to the original link that you posted in an earlier post, and from which I got my 2nd screen shot above
-- So I'm still stuck with not having the proper script to create the sample schemas. for example, I don't see mksample.sql as the main script, nor do I see any of these HR scripts anywhere:

HR schema from documentation
So I'm kind of back to square one - I need the scripts for the sample d.b.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230502
The file hr_main.sql wasn't contained in that zip file anywhere?  For 500 Meg, it had to contain something...
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230538
hr_main.sql nowhere to be found . . . :-(

not in my installed "demo\schema" folder nor in the zip file you posted . . .
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230561
This seems to be common, here. None of them had a resolution without a companion CD.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40230627
I don't know what to tell you other than to rerun the installer and see if the samples are in the list of things to install.

I have an out-of-the-box Enterprise Edition install and all the sample schema installs are there.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230750
slightwv, ok, I'll look at re-running the installer. I also found this link that seems to have all the files, but I can't figure out how to open / download them.

Will have to pick this up tomorrow afternoon, thanks.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 150 total points
ID: 40230767
I would not download and install anything for Oracle that did not come from Oracle.  You have no idea of their validity.  They may exploit vulnerabilities and compromise your system.

I re-ran my installer.  Take a look at the pic below.  This is what I have installed.

maybe this?
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40230824
Ok, thanks for posting. But I think that's the "examples" and not the actual schemas.

I did find this link and it looks like it's what I need . . . all the hr files as well as the mksample.sql . . .

it has BI, HR, IX, OE, PM, and Shipping. The documentation has
               HR, IX, OE, PM, SH,

I'll review in more depth before running, won't get back to this until tomorrow afternoon.

Thanks !
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40232865
>>But I think that's the "examples" and not the actual schemas.

My guess right now is that installs the scripts to allow you to create the sample schemas.

>>I did find this link and it looks like it's what I need

I again warn you against dowloading things from generic sites on the web.  They might contain 'bad' things.  You will run them at your own risk!!!
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 40234310
>>I again warn you against dowloading things from generic sites on the web.
-- yes, proceeding with known risk !

I am working through the scripts now, I have a number of questions on them, but I'll post the questions separately. I set up a new account, so I'll be at "Gadsden Consulting".
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40234325
>> I set up a new account, so I'll be at "Gadsden Consulting".

Did you work with Experts-Exchange on this?  They frown on duplicate accounts.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DNS problems and now some PC can't connect to \\servername 14 67
Moving Oracle Database to other server 2 45
Automate an Oracle update in Excel 7 61
construct a query sql 11 35
By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.

734 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