Oracle常见数据导入导出整理

设置NLS_LANG : export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 --1 导出26表数据 pub开头

exp mpaccs/mpaccs@192.168.0.26/mds file=/oradata/mdsbiaoshuju_pub26.dmp log=/oradata/mdsbiaoshuju_pub26.log statistics=none buffer=8192000 tables=PUB_APP,PUB_CANT,PUB_CANT_TYPE,PUB_CMD_LOG,PUB_COMMON_SINGLE_PARMS,PUB_CONF_ROLES,PUB_CONF_ROLESET,PUB_COUNTRY,PUB_DATA_RES_TYPE,PUB_DOC_CONTENT,PUB_DOMAIN,PUB_DOMAIN_ADMIN,PUB_DOMAIN_RESOURCE,PUB_DOMAIN_RESOURCE_BAK1,PUB_DOMAIN_STRU,PUB_D_DEV_BATCH_INFO_BAK,PUB_EXAMPLE_ORGAN,PUB_FIN_MONTH,PUB_FIN_MPERIOD,PUB_FIN_PTYPE,PUB_FIN_YEAR,PUB_FUNCTIONS,PUB_GET_BACK_PASSWORD,PUB_GLOBAL,PUB_GLOBALPARMS,PUB_HR_EVENT,PUB_HR_EVENT_TYPE,PUB_IDTABLE,PUB_ISC_FUNCTIONS,PUB_LDAP_USER,PUB_MENU_PERITEM,PUB_MENU_STRU,PUB_MENU_STRU_TYPE,PUB_MENU_SYS,PUB_MENU_SYSITEM,PUB_MENU_TEMPLATES,PUB_MENU_TYPE,PUB_MODULES,PUB_ONLINE,PUB_ONLINE_HIST,PUB_ORGAN,PUB_ORGAN_HIST,PUB_ORGAN_PARMS,PUB_ORGAN_PARMS_T,PUB_ORGAN_TYPE,PUB_ORGAN_WORKDAY,PUB_PARA,PUB_PASSWORD_HIST,PUB_PERMIT,PUB_RESOURCES,PUB_RES_EXT_TYPE,PUB_RES_TYPE,PUB_ROLES,PUB_ROLE_ACCESS,PUB_ROLE_PERMIT,PUB_ROLE_PRIVS,PUB_ROLE_RESOURCE,PUB_ROLE_RESOURCE_BAK,PUB_ROLE_RESOURCE_BAK1,PUB_ROLE_TYPE,PUB_SAME_CLIENT,PUB_SCN_INFO,PUB_SECURITY_LEVEL,PUB_SECURITY_LOG,PUB_SECURITY_LOG_BAK_20170525,PUB_SESSION,PUB_STRU,PUB_STRU_HIST,PUB_STRU_RULE,PUB_STRU_TYPE,PUB_STRU_TYPE_REF,PUB_STRU_VERSION,PUB_TEMPLATES,PUB_TMENU_SYS,PUB_TMENU_SYSITEM,PUB_TROLES,PUB_TROLE_OPTS,PUB_TROLE_PERMIT,PUB_URLS,PUB_USERROLE_AUDIT,PUB_USERS,PUB_USER_AUDIT,PUB_USER_DATAREF,PUB_USER_FUNC_RESOURCE,PUB_USER_FUNC_RESOURCE_BAK,PUB_USER_FUNC_RESOURCE_BAK1,PUB_USER_HIST,PUB_USER_IP,PUB_USER_MAP,PUB_USER_MENU,PUB_USER_PARMS,PUB_USER_PROFILE,PUB_USER_PROXY,PUB_USER_RES_DATAREF,PUB_USER_ROLE,PUB_USER_ROLE_BAK,PUB_USER_STRU_TEMP,PUB_USER_TIME,PUB_WORKPLACE

1.1 导出18所有数据 exp bm/bm@192.168.0.18/mds file=/oradata/mdsbiaoshuju_all18.dmp log=/oradata/mdsbiaoshuju_all18.log statistics=none buffer=8192000 owner=bm

1.1.1 导出26所有数据 exp mpaccs/mpaccs@192.168.0.2/mds file=/oradata/mdsbiaoshuju_all22610.dmp log=/oradata/mdsbiaoshuju_all22610.log statistics=none buffer=8192000 owner=mpaccs

