Oracle µÄһЩµ¼ÈëºÍµ¼³ö·½·¨
֮ǰÏîÄ¿ÓÐÓõ½µÄһЩµ¼ÈëºÍµ¼³ö£¬Ê±ÖÁÒѾÃÕûÀíһϣ¬×ö¸ö¼ÇºÅ
µ¼ÈëÎļþ£º
1. ÔÚij·¾¶ÏÂд¿ØÖÆÎļþ e:\testRegionControl.ctl £º
load data
infile e:\region.txt
truncate into table region
fields terminated by X'09'
TRAILING NULLCOLS
(
PPCC_ID :PPCC_ID),
PPCC_PRINT_CODE :PPCC_PRINT_CODE,
PPCC_STATUS :PPCC_STATUS,
PPCC_STATUS :PPCC_STATUS,
filler1 FILLER,
PPCC_MPDC_CREATE_DATE to_date('" + PPCC_MPDC_CREATE_DATE + "','YYYY-MM-DD'),
PPCC_MPDC_UPDATE_POINT_FLAG constant '1',
PPCC_MPDC_AMT to_number(trim(:PPCC_MPDC_AMT))
)
2. ÓÃSQLldrÃüÁîµ¼ÈëÊý¾Ý£º
sqlldr silent=header feedback discards partitions userid=shawn/shawn@DEMO control=e:\testRegionControl.ctl log=e:\testRegionControl.log bad=e:\testRegionControl.bad
ÓÃexpdp µ¼³ö£¬Ç°ÌáÊÇdirectoryÒѾËüËùÖ¸ÏòµÄ·¾¶´æÔÚ£º
expdp shawn/shawn@DEMO directory=TEST_DIRECTORY dumpfile=test.dmp logfile=test.log
ÓÃexp µ¼³ö
exp shawn/shawn@DEMO file=D:/test1.dmp tables=(region)
exp shawn/shawn@DEMO file=D:/test2.dmp tables=(emp,dept)
ÓÃSpool µ¼³öÊý¾Ýµ½Îı¾£º
1. Ê×Ïȱà¼Îļþ test_sqlldr_exp.sql:
**********************************************
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool e:\Others\sp_test.txt
select * from region;
spool off
/
**********************************************
»òÕß
**********************************************
set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off
set lines 1024 trimo on trimspo on
set echo off
spool e:\Others\sp_test.txt
select * from dual where dual.dummy = null
/
select * from re
Ïà¹ØÎĵµ£º
p580 ÎÄƽ
³£¼ûÎÊÌâÒ»:°º¹óµÄÊý¾Ý¿âÁ¬½Ó¿ªÏú
ÔÚÓ¦Óÿª·¢ÖУ¬¿Í»§¶ËΪÁËijÖÖÊý¾Ý¿â²Ù×÷¶ø½øÐÐijÖÖÊý¾Ý¿âÁ¬½ÓºÍ¶Ï¿ªµÄ²Ù×÷¡£ÕâÊÇ2000ÄêÇ°ºó¶¯Ì¬ÍøÒ³Ó¦ÓÃÀàÐͳ£¼ûµÄ´íÎó¡£ÔÚÕâÖÖÓ¦ÓÃÖУ¬Ã¿µ±Ò»¸öÓû§µ¥»÷Ò»¸öÍøÒ³£¬Èç¹ûÕâ¸öÍøҳǶÈëÁËÊý¾Ý¿â²Ù×÷£¬Ôò¸ÃÍøÒ³Òª½øÐÐÒ»´Î»òÈô¸É´ÎµÄÊý¾Ý¿âÁ¬½ÓºÍ¶Ï¿ª¡ ......
1.DUAL±íµÄÓÃ;
Dual ÊÇ OracleÖеÄÒ»¸öʵ¼Ê´æÔÚµÄ±í£¬ÈκÎÓû§¾ù¿É¶ÁÈ¡£¬³£ÓÃÔÚûÓÐÄ¿±ê±íµÄSelectÓï¾ä¿éÖÐ
--²é¿´µ±Ç°Á¬½ÓÓû§
SQL> select user from dual;
USER
------------------------------
SYSTEM
--²é¿´µ±Ç°ÈÕÆÚ¡¢Ê±¼ä
SQL> select sysdate from dual;
SYSDATE
-----------
2007-1-2 ......
ÎﻯÊÓͼÊÇÒ»ÖÖÌØÊâµÄÎïÀí±í£¬“Îﻯ”(Materialized)ÊÓͼÊÇÏà¶ÔÆÕͨÊÓͼ¶øÑԵġ£ÆÕͨÊÓͼÊÇÐéÄâ±í£¬Ó¦ÓõľÖÏÞÐÔ´ó£¬ÈκζÔÊÓͼµÄ²éѯ£¬Oracle¶¼Êµ¼ÊÉÏת»»ÎªÊÓͼSQLÓï¾äµÄ²éѯ¡£ÕâÑù¶ÔÕûÌå²éѯÐÔÄܵÄÌá¸ß£¬²¢Ã»ÓÐʵÖÊÉϵĺô¦¡£
1¡¢ÎﻯÊÓͼµÄÀàÐÍ£ºON DEMAND¡¢ON COMMIT
&n ......
delete ɾ³ýÒ»ÕÅ´ó±íʱ¿Õ¼ä²»ÊÍ·Å£¬·Ç³£ÂýÊÇÒòΪռÓôóÁ¿µÄϵͳ×ÊÔ´£¬Ö§³Ö»ØÍ˲Ù×÷£¬¿Õ¼ä»¹±»ÕâÕűíÕ¼ÓÃ×Å¡£
truncate table ±íÃû (ɾ³ý±íÖмǼʱÊͷűí¿Õ¼ä)
DML Óï¾ä£º
±í¼¶¹²ÏíËø£º ¶ÔÓÚ²Ù×÷Ò»ÕűíÖеIJ»Í¬¼Ç¼ʱ£¬»¥²»Ó°Ïì
Ðм¶ÅÅËüËø£º¶ÔÓÚÒ»ÐмǼ£¬oracle »áÖ»ÔÊÐíÖ»ÓÐÒ»¸öÓû§¶ÔËüÔÚͬһʱ¼ä½øÐÐÐ޸IJÙ×÷ ......
Êý¾ÝÀàÐÍ
²ÎÊý
ÃèÊö
char(n)
n=1 to 2000×Ö½Ú
¶¨³¤×Ö·û´®£¬n×Ö½Ú³¤£¬Èç¹û²»Ö¸¶¨³¤¶È£¬È±Ê¡Îª1¸ö×Ö½Ú³¤£¨Ò»¸öºº×ÖΪ2×Ö½Ú£©
varchar2(n)
n=1 to 4000×Ö½Ú
¿É±ä³¤µÄ×Ö·û´®£¬¾ßÌ嶨ÒåʱָÃ÷×î´ó³¤¶Èn£¬
ÕâÖÖÊý¾ÝÀàÐÍ¿ÉÒÔ·ÅÊý×Ö¡¢×ÖĸÒÔ¼°ASCIIÂë×Ö·û¼¯(»òÕßEBCDICµÈÊý¾Ý¿âϵͳ½ÓÊܵÄ×Ö·û¼¯±ê×¼)ÖеÄËùÓзûºÅ¡£
Èç¹ ......