Solved

How to create an instance / schema / users on Oracle on Windows 7; plus sample database and code

Posted on 2014-07-29
17
598 Views
Last Modified: 2014-07-30
I recently successfully installed Oracle (OraDb11g_home1) on Windows 7, and I was able to install Oracle Sql Developer (3.2....). I can log on to Oracle via Sql Developer, but all I have is connection "Orcl", user name = "sys as sysdba", via TNS, and this is all system tables

- Where do I find tnsnames on Windows 7 ? I can't seem to find it.

- How do I create a create a new instance / schema, separate from the system tables ?

- Can I get a sample d.b. somewhere, like Sql Server Adventure Works ? I don't want to create some dopey tables one by one if I can get some sample d.b., and would also like some sample PL*Sql Developer code. I can write PL Sql code but never have done anything with Java and oracle.
0
Comment
Question by:Alaska Cowboy
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 125 total points
Comment Utility
tnsnames.ora file will be in a subdirectory of the ORACLE_HOME where the software was installed.  It should be in the network/admin directory.  As a fresh install, there probably isn't one.  There should be a sample in the network/admin/samples directory.  Being new to Oracle, I would highly recommend using the netca utility to create the file for you.

You would create a new user/schema with the CREATE USER (or CREATE SCHEMA) command.

Sample schema is installed separately.  Documentation here -> http://docs.oracle.com/cd/E11882_01/server.112/e10831/toc.htm
0
 
LVL 12

Assisted Solution

by:praveencpk
praveencpk earned 125 total points
Comment Utility
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
johnsone,

- I found tnsnames under "E:\app\Stephen\product\11.2.0\dbhome_1\network\admin\tnsnames.ora". I thought home was "E:\Program Files (x86)\Oracle\database"

- I looked at the netca utility, will come back to that later, but good tip

- for creating a user / schema, I'm confused about the "database" / connection. I want it to be separate from the "orcl" database, which is all system tables. I'll research this more.

- good on sample schema, thanks

praveencpk

- great on the sample code, thanks ! Do you know if the associated schemas are part of the code samples ?

=========
so you've answered my questions
- I found tnsnames
- I have a starting point for creating a new instance / schema
- I got sample code.

So I will close this out, do more research and play around, and post other questions as needed.

Thanks !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I want it to be separate from the "orcl" database, which is all system tables

You cannot compare Oracle and SQL Server terms.

In Oracle it goes like this:
Database: Files on disk.
Instance: Memory structures and server processes.
Schema: Basically a database user that owns objects.

The default database and instance that gets created is ORCL.  Yes, it contains the 'system' tables but ALL databases contain them.

If you create a new database, it will have a SYS and SYSTEM user as well.

What you are after is creating a new schema not a new instance/database.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Based on the paths that you posted, I would say that the ORACLE_HOME is E:\app\Stephen\product\11.2.0\dbhome_1

You database files are in E:\Program Files (x86)\Oracle\database

ORACLE_HOME is the directory where all the database software is installed.  Not sure on windows, but I would hope that the variable is set.  You should be able to find it by looking at the environment.  As I said, it isn't necessarily created by default and if you are not extremely familiar with the file, I wouldn't edit it.  They built the utility for a reason.  Too many people mess up that file.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
Slightwv,

thanks for the expln, that clears up my confusion. I guess I would remove all the system tables via filters. I'll work on the sample schemas and post other questions, appreciate the follow-up.

btw, what main tools do you use ?
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Normal users would be able to see the SYS and SYSTEM objects.  You would need privileges to see those.  Once you create the sample schema/user and log in as that user you shouldn't be able to see the SYS and SYSTEM objects, other than the views that all users should have access to (they typically begin with USER_ and ALL_).
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I guess I would remove all the system tables via filters

You don't need to do this.

If you create a schema, there is no need to grant a privilege like DBA to it so the user cannot see them.

Most products go against the USER level views (like USER_TABLES) to see objects.  Some use ALL level views (ie ALL_TABLES).

The difference:
USER_ are the objects the schema you are logged in as actually owns.  The ALL_ level is all the objects the user has been granted permission to see.

>> btw, what main tools do you use ?

I'm a sqlplus command line guy.

I use a vi emulator for Windows (similar to GVim) for all my script editing.  Yes, I spent way to many years in the Unix world before moving over to Windows.  My vi skills have been permanently burned into memory.

GUI's have their place at times but I tend to find them to be more problems than they are worth.

I can probably create a few tables and indexes before SQL Developer even launches...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

Expert Comment

by:johnsone
Comment Utility
As far as tools go, I am pretty old school and use SQL*Plus for the most part.  For me, GUI interfaces take too long to load.  Command line is a lot faster and easier.

