Shulou(Shulou.com)06/01 Report--

Oracle provides tools for sqlldr, and sometimes you need to import data into text, and oracle's spool can be easily implemented.

It is convenient to realize oracle to export data to txt and txt to import data to oracle.

First, export data to txt

Test with all_objects table

SQL > desc all_objects; Name Null? Type-OWNER NOT NULL VARCHAR2 (30) OBJECT_NAME NOT NULL VARCHAR2 (30) SUBOBJECT_NAME VARCHAR2 (30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2 (19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2 (19) STATUS VARCHAR2 (7) TEMPORARY VARCHAR2 (1) GENERATED VARCHAR2 (1) SECONDARY VARCHAR2 (1)

Use object_id,object_name for export and import tests.

Some settings meet the style of data export:

Vi exp_table.sql

Set line 1000-set line length set pagesize 0-output does not page change set feedback off-default when a sql is issued, oracle will give a feedback, for example, when creating a table, if the command line is successful, it will return something like: feedback from Table created, no feedback set heading off after off-no header information set trimspool on-if trimspool is set to on The trailing empty set trims on in the spool file will be removed-the empty character set echo off will be removed -- displays each sql command in the script started by start, defaults to onset colsep'|'--sets the delimiter set termout off-- does not display the result spool db1.txt on the screen-- records the data to db1.txtselect object_id,object_name from all_objects;-- exports the data statement spool off-- collects the exit

Export data when everything is in place:

[oracle@centos5] $sqlplus test/test @ exp_table.sql SQL*Plus: Release 10.2.0.4.0-Production on Thu Jun 13 16:35:14 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@centos5 ~] $sed-I's / g 'db1.txt-optional, remove the space [oracle@centos5 ~] $more db1.txt 20 at the beginning of each line | ICOL$44 | I_USER128 | CON$15 | UNDO$29 | C_COBJ#3 | I_OBJ#25 | PROXY_ROLE_DATA$

Check whether the number of records of the data is correct after export

[oracle@centos5 ~] $cat db1.txt | wc-l49988 [oracle@centos5 ~] $sqlplus test/testSQL*Plus: Release 10.2.0.4.0-Production on Thu Jun 13 16:36:21 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > select count (*) from all_objects; COUNT (*)-49988-data is correct

Import data from txt to oracle

Sqlldr is set through a control file to import data from text.

Set up a test table

SQL > create table tb_sqlldr (id number,name varchar2 (50)); Table created.

Create a control file

Vi tb_sqlldr.ctl

Load data infile 'db1.txt'-- data source text append into table tb_sqlldr-- the data is imported into table tb_sqldr by appending. If you want to overwrite fields terminated by "|"-- 4, the field ends with a tab (tab) (id,name)-- define the corresponding field name. Note the order.

The imported data can be divided into four modes, which can be selected according to your needs:

APPEND / / the data in the original table is added at the end.

INSERT / / load empty table if the original table has data sqlloader will stop the default value

REPLACE / / previous tables have data and all previous data will be deleted

TRUNCATE / / the same content as replace will delete existing data with the truncate statement

Perform import operation

Sqlldr userid=test/test control=tb_sqlldr.ctl

The data of almost 5 weeks can be solved in just 2 seconds.

Validate data after import

SQL > select count (*) from tb_sqlldr; COUNT (*)-49988

Import succeeded

Perform the import operation again, because it is set to append:

SQL > select count (*) from tb_sqlldr; COUNT (*)-99976

Record doubling

Sqlldr also has many parameters to choose from, such as log and bad. Just check out the help.

[oracle@centos5] $sqlldrSQL*Loader: Release 10.2.0.4.0-Production on Thu Jun 13 17:07:26 2013Copyright (c) 1982, 2007, Oracle. All rights reserved.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) commit_discontinued-commit loaded rows when load is discontinued (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 columnarray (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 statementresumable_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 byposition or by keywords. An example of the former case is' sqlldrscott/tiger foo'; an example of the latter is' sqlldr control=foouserid=scott/tiger'. One may specify parameters by position beforebut 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 theposition of the parameter 'log' is correct.

Summary

The above is the whole content of this article on oracle data and text import and export source code examples, interested friends can refer to: ORACLE SQL statement optimization technical points analysis, oracle database startup phase analysis, oracle database import and export command analysis, if there are deficiencies, welcome to comment, hope to help you. Thank you for your support to the website.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.