Solved

new schema in oracle9i

Posted on 2007-03-21
7
2,786 Views
Last Modified: 2013-12-19
hi experts
I'm new in oracle 9i i would like to create a new schema to be viewed as "sys schema" under the database
How could i do  taht ?
thanks for your help
0
Comment
Question by:A_najem
7 Comments
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18768736
You cannot create a "sys" schema. sys account are special oracle accounts that are automatically created when the database is created. It's something like a windows administrator account except that this is for the oracle database.
0
 

Author Comment

by:A_najem
ID: 18769130

Hi

I want to see a new schema to create tables and views within  as same as any schema created with setting up oracle9i.
when I open the database many schema viewed. like system, sys, public, scott.....
i can not make a new schema be viewed in the same place?



0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18769142
A schema is like a user space. You cannot create a schema within another schema. To create a new schema is simply to create a new user.

Create user test1 identified by test1;

To allow the user to be able to login, you can grant the minimum rights of

Grant create session to test1;

Nickson
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:A_najem
ID: 18769244
users are created under the security + users.
any suggest for a tutorial?

0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18769291
You can do it from the SQL Plus (an SQL command line tool) or a GUI tool like the Enterprise Console which is like u said from security > users.

Sorry, I do not of any tutorial link. Learning oracle is quite a huge subject. If you looking to just add the user, try reading the online help.

Nickson

0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 18769399
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 18770406
Start SQL*Plus and connect as user SYS:

SQL>connect sys/manager@some_instance as sysdba

First create tablespaces for the user:

CREATE TABLESPACE "NHIFD" DATAFILE '/u02/oradata/o10d/nhifd00.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M LOGGING
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 

CREATE TABLESPACE "NHIFI" DATAFILE '/u02/oradata/o10d/nhifi00.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 32767M LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TEMPORARY TABLESPACE "NHIFTEMP" TEMPFILE '/u02/oradata/o10d/nhiftemp01.dbf'
SIZE 97M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 32767M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

after that create the user:

DROP USER nhif CASCADE;

CREATE USER nhif IDENTIFIED BY nhif
DEFAULT TABLESPACE NHIFD  TEMPORARY TABLESPACE NHIFTEMP;
ALTER USER nhif QUOTA UNLIMITED ON NHIFD;
ALTER USER nhif QUOTA UNLIMITED ON NHIFI;
ALTER USER nhif QUOTA 0 ON USERS;

Grant some privileges:

GRANT create procedure, create session,create table,create type,create view,create synonym TO nhif;
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error when running a simple alter statement in Aqua studio for Oracle 6 43
dates - loop 12 57
Best RAID for a BDD Oracle 4 61
PL/SQL Display based on value 4 17
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

914 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

18 Experts available now in Live!

Get 1:1 Help Now