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

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.logresults 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 GRANT READ,WRITE ON DIRECTORY IOLABDIR TO IOLAB Create text file 1: John,Watson 2: Roopesh,Ramklass 3: Sam,AlapatiEdit 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 22: TERMINATED BY", "23:" LAST "CHAR (255) 24: TERMINATED BY" "25:) 26:) 27: location 28: (29: 'names.txt' 30:) 31:) REJECT LIMIT UNLIMITEDEdit it as you like and then create external tableResults of query 1: SQL > select * from names 2: 3: FIRST LAST 4:-- 5: John Watson 6: Roopesh Ramklass 7: Sam AlapatiChapter: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=allImport from file (The directory object should exist) 1: impdp system/manager@orcl11g full=y directory=samba_dir dumpfile=full_%U.dmpTransport tablespace (The outline) 1: SQL 'ALTER TABLESPACE XXX OFFLINE/READONLY' ON SOURCE HOST 2: EXPORT METADATA OF THE TABLESPACE BY DATAPUMP 3: COPY DATAFILES AND METADATA FILES OF THE TABLESPACE TO DESTINATE DATABASE 4: IMPORT METADATA AND DATAFILES OF THE TABLESPACE BY DATAPUMP ON DESTINATE HOST 5: SQL 'ALTER TABLESPACE XXX ONLINE' ON SOURCE HOSTHints: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 SELECT FROM TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME' ts1.dbf 'from platform='Linux IA (32-bit)' format'/ U02 SELECT * FROM TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME'

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.