大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
Chapter:SQL*Loader
创新互联公司专业为企业提供蚌山网站建设、蚌山做网站、蚌山网站设计、蚌山网站制作等企业网站建设、网页设计与制作、蚌山企业网站模板建站服务,十多年蚌山做网站经验,不只是建网站,更提供有价值的思路和整体网络服务。
Lab1.Import text file to database
assume text file is like this:
1: 60,CONSULTING,TORONTO2: 70,HR,OXFORD3: 80,EDUCATION,
Then user can write a control file of import as following:
1: LOAD DATA2: INFILE 'depts.txt'3: BADFILE 'depts.bad'4: DISCARDFILE 'depts.dsc'5: APPEND6: INTO TABLE DEPT7: FILEDS TERMINATED BY ','8: TRAILING NULLCOLS9: (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 LOC4: ---------- -------------------- ----------5: 60 CONSULTING TORONTO6: 70 HR OXFORD7: 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.
1: CREATE DIRECTORY IOLABDIR AS '/u01/app/oracle/iolab';2: GRANT READ,WRITE ON DIRECTORY IOLABDIR TO IOLAB;
Create text file
1: John,Watson2: Roopesh,Ramklass3: Sam,Alapati
1: LOAD DATA2: INFILE 'names.txt'3: BADFILE 'names.bad'4: DISCARD 'names.dsc'5: TRUNCATE6: INTO TABLE NAMES7: FIELDS TERMINATED BY ','8: TRAILING NULLCOLS9: (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 external7: (8: TYPE oracle_loader9: DEFAULT DIRECTORY IOLABDIR10: ACCESS PARAMETERS11: (12: RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF813: BADFILE 'IOLABDIR':'names.bad'14: DISCARDFILE 'IOLABDIR':'names.dsc'15: LOGFILE 'names.log_xt'16: READSIZE 104857617: FIELDS TERMINATED BY "," LDRTRIM18: MISSING FIELD VALUES ARE NULL19: REJECT ROWS WITH ALL NULL FIELDS20: (21: "FIRST" CHAR(255)22: TERMINATED BY ",",23: "LAST" CHAR(255)24: TERMINATED BY ","25: )26: )27: location28: (29: 'names.txt'30: )31: )REJECT LIMIT UNLIMITED
1: SQL> select * from names;2:3: FIRST LAST4: -------------------- --------------------5: John Watson6: Roopesh Ramklass7: 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
1: impdp system/manager@orcl11g full=y directory=samba_dir dumpfile=full_%U.dmp
Transport tablespace(The outline)
1: SQL 'ALTER TABLESPACE XXX OFFLINE/READONLY' ON SOURCE HOST2: EXPORT METADATA OF THE TABLESPACE BY DATAPUMP3: COPY DATAFILES AND METADATA FILES OF THE TABLESPACE TO DESTINATE DATABASE4: IMPORT METADATA AND DATAFILES OF THE TABLESPACE BY DATAPUMP ON DESTINATE HOST5: 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’;