Oracle sqlplus copyÃüÁîÓ÷¨
±¾ÎĽéÉÜÁËÈçºÎÀûÓÃsqlplus copy ÃüÁîÔÚÁ½¸öÊý¾Ý¿â¼ä×ªÒÆÊý¾Ý
ÎÞÐèÓõ½dblink, Á½¸öÊý¾Ý¿â¼ä²»ÐèÖ±½ÓͨѶ£¬µ±È»£¬ÐèÒªÓÐÒ»¸öclient¶ÎÄÜͬʱÒÔsqlplusÁ¬½Óµ½Á½¸öÊý¾Ý¿â
ÎÊÌâµÄÌá³ö
ÂÛ̳ÉÏÓÐÈËÌá³öÕâÑùµÄÎÊÌ⣺
¼ÙÉèÓÐÁ½¸öÊý¾Ý¿â,·Ö±ð´¦ÓÚÁ½¸ö²»Í¬µÄÍøµ«ÓÐÒ»¸ö¿Í»§»ú°²ÁËÁ½¿éÍø¿¨¿ÉÒÔͬʱÁ¬µ½Á½¸öÊý¾Ý¿âÇëÎÊÈç¹û²»Í¨¹ýÔÚ¿Í»§»úÉϽ¨ÖÐת±í,ÓÐûÓа취ʵÏÖÕâÁ½¸öÊý¾Ý¿âÖдÓijһ¸öÍùÁíÒ»¸ö¿½±í.
ÎÊÌâµÄ½â´ð
¿ÉÒÔʹÓÃsqlplusµÄcopyÃüÁîÀ´´ïµ½¡£
copyµÄÃüÁîµÄÕâ¸öÑù×ӵģº
usage: COPY from <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
<db> : database string
<opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
<table>: name of the destination table
<cols> : a comma-separated list of destination column aliases
<sel> : any valid SQL SELECT statement
ʵ¼ÊÉÏдµÄÓ¦¸ÃºÜÇå³þÁË£¬°´ÕÕÄÇÑùµÄÓï·¨¾Í¿ÉÒÔÍê³ÉÁË¡£
ËüÏ൱ÓÚ´Ófrom DB¸ù¾Ý<sel>°ÑÊý¾ÝÈ¡µ½»º³åÇø£¬ÔÙ¸ù¾Ý<opt> ÔÚTO DBÖд´½¨table(create)»òÕß ÖØÐ´´½¨table(replace)£¬È»ºó°ÑÊý¾Ýinsert»òÕßappend insert µ½TO DBµÄtableÖС£
Ø ÓÐÒ»µãÐèҪעÒâµÄ£¬Í¨³£»áÉèÖøßÒ»µãµÄarraysize£¬ÒÔ±ãʹsqlplusÓëDBµÄ½»»¥¼õÉÙ£¬ÒÔ´ïµ½¸üºÃµÄÐÔÄÜ¡£
Ø ÁíÍ⣬ÔÚдdatabase stringʱ£¬¿ÉÒÔ²»±ØÐ´³öÃÜÂ룬sqlplus»áÉÔºó»áÌáʾÄãÊäÈëÃÜÂë¡£
Ò»¸öÍêÕûµÄÀý×Ó:
sqlplus /nolog
SQL> set arraysize 5000
SQL> copy from serol/luo@mescp to serol/luo@ractest create test_copy_table using select * from dba_objects;
Array fetch/bind size is 5000. (arraysize is 5000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
SQLRCN in cpytbl failed: -2120
Table TEST_COPY_TABLE created.
12579 rows selected from serol@mescp.
12579 rows inserted into TEST_COPY_TABLE.
12579 rows committed into TEST_COPY_TABLE at serol@ractest.
Ïà¹ØÎĵµ£º
ËäȻѧϰJavaºÜ¾ÃÁË£¬×Ô¼ºÒ²Á¬½Ó¹ýһЩÊý¾Ý¿â£¬±ÈÈçmysqlÖ®ÀàµÄ£¬Èç½ñÄØ£¬Ò²Ñ§Ï°ÁËÒ»¶Îʱ¼äµÄOracle£¬È»¶øÄØ£¬½ñÌìÊÇÎÒµÚÒ»´ÎÁ¬½ÓOracle£¬ºÙºÙ£¬Ó¦¸Ã»¹²»ËãÌ«³Ù°É¡£
½ñÌìÄØ£¬Óе㱿׾£¬´ó¼ÒĪЦ£¡
ÎÒÕâÊÇÒ»¸ö²éѯÀý×Ó
Ê×ÏÈ£¬Ô ......
Óï·¨:
select *
from [TABLE] as of timestamp
to_timestamp('ʱ¼ä', ’ʱ¼ä¸ñʽ')
×÷Óãº
²éѯij¸öʱ¼äµãµÄÊý¾Ý£¬ÔÚÕâ¸öʱ¼äµãÖ®ºó£¬Êý¾Ý¸ü¸ÄÒѾÌá½»ÁË¡£
¿ÉÒÔÓÃÀ´¸üÕýÓû§¶ÔÊý¾ÝµÄÎó²Ù×÷
¿ÉÒÔÓÃÀ´»ñÈ¡Êý¾ÝµÄ¸ü¸ÄÇé¿ö£¬±ÈÈçÆµÂʵÈ
ÔÀí£º
µ±Êý¾Ýupdate»òdeleteʱ£¬ÔÀ´µÄÊý¾Ý ......
1.ÐÞ¸Ä/etc/oratab £¬Ìí¼Ó$ORACLE_SID:$ORACLE_HOME:Y --
Y´ú±íOSÆô¶¯ÔòDBÆô¶¯±ØÐëÉèÖÃΪY£¬·ñÔòdbstartºÍdbstop²»¿ÉÓã¬NΪ²»Æô¶¯£¬$ORACLE_SIDÊÇDB
SID£¬$ORACLE_HOMEÊÇDB ¾ø¶Ô·¾¶
2.ÐÞ¸Ä/etc/rc.d/rc.loacl,¼ÓÈëÒÔÏ£º
#listener command
COMM_LISTENER=/opt/oracle/product/10.2.0/db_1/bin/lsnrctl
L ......
1.¼¯ºÏ²Ù×÷
ѧϰoracleÖм¯ºÏ²Ù×÷µÄÓйØÓï¾ä£¬ÕÆÎÕunion,union all,minus,interestµÄʹÓÃ,Äܹ»ÃèÊö½áºÏÔËË㣬²¢ÇÒÄܹ»½«¶à¸ö²éѯ×éºÏµ½Ò»¸ö²éѯÖÐÈ¥£¬Äܹ»¿ØÖÆÐзµ»ØµÄ˳Ðò¡£
°üº¬¼¯ºÏÔËËãµÄ²éѯ³ÆÎª¸´ºÏ²éѯ¡£¼û±í¸ñ1-1
±í1-1
Operator Returns   ......
SQLÖеĵ¥¼Ç¼º¯Êý
1.ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from d ......