Oracle Import and Export

发布时间：2020-04-14 01:46:03 阅读：507 作者：lbq0711 栏目：关系型数据库

开发者测试专用服务器限时活动，0元免费领，库存有限，领完即止！点击查看>>

Chapter:SQL*Loader

Lab1.Import text file to database

assume text file is like this:
   1: 60,CONSULTING,TORONTO
   2: 70,HR,OXFORD
   3: 80,EDUCATION,
Then user can write a control file of import as following:
   1: LOAD DATA 
   2: INFILE 'depts.txt'
   3: BADFILE 'depts.bad'
   4: DISCARDFILE 'depts.dsc'
   5: APPEND
   6: INTO TABLE DEPT
   7: FILEDS TERMINATED BY ','
   8: TRAILING NULLCOLS
   9: (DEPTNO INTEGER EXTERNAL(2),
  10: DNAME,
  11: LOC)
execute OS command:
   1: sqlldr control=depts.ctl log=depts.log
results of select:
   1: SQL> select * from iolab.dept;
   2:  
   3:     DEPTNO DNAME                LOC
   4: ---------- -------------------- ----------
   5:         60 CONSULTING           TORONTO
   6:         70 HR                   OXFORD
   7:         80 EDUCATION
Hints:One can use method of “Direct Path” to load data from text file.It load content from text file and write it to datafile directly,not like normal way of generating SQL sentences to insert every row to tables.

Lab2.External table

Function:It uses textfile on OS to be queried by database and it can’t be modified by database.

Create directory object

   1: CREATE DIRECTORY IOLABDIR AS '/u01/app/oracle/iolab';
   2: GRANT READ,WRITE ON DIRECTORY IOLABDIR TO IOLAB;

```
Create text file
```

   1: John,Watson
   2: Roopesh,Ramklass
   3: Sam,Alapati

Edit control file of import

   1: LOAD DATA
   2: INFILE 'names.txt'
   3: BADFILE 'names.bad'
   4: DISCARD 'names.dsc'
   5: TRUNCATE
   6: INTO TABLE NAMES
   7: FIELDS TERMINATED BY ','
   8: TRAILING NULLCOLS
   9: (FIRST,LAST)

```
Execute OS command
```

   1: sqlldr iolab/iolab control=names.ctl log=names.log external_table=generate_only;

View the log and get the model of “CREATE EXTERNAL TABLE”

   1: CREATE TABLE "SYS_SQLLDR_X_EXT_NAMES"
   2: (
   3:   "FIRST" CHAR(20),
   4:   "LAST" CHAR(20)
   5: )
   6: ORGANIZATION external
   7: (
   8:   TYPE oracle_loader
   9:   DEFAULT DIRECTORY IOLABDIR
  10:   ACCESS PARAMETERS
  11:   (
  12:     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  13:     BADFILE 'IOLABDIR':'names.bad'
  14:     DISCARDFILE 'IOLABDIR':'names.dsc'
  15:     LOGFILE 'names.log_xt'
  16:     READSIZE 1048576
  17:     FIELDS TERMINATED BY "," LDRTRIM
  18:     MISSING FIELD VALUES ARE NULL
  19:     REJECT ROWS WITH ALL NULL FIELDS
  20:     (
  21:       "FIRST" CHAR(255)
  22:         TERMINATED BY ",",
  23:       "LAST" CHAR(255)
  24:         TERMINATED BY ","
  25:     )
  26:   )
  27:   location
  28:   (
  29:     'names.txt'
  30:   )
  31: )REJECT LIMIT UNLIMITED

Edit it as you like and then create external table
Results of query

SQL> select * from names;
  
FIRST                LAST
-------------------- --------------------
John                 Watson
Roopesh              Ramklass
Sam                  Alapati

Chapter:Data Pump(summary)

Function:Data Pump utilites can import and export data from or to oracle-exclusive file.

Export to file(The directory object should exist)

   1: expdp system/manager@orcl11g full=y dumpfile=datadir:full_%U.dmp filesize=2G compression=all

Import from file(The directory object should exist)

   1: impdp system/manager@orcl11g full=y directory=samba_dir dumpfile=full_%U.dmp

```
Transport tablespace(The outline)
```

SQL 'ALTER TABLESPACE XXX OFFLINE/READONLY' ON SOURCE HOST
EXPORT METADATA OF THE TABLESPACE BY DATAPUMP
COPY DATAFILES AND METADATA FILES OF THE TABLESPACE TO DESTINATE DATABASE
IMPORT METADATA AND DATAFILES OF THE TABLESPACE BY DATAPUMP ON DESTINATE HOST
SQL 'ALTER TABLESPACE XXX ONLINE' ON SOURCE HOST

Hints:When it’s performing transporting tablespace,operator should focus on ENDIAN_FORMAT on different platform.

If the source and the destination databases’ ENDIAN_FORMAT are not matched,operator would use RMAN to convert datafile.

As an example:RMAN> convert datafile ‘/u02/ttsfiles/ts1.dbf’ from platform=’Linux IA (32-bit)’ format ‘/u02/ttsfiles/ts1conv.dbf’

the characters of platform is referred by query ‘SELECT * FROM TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME’;

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>

向AI问一下细节

Oracle Import and Export

猜你喜欢

最新资讯

相关推荐

开发者交流群：

相关标签