Home » RDBMS Server » Enterprise Manager » Create first local database
Create first local database [message #246774] Thu, 21 June 2007 20:44 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I have Oracle 9i installed on my local Windows XP workstation that will be used for local development only.
Now I am trying to create a database and using OEM Database Configuration Assistant.
I put in FirstDB for the Global Database Name and put in FirstDB for the SID.

I completed the wizard and set up my tnsnames.ora file with the SID and hostname. Then I tried to connect with the Sys login using OEM and it says:
ORA-12545:Connect failed because target host or object does not exist.

Should I put in localhost or 127.0.0.1 for my Global Database Name?

I assume I connect as Sys or System and the password I created after right after creating the database.

Please advise how I can get this to work?

[Updated on: Thu, 21 June 2007 20:45]

Report message to a moderator

Re: Create first local database [message #246949 is a reply to message #246774] Fri, 22 June 2007 10:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
I completed the wizard and set up my tnsnames.ora file with the SID and hostname. Then I tried to connect with the Sys login using OEM and it says:
ORA-12545:Connect failed because target host or object does not exist.


Your LISTENER is running??


Quote:

Should I put in localhost or 127.0.0.1 for my Global Database Name?



Where you want to use this in OEM?

Quote:
I assume I connect as Sys or System and the password I created after right after creating the database.


could you connect through SQLPLUS?
Re: Create first local database [message #246962 is a reply to message #246949] Fri, 22 June 2007 11:15 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks,

How do I find out if LISTENER is running on my local Windows XP?


I would like to get access to the database by SQL Plus and OEM but cant connect in both of them.
Re: Create first local database [message #246964 is a reply to message #246962] Fri, 22 June 2007 11:18 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Goto commands prompt

And run this command


LSNRCTL STATUS and post the output of this command.
Re: Create first local database [message #247023 is a reply to message #246964] Fri, 22 June 2007 21:27 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks, I started listener and can now connect to local database.
I have to log in as SYS. I assume the next steps are:
1-Logged in as SYS create a username called jones
2-Create Schema called firstSchema
3-During creation of firstSchema give jones full privileges to all the objects of the firstSchema such as creating and manipulating tables, query builds, triggers, sequences, view/edit records.

Please advise.
Re: Create first local database [message #247024 is a reply to message #246774] Fri, 22 June 2007 21:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I wonder why you are bothering with "9i" which goes desupported next month.

My very serious advice is that you take some time and Read The Fine Concepts Manual found below.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/toc.htm

From my perspective there is no real difference between a schema & a user in Oracle.

You'll make your task many times more difficult that it needs to by attempting any like the following:
"During creation of firstSchema give jones full privileges to all the objects of the firstSchema such as creating and manipulating tables, query builds, triggers, sequences, view/edit records."

Part of your problem is that you don't know, what you don't know.
Re: Create first local database [message #247032 is a reply to message #247024] Sat, 23 June 2007 00:39 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
1-Logged in as SYS create a username called jones



create user          JONES
identified by         JONES
default   tablespace ts_users
temporary tablespace ts_temp;


Quote:
2-Create Schema called firstSchema


WOWW thats new to me..please tell me how to do that??

by the way when you create user jones its mean Jones schema.

SCHEMA definition

    * A schema is the set of objects (tables, views, indexes, etc) belonging to an account. 
It is often used as another way to refer to an Oracle account. The CREATE SCHEMA statement lets one specify 
(in a single SQL statement) all data and privilege
 definitions for a new schema. One can also
 add definitions to the schema later using DDL statements.



3-During creation of firstSchema give
 jones full privileges to all the objects 
of the firstSchema such as creating and manipulating tables, query builds, triggers, sequences, view/edit records.





grant user create session to JONES;
grant create table to JONES;


and


whatever you want...

[Updated on: Sat, 23 June 2007 00:41]

Report message to a moderator

Re: Create first local database [message #247424 is a reply to message #247032] Mon, 25 June 2007 19:21 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks for all info.

If I understand this correctly I can have a Jones Schema and if I want to create a new user with the name Smith that needs create table privilege to Jones Schema I would do these SQL statements?

create user          SMITH
identified by         SMITH
default   tablespace ts_users
temporary tablespace ts_temp;
grant create table to JONES;
Re: Create first local database [message #247433 is a reply to message #246774] Mon, 25 June 2007 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>a new user with the name Smith that needs create table privilege to Jones Schema
>grant create table to JONES;
Nope.
Please note that within the GRANT there is NO mention of SMITH.
Why couldn't it apply to any other schema in the database?

FWIW - grant create table to JONES;
Above does EXACTLY what it says.
It allows JONES to create in his own schema (ONLY).

It is VERY poor technique to design an application where USER_A creates objects in another schema.

Why are you obessed with trying to accomplish this?

If a table needs to be created in the JONES schema why can't JONES do it himself?

What is to be gained by having JONES - CREATE TABLE SMITH.FUBAR?
Re: Create first local database [message #247627 is a reply to message #247433] Tue, 26 June 2007 10:44 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
if I want to create a new user with the name Smith that needs create table privilege to Jones Schema I would do these SQL statements?


In this Smith will be able to create table in his own schema not in Jones schema. For that you should grant create any table system privilege but ANNA told you thats Poor design.
Re: Create first local database [message #247688 is a reply to message #247627] Tue, 26 June 2007 21:07 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks, I understand what you are saying about Smith and Jones being seperate schemas.

What if I had a Projects Schema and I wanted several people in my group to have access to the Projects Schema where they can create tables and edit values. I think this is where I am confused because each user would then have to have their own username/password to get into the Projects schema or would we just have one Projects schema login and password?
Re: Create first local database [message #247689 is a reply to message #246774] Tue, 26 June 2007 21:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Typically (more often than not) an application is contained within a single schema.
Typically end/application users do NOT, do NOT, do NOT have their own schema.
The application "owns" all the objects & all code (PL/SQL packages).
The application manages ALL access to its own objects.
The application presents a "logon" screen to the end user.
The end user enters a valid application username & password that has NOTHING to do with Oracle security.
The end user can only do what the application s/w allows him to do.
The end user NEVER get close to issuing actual SQL statements.
EVERYTHING that occurs within the application is done by the application schema/user/owner.
So there is NEVER a need to create any object in another schema; primarily because there is only 1 schema actually logged into the DB.

UNDERSTAND?

[Updated on: Tue, 26 June 2007 21:26] by Moderator

Report message to a moderator

Re: Create first local database [message #247693 is a reply to message #247689] Tue, 26 June 2007 21:49 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks for quick reply and all your info and time!!
I understand about the end/application users editing data and their logins which is where we will be using JSP and Servlets for the front end.

In my case I am talking about the several people that will be using Oracle OEM to build and create tables in the Project schema and that is why I am asking about multiple people having privileges to the Project schema. Will these multiple people in my group that are using OEM to build the tables use the same Project schema login and password?
Re: Create first local database [message #247694 is a reply to message #246774] Tue, 26 June 2007 21:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
All login to oracle as the schema PROJECT.
However OEM is a suboptimal tool to develop an application.

[Updated on: Tue, 26 June 2007 21:54] by Moderator

Report message to a moderator

Re: Create first local database [message #247696 is a reply to message #247694] Tue, 26 June 2007 22:01 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
Thanks for the great amount of info you all provided.

"However OEM is a suboptimal tool to develop an application."
I am using a seperate Web tool to build the JSP and Servlets Front end and using OEM for the database.
Re: Create first local database [message #250024 is a reply to message #246774] Sat, 07 July 2007 01:32 Go to previous messageGo to next message
malikjee
Messages: 80
Registered: May 2007
Location: lahore
Member

hi there !

there is a mistake made by u in any process;
so plz try againg after dellete the database and creeate it agaian via dbca. it will provide u more deatilinof abt instalation process in formof logs file whitch will giude u though proceed more.foloow itor if need u will have to run bin direcert and run emca for EM database.
mfayyazawan@yahoo.com is my address to helpmore
Re: Create first local database [message #250036 is a reply to message #250024] Sat, 07 July 2007 03:27 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't write IM speak, write plain english
2/ Is this the only solution you can give: destroy the database??? Well, I hope no one will ask you to help him!

Regards
Michel

Previous Topic: OEM kills laptop
Next Topic: AWR and ADDR
Goto Forum:
  


Current Time: Sat Apr 20 05:03:35 CDT 2024