| Loading data to another schema [message #474338] |
Sun, 05 September 2010 15:03  |
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 #474342 is a reply to message #474339] |
Sun, 05 September 2010 15:23  |
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 |
cookiemonster
Messages: 13962
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 #474354 is a reply to message #474350] |
Sun, 05 September 2010 16:11 |
 |
Barbara Boehmer
Messages: 9102
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 #474430 is a reply to message #474357] |
Mon, 06 September 2010 05:58 |
 |
Littlefoot
Messages: 21823
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
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 |
|
Barbara Boehmer
Messages: 9102
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 |
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 #474508 is a reply to message #474498] |
Mon, 06 September 2010 15:15 |
|
Barbara Boehmer
Messages: 9102
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
