Oracle Unleashed- P5

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem

Oracle Unleashed- P5

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P5: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:

Nội dung Text: Oracle Unleashed- P5

  1. CONNECT INTERNAL; CONNECT SYSTEM/MANAGER; CONNECT SCOTT/TIGER; DISCONNECT Description: You use the DISCONNECT command to disconnect from the current instance without exiting SQL*DBA. It has no additional parameters. You can use this command in conjunction with SET INSTANCE to access multiple instances at your site without exiting and reentering SQL*DBA. Command syntax: DISCONNECT Example: DISCONNECT; EXECUTE Description: You use EXECUTE to execute a one-line PL/SQL statement. If you want to execute more than one line, you must use the BEGIN . . . END format for PL/SQL. You must also be connected to a database before executing. Command syntax: EXECUTE PL/SQL statement Example: EXECUTE total_orders; EXIT Description: EXIT is the command you use to exit the SQL*DBA session. It automatically disconnects you from the current database if a connection has been established. This command has no parameters or keywords. Command syntax: EXIT Example: EXIT; HOST Description: The HOST command executes an operating system command or program while you're still in SQL*DBA. This command shells you out of SQL*DBA for the duration of the command. If issued by itself, it shells you to the Please purchase PDF Split-Merge on to remove this watermark.
  2. operating system until you type EXIT to return to SQL*DBA. Command syntax: HOST operating system command HOST Keywords: operating system command A valid operating system command or program. Examples: HOST who; HOST dir; HOST; MONITOR Description: The MONITOR command enables you to monitor various statistics and attributes of the database, processes, or users. This command is extremely helpful in analyzing and resolving database problems. You can specify any parameters that the menu requests in order on the command line. Command syntax: MONITOR CIRCUIT DISPATCHER FILEIO LATCH LCACHE LOCK PROCESS QUEUE ROLLBACK SESSION SESSIONSTATISTIC SHARED SQLAREA Please purchase PDF Split-Merge on to remove this watermark.
  3. SYSTEMIO SYSTEMSTATISTIC TABLE Keywords: Displays current information on the virtual circuits owned by each shared server in a Multi- CIRCUIT Threaded Server environment. Displays current information about a shared server's dispatcher processes in a Multi-Threaded DISPATCHER Server environment. FILEIO Displays read/write information for every database file associated with the current instance. LATCH Displays information on all current latches. LCACHE Displays current information on the library cache. Lists the current processes and the locks they are waiting on. Using ALL lists all locks being LOCK held by current processes. PROCESS Monitors summary information for every process connected to the current instance. QUEUE Lists information on each shared server's message queues. ROLLBACK Shows activity on every active rollback segment in the instance. SESSION Displays active process information. SESSIONSTATISTIC Shows user session statistics for current user processes. SHARED Monitors shared server activity. SQLAREA Gives various statistics on the shared SQL area. Summarizes the read/write statistics for each Oracle process. This information is not precise SYSTEMIO but instead is representative of relative distribution of I/O. SYSTEMSTATISTIC Shows system statistics for the current database. Please purchase PDF Split-Merge on to remove this watermark.
  4. TABLE Displays table names of tables referenced in SQL statements that have been recently parsed and reside in the shared SQL area. Examples: MONITOR PROCESS; MONITOR LATCH; MONITOR LOCK 10 20; MONITOR SYSTEMSTATISTIC; PRINT Description: The PRINT command prints the value of a variable that you defined using the SQL*Plus command VARIABLE. Command syntax: PRINT variable Keywords: variable The name of the variable defined with the VARIABLE command. Examples: PRINT COUNTER; PRINT NAME; RECOVER Description: You use the RECOVER command to perform media recovery on data files, tablespaces, or entire databases as required. You must be connected as INTERNAL to use this command, and you must have a dedicated process. You cannot be connected through Oracle's Multi-Threaded Server. Command syntax: RECOVER DATABASE RECOVER DATABASE UNTIL CANCEL CHANGE integer TIME date RECOVER DATABASE USING BACKUP CONTROLFILE Please purchase PDF Split-Merge on to remove this watermark.
  5. RECOVER TABLESPACE tablespace RECOVER DATAFILE filename Keywords: Requests the recovery of an entire database. Will apply redo log files to all DATABASE tablespaces needing media recovery. Used to specify an incomplete RECOVER to a specific time. You must specify UNTIL TIME date the date in the following format: 'YYYY-MM-DD:HH24:MI:SS' YYYY is a four-digit year. MM is a two-digit month. HH24 is the time in 24-hour specification. MI is minutes. SS is seconds. Used to recover until a specific change number. This is very useful in restoring a UNTIL CHANGE integer tablespace where a table was accidentally dropped. integer must be a valid change number, and the redo logs must be available to Oracle. Specifies recovery should continue applying redo logs until the operator cancels UNTIL CANCEL the operation. Recovery continues redo log by redo log until canceled. Tells the database to use a backup version of the control file instead of the USING BACKUP CONTROLFILE primary one. This control file must be available to Oracle, or the command will fail. Recovers the specified tablespace, or tablespaces. You can recover up to 16 of TABLESPACE tablespace them in a single statement. DATAFILE filename Specifies a particular data file belonging to a tablespace that you want to restore. There is no limit to the number of data files you can recover in a given statement. Examples: RECOVER TABLESPACE tools; Please purchase PDF Split-Merge on to remove this watermark.
  6. RECOVER DATABASE; RECOVER DATAFILE 'users_01.dbf'; RECOVER DATABASE UNTIL '1994-10-11:15:01:00'; RECOVER TABLESPACE tools, users; SET Description: The SET command sets characteristics for the current SQL*DBA session. These characteristics are not saved for future sessions. Command syntax: SET ARRAYSIZE integer AUTORECOVERY ON/OFF CHARWIDTH integer COMPATIBILITY V6/V7 CYCLE integer DATEWIDTH integer ECHO ON/OFF FETCHROWS integer HISTORY integer INSTANCE instance-path/LOCAL LABWIDTH LINES integer LOGSOURCE pathname/DEFAULT LONGWIDTH integer MAXDATA integer NUMWIDTH integer RETRIES integer/INFINITE SERVER OUTPUT OFF/ON SIZE integer SPOOL filename/OFF STOPONERROR ON/OFF TERM PAGE/NOPAGE Please purchase PDF Split-Merge on to remove this watermark.
  7. TERMOUT ON/OFF TIMING ON/OFF Keywords: Indicates the number of rows that are fetched from the database at one ARRAYSIZE integer time. The default is 20, and the maximum is specific to the operating system you are running. Tells the database to automatically apply all redo logs necessary to bring the database, tablespace, or data file to a usable state. When this is ON, the database begins recovery without requesting input from the operator. AUTORECOVERY Log filenames are derived from the database parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. If the files cannot be located, operator input is requested. Defines the column width displayed for columns of type CHAR. The CHARWIDTH integer default is 80, and if no integer is specified, the parameter is reset to 80. Sets the SQL*DBA compatibility mode to either Version 6 or Version 7. COMPATIBILITY V6/V7 This parameter affects how you specify columns of type CHAR, integrity constraint definitions, and the storage parameters for rollback segments. Used for the MONITOR command, sets the time that the monitor screens cycle in gathering statistics. The default is 5 seconds. The minimum is 1 CYCLE integer second; maximum is 3600 seconds. The smaller the number, the higher the impact on the database. Sets the width for DATE data to be displayed. The default is 9, and if DATEWIDTH integer entered with no integer, it is reset to 9. The range of values for this parameter is operating-system specific. Enables echoing of commands that are executed from command files. The ECHO ON/OFF default is OFF. In this mode, only the output is displayed. This parameter limits the number of rows that are returned by a database query. It can be very useful in returning only the first 20 or 10 rows from FETCHROWS integer a database table. The default returns all rows that match the given criteria, and as with all other parameters, entering the SET command without an integer resets the value to all. Sets the number of SQL*DBA commands saved in the history buffer. Any commands that reside in this buffer can be recalled and reexecuted using HISTORY integer the Previous and Next Command options in menu mode. The default is 10 commands. Please purchase PDF Split-Merge on to remove this watermark.
  8. Sets the instance name to where all SQL*DBA commands are applied. The instance-path is defined by a system node name and database name INSTANCE instance-path/LOCAL separated with a hyphen. A sample connect string is my_host- my_database. Issuing the command with no database definition resets the instance back to the local default instance. LABWIDTH Used strictly with Oracle's Trusted Server package. Limits the number of lines the output window of SQL*DBA can store and LINES integer recall. After reaching the limit, the lines at the beginning of the buffer are erased. The default value is 1000. Tells Oracle where to find archived redo logs to be used during a recovery LOGSOURCE pathname/DEFAULT session. Use this to set the location to a temporary location where redo logs have been restored. Tells SQL*DBA how to display LONG data. By default, the display is 80 LONGWIDTH integer characters only. Once again, the operating system defines the range of values for this parameter. Sets the maximum number of bytes that you can fetch from the database MAXDATA integer in a single SELECT statement. The default is 20,480 bytes (20K). Your operating system defines the maximum number for this parameter. Defines the length in characters that data types of NUMBER are NUMWIDTH integer displayed in. The default is 10, and the minimum and maximum values are operating-system dependent. Used with the STARTUP command, this specifies how many times the RETRIES integer/INFINITE startup command attempts to start the database. INFINITE means it tries until it succeeds or is canceled. Specifies the size of the message buffer, in bytes, that can accumulate at SERVER OUTPUT OFF/ON SIZE integer one time. This message buffer is used by the PUT() and PUT_LINE() commands. Captures commands and output from the current session to a file. SPOOL filename/OFF Specifying OFF closes the previously opened file. Tells SQL*DBA, when executing a command file, to stop if it encounters STOPONERROR ON/OFF an error. If it finds one, the rest of the command file is not executed, and it returns control to the operating system. Tells SQL*DBA to display output one page at a time. The default, TERM PAGE/NOPAGE NOPAGE, scrolls all output to the output window. After that, you can navigate to the output window and scroll through the saved output. Please purchase PDF Split-Merge on to remove this watermark.
  9. Controls the display of output from SQL commands to the output window. ON enables display of the output whereas OFF disables the TERMOUT ON/OFF output. This is helpful if you're spooling output to files. The output is sent to the spool file but not the terminal. TIMING ON/OFF Displays the parse, execute, and fetch times for every SQL statement executed. The default is OFF. This option is useful for establishing response times. Examples: SET INSTANCE D:DEV-PROD SET HISTORY 50; SET NUMWIDTH 20; SHOW Description: Using the SHOW command shows the values of all the parameters set by the SET command. Additionally, this command supports several other parameters listed in the Keywords section. For a definition of any parameter listed, refer to the previous section on SET. Command Syntax: SHOW ARRAYSIZE AUTORECOVERY CHARWIDTH COMPATIBILITY CYCLE DATEWIDTH ECHO FETCHROWS HISTORY INSTANCE LABWIDTH LINES LOGSOURCE LONGWIDTH MAXDATA Please purchase PDF Split-Merge on to remove this watermark.
  10. NUMWIDTH RETRIES SERVER OUTPUT SPOOL STOPONERROR TERM TERMOUT TIMING ALL PARAMETERS ERRORS Keywords: Shows the values of all settings. Does not show ERRORS, PARAMETERS, or SGA, which must be ALL displayed separately. Shows all errors encountered during the last compilation of a function, procedure, or package. ERRORS Output includes the line, column, and error message generated. LABEL This is a Trusted Oracle parameter. Shows the current values for all database parameters specified in the startup files for the current instance. Used alone, it displays all parameters. Used in conjunction with a parameter name, it shows PARAMETERS the specific parameter. If a partial parameter name is used, the output includes all parameters that are similar. SGA Shows current information on the System Global Area for the connected instance. Examples: SHOW SGA; SHOW TERMOUT; SHOW ALL; SHOW PARAMETERS COUNT; SHOW ERRORS; Please purchase PDF Split-Merge on to remove this watermark.
  11. SHUTDOWN Description: You use SHUTDOWN to stop a currently running database. Various options include closing or dismounting the database. Command syntax: SHUTDOWN ABORT dbname IMMEDIATE dbname NORMAL dbname Keywords: Shuts down the database immediately, without checkpointing the database. This procedure ABORT immediately kills all active sessions without rolling back or committing transactions and then closes and dismounts the database. If you use this command, the database will require recovery. Shuts down the database by preventing new connections, terminating all existing sessions, and IMMEDIATE committing or rolling back current transactions and then checkpoints the database before dismounting and closing it. Media recovery is not required. This is the default. It waits for currently connected users to disconnect, prevents new connections, NORMAL checkpoints, and then closes and dismounts the database. No media recovery is required. dbname This is a Trusted Oracle parameter that you should not use for normal or parallel operations. Examples: SHUTDOWN; SHUTDOWN IMMEDIATE dev; SHUTDOWN ABORT; SPOOL Description: The SPOOL command begins or ends the spooling of command output to a specified file. Command syntax: SPOOL OFF SPOOL filename Keywords: Please purchase PDF Split-Merge on to remove this watermark.
  12. OFF Closes the currently opened file. filename The file to spool output to. Examples: SPOOL ON 'kelly.spl'; SPOOL OFF; STARTUP Description: You use STARTUP to start a database. With options, this command enables you to bring the database into various stages of use for maintenance. As with the SHUTDOWN command, you must be connected as internal, and you cannot be connected via Oracle's Multi-Threaded Server. Command syntax: STARTUP FORCE RESTRICT PFILE=filename MOUNT NOMOUNT OPEN RECOVER database mount options Keywords: Issues a shutdown abort of the current instance and then FORCE attempts to start the instance again. This is sometimes required if there were shutdown errors. Same as the ALTER SYSTEM ENABLE RESTRICTED SESSION command; enables the database to start up in RESTRICT restricted mode and will only give access to users with the RESTRICTED SESSION role. Please purchase PDF Split-Merge on to remove this watermark.
  13. Enables the database to start up with a specific parameter file (INIT.ORA). Very useful if the INIT.ORA file is not in PFILE=filename the current directory or if you are starting up a new database. MOUNT Mounts the database but does not open it for use. Does not mount the database. You cannot use this option NOMOUNT with the MOUNT, OPEN, PARALLEL, SHARED, or EXCLUSIVE options. OPEN Default option; mounts and opens the default database. Similar to the RECOVER DATABASE command; starts the current instance and recovers the database, if required. RECOVER The recovery works as if AUTORECOVERY were set to ON. If the recovery fails, the database remains mounted but does not open. Starts this specific instance. If no instance is specified, database startup runs on the default instance. mount options Specifies three types of options for the startup: Mounts and opens the database for a EXCLUSIVE single instance to use. Mounts and opens the database for PARALLEL parallel mode. SHARED Another name for PARALLEL. RETRIES Specifies the number of retries before failing. Examples: STARTUP; STARTUP MOUNT; STARTUP PFILE='/home/oracle/init.ora' PARALLEL; STARTUP MOUNT RESTRICT; STARTUP FORCE; Please purchase PDF Split-Merge on to remove this watermark.
  14. Summary SQL*DBA is a powerful tool that is useful for creating, managing, and tuning all your Oracle instances. The flexibility to run in command, line, or menu mode gives the user a definite advantage that many other packages do not provide. The capability to automate many of the SQL*DBA commands enables the database administrator to turn over some of the database operations to secondary support personnel—which can help create a higher availability system with less down time. Overall, this tool is valuable in the day-to-day responsibilities of every database administrator and custom developer. Previous Next TOC Home Page Page Please purchase PDF Split-Merge on to remove this watermark.
  15. Previous Next TOC Home Page Page q 8 r SQL*Loader s Introduction s Basic SQL*Loader Components s The Input Data s The Control File s The Log File s Discard and Bad Files s Physical Versus Logical Records s Concatenated Records s SQL*Loader Paths s The Conventional Path s The Direct Path s Parallel Data Loading s Control File Syntax s OPTIONS Clause s UNRECOVERABLE/RECOVERABLE Clause s LOAD DATA Clause s INFILE Clause s Table Loading Methods s CONCATENATION Clause s INTO TABLE Clause s Table-Specific Loading Method s Table-Specific OPTIONS Clause s WHEN Clause s FIELDS Clause s TRAILING NULLCOLS Clause s Index Options s Field Conditions s Column Specifications s Figure 8.13. Column specifications. s Multiple Table Statements s Command-Line Options and Parameter Files s Summary 8 SQL*Loader Introduction One of the many challenges DBAs face today is the problem of migrating data from external sources into the Oracle database. This task has increased in complexity with the introduction of data warehousing; the demand has gone from migrating megabytes of data to gigabytes, and in some cases, even terabytes. Oracle addresses this need with the Please purchase PDF Split-Merge on to remove this watermark.
  16. SQL*Loader utility, a very versatile tool that loads external data into Oracle database tables. SQL*Loader is very flexible, and it is configurable to the point that you often can avoid development of 3GL procedures with embedded SQL. Whenever you face the task of converting foreign data into the Oracle format, first consider the use of SQL*Loader before resorting to other alternatives. The purpose of this chapter is to provide an overview of SQL*Loader's functionality, highlight its capabilities, describe commonly used syntax, and provide practical examples. For additional information and in-depth reference material, refer to the Oracle7 Server Utility User's Guide. Basic SQL*Loader Components SQL*Loader requires two types of input: the external data, which can reside on disk or tape, and control information (contained in the control file), which describes the characteristics of the input data and the tables and columns to load. The outputs, some of which are optional, include the Oracle table(s), log file, bad file(s) and discard file(s). Figure 8.1 illustrates the components. Figure 8.1. SQL*Loader components. The Input Data SQL*Loader can process practically any type of data file, and it supports native data types for almost any platform. Data is usually read from one or more data files; however, data also may be embedded in the control file, after the control information. The data file can exist as a fixed or variable format. In fixed format, the data exists in fixed-length records that all have the same format. The fields for fixed-format files are defined by starting and ending positions within the record, and the fields contain the same data type and length throughout the file. (see Figure 8.2.) Binary data must be in a fixed-format file, as SQL*Loader cannot handle it in a variable format. Figure 8.2. Fixed-format records. In variable-format files, the data exists in records that may vary in length, depending on the length of the data in the fields. The fields are only as long as necessary to contain the data. Fields in variable-format files may be separated by termination characters (such as commas or white space), enclosed by delimiter characters (such as quotation marks), or both. (See Figure 8.3.) Figure 8.3. Variable-format records. If you are using files with termination characters, make sure that any field that contains the termination character as part of the data is delimited. For example, if you are using a file with comma separation, you can use double quotes to delimit any field containing a comma. To increase performance, use fixed-length records. Based on the tests I have done, SQL*Loader takes about 50% longer to process a variable-format file than a fixed-format file. I used the same amount of data for both Please purchase PDF Split-Merge on to remove this watermark.
  17. formats and comma separation for the variable data. Through the Oracle National Language Support (NLS), SQL*Loader has the capability to interpret and convert data with different character encoding schemes from other computer platforms and countries. For example, SQL*Loader can load an EBCDIC file into a database on an ASCII platform, or it can load an Asian character-set file into an American character-set database. When porting a file from one platform (like EBCDIC mainframe) to a different target platform (like ASCII UNIX) that contains binary data (like packed decimal), remember that the file is transferred in an image or binary state. For example, it you are using FTP to port the file, after you connect to the host system, be certain to put the session in an image state by typing binary at the FTP prompt. Otherwise, FTP interprets and converts the data, thereby corrupting the binary data. The Control File Before SQL*Loader can process the data in the data file(s), you must define the data specifications to SQL*Loader. You use the control file to define the physical data file specifications, as well as the format of the data in the file(s). The control file is a free-format file that also contains additional control data, instructing SQL*Loader how to process the data. The details concerning the control file are described in a later section. The Log File Upon execution, SQL*Loader creates a log file containing detailed information about the load, including these items: q Names of the input data file(s), control file, bad file(s), and discard file(s) q Input data and associated table specifications q SQL*Loader errors q SQL*Loader results q Summary statistics See Figure 8.4 for a sample log file. Figure 8.4. Sample log file. Discard and Bad Files SQL*Loader has the built-in functionality, through specifications in the control file, to format the input data and include or exclude the input record based on record-selection criteria. If SQL*Loader includes the record for processing, it is passed to the Oracle kernel for insertion into the database table. Figure 8.5 shows the record-filtering process. Figure 8.5. Record-filtering process. Please purchase PDF Split-Merge on to remove this watermark.
  18. If SQL*Loader rejects the record due to a format error, or the Oracle kernel cannot insert the record into the database table(s) for any reason, the input record is written to the BAD file, in the same format as the original input data file. If SQL*Loader rejects the record due to an exclusion based on the record-selection criteria, the input record is written to the DISCARD file (providing it was specified in the control file), also in the same format as the original input data file. Because both the BAD and DISCARD files are written in the same format as the original input data file, they can be edited, if necessary, and used as input data files to another SQL*Loader session. Physical Versus Logical Records Physical records are the individual lines in the input data file as they were created by the operating system on the platform from which the file originated. Physical records are terminated by a record-terminator character (like a carriage return). Logical records correspond to a row in a database table. A physical record can have a one-to-one association with a logical record. (See Figure 8.6.) SQL*Loader also enables you to create a many-to-one association between physical and logical records through the use of the CONCATENATE or CONTINUEIF clause in the control file. You may create a one-to-many relationship by splitting one physical record into multiple logical records. Figure 8.6. Physical versus logical records. Concatenated Records SQL*Loader can concatenate records either by specifying the number of physical records that form one logical record or by specifying conditional evaluations based on character occurrences within the data. If the number of physical records that comprise a logical record varies, then you must base the concatenation on conditional evaluations. The CONCATENATE and CONTINUEIF clauses in the control file facilitate physical record concatenation. SQL*Loader Paths SQL*Loader provides two paths for loading data: q The conventional path q The direct path The Conventional Path The conventional path generates a SQL INSERT command with the array processing interface to load the data into the table(s). Because of this interface, SQL*Loader competes with all other Oracle processes for buffer cache resources; Oracle looks for and tries to fill partially filled blocks on each insert. (See Figure 8.7.) If you are loading small amounts of data, this method is usually acceptable; however, with large volumes of data, this technique becomes too time consuming and resource-intensive. Figure 8.7. Conventional loader path. These conditions exist when you load data with the conventional path: q Other users and Oracle processes can be accessing the table concurrently with SQL*Loader. q Indexes are updated as the rows are inserted into the table. q All referential and integrity constraints are enforced as the data is loaded into the table. Please purchase PDF Split-Merge on to remove this watermark.
  19. q Database Insert triggers fire as the rows are inserted into the table. q You can apply SQL functions to the input data as it is being loaded. q Data can be loaded into a clustered table. q Data can be loaded with SQL*Net. The Direct Path In contrast to the conventional path, the direct path is optimized for bulk data loads. Instead of using the buffer cache to obtain and fill the database blocks, the direct path option uses the extent manager to get new extents and adjust the high water mark. Direct path creates formatted database blocks and writes them directly to the database. (See Figure 8.8.) Figure 8.8. Direct loader path. The direct path has several significant advantages: q You can load and index large amounts of data into empty or non-empty table(s) in a relatively short period of time. q If loading data into empty tables, you can load presorted data and eliminate the sort and merge phases of the index build, thereby significantly increasing performance. q You can load data in parallel, which enables multiple SQL*Loader sessions to perform concurrent direct path loads into the same table. q You can specify that a direct path load be done in an UNRECOVERABLE mode, which bypasses Oracle's redo logging activity and significantly increases performance. Although the direct path significantly increases performance, it does have some limitations: q The table(s) and index(es) into which you are loading data are exclusively locked at the start of the load and not released until the load is finished; the table(s) cannot have any active transactions on them and are not available to other users or processes until the load is completed. q Indexes are put into a direct load state at the start of the load and need to be rebuilt, either automatically or manually, after the load is completed. If the SQL*Loader session does not complete successfully, the indexes are left in the direct load state and need to be rebuilt manually. Any PRIMARY KEY or UNIQUE constraints are not validated until after the load is complete and the index rebuild occurs; you may have duplicate keys and need to correct them through the use of the exceptions table before you can rebuild the index. q The NOT NULL constraint is the only constraint checked at insertion time. All other integrity and referential constraints are re-enabled and enforced after the load is complete. If any violations exist, they are placed into the exceptions table, which you should specify when you create the constraint. The exceptions table must be created before the load session. q Database Insert triggers do not fire. Any application functionality that relies on them must be accomplished through some other method. q You cannot apply SQL functions to the input data as it is being loaded. q Data cannot be loaded into a clustered table. q Only in the case where both computer systems belong to the same family, and both are using the same character set, can data be loaded through SQL*Net. You should not use SQL*Net for direct path loads, as the direct path Please purchase PDF Split-Merge on to remove this watermark.
  20. should be used only for large amounts of data. Network overhead offsets any performance gains associated with the direct path. q DEFAULT column specifications are not available with the direct path. q Synonyms that exist for the table(s) being loaded must point directly to the table; they cannot point to another synonym or view. The main advantage to the direct path method is performance. No hard and fast rules exist to determine when to use the conventional method vs. the direct path method, because the definition of "large amounts of data" varies from application to application. Other external factors vary also, such as database availability, the cleanliness of the data being loaded, and system resources. I have seen loads go from days to hours using the parallel, direct path method vs. the conventional path method, with clean data on a multi-processor system. But I have also seen cases where significant amounts of time were spent cleaning the table data so an index could rebuild after a direct path load. You need to analyze your situation, and based on all the factors, decide which method is better for your application. Parallel Data Loading Oracle version 7.1 introduced the functionality of performing direct path loads in parallel. This feature gives SQL*Loader nearly linear performance gains on multi-processor systems. By using the parallel option, you can load multiple input files concurrently into separate tablespace files, with each file belonging to the tablespace in which the table resides. Maximum throughput is achieved by striping the tablespace across different devices and placing the input files on separate devices, and preferably separate controllers. (See Figure 8.9.) Figure 8.9. Striping parallel loads. When using the parallel option, SQL*Loader creates temporary segments, sized according to the NEXT storage parameter specified for the table, in the tablespace file specified in the OPTIONS clause of the control file. The specified file must be in the tablespace in which the table resides, or you get a fatal error. Upon completion of the SQL*Loader session, the temporary segments are merged (with the last extent trimmed of any free space) and added to the existing table in the database, above the high water mark for the table. You should drop indexes, primary key constraints, and unique key constraints on the table before doing a parallel load. Oracle version 7.1 introduced parallel index creation, but you may use it only with a CREATE INDEX statement, not within an ALTER TABLE CREATE CONSTRAINT statement. Therefore, the best method is to create the indexes first, using the parallel option, then create the primary key and unique constraints, which use the previously created indexes. Parallel SQL*Loader sessions that are interrupted normally (for example, by a Ctrl+C in UNIX) will terminate normally; they stop processing where they were interrupted. Any temporary segments created during the session are added to the table's high water mark. Any parallel session that was "killed" from an O/S command terminates Please purchase PDF Split-Merge on to remove this watermark.
Đồng bộ tài khoản