Home » SQL & PL/SQL » SQL & PL/SQL » Create User/Schema
Create User/Schema [message #263271] Wed, 29 August 2007 10:08 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I just installed and created a new Oracle 9i developer database on my Windows Workstation. I am using Sys to log in as sysdba.
I see there are many example schemas that came with the database but now I want to create my own user/schema.

Please advise if these are the correct commands?

CREATE USER myuser IDENTIFIED BY mypassword 
DEFAULT TABLESPACE unknownName 
TEMPORARY TABLESPACE unknowntempNmae;

CREATE SCHEMA AUTHORIZATION myuser;


I dont know the name of my tablespaces so I assume there is a SQL statement to find the tablespaces available that I can use?

Re: Create User/Schema [message #263285 is a reply to message #263271] Wed, 29 August 2007 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am using Sys to log in as sysdba.

This is a bad idea. Use SYS only for database maintenance purpose. For user, object, data maintenance, use a DBA.

CREATE USER
CREATE SCHEMA

You second statement is incomplete.

Regards
Michel
Re: Create User/Schema [message #263330 is a reply to message #263271] Wed, 29 August 2007 13:45 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
For search existing tablespace

select tablespace_name,contents from dba_tablespaces
Re: Create User/Schema [message #263439 is a reply to message #263285] Thu, 30 August 2007 01:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As long as you use your database for testing & development, and you are not interested in the actual data (in other words, if it crashes, you simply create a new database), there is nothing wrong with using SYS as dba-user, unless you want to practice dba-things.
If you want to use the database for developer's things, you will hardly ever need a DBA user anyway.
Re: Create User/Schema [message #263448 is a reply to message #263439] Thu, 30 August 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Thu, 30 August 2007 08:24
As long as you use your database for testing & development [...] there is nothing wrong with using SYS as dba-user, unless you want to practice dba-things.
If you want to use the database for developer's things, you will hardly ever need a DBA user anyway.

But does not test or development environment reflect the real environment as long as this does not interfere with the purpose of the environment?
I saw and still see so many development and testing made with the schema owner and then when it comes into production with real non privileged users it does not work. And then the solution is give full privilege to users.
How many softwares require *ANY* privileges just because they have only been tested with owner? Too much!

The case of SYS is even worse.
When you use SYS you don't execute the same code path.
When you use SYS some code path are disable and others enable.

When I was young I learned iaido (japanese sword art), my sensei (teacher) always says: "don't take your training sword by the blade even if it is safe" (training sword are not sharpened) "because the day you will use a real one, you will do the same things and lose your fingers". (sorry if there are incorrect english words)

Regards
Michel
Re: Create User/Schema [message #263450 is a reply to message #263448] Thu, 30 August 2007 02:05 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe I didn't word my post correctly, as you seem to have missed my biggest point.
I did NOT say to use SYS as the development user, I said it is ok to use SYS as the DBA user.
If you have a local database to test (DEVELOPERS) stuff, using a developers user (not SYS) and you are not interested in doing DBA-stuff, why on earth would you go through the trouble of creating a dba-user?
You would not advise him to create a 8-tape backup schema for such a database either, would you?
Re: Create User/Schema [message #263462 is a reply to message #263271] Thu, 30 August 2007 02:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

@florida you also have to grant create session and give quota on default tablespace.
Re: Create User/Schema [message #263467 is a reply to message #263450] Thu, 30 August 2007 02:26 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Maybe I didn't word my post correctly, as you seem to have missed my biggest point.
I did NOT say to use SYS as the development user, I said it is ok to use SYS as the DBA user.

Sorry this is me. I didn't either mean using SYS as developer user, I meant even as a DBA because it is not the same code you execute and maybe you will get some things in this environment you will not get in production simply because you did something with SYS in your application schema.
Quote:
why on earth would you go through the trouble of creating a dba-user?

create user mydba identified by mypsw;
grant dba to mydba;

Bother? 2 lines. You can even create it as OPS$ user if you don't want to bother with password.

Regards
Michel
Previous Topic: Retrieve Multiple result
Next Topic: Direct Path Loading
Goto Forum:
  


Current Time: Sun Dec 11 00:40:15 CST 2016

Total time taken to generate the page: 0.26906 seconds