create temporary tablespace tbs_tmp_zljyhis
tempfile 'D:\oracle\oradata\Oracle11\tbs_tmp_zljyhis.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第2步:创建数据表空间 */
create tablespace tbs_data_zljyhis
logging
datafile 'D:\oracle\oradata\Oracle11\tbs_data_zljyhis.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*第3步:创建索引表空间 */
create tablespace tbs_idx_zljyhis
logging
datafile 'D:\oracle\oradata\Oracle11\tbs_idx_zljyhis.dbf'
size 50m
autoextend on
next 50m maxsize 2048m
extent management local;
/*第4步:创建用户并指定表空间 */
create user zljyhis identified by zljy1223
default tablespace tbs_data_zljyhis
temporary tablespace tbs_tmp_zljyhis;
/*给已有用户指定表空间
alter user zljyhis
default tablespace tbs_data_zljyhis
temporary tablespace tbs_tmp_zljyhis;;
*/
/*第5步:给用户授予权限 */
grant connect,resource,dba to zljyhis ;
--导入备份 zljyhis/zljy1223@zljyhis(用户名/密码@实例名),file=备份文件的位置, log记录导入时的一些信息
imp zljyhis/zljy1223@zljyhis buffer=64000 FILE=d:zljyhis.dmp log=d:\zljyhis_imp.log full=y ignore=y
----------------------------------------------------------------------------------------------------------------------------------------------------------------
如果失败可以删除用户重建
select sid,serial# from v$session where username='user_name';//注意user_name区分大小写
alter system kill session 'sid,serial';
alter system kill session '527,2832';
然后再执行
drop user zljyhis cascade;
/*第4步:创建用户并指定表空间 */
create user zljyhis identified by zljy1223
default tablespace tbs_data_zljyhis
temporary tablespace tbs_tmp_zljyhis;
/*第5步:给用户授予权限 */
grant connect,resource,dba to zljyhis ;
--导入备份 zljyhis/zljy1223@zljyhis(用户名/密码@实例名),file=备份文件的位置, log记录导入时的一些信息
imp zljyhis/zljy1223@zljyhis buffer=64000 FILE=d:zljyhis.dmp log=d:\zljyhis_imp.log full=y ignore=y
数据库备份
数据导出:
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"