Home » SQL & PL/SQL » SQL & PL/SQL » CREATING A NEW SCHEMA?
CREATING A NEW SCHEMA? [message #18495] Fri, 01 February 2002 11:34 Go to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
How do i create a new schema..
I have the script to create all the tables and indexes etc..(ERWIN generated).

So how do i create a different schema..

Can I port data across 2 schemas..
Example:
IF TABLE1 IS IN SCHEMA1 and
TABLE2 IS IN SCHEMA2..

CAN I FETCH RECORDS FROM SCHEMA1.TABLE1 AND INSERT IT INTO SCHEMA2.TABLE2?

IF THE ANSWER IS YES, WHAT IS THE BEST WAY OF DOING IT?? USING A PROCEDURE AND A CURSOR OR HOW?
Re: CREATING A NEW SCHEMA? [message #18497 is a reply to message #18495] Fri, 01 February 2002 11:45 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
yes, you can insert data into table from other schema

1) connect to schema1

2) grant select on table1 to schema2

3) connect to schema2

insert into table2 (select * from schema1.table1)

Note: use create user command to create schema
and grant privileges .
CAN I USE THE SCHEMA MANAGER - OEM TO CREATE A NEW SCHEMA? [message #18499 is a reply to message #18495] Fri, 01 February 2002 12:24 Go to previous messageGo to next message
CATHYBEE
Messages: 20
Registered: January 2002
Junior Member
Can i use the schema manager to create a new schema under the same username?

If not, than can you send me the exact syntax to create a new schema and grant privileges?

thanx
Re: CAN I USE THE SCHEMA MANAGER - OEM TO CREATE A NEW SCHEMA? [message #18500 is a reply to message #18499] Fri, 01 February 2002 17:52 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
if u have already user, grant privileges to that user. you dont need to separately create schema (there is no such command in oracle).

SQL> grant resource to user_name_here;

resource is predefined role having privileges required for developers. if it dont suits ur requirement, create your own role with privileges and assign it to user.

For ex:

to create table in schema

you need these create table privilege and space quota in users default tablespace.

SQL> grant create table to rob;

SQL> alter user rob quota on tbs 10M;

first command gives create table privilege to user rob.

second command gives user rob quota of 10MB in tbs tablespace.

HTH
Suresh
Previous Topic: Average a Date?
Next Topic: How i do a sequence?
Goto Forum:
  


Current Time: Sun Mar 29 23:42:54 CDT 2020