1.1.1.1 导出56表数据 pub开头 -更新 exp mpaccs/mpaccs@192.168.0.56/mds file=/oradata/mdsbiaoshuju.dmp log=/oradata/mdsbiaoshuju.log statistics=none buffer=8192000 tables=PUB_USER_PARMS,PUB_CONF_ROLES,PUB_CONF_ROLESET,PUB_COUNTRY,PUB_DATA_RES_TYPE,PUB_DOMAIN,PUB_DOMAIN_ADMIN,PUB_DOMAIN_RESOURCE,PUB_DOMAIN_STRUPUB_D_DEV_BATCH_INFO_BAK,PUB_FUNCTIONS,PUB_GET_BACK_PASSWORD,PUB_GLOBAL,PUB_GLOBALPARMS,PUB_HR_EVENT,PUB_HR_EVENT_TYPE,PUB_IDTABLE,PUB_MENU_PERITEM,PUB_MENU_STRU,PUB_MENU_STRU_TYPE,PUB_MENU_SYS,PUB_MENU_SYSITEM,PUB_MENU_TEMPLATES,PUB_MENU_TYPE,PUB_MODULES,PUB_ONLINE,PUB_ONLINE_HIST,PUB_ORGAN,PUB_ORGAN_HIST,PUB_ORGAN_TYPE,PUB_PARA,PUB_PASSWORD_HISTPUB_RESOURCES,PUB_RES_EXT_TYPE,PUB_RES_TYPE,PUB_ROLES,PUB_ROLE_ACCESS,PUB_ROLE_PRIVS,PUB_ROLE_RESOURCE,PUB_ROLE_RESOURCE_BAK,PUB_SCN_INFO,PUB_SECURITY_LEVEL,PUB_SECURITY_LOG,PUB_SESSION,PUB_STRU,PUB_STRU_HIST,PUB_STRU_RULE,PUB_STRU_TYPE,PUB_STRU_TYPE_REF,PUB_STRU_VERSION,PUB_TEMPLATES,PUB_TMENU_SYS,PUB_TMENU_SYSITEM,PUB_TROLESPUB_TROLE_OPTS,PUB_URLS,PUB_USERROLE_AUDIT,PUB_USERS,PUB_USER_DATAREF,PUB_USER_FUNC_RESOURCE,PUB_USER_FUNC_RESOURCE_BAK,PUB_USER_PROFILE,PUB_USER_ROLE,PUB_USER_STRU_TEMP,PUB_WORKPLACE,PUB_USER_HIST,PUB_APP,PUB_CMD_LOG,PUB_COMMON_SINGLE_PARMS,PUB_DOC_CONTENT,PUB_EXAMPLE_ORGAN,PUB_FIN_MONTHP,UB_FIN_MPERIOD,PUB_FIN_PTYPE,PUB_FIN_YEAR,PUB_ISC_FUNCTIONS,PUB_LDAP_USER,PUB_ORGAN_PARMS,PUB_ORGAN_PARMS_T,PUB_ORGAN_WORKDAY,PUB_PERMIT,PUB_ROLE_PERMIT,PUB_ROLE_TYPE,PUB_SAME_CLIENT,PUB_TROLE_PERMIT,PUB_USER_AUDIT,PUB_USER_MAP,PUB_USER_MENU,PUB_USER_RES_DATAREF,PUB_ISC_USERS,PUB_FUNCTION_MENU,PUB_ISC_DOMAINS,PUB_ISC_MENUS,PUB_ISC_PERMIT,PUB_ISC_ROLES,PUB_ISC_RESOURCES,PUB_ISC_ORGANS

--2导入部分表数据至63 imp mpaccs/mpaccs@192.168.0.63/mds fromuser=mpaccs touser=mpaccs FILE=/oradata/mdsbiaoshuju_wf26.dmp log=/oradata/mdsbiaoshuju_wf26.log statistics=none buffer=8192000 ignore=y commit=y

--3 导出26库所有表结构 exp mpaccs/mpaccs@192.168.0.26/mds file=/oradata/mdsbiaojiegou_all226.dmp log=/oradata/mdsbiaojiegou_all226.log owner=mpaccs rows=n

