ORACLEÖÐSQLÈ¡×îºóÒ»Ìõ¼Ç¼µÄ¼¸ÖÖ·½·¨
ÔÚETL¹ý³ÌÖУ¬¾³£»áÅöµ½È¡½á¹û¼¯µÄ×îºó»ò×îǰһÌõ¼Ç¼¡£ÈçÈ¡»îÆÚ´æ¿îµÄµ±Ç°ÀûÂÊ£¬¿ª»§½ð¶î£¬Ð¶¨ÀûÂʵȡ£Èç¹û²»ÓÃLOOKUPµÄ·½Ê½£¬Èçͨ¹ýÓαêÈ¡»òÕßETL¹¤¾ßLOOKUP×é¼þʲôµÄ£¬ÔÚÒ»ÌõSQLÀïʵÏÖ£¬Ä¿Ç°ÊµÏÖÓм¸ÖÖ·½·¨¡£
1.ÒÔʱ¼ä»òÆäËû×ֶηÖ×éºóÔÚ×ÔÁ¬×Ô¼º£¬ÕâÑù²»½ö¿ÉÒÔ´ø³öÐèÒªLOOKUPµÄ×ֶΣ¬»¹¿ÉÒÔ´ø³öÆäËûÐèÒªµÄ×ֶΡ£
SELECT A.CDDPTY CDDPTY,A.CDCURR CDCURR,A.CDVLDT CDVLDT,
A.CDYRAT CDYRAT
from DCPPDATA.TBBFMCDRT A INNER JOIN
(SELECT B.CDDPTY,B.CDCURR,MAX(B.CDVLDT) CDVLDT
from DCPPDATA.TBBFMCDRT B
GROUP BY B.CDDPTY, B.CDCURR) C
ON A.CDDPTY =C.CDDPTY
AND A.CDCURR =C.CDCURR
AND A.CDVLDT =C.CDVLDT
2.ÓÃROW_NUMBER() OVER(ORDER BY filedName)
SELECT B.CDDPTY,B.CDCURR,
ROW_NUMBER() OVER(ORDER BY B.CDVLDT DESC)
from DCPPDATA.TBBFMCDRT B
WHERE ROWNUM = 1
Ïà¹ØÎĵµ£º
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
³£ÒªoracleÊý¾Ý¿â¶¨Ê±µÄ×Ô¶¯Ö´ÐÐһЩ½Å±¾£¬»ò×öÊý¾Ý¿â±¸·Ý£¬»ò×öÊý¾ÝµÄÌáÁ¶£¬»ò×öÊý¾Ý¿âµÄÐÔÄÜÓÅ»¯£¬°üÀ¨Öؽ¨Ë÷ÒýµÈµÈµÄ¹¤×÷,ÕâʱÐèÒªÓõ½Ò»¸öº¯Êýdbms_job.submit£¬À´Íê³ÉOracle¶¨Ê±Æ÷Jobʱ¼äµÄ´¦ÀíÉÏ¡£Ê¹ÓÃdbms_job.submitÕâ¸öº¯Êý£¬ÎÒÃÇÖ»ÐèÒª¿¼ÂÇÁ½¸öÊÂÇ飺°²ÅÅijһÈÎÎñ£¬ºÍ¶¨ÖÆÒ»¸öÖ´ÐÐÈÎÎñµÄʱ¼äµã¡£µ«×îÖØÒªÒ²ÊÇ× ......
Ò»¡¢ORACLEµÄÆô¶¯ºÍ¹Ø±Õ
1¡¢ÔÚµ¥»ú»·¾³ÏÂ
ÒªÏëÆô¶¯»ò¹Ø±ÕORACLEϵͳ±ØÐëÊ×ÏÈÇл»µ½ORACLEÓû§£¬ÈçÏÂ
su - oracle
a¡¢Æô¶¯ORACLEϵͳ
oracle>svrmgrl
SVRMGR>connect internal
SVRMGR>startup
SVRMGR>quit
b¡¢¹Ø±ÕORACLEϵͳ
oracle>svrmgrl
SVRMGR>connect internal
SVRMGR>shutdown
SVR ......
µ¼³ö. ÏëÒªµ¼³öÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐEXP_FULL_DATABASE½ÇÉ«
exp Óû§Ãû/ÃÜÂë file=·¾¶ tables=(±íÃû1,±íÃû2,...) µ¼Èë. ÏëÒªµ¼ÈëÆäËûÓû§µÄ¶ÔÏóÐèÒªÓÐIMP_FULL_DATABASE½ÇÉ«
imp Óû§Ãû/ÃÜÂë file=·¾¶ Êý¾Ý±Ãµ¼³ö
a.) Ö»Äܵ¼³öµ½Oracle¹Ü¿ØÄ¿Â¼. ¿ÉÒÔʹÓÃĬÈϵÄDATA_PUMP_DIR
b.) ²éѯ ......
È·ÈÏÉÁ»ØÆôÓÃÖÐ
SHOW PARAMETER RECYCLEBIN; ÆôÓÃÉÁ»Ø
ALTER SYSTEM SET RECYCLEBIN = ON; ÉÁ»ØDROPµÄ±í
FLASHBACK TABLE xxx TO BEFORE DROP; ³¹µ×Çå³ýDROPµÄ±í,½«²»ÄÜÔÙÉÁ»Ø.
PURGE TABLE xxx; Ö±½Ó³¹µ×DROPµô±í
DROP TABLE xxx PURGE; Çå¿ÕËùÓÐDROPµÄ±í
PURGE RE ......