;NBSP;NBSP;NBSP;NBSP;NBSP;NBSP;NBSP;NBSP;10 363AlexAlda 14517-MAY-01 9000nBsp;.1580 401JesseCromwell 20317-MAY-017000
040 402AbbyApplegate 10317-may-019000 .2 403CarolCousins 10017-may-0127000 nbSp;.390 404johnrichardson205 17-MAY-015000 0110select* from Scott.prod_my; View all information then import the external table into the databasea Create a table of entities firstCREATE Tablescott.ldr (
C1 Number (5),
C2 Varchar2, C3 varchar2), C4 varchar2 (+
),
c5 Number (5),
C6 date,
C7 number (8,3),
> Use sqlloader to import data
Environment Description:Database: oracle8.1.6oem;System: Windows2000 Server.**************************************** **************************************** ***********Use NotePad to create the control file input. CTL. The content is as follows:Load dataInfile 'test.txt '-- Data FileAppend into Table TestFields terminated by '|' -- or '09', that is, the field termination and Tab(-- Field nameCp
Label:1. Sqlloader Control File//********************************************************************************//Basic format:LOAD DATAINFILE ' T.DAT '//Data file to import (format 1)INFILE ' TT. DAT '//import multiple files (can be used in parallel with format 1)INFILE *//the content to be imported is in the control file the following begindata is the imported content (and format 1 mutex use)APPEND into table table_name//Specify the loaded table (h
Oracle sqlloader notes command:
Sqlldr userid = username/pwd control = file log = logfile load data -- syntax keyword infile 'filename' -- data file for data import. The default format is. dat. If you want to import data from multiple files, then you can add infile in sequence to enable infile 'filename' -- and so on inifile * -- the data to be imported will be in the control file, And the begindata will guide the data starting point concatenate numbe
View has been successfully inserted.
Re-learning Sqlldr
One of the simplest examples of SQLLDR import data:
The load Data
infile *-tells SQLLDR to load the information contained in the control file itself
into table dept-to which table
fields terminated by ', ' The data load form should be a comma-delimited value
(deptno,dname,loc)--The column begindata to be loaded-
-to tell the SQLLDR that the following is loaded into the Dept table Data
10,sales, Virginia
20,accounting,virgin
Step: 1.cmd into the Oracle installation Bin directory, you can use Oracle multiple self-brought programs, such as SQLLDR.2. Write the CTL command control file as follows:Load'E:\test1.csv'intotable by 'by' "' ( Zonelevel, Zonecode, ZONENAME, zoneshort)Enter CMD to execute as follows:D:\Oracle\product\10.2. 0\db_1\bin>sqlldr wjw/zkr_123#embed@192. 168.200. Wuyi / Qyrk Control = Log = E:\test. log rows=50000 Skip=1 where skip=1 represents ignoring the first line of the CSV file.Import a CS
multiple import tasks at the same time.Sqlldr userid=/control=result1.ctl direct=true parallel=trueSqlldr userid=/control=result2.ctl direct=true parallel=trueSqlldr userid=/control=result2.ctl direct=true parallel=trueWhen loading large amounts of data (approximately 10GB), it is best to suppress the generation of logs:Sql>alter TABLE resultxt nologging;This does not produce redo LOG, which can improve efficiency. The CONTROL file then adds a line to the load data: Unrecoverable, this option m
1. Sqlloader Control File Basic format:OPTIONS (errors=1000)LOAD DATAINFILE ' T.DAT '//Data file to import (format 1)INFILE ' TT. DAT '//import multiple files (can be used in parallel with format 1)INFILE *//the content to be imported is in the control file the following begindata is the imported content (and format 1 mutex use) APPEND into table table_name//Specify the loaded table (here are several loading methods) Here are 4 ways to load tablesAPPE
1. Create a table:Sql> conn Scott/[email protected];is connected.Sql> CREATE TABLE EMP2 as SELECT * from EMP WHERE 1=2;The table is created.Sql> SELECT * from EMP2;Row not selected====================2. Import data in control file mode= = Write
oracle sqlloader used to load the contents of the data file on the operating system into the oracle database . to use sqlloader, , inside tell sqlloader started sqlloader After the policy of loading data . Create a new studentdataload.ctl file , write Load data infile ' studentdata.tx
For the following content, see Oracle space database development and application guide (HE yuanrong and others) oralcespail's shapefile Conversion Tool: After shp2sdo (provided by oracle) is converted, sqlloader can be uploaded. Mapinfo interchange format files. mif and. mid (the interchange file format is similar to esri shapefile files ).
For the following content, see Oracle space database development and application guide (HE yuanrong and others)
)So change to append (additional method)
The direct path method is faster than the conventional load method and has poor functions.
Sqlldr inventory/verysecure control = lab_18_05.ctl
DataPump:DataPump calls the following packageExpdp impdp web-based interface
DataPump has powerful functions, such as traditional Import and ExportExpdp must specify the directory object. This parameter must be used for both the backup source and destination.
Remap: remap_datafile = .....Transmission property:
Homepage → exp/IMP →>
How to import data exp into SQL Loader
Excerpt: Unknown Exporting table data to SQL * loader files Exporting table data to SQL * loader files The following describes how to import data from a specified table to an SQL * loaderl statement using a written script. There are three scripts:Exporttable. SQLGetloadercontrol. SQLGetloaderdata. SQL (can be joined)Use these scripts to save the specified table data in ASCII forma
--------------------------------------------------------------------------------------------------------------------------------------19 _ syssmu19 $Pending offlineUndotbs1 c0000001a7bf5218 71 39809 reportSqlldr @ rpt01(TNS cs_rpt01 report16
This rollback segment is corrupted and the active transaction is a sqlloader process (the SQL statement that Sid 71 is executing in the database just now is such an insert Statement ). This confirms my initial gue
The following article describes how to quickly generate a large amount of relevant data in Oracle databases. The following describes how to generate a large amount of relevant test data. The following describes the relevant content.
1. Use plsql developer to generate a csv file
2. Use> output redirection to append it to a cvs file.
3. Use SQL loader to quickly load data.
Run in plsql developer
SQL code
SELECT object_id, object_name FROM dba_objects;
Right-click plsql developer to export the csv
data Sqlloader will stopReplace: Original table with data the original data will be deleted allTruncate: The same content as replace will delete the existing data with the TRUNCATE statementBadfile ' Bf_name.bad ': Specifies the location and name of the record where the error occurred. If this parameter is not specified, a file with the same name as the file holding the data will be generated by default in the same directory as the control file.Field
,2390.12,80/05/03
Then, the following control file is created by Notepad, which contains the name and format of the data file, how the File field is bounded, how the data type is, and so on, save the load control file as Emp.ctl:
LoadData INFILE ' C:\EMP. CSV ' Badfile ' EMP. Bad ' Discardfile ' EMP. DSC ' APPEND intotableemp fileds terminated by ', ' (Emp-no,ename,salary,hiredatedata (8) "Y Y/mm/dd")
After that, double-click the Sql*loader icon, enter the Ora 睠 LE7 Data Loading tool, in the
=scott/tiger '. The time of the specified parameter must be earlier than
But not later than the parameters specified by the keyword. For example
' Sqlload scott/tiger control=foo logfile=log ', but
' Do not allow sqlload scott/tiger control=foo log ', even if allowed
The parameter ' log ' is in the correct position.
Introduction to control Files
LOAD DATA
INFILE ' T.dat '//files to be imported
INFILE ' tt.date '//import of multiple files
INFILE *//the content to be imported is in the co
Import and export of DB2 tables I know the following two ways:1)Db2move dbname Export-tn tabname1This export can select a schema, or select multiple tables, if you need to export more than one table, you can separate the table with commas, as follows:Db2move dbname Export-tn Tabname1,tabname2,tabname3The file will have a. ixf file and a. msg file. ixf files are text-formatted filesImport Method:Db2move dbname ImportDb2move dbname LoadTo do a test, it is obvious that Db2move dbname load is more e
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 can be specified by location or keyword。 The former example is ' SqlloadScott/tiger foo '; An example of the latter case is ' Sqlldr Control=fooUserid=scott/tiger '. Position specifies that the parameter must be earlier thanBut not later than the parameters specified by the keyword. For example
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.