--4 导入所有对象表数据/表结构 export NLS_LANG=AMERICAN_AMERICA.UTF8 imp mpaccs/mpaccs@192.168.0.63/mds fromuser=mpaccs touser=mpaccs FILE=/oradata/mdsbiaoshuju_all22610.dmp log=/oradata/mdsbiaoshuju_all22610.log statistics=none buffer=8192000 ignore=y

--5导入部分表数据（可以指定表） export NLS_LANG=AMERICAN_AMERICA.UTF8 imp mpaccs/mpaccs@192.168.0.63/mds fromuser=mpaccs touser=mpaccs FILE=/oradata/mdsbiaoshuju_wf26.dmp log=/oradata/mdsbiaoshuju_wf26.log statistics=none buffer=8192000 tables=WFACTIVITYINST,WFAGENT,WFAGENTITEM,WFAGENTSCOPE,WFAPIMSG_X,WFAPIMSG_X_INDEX,WFAPPMSG_X,WFAPPMSG_X_INDEX,WFAPPRMSG_X,WFAPPRMSG_X_INDEX,WFAUDITRECORD,WFBIZINFO,WFBIZMAPPING,WFBIZ_CALENDAR_DETAIL,WFBIZ_CALENDAR_INFO,WFBIZ_CALEPARTI_RELATION,WFBIZ_CATALOG_INFO,WFBIZ_CATAPERM_RELATION,WFBIZ_EXTSUBPROCMONITOR_INFO,WFBIZ_EXTSUBPROCMONITOR_INST,WFBIZ_EXTSUBPROCSERVICE_INFO,WFBIZ_EXTSUBPROCSERVICE_INST,WFBIZ_HUMANTASK_INFO,WFBIZ_HUMANTASK_INST,WFBIZ_OBJECT_INFO,WFBIZ_OBJECT_INST,WFBIZ_OBTATTRIBUTE_INFO,WFBIZ_OBTATTRIBUTE_INST,WFBIZ_OPERATION_INFO,WFBIZ_OPERATION_INST,WFBIZ_PARAMETER_INFO,WFBIZ_PARAMETER_INST,WFBIZ_RES_RELATION,WFBIZ_RULE_INFO,WFBIZ_RULE_INST,WFBIZ_SERVICE_INFO,WFBIZ_SERVICE_INST,WFBIZ_VAR_INFO,WFBIZ_VAR_INST,WFEXTSUBPROCMAPPER,WFEXT_RESFILE_INFO,WFEXT_RESLIB_INFO,WFEXT_RESTYPE_INFO,WFEXT_RESTYPE_RELATION,WFMSG_EXECUTOR,WFNODE_STATUS,WFPERSONINFO,WFPROCESSDEFINE,WFPROCESSDEFINETEMP,WFPROCESSDEFINE_BAK,WFPROCESSINST,WFPROCESSINSTATTR,WFSYSTEMINFO,WFTIMER,WFTRANSITION,WFWIPARTICIPANT,WFWORKITEM,WFWORKITEMHISTORY,WF_EXT_PUSHTASK,WF_H_ACTIVITYINST,WF_H_BIZINFO,WF_H_PROCESSINST,WF_H_PROCESSINSTATTR,WF_H_TRANSITION,WF_H_WIPARTICIPANT,WF_H_WORKITEM,WF_INVOCATION_CONTEXT,WF_MULTISYS_ORG,WF_MULTISYS_RELATION,WF_MULTISYS_USER,WF_RESOURCE_CHANGED_INFO,WF_RESOURCE_CHANGED_SEQ,WF_SYS_VAR,WF_WS_SECURITY

查询oracle server端的字符集：select userenv('language') from dual; *************************** windows下导出数据 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

exp mpaccs/mpaccs@192.168.0.2/mds file=D:\oradata\mdsbiaoshuju_all22610.dmp log=D:\oradata\mdsbiaoshuju_all22610.log statistics=none buffer=8192000 owner=mpaccs ***************************

**************************************** 其他 **************************************** 创建表空间：CREATE TABLESPACE BM   DATAFILE '/oradata/mdsdata/mdsmpac001' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE MPAC OFFLINE; DROP TABLESPACE MPAC INCLUDING CONTENTS AND DATAFILES; DROP USER LCAM CASCADE;