?
Solved

new schema in oracle9i

Posted on 2007-03-21
7
Medium Priority
?
2,801 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 48

Accepted Solution

by:
schwertner earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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