1.登录:
sys/666666 as sysdba 或者是cmd的 C:\Users\Administrator>sqlplus sys/666666 as sysdba(cmd下进入
SQL可以复制粘贴指令)
2.创建表空间:
create tablespace BANKS datafile 'D:\oracle\datafile\BANKS.DBF' size 50M autoextend on;
(导入的表空间需要和导出时的表空间相同吗 ?教学端的表空间要求BANKS)
3.删除用户:包括旗下所有的设备
drop user banksbak cascade;
4.创建用户:
create user banksbak identified by 666666 default tablespace bankis
create user teachingNewss identified by 666666 default tablespace BANKS
5.用户赋权:
grant connect,resource to banksbak ----有可能要赋权dba才能导出
grant connect,resource to teachingNewss
6.导入数据:在cmd下执行,不能有分号
imp system/123456@10.1.130.95/orcl file = D:\GMJX1.1\databak\banksbak20151203.dmp fromuser=banksbak touser=banksbak
(不能有分号, fromuser 和 full=y 不能同时使用;指定导出用户可解决字符集不一致的问题)
imp system/123456@10.1.130.95/orcl file = D:\GMJX1.1\databak\banksbak20151203.dmp fromuser=banksbak touser=banksbak
full=y ignore=y
(会有字符集不一致的问题)
7.导出数据:
exp banksbak/666666@10.1.130.95/orcl file = D:\GMJX1.1\databak0330-1\banksbak.dmp wner=banksbak
exp teachingNewss/666666@10.1.130.95/orcl file = D:\GMJX1.1\databak0330\teaching.dmp wner=teachingNewss
======================================================
*****其他常用SQL:
1.删除表空间,同时删除数据文件:
drop tablespace BANKS including contents and datafiles;
2.查看所有用户
select username from dba_users;
3.重启
oracleSQL> shutdown immediate
SQL> startup
4.查看所有表空间名称
select tablespace_name from dba_tablespaces
5.删除表空间:慎用
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
6.查询表所在的表空间:表名一定要大写
select * from user_tables where table_name='表名';