Home » RDBMS Server » Server Utilities » Loading data to another schema (Oracle 9i.0.2, Windows. )
Loading data to another schema [message #474338] Sun, 05 September 2010 15:03 Go to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi,

please any one tell me, how to load data to another schema's table through Sql*loader.

I tried the below things in the control file.

Load data
"
Into table scott.emp

Thanks in advance
Re: Loading data to another schema [message #474339 is a reply to message #474338] Sun, 05 September 2010 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>please any one tell me, how to load data to another schema's table through Sql*loader.
You do NOT.
SQL*LOader is wrong tool for this task.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Loading data to another schema [message #474342 is a reply to message #474339] Sun, 05 September 2010 15:23 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Thanks Blackswan,

1) Do u mean, we can't or shouldn't?

2) Then what is the alternative way for DBA to load bulk amount of data to another schema?
Is it to load data with the individual user's
Username and password?

Thanks in advance.
Re: Loading data to another schema [message #474344 is a reply to message #474342] Sun, 05 September 2010 15:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can't, unless you extract the data to a file first. Which would be an epic waste of time.
2) Straight insert/select statement. As long as you have select permission on the source and insert on the target it'll work fine. You certainly don't need any passwords.
Re: Loading data to another schema [message #474346 is a reply to message #474344] Sun, 05 September 2010 15:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use external table.

Regards
Michel
Re: Loading data to another schema [message #474347 is a reply to message #474346] Sun, 05 September 2010 15:45 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hello,

Thanks to all...
Re: Loading data to another schema [message #474349 is a reply to message #474347] Sun, 05 September 2010 15:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Let's clarify - are you trying to load data from a file into a schema other than the one you're logged in as?

If so use external table as Michel said.
Re: Loading data to another schema [message #474350 is a reply to message #474349] Sun, 05 September 2010 15:52 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Cookimonster,

1) Yes I've a external file of CSV format, wanted to load to another schema.

2)Hope External file method supports all type of the files?

Thanks in advance
Re: Loading data to another schema [message #474354 is a reply to message #474350] Sun, 05 September 2010 16:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Yes, you can load data from a csv file into another user's schema using SQL*Loader, as long as you have necessary privileges. In the demo below, user scott loads data from a csv file into a dept table in the test schema. You could just as well use an external table and insert from there. If using SQL*Loader, the csv file can be on your client, but if using an external table, the csv file must be on the server. If you are getting errors, it may be because you do not have the necessary privileges to insert into the table in the other schema or due to some other syntax error. Your other post indicated that you do not understand what a csv file is and what you are trying to load is not a csv file. A text file can be given any three-character extension, like csv or dat. Just because somebody has labeled it csv does not mean that it is a csv file.

-- test.csv:
10,ACCOUNTING,NEW YORK,
20,RESEARCH,DALLAS,
30,SALES,CHICAGO,
40,OPERATIONS,BOSTON,


-- test.ctl
load data
infile test.csv
into table test.dept
fields terminated by ','
(deptno, dname, loc)


SCOTT@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> create user test identified by test
  2  /

User created.

SCOTT@orcl_11gR2> grant connect, resource to test
  2  /

Grant succeeded.

SCOTT@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> create table dept
  2    (deptno number,
  3  	dname  varchar2(15),
  4  	loc    varchar2(15))
  5  /

Table created.

TEST@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> select * from dept
  2  /

    DEPTNO DNAME           LOC
---------- --------------- ---------------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH        DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

4 rows selected.

TEST@orcl_11gR2> connect scott/tiger
Connected.
SCOTT@orcl_11gR2> drop user test cascade
  2  /

User dropped.

SCOTT@orcl_11gR2>

Re: Loading data to another schema [message #474357 is a reply to message #474354] Sun, 05 September 2010 16:34 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Thanks Barbara...
Re: Loading data to another schema [message #474430 is a reply to message #474357] Mon, 06 September 2010 05:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barbara,

if I'm not wrong, your user SCOTT has certain privileges that "ordinary" users don't have. It is, for example, capable of creating another user. It also is allowed to work with other users' tables.

My last sentence points to a fact that you didn't explicitly grant privileges from user TEST to user SCOTT so that SCOTT would be able to insert records into TEST's table. In other words, regarding your introductory paragraph (about Muktha_22 being not that experienced user), perhaps your example should have contained something like
connect test/test

GRANT ALL ON dept TO scott;      --> this line

host sqlldr scott/tiger control=test.ctl log=test.log
so that it would be somewhat more obvious.

Though, GRANT ALL is probably too generous, but I hope you understood what I meant.
Re: Loading data to another schema [message #474474 is a reply to message #474430] Mon, 06 September 2010 11:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I said,

"... you can load data from a csv file into another user's schema using SQL*Loader, as long as you have necessary privileges. ... If you are getting errors, it may be because you do not have the necessary privileges to insert into the table in the other schema ..."

My user scott has DBA privileges.


[Updated on: Mon, 06 September 2010 11:35]

Report message to a moderator

Re: Loading data to another schema [message #474498 is a reply to message #474474] Mon, 06 September 2010 14:52 Go to previous messageGo to next message
muktha_22
Messages: 527
Registered: December 2009
Senior Member
Hi Barbara,

1) To insert data to another schema, means that schema owner must have been created by the person, who is running sql*loader?

2) I created the user TEST with SYS Login.
And given the privilege GRANT ALL ON TEST.TABLE1 TO SCOTT;

3) Now I logged in with SCOTT user and trying to run Sql loader to the TEST schema. Which shows error.

please point out my mistakes.
Re: Loading data to another schema [message #474502 is a reply to message #474498] Mon, 06 September 2010 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>please point out my mistakes.

Your mistake is NOT following Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Loading data to another schema [message #474508 is a reply to message #474498] Mon, 06 September 2010 15:15 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What you have described works, as shown below. I used a user scott2 instead of scott, so that it would not be confused with the scott user that has additional privileges. You say it shows an error. What error does it show? It probably has nothing to do with the schemas. If you are still using the wrong delimiter and have the lines in the wrong order in your control file, as in your other posts, then that would cause an error.

SYS@orcl_11gR2> create user test identified by test
  2  /

User created.

SYS@orcl_11gR2> grant connect, resource to test
  2  /

Grant succeeded.

SYS@orcl_11gR2> create user scott2 identified by scott2
  2  /

User created.

SYS@orcl_11gR2> grant connect, resource to scott2
  2  /

Grant succeeded.

SYS@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> create table table1
  2    (deptno number,
  3  	dname  varchar2(15),
  4  	loc    varchar2(15))
  5  /

Table created.

TEST@orcl_11gR2> grant all on test.table1 to scott2
  2  /

Grant succeeded.

TEST@orcl_11gR2> connect scott2/scott2
Connected.
SCOTT2@orcl_11gR2> host sqlldr scott2/scott2 control=test.ctl log=test.log

SCOTT2@orcl_11gR2> connect test/test
Connected.
TEST@orcl_11gR2> select * from test.table1
  2  /

    DEPTNO DNAME           LOC
---------- --------------- ---------------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH        DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

4 rows selected.

TEST@orcl_11gR2> 


Previous Topic: CSV file as a Datafile in Sql*loader
Next Topic: TRAILING NULLCOLS vs TERMINATED BY....
Goto Forum:
  


Current Time: Thu Mar 28 07:45:17 CDT 2024