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

In daily work, we often encounter such needs:

The Oracle datasheet interacts with the text or file format; the contents of the specified file are imported into the corresponding Oracle datasheet or exported from the Oracle datasheet. Tables in other databases interact with the Oracle database.

If it is a small amount of data, there are many alternative solutions. Commonly used with Pl/SQL developer tools, or manually converted to INSERT statements, or through API. But there is a large amount of data; the efficiency of using the above method is too bad. This article talks about loading and unloading Oracle data.

DBLINKOracle load data in Oracle-external table Oracle load data-sqlldr tool Oracle unload data-sqludr

one。 DBLINK in Oracle

In daily work, you will encounter different databases for data docking; each database has a function; for example, Oracle has DBLINK; PostgreSQL has external tables.

1.1 Oracle DBlink syntax

CREATE [PUBLIC] DATABASE LINK link

CONNECT TO username

IDENTIFIED BY password

USING 'connectstring'

1.2 Oracle To Mysql

Configure the dblink of the mysql database in oracle

II. Oracle load data-external tables

ORACLE external tables are used to access text files (Text File) or ORACLE proprietary format files outside the database. Therefore, no storage structures such as segments, regions, and data blocks are generated when an external table is established, and only table-related definitions are placed in the data dictionary. An external table, as its name implies, is a table stored outside the database. The data can only be obtained from the ORACLE proprietary format file when accessed. The external table is for query only and cannot modify the contents of the external table (INSERT, UPDATE, DELETE operations). External tables cannot be indexed.

2.1 directories required to create external tables

# the directory needed to create an external table SQL > create or replace directory DUMP_DIR as'/ data/ora_ext_lottu'; Directory created.# grants the user the operation permission of the specified directory SQL > GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO lottu;Grant succeeded.

2.2 external table source file lottu.txt

10,ACCOUNTING,NEW YORK20,RESEARCH,DALLAS30,SALES,CHICAGO40,OPERATIONS,BOSTON

2.3 create an external table

Drop table dept_external purge;CREATE TABLE dept_external (deptno NUMBER (6), dname VARCHAR2 (20), loc VARCHAR2 (25)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY DUMP_DIR ACCESS PARAMETERS (RECORDS DELIMITED BY newline BADFILE 'lottu.bad' LOGFILE' lottu.log' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY'"'(deptno INTEGER EXTERNAL (6), dname CHAR (20), loc CHAR (25) LOCATION ('lottu.txt')) REJECT LIMIT UNLIMITED

View data

SQL > select * from dept_external; DEPTNO DNAME LOC- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

three。 Oracle load data-sqlldr tool

3.1 prepare the subjects

Create the file lottu.txt; and the table tbl_load_01.

[oracle@oracle235 ~] $seq 1000 | awk-vOFS= ","'{print $1, "lottu", systime ()-$1}'> lottu.txt [oracle@oracle235 ~] $sqlplus lottu/li0924SQL*Plus: Release 11.2.0.4.0 Production on Mon Aug 13 22:58:34 2018Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > create table tbl_load_01 (id number,name varchar2 (10), accountid number); Table created.

3.2 create a control file lottu.ctl

Load datacharacterset utf8 infile'/ home/oracle/lottu.txt' truncate into table tbl_load_01 fields terminated by', 'trailing nullcols optionally enclosed by' 'TRAILING NULLCOLS (id, name, accountid)

3.3.Performing sqlldr

[oracle@oracle235] $sqlldr 'lottu/ "li0924" control=/home/oracle/lottu.ctl log=/home/oracle/lottu.log bad=/home/oracle/lottu.badSQL*Loader: Release 11.2.0.4.0-Production on Mon Aug 13 23:10:12 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Commit point reached-logical record count 64Commit point reached-logical record count 128Commit point reached-logical record count 192Commit point reached-logical record count 256Commit point reached-logical record count 320Commit point reached-logical record count 384Commit point reached-logical record count 448Commit point reached-logical record count 512Commit point reached-logical record count 640Commit point reached-logical record count 704Commit point reached-logical record count 768Commit point reached-logical record count 832Commit point reached-logical record count 896Commit point reached-logical record count 960Commit point reached-logical record count 1000

IV. Oracle uninstall data-sqludr

Sqludr exports Oracle datasheets to text; it was developed by Niuren Lou Fangxin. It is not included with Oracle; you need to download and install it to use it.

4.1 sqludr installation

[oracle@oracle235 ~] $unzip sqluldr2linux64.zip Archive: sqluldr2linux64.zip inflating: sqluldr2linux64.bin [oracle@oracle235 ~] $mv sqluldr2linux64.bin $ORACLE_HOME/bin/sqludr

4.2 View sqludr help

[oracle@oracle235] $sqludr -? SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2004-2010, all rights reserved.License: Free for non-commercial useage, else 100 USD per server.Usage: SQLULDR2 keyword=value [, keyword=value,...] Valid Keywords: user = username/password@tnsname sql = SQL file name query = separator string between fields record = separator string between records rows = print progress for every given rows (default 1000000) file = output file name (default: uldrdata.txt) log = log file name, prefix with + to append mode fast = auto tuning the session level parameters (YES) text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH). Charset = character set name of the target database. Ncharset= national character set name of the target database. Parfile = read command option from parameter file for field and record, you can use '0x' to specify hex character code,\ r=0x0d\ n=0x0a | = 0x7c, = 0x2c,\ t=0x09,: = 0x3a, # = 0x23, "= 0x22' = 0x27

4.3 execute sqludr

[oracle@oracle235] $sqludr lottu/li0924 query= "tbl_load_01" file=lottu01.txt field= "," 0 rows exported at 2018-08-13 23:47:55, size 0 MB. 1000 rows exported at 2018-08-13 23:47:55, size 0 MB. Output file lottu01.txt closed at 1000 rows, size 0 MB.

Summary

The above is the realization method of loading and unloading Oracle data introduced by the editor to you. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply to you in time. Thank you very much 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.