12c OCP QUESTION 143

In which two scenarios do you use SQL* Loader to load data?

A. Transform the data while it is being loaded into the database.

B. Use transparent parallel processing without having to split the external data first.

C. Load data into multiple tables during the same load statement.

D. Generate unique sequential key values in specified columns.

答案 AD

答案D的内容很让人困惑，“Generate unique sequential key”，这是SQL Loader的适用场景？它还有这个功能么？

我在下面这篇文章中找到了这个用法的描述，注意在Features Source -Docs部分的高亮显示。

链接如下：

http://www.ordba.net/Tutorials/OracleUtilities~SQLLoader.htm

Other Utilities and Tools  SQL*Loader  Commands and Parameters  Usage: SQLLDR keyword=value [,keyword=value,...] Valid Keywords: userid  -- ORACLE username/password  control -- Control file name  log  -- Log file name  bad -- Bad file name  data -- Data file name  discard -- Discard file name  discardmax -- Number of discards to allow (Default all) skip  -- Number of logical records to skip (Default 0) load  -- Number of logical records to load (Default all) errors -- Number of errors to allow (Default 50) rows -- Number of rows in conventional path bind array or between direct path data saves  (Default: Conventional path 64, Direct path all) bindsize  -- Size of conventional path bind array in bytes (Default 256000) silent  -- Suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parfile  -- parameter file: name of file that contains parameter specifications parallel  -- do parallel load (Default FALSE) file  -- File to allocate extents from  skip_unusable_indexes  -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance  -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) readsize  -- Size of Read buffer (Default 1048576) external_table  -- use external table for load;  NOT_USED,  GENERATE_ONLY,  EXECUTE  (Default NOT_ USED) columnarrayrows -- Number of rows for direct path column array (Default 5000) streamsize -- Size of direct path stream buffer in bytes  (Default 256000) multithreading  -- use multithreading in direct path  resumable  -- enable or disable resumable for current session (Default FALSE) resumable_name  -- text string to help identify resumable statement  resumable_timeout  -- wait time (in seconds) for RESUMABLE  (Default 7200) date_cache  -- size (in entries) of date conversion cache  (Default 1000) PLEASE NOTE:  Command-line parameters may be specified either by position or by keywords.  An example of the former case is 'sqlldr scott/tiger foo';  an example of the latter is 'sqlldr  control=foo userid=scott/tiger'.  One may specify parameters by position before but not after parameters specified by keywords.  For example,  'sqlldr scott/tiger control=foo logfile=log'  is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the parameter 'log' is correct. Features  Source - Oracle Docs SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data-parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following: Load data from multiple datafiles during the same load session.  Load data into multiple tables during the same load session.  Specify the character set of the data.  Selectively load data (you can load records based on the records' values).  Manipulate the data before loading it, using SQL functions.  Generate unique sequential key values in specified columns.  Use the operating system's file system to access the datafiles.  Load data from disk, tape, or named pipe.  Generate sophisticated error reports, which greatly aids troubleshooting.  Load arbitrarily complex object-relational data.  Use secondary datafiles for loading LOBs and collections.  Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.  Use a DB2 Load Utility control file as a SQL*Loader control file with few or no changes involved. Elements 01. CONTROLFILE 02. DATAFILE OR DATAFILES 03. BAD FILE 04. DISCARD FILE 05. LOG FILE 06. DATABASE TABLES AND INDEXES CONTROLFILE This is the most vital part of the SQL*Loader. This file is a multiple lined text files created using the syntax as understood by SQL*Loader The first section contains session-wide information, for example: Global options such as bindsize, rows, records to skip, and so on  INFILE clauses to specify where the input data is located  Data to be loaded  The second section consists of one or more INTO TABLE blocks. Each of these blocks contains information about the table into which the data is to be loaded, such as the table name and the columns of the table. The third section is optional and, if present, contains input data. Some control file syntax considerations to keep in mind are: The syntax is free-format (statements can extend over multiple lines).  It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.  In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The optional third section of the control file is interpreted as data rather than as control file syntax; consequently, comments in this section are not supported.  The CONSTANT keyword has special meaning to SQL*Loader and is therefore reserved. To avoid potential conflicts, Oracle Corporation recommends that you do not use the word CONSTANT as a name for any tables or columns. DATAFILE OR DATAFILES SQL*Loader accepts single or multiple files and reads, parses and loads data into the intended tables per the controlfile SQL*Loader reads data from one or more files (or operating system equivalents of files) specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The record format can be specified in the control file with the INFILE parameter. If no record format is specified, the default is stream record format. If data is specified inside the control file (that is, INFILE * was specified in the control file), then the data is interpreted in the stream record format with the default record terminator. Oracle has provided 11 case studies and they are really helpful. Metalink Doc ID Note: 160521.1 Last Revised on 16-JAN-2004 PURPOSE  The purpose of this article is to prevent running into many kinds of unclear errors due to incorrect environment settings or unknown limits before starting Sql*Loader.  Checklist before starting Sql*Loader  Environment Variables  The first and most important thing to take into a account is setting the environment variables correctly before starting Sql*Loader. Check the value of the environment variables at the machine from where you are starting your Sql*Loader session.  How to check your environment variables (e.g. NLS_LANG):  - UNIX  $ env | grep NLS_LANG  - Windows  Start --&gt Run --&gt regedit --&gt HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\\NLS_LANG  1 - Check NLS_LANG setting  Set NLS_LANG to the desired territory and characterset to prevent wrong data to be loaded or getting errors due to this data.  Syntax: NLS_LANG=  Example: NLS_LANG="Dutch_The_Netherlands.WE8ISO8859P15"  Possible problems due to an incorrect value:  - ORA-1722 invalid number  Reason:  NLS_NUMERIC_CHARACTERS is equal to ',.' or '.,' based upon the Territory (NLS_LANG) you are in.  Example:  The NLS_LANG setting above results in ',.' For NLS_NUMERIC_CHARACTERS.  The number 13.4 is not valid in this case and produces ORA-1722.  - Characters not loaded correctly due to an incorrect characterset specified.  Reason:  The characterset specified needs to be the characterset of the data to be loaded (unless the CHARACTERSET keyword is used).  2 - Check ORACLE_HOME:  Set above environment variables for the desired ORACLE_HOME from where you want SQL*Loader to be started.  Example: ORACLE_HOME=/u01/app/oracle/product/9.0.1  In Windows you can set your primary ORACLE_HOME using the Home Selector:  Start --&gt Programs --&gt Oracle Installation Products --&gt Home Selector  Possible problem due to an incorrect value:  - ORA-12560: TNS: protocol adapter error  Reason:  The SQL*Net connection fails because of the mixed environments  3 - Check LD_LIBRARY_PATH (Unix only):  Check whether $ORACLE_HOME/lib is included in LD_LIBRARY_PATH.  Example: LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH  Possible problem:  - libwtc8 library cannot be found.  4 - Check ORA_NLS33 (or ORA_NLS32) setting  Example: ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data  Possible problem due to an incorrect value:  - Segmentation Fault; Core Dump  Reason:  ORA_NLS33 points to the $ORACLE_HOME of another installation.  For example, the ORACLE_HOME where your Developer software is installed.  Limits / Defaults  1 - Check the field lengths of the data to be loaded.  Specify a length for the fields defined in the controlfile based upon the data to be loaded. Also check if the data to be loaded that it fits in the table columns specified.  A variable length field defaults to 255 bytes for a CHAR. If no datatype is specified, it defaults to a CHAR of 255 bytes as well.  See the 'Utilities Manual' chapter 'SQL*Loader Control File Reference' (see references) part 'Calculating the Size of Field Buffers' or related.  Possible errors are:  - ORA-1401: inserted value too large for column  - Field in data file exceeds maximum length.  2 - Check the datafile File Size Limit on your Operating System (Unix only)  On Unix, the filesize is limited by the shell's filesize limit.  Set the limit of your filesize with ulimit (ksh and sh) or limit  (csh) command to a value larger than the size of your sqlloader datafile.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29047826/viewspace-1670348/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29047826/viewspace-1670348/