linux给oracle导入sql语句,Linux中部署Oracle数据库常用的sql语句

--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 导出56所有数据 exp mpaccs/mpaccs@192.168.0.56/mds file=/oradata/mdsbiaoshuju_all.dmp log=/oradata/mdsbiaoshuju_all.log statistics=none buffer=8192000 owner=mpaccs

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

1.2 导出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_pub26.dmp log=/oradata/mdsbiaoshuju_pub26.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 导入所有对象表数据/表结构 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

设置NLS_LANG : export NLS_LANG=AMERICAN_AMERICA.UTF8

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

查询oracle server端的字符集：select userenv(‘language‘) from dual;

Linux中部署Oracle数据库常用的sql语句

标签：utf8   info   min   local   workplace   common   data   创建表   creat

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/yh62-mds/p/10441794.html