On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table
in the HR schema by issuing the following command:
$> sqlldr hr/hr@pdb table=employees
Which two statements are true regarding the command?
A.
It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in
the database.
B.
It fails because no SQL *Loader data file location is specified.
C.
It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
D.
It fails because no SQL *Loader control file location is specified.
Explanation:
Note:
* SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that
establish session characteristics.
SQL> select * from dba_sys_privs where grantee=’AAA’;
AAA CREATE TABLE NO NO
AAA CREATE SESSION NO NO
So the AAA user hasn’t CREATE ANY DIRECTORY privilege.
Invoke SQLLDR:
ora-1123:oracle> sqlldr aaa/oracle@x table=t2
SQL*Loader: Release 12.1.0.1.0 – Production on Wed Apr 9 13:07:12 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: T2
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file t2.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: T2
Path used: Direct
Load completed – logical record count 10.
Table T2:
10 Rows successfully loaded.
Check the log file:
t2.log
for more information about the load.
ora-1123:oracle>
So there is ONLY ONE right answer : A ! Partial right is C, because “directory load” fails, but the command in whole succeeds. If the user has the CREATE ANY DIRECTORY he also needs DROP ANY DIRECTORY because of possible failure during deletion of temporary directory!
i think A and C ……Correct
http://docs.oracle.com/database/121/SUTIL/ldr_params.htm#SUTIL004
Oracle® Database Utilities
12c Release 1 (12.1)
E41528-04
Therefore, when the EXECUTE option is specified, you must have the CREATE ANY DIRECTORY privilege. If you want the directory object to be deleted at the end of the load, then you must also have the DROP ANY DIRECTORY privilege.
Igor is right. SQL-Loader express has defaults. Only answer A is correct here.
Default Values Used by SQL*Loader Express Mode
By default, a load done using SQL*Loader express mode assumes the following unless you specify otherwise:
If no data file is specified, then it looks for a file named table-name.dat in the current directory.
External tables is the load method. For some errors, SQL*Loader express mode automatically switches from the default external tables load method to direct path load. An example of when this might occur would be if a privilege violation caused the CREATE DIRECTORY SQL command to fail.
Answer C is also correct, see documentation:
External tables is the load method. For some errors, SQL*Loader express mode automatically switches from the default external tables load method to direct path load. An example of when this might occur would be if a privilege violation caused the CREATE DIRECTORY SQL command to fail.
Reference:
http://docs.oracle.com/database/121/SUTIL/GUID-0F35B551-861B-450D-8BF3-2312893A67D7.htm
AC I would opt for AC if I get this question with the for possibilities and two answers requested!
A&C
[oracle@db12c ~]$ sqlldr picoman/Welcome1@mydb table=test
SQL*Loader: Release 12.1.0.2.0 – Production on Mon Feb 15 20:14:35 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: TEST
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file test.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: TEST
Path used: Direct
Load completed – logical record count 2.
Table TEST:
1 Row successfully loaded.
Check the log file:
test.log
for more information about the load.
[oracle@db12c ~]$ sqlplus sys/Welcome1@mydb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 20:17:54 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> grant create any directory to picoman;
Grant succeeded.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[oracle@db12c ~]$ sqlldr picoman/Welcome1@mydb table=test
SQL*Loader: Release 12.1.0.2.0 – Production on Mon Feb 15 20:18:08 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: TEST
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-817: error dropping directory object SYS_SQLLDR_XT_TMPDIR_00000
ORA-01031: insufficient privileges
Table TEST:
1 Row successfully loaded.
Check the log files:
test.log
test_%p.log_xt
for more information about the load.
AC