SQL Developer is probably the tool most people use.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
johnsone
>>Normal users would [NOT] be able to see the SYS and SYSTEM objects.  You would need privileges to see those.
-- ok, makes sense, thanks.

>>I am pretty old school and use SQL*Plus for the most part.
-- ahhhhhhhhh . . . I couldn't do that, although I like Unix/vi

slightwv,
>>You don't need to [remove all the system tables via filters], etc.
-- makes sense, thanks.

>>I'm a sqlplus command line guy.
- ahhhhhhhhhhhhhhh, sorry, that's like using a spoon to dig a ditch ! TOAD is way overkill, Sql Developer is "eh, ok", but PL Sql Developer is awesome. I showed a accountant how to use it in 30 minutes (he new the tables), and he was  swimming soon thereafter !

>>I use a vi emulator for Windows (similar to GVim) for all my script editing.
-- I too am a big fan of vi, I was on Unix the past 8 years . . . those guys were pretty smart and slick

>>I can probably create a few tables and indexes before SQL Developer even launches...
-- ok, I can understand that, but PL Sql Developer is super-awesome, and it doesn't insult you like TOAD does . . . PL Sql Developer is your friend . . .


============
johnson and slightwv
- I'm going out on a limb and will bet you a steak dinner (at Denny's) that if you try PL Sql Developer you will start to use it and see how efficient / effective it is. C'mon, give it a 30-day free trial ! it doesn't cost an arm and a leg like TOAD, and it's much more sophisticated than Sql Developer. It's elegant while TOAD is on steroids and Sql Developer is a tad clunky. Try it, you'll like it !!!!! LOL
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I showed a accountant how to use it in 30 minutes

Sort of proves my point...  Accountants shouldn't be in the database directly manipulating anything...

The GUI tends to mask the commands to where the "DBA/Developer" has no actual idea what is actually begin done.

There will also be times when you will be without a GUI and need to actually type a command.  Will you actually know the command you need to type?

>> bet you a steak dinner (at Denny's)

lol...

Sorry, but I'll lose that bet on purpose...   ;)

I might get around to trying it someday.  At times an interactive pl/sql debugger would be nice!!!
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Users with a GUI are the most dangerous people out there.  They will absolutely the cause of all your performance problems.

Using a command line tool, I can get at least twice as much work done.  GUIs hide the commands that you really need to know.  If you really want to learn, then use the command line.  Once you can do everything with a command line, then use the GUI.  I have had more times than I can count where command line access was the only way to get to the system.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
slightwv,

>>At times an interactive pl/sql debugger would be nice!!!
-- bingo ! that alone is worth to have Pl Sql Developer, even if it's on the side . . . .

>>Accountants shouldn't be in the database directly manipulating anything...
-- he was just writing queries with read only access. Export to Excel, etc. etc.

>>The GUI tends to mask the commands to where the "DBA/Developer" has no actual idea what is actually begin done.
-- I get your point but same with Erwin, etc.

johnsone
>> Users with a GUI are the most dangerous people out there.
-- good point, but I'm not recommending users, the accountant was a data jockey anyway he just needed a good tool. It would have been crazy to teach him TOAD (too many bells and whistles) or Sql Developer (too clunky). Pl Sql Developer is smooth as silk

>> Using a command line tool, I can get at least twice as much work done.
-- Hmmm, obviously that's your take, but you spend most of your time typing. Let the computer do the work, e.g., intellisense to pick the column names. Do you really have to type every column name by hand ? just wondering.

Thanks !
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Most of us old-dogs save everything to scripts (and have extensive libraries of them).

It's pretty rare to do one-off ad-hoc queries.  When I do, I don't find typing that difficult...

Once we get our bag-o-trick scripts set up it is pretty simple:
sqlplus
username/password
@script_name

*review results*
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
slightwv,

- ok, got 'cha. I'm sure I'll be back with more questions, thanks !
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
We definitely have a large bag of scripts in our arsenals.  Most of the time, I'm not writing a query.  But, honestly, I type over 100 words a minute.  I can type a lot faster than you could ever click something.  If you know your database and know the commands, there is no way you can do it faster in a GUI.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
Comment Utility
johnsone,

ok, good perspective. But with many large databases, I could never know all the columns, so the intellisense is a must have for me.

To each his own !
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 teach you how to the overview of Microsoft Security Essentials. This is a free anti-virus software that guards your PC against viruses, spyware, worms, and other malicious software. This will be demonstrated using Windows…

771 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

11 Experts available now in Live!

Get 1:1 Help Now