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

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.
LVL 1
Alaska CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
0
Alaska CowboyAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
>>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
johnsoneSenior Oracle DBACommented:
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
Alaska CowboyAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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
slightwv (䄆 Netminder) Commented:
>>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
johnsoneSenior Oracle DBACommented:
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
Alaska CowboyAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
johnsoneSenior Oracle DBACommented:
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
Alaska CowboyAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Alaska CowboyAuthor Commented:
slightwv,

- ok, got 'cha. I'm sure I'll be back with more questions, thanks !
0
johnsoneSenior Oracle DBACommented:
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
Alaska CowboyAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.