Solved

new schema in oracle9i

Posted on 2007-03-21
7
2,791 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

733 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