{"id":1530,"date":"2019-02-26T22:43:39","date_gmt":"2019-02-26T14:43:39","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=1530"},"modified":"2020-09-01T15:15:33","modified_gmt":"2020-09-01T07:15:33","slug":"%e6%95%b0%e6%8d%ae%e6%b3%b5%e5%af%bc%e8%87%b4%e8%a1%a8%e9%83%a8%e5%88%86%e5%88%97","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=1530","title":{"rendered":"\u6570\u636e\u6cf5\u5bfc\u51fa\u8868\u90e8\u5206\u5217"},"content":{"rendered":"\n

\u770b\u5230\u7fa4\u91cc\u6709\u4eba\u63d0\u51fa\u4e00\u4e2a\u9700\u6c42\uff0c\u4e00\u5f20\u8868\u6570\u636e\u91cf\u5f88\u5927\uff0c\u53ea\u60f3\u5bfc\u51fa\u5176\u4e2d\u4e00\u90e8\u5206\u5217\u3002<\/p>\n\n\n\n

\u65e0\u8bba\u662f\u8001\u7248\u672cexp\u8fd8\u662f\u6570\u636e\u6cf5expdp\uff0cOracle\u90fd\u63d0\u4f9b\u4e86QUERY\u7684\u529f\u80fd\uff0c\u8fd9\u4f7f\u5f97\u67e5\u8be2\u8868\u4e2d\u90e8\u5206\u8bb0\u5f55\u7684\u529f\u80fd\u53ef\u4ee5\u5b9e\u73b0\uff0c\u4f46\u662fQUERY\u53ea\u80fd\u8fc7\u6ee4\u884c\uff0c\u800c\u4e0d\u80fd\u8fc7\u6ee4\u5217\uff0cOracle\u6570\u636e\u6cf5\u4f1a\u8bfb\u53d6\u8868\u4e2d\u5168\u90e8\u5217\u7684\u3002<\/p>\n\n\n\n

\u572812c\u4e2d\uff0cOracle\u4e3a\u6570\u636e\u6cf5\u63d0\u4f9b\u4e86VIEW\u529f\u80fd\uff0c\u4f7f\u5f97\u5bfc\u51fa\u7684\u65f6\u5019\u53ef\u4ee5\u6839\u636e\u89c6\u56fe\u7684\u5b9a\u4e49\u6765\u5bfc\u51fa\u8868\u4e2d\u7684\u6570\u636e\uff1a<\/p>\n\n\n\n

SQL>\nselect banner from v$version;<\/p>\n\n\n\n

BANNER<\/p>\n\n\n\n

——————————————————————————–<\/p>\n\n\n\n

Oracle\nDatabase 18c Enterprise Edition Release 18.0.0.0.0 – Production<\/p>\n\n\n\n

SQL>\nCREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES;<\/p>\n\n\n\n

\u8868\u5df2\u521b\u5efa\u3002<\/p>\n\n\n\n

SQL>\ncreate view v_tables as select owner, table_name, tablespace_name from t_tables\nwhere owner not in (‘SYS’);<\/p>\n\n\n\n

\u89c6\u56fe\u5df2\u521b\u5efa\u3002<\/p>\n\n\n\n

\u5229\u7528\u6570\u636e\u6cf5\u7684VIEWS_AS_TABLES\u53c2\u6570\u53ef\u4ee5\u76f4\u63a5\u5bfc\u51fa\u89c6\u56fe\u5bf9\u5e94\u7684\u8868\u6570\u636e\uff1a<\/p>\n\n\n\n

C:\\Users\\yangt>expdp\nc##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables<\/p>\n\n\n\n

Export:\nRelease 18.0.0.0.0 – Production on \u661f\u671f\u4e8c 2\u6708 26 13:37:31\n2019<\/p>\n\n\n\n

Version\n18.3.0.0.0<\/p>\n\n\n\n

Copyright\n(c) 1982, 2018, Oracle and\/or its affiliates. \nAll rights reserved.<\/p>\n\n\n\n

\u53e3\u4ee4:<\/p>\n\n\n\n

\u8fde\u63a5\u5230: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 –\nProduction<\/p>\n\n\n\n

\u8b66\u544a: \u8fde\u63a5\u5230\u5bb9\u5668\u6570\u636e\u5e93\u7684\u6839\u6216\u79cd\u5b50\u65f6\u901a\u5e38\u4e0d\u9700\u8981 Oracle Data Pump \u64cd\u4f5c\u3002<\/p>\n\n\n\n

\u542f\u52a8 “C##U1”.”SYS_EXPORT_TABLE_01″:  c##u1\/******** directory=d_output\ndumpfile=t_tab_view views_as_tables=v_tables<\/p>\n\n\n\n

\u5904\u7406\u5bf9\u8c61\u7c7b\u578b TABLE_EXPORT\/VIEWS_AS_TABLES\/TABLE_DATA<\/p>\n\n\n\n

\u5904\u7406\u5bf9\u8c61\u7c7b\u578b TABLE_EXPORT\/VIEWS_AS_TABLES\/TABLE<\/p>\n\n\n\n

. . \u5bfc\u51fa\u4e86\n“C##U1″.”V_TABLES”                          8.289 KB      71 \u884c<\/p>\n\n\n\n

\u5df2\u6210\u529f\u52a0\u8f7d\/\u5378\u8f7d\u4e86\u4e3b\u8868\n“C##U1”.”SYS_EXPORT_TABLE_01″<\/p>\n\n\n\n

******************************************************************************<\/p>\n\n\n\n

C##U1.SYS_EXPORT_TABLE_01\n\u7684\u8f6c\u50a8\u6587\u4ef6\u96c6\u4e3a:<\/p>\n\n\n\n

  D:\\TEMP\\T_TAB_VIEW.DMP<\/p>\n\n\n\n

\u4f5c\u4e1a “C##U1”.”SYS_EXPORT_TABLE_01″ \u5df2\u4e8e \u661f\u671f\u4e8c 2\u6708 26 13:37:40 2019 elapsed 0 00:00:06 \u6210\u529f\u5b8c\u6210<\/p>\n\n\n\n

\u867d\u7136\u662f\u901a\u8fc7\u89c6\u56fe\u5bfc\u51fa\u7684\uff0c\u4f46\u662f\u6570\u636e\u6cf5\u628a\u5b83\u5f53\u4f5c\u4e00\u4e2a\u8868\u6765\u5904\u7406\uff0c\u56e0\u6b64\u8fd9\u4e2adump\u6587\u4ef6\u662f\u53ef\u4ee5\u76f4\u63a5\u5bfc\u5165\u5230\u6570\u636e\u5e93\u4e2d\u53d8\u6210\u8868\u7684\uff1a<\/p>\n\n\n\n

SQL>\ndrop view v_tables;<\/p>\n\n\n\n

\u89c6\u56fe\u5df2\u5220\u9664\u3002<\/p>\n\n\n\n

\u5b8c\u6210\u5bfc\u5165\u64cd\u4f5c\uff1a<\/p>\n\n\n\n

C:\\Users\\yangt>impdp\nc##u1 directory=d_output dumpfile=t_tab_view full=y<\/p>\n\n\n\n

Import:\nRelease 18.0.0.0.0 – Production on \u661f\u671f\u4e8c 2\u6708 26 14:33:02\n2019<\/p>\n\n\n\n

Version\n18.3.0.0.0<\/p>\n\n\n\n

Copyright\n(c) 1982, 2018, Oracle and\/or its affiliates. \nAll rights reserved.<\/p>\n\n\n\n

\u53e3\u4ee4:<\/p>\n\n\n\n

\u8fde\u63a5\u5230: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 –\nProduction<\/p>\n\n\n\n

\u8b66\u544a: \u8fde\u63a5\u5230\u5bb9\u5668\u6570\u636e\u5e93\u7684\u6839\u6216\u79cd\u5b50\u65f6\u901a\u5e38\u4e0d\u9700\u8981 Oracle Data Pump \u64cd\u4f5c\u3002<\/p>\n\n\n\n

\u5df2\u6210\u529f\u52a0\u8f7d\/\u5378\u8f7d\u4e86\u4e3b\u8868\n“C##U1”.”SYS_IMPORT_FULL_01″<\/p>\n\n\n\n

\u542f\u52a8 “C##U1”.”SYS_IMPORT_FULL_01″:  c##u1\/******** directory=d_output\ndumpfile=t_tab_view full=y<\/p>\n\n\n\n

\u5904\u7406\u5bf9\u8c61\u7c7b\u578b TABLE_EXPORT\/VIEWS_AS_TABLES\/TABLE<\/p>\n\n\n\n

\u5904\u7406\u5bf9\u8c61\u7c7b\u578b TABLE_EXPORT\/VIEWS_AS_TABLES\/TABLE_DATA<\/p>\n\n\n\n

. . \u5bfc\u5165\u4e86\n“C##U1″.”V_TABLES”                          8.289 KB      71 \u884c<\/p>\n\n\n\n

\u4f5c\u4e1a “C##U1”.”SYS_IMPORT_FULL_01″ \u5df2\u4e8e \u661f\u671f\u4e8c 2\u6708 26 14:33:07 2019 elapsed 0 00:00:02 \u6210\u529f\u5b8c\u6210<\/p>\n\n\n\n

\u67e5\u8be2\u8868\u6570\u636e\uff1a<\/p>\n\n\n\n

SQL>\nselect count(*) from v_tables;<\/p>\n\n\n\n

  COUNT(*)<\/p>\n\n\n\n

———-<\/p>\n\n\n\n

        71<\/p>\n\n\n\n

SQL>\nselect table_name, tablespace_name from user_tables where table_name =\n‘V_TABLES’;<\/p>\n\n\n\n

TABLE_NAME                     TABLESPACE_NAME<\/p>\n\n\n\n

——————————\n——————————<\/p>\n\n\n\n

V_TABLES                       USERS<\/p>\n\n\n\n

\u8fd9\u4e2a\u529f\u80fd\u662f12c\u7684\u7279\u65b0\u7279\uff0c\u5982\u679c\u662f12c\u4e4b\u524d\u7684\u7248\u672c\uff0c\u53ef\u4ee5\u8003\u8651\u4f7f\u7528ORACLE_DATAPUMP\u7c7b\u578b\u7684\u5916\u90e8\u8868\u6765\u5b9e\u73b0\uff1a<\/p>\n\n\n\n

SQL>\ncreate table t_external_tables (owner, table_name, tablespace_name)\norganization external<\/p>\n\n\n\n

  2 \n(type oracle_datapump<\/p>\n\n\n\n

  3 \ndefault directory d_output<\/p>\n\n\n\n

  4 \nlocation (‘external_table.dp’))<\/p>\n\n\n\n

  5  as\nselect owner, table_name, tablespace_name from t_tables where owner not in\n(‘SYS’);<\/p>\n\n\n\n

\u8868\u5df2\u521b\u5efa\u3002<\/p>\n\n\n\n

\u901a\u8fc7\u5c06\u5916\u90e8\u8868\u5bfc\u51fa\u7684external_table.dp\u6587\u4ef6\u62f7\u8d1d\u5230\u76ee\u6807\u73af\u5883\u5bf9\u5e94\u7684\u76ee\u5f55\u4e0b\uff0c\u6839\u636e\u4e0a\u9762\u7684\u5b9a\u4e49\u91cd\u5efa\u5916\u90e8\u8868\uff0c\u6ce8\u610f\u76ee\u6807\u73af\u5883\u521b\u5efa\u8fc7\u7a0b\u7531\u4e8e\u5df2\u7ecf\u62f7\u8d1d\u4e86\u5bfc\u51fa\u7684\u6570\u636e\uff0c\u56e0\u6b64\u8bed\u6cd5\u622a\u81f3\u5230location\u8bed\u53e5\u5c31\u7ed3\u675f\u4e86\uff0c\u4e0d\u8981\u518d\u5305\u542bas select\u90e8\u5206\u3002<\/p>\n\n\n\n

SQL>\ncreate table t_target_ext_tab (owner varchar2(30), table_name varchar2(128),\ntablespace_name varchar2(30))<\/p>\n\n\n\n

  2 \norganization external<\/p>\n\n\n\n

  3 \n(type oracle_datapump<\/p>\n\n\n\n

  4 \ndefault directory d_output<\/p>\n\n\n\n

  5 \nlocation (‘external_table.dp’));<\/p>\n\n\n\n

\u8868\u5df2\u521b\u5efa\u3002<\/p>\n\n\n\n

SQL>\nselect count(*) from t_target_ext_tab;<\/p>\n\n\n\n

  COUNT(*)<\/p>\n\n\n\n

———-<\/p>\n\n\n\n

        71<\/p>\n\n\n\n

SQL>\ncol table_name for a30<\/p>\n\n\n\n

SQL>\nselect * from t_target_ext_tab where owner = ‘SYSTEM’;<\/p>\n\n\n\n

OWNER                          TABLE_NAME                     TABLESPACE_NAME<\/p>\n\n\n\n

——————————\n—————————— ——————————<\/p>\n\n\n\n

SYSTEM                         OL$<\/p>\n\n\n\n

SYSTEM                         OL$HINTS<\/p>\n\n\n\n

SYSTEM                         OL$NODES<\/p>\n\n\n\n

SYSTEM                         HELP                           SYSTEM<\/p>\n\n\n\n

\u6240\u4ee5\u5bf9\u4e8e11g\u548c10g\u7684\u7248\u672c\uff0c\u4e5f\u53ef\u4ee5\u4e0d\u901a\u8fc7\u65b0\u5efa\u8868\uff0c\u7136\u540e\u62f7\u8d1d\u6570\u636e\u7684\u65b9\u5f0f\u6765\u5b9e\u73b0\u6570\u636e\u7684\u5bfc\u51fa\u548c\u8f6c\u79fb\u3002\u4f46\u662f\u5982\u679c\u662f9i\u53ca\u4ee5\u524d\u7684\u7248\u672c\uff0c\u90a3\u4e48\u8981\u4e0d\u7136\u901a\u8fc7\u5efa\u8868\u7136\u540eexp\u5bfc\u51fa\u7684\u65b9\u5f0f\uff0c\u8981\u4e0d\u7136\u81ea\u5df1\u5199\u7a0b\u5e8f\u5b9e\u73b0\u6570\u636e\u7684\u5bfc\u51fa\u5427\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"

\u770b\u5230\u7fa4\u91cc\u6709\u4eba\u63d0\u51fa\u4e00\u4e2a\u9700\u6c42\uff0c\u4e00\u5f20\u8868\u6570\u636e\u91cf\u5f88\u5927\uff0c\u53ea\u60f3\u5bfc\u51fa\u5176\u4e2d\u4e00\u90e8\u5206\u5217\u3002 \u65e0\u8bba\u662f\u8001\u7248\u672cexp\u8fd8\u662f\u6570\u636e\u6cf5expdp\uff0cOracle\u90fd\u63d0\u4f9b\u4e86QUERY\u7684\u529f\u80fd\uff0c\u8fd9\u4f7f\u5f97\u67e5\u8be2\u8868\u4e2d\u90e8\u5206\u8bb0\u5f55\u7684\u529f\u80fd\u53ef\u4ee5\u5b9e\u73b0\uff0c\u4f46\u662fQUERY\u53ea\u80fd\u8fc7\u6ee4\u884c\uff0c\u800c\u4e0d\u80fd\u8fc7\u6ee4\u5217\uff0cOracle\u6570\u636e\u6cf5\u4f1a\u8bfb\u53d6\u8868\u4e2d\u5168\u90e8\u5217\u7684\u3002 \u572812c\u4e2d\uff0cOracle\u4e3a\u6570\u636e\u6cf5\u63d0\u4f9b\u4e86VIEW\u529f\u80fd\uff0c\u4f7f\u5f97\u5bfc\u51fa\u7684\u65f6\u5019\u53ef\u4ee5\u6839\u636e\u89c6\u56fe\u7684\u5b9a\u4e49\u6765\u5bfc\u51fa\u8868\u4e2d\u7684\u6570\u636e\uff1a SQL> select banner from v$version; BANNER ——————————————————————————– Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 – Production SQL> CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES; \u8868\u5df2\u521b\u5efa\u3002 SQL> create view v_tables as select owner, table_name, tablespace_name from t_tables … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[341,27,1176],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-oG","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/1530"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1530"}],"version-history":[{"count":2,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/1530\/revisions"}],"predecessor-version":[{"id":1533,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/1530\/revisions\/1533"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}