准备工作
第一步: 创建教师和学生用户
- 教师用户/密码 TEACHER/t123456
- 学生用户/密码 STUDENT/s123456
参考链接 http://www.cnblogs.com/whatlonelytear/articles/5009464.html#createUser
第二步: 创建教师表空间和学生表空间
http://www.cnblogs.com/whatlonelytear/articles/5009464.html#createTablespace
- 参考以上链接,创建教师表空间TBS_TEACHER和学生表空间TBS_STUDENT ,
第三步: 以指定用户创建教师表和学生表
- 以学生用户登录创建学生表(不用)
- 以老师用户登录创建老师表1(teacher1)和教师表2(teacher2)
学生表样例sql
--建学生表 create table STUDENT ( id INTEGER not null, name VARCHAR2(100), age INTEGER ); CREATE INDEX IDX_STU_NAME ON STUDENT(NAME);--添加索引 ALTER TABLE STUDENT ADD CONSTRAINT CST_ID PRIMARY KEY(ID);--添加主键 COMMENT ON COLUMN STUDENT.NAME IS '姓名';--添加注释 INSERT INTO STUDENT (ID, NAME, AGE) VALUES (1, 'aaa', 10); INSERT INTO STUDENT (ID, NAME, AGE) VALUES (2, 'bbb', 12); INSERT INTO STUDENT (ID, NAME, AGE) VALUES (3, 'ccc', 11); COMMIT;
第三步: 给学生和教师指定默认表空间
-- 修改用户表空间 ALTER USER student DEFAULT TABLESPACE TBS_STUDENT; ALTER USER teacher DEFAULT TABLESPACE TBS_TEACHER; -- 回收unlimited tablespace无限表空间权限,不让student使用之前的user表空间其它空间 revoke unlimited tablespace from student; -- 将student用户在 TBS_TEACHER 表空间的配额置为 0 alter user student quota 0 on TBS_TEACHER; -- 设置student用户在 TBS_STUDENT 表空间配额不受限 alter user student quota unlimited on TBS_STUDENT; -- 查看用户表空间配额,MAX_BYTES为0代表用户对表空间无使用权,为-1代表可以无限使用 SELECT * FROM USER_TS_QUOTAS;
我的导出导入语句
我的目标 : 把TEACHER用户下的teacher1表导从TBS_TEACHER表空间导入到STUDENT用户下的TBS_STUDENT表空间下.
参数说明如下:
- ROWS=N 表示只导表结构,不导表数据 (导出数据行数,N代表0行)
- TABLES=表示导哪些表,和FULL不可共存
- IGNORE=y 表示忽略创建错误，继续后面的操作
- FILE=xxx.dmp 表示要导入导出的dmp文件位置
- LOG=xxx.log 表示导入导出时的日志
- BUFFER=1024000 表示数据行的缓冲区大小，单位Byte默认值根据系统而定,1024000是一个比较好的值，这也是别人的经验。回滚端的问题主要出现在commit选项，大数据量的exp和 imp中commit=y可以避免回滚段益出。
- FULL=y 表示全库导出: 导出除ORDSYS,MDSYS,CTXSYS,ORDPLUGINS,LBACSYS 这些系统用户之外的所有用户的数据.
-------导出,导出teacher1,teacher2表---------- exp "TEACHER/t123456@1.2.3.4:1521/orcl" ROWS=N BUFFER=1024000 tables=teacher1,teacher2 file=/data/oradata/dmp/backup.dmp LOG=/DATA/logs/exp.log -------导入,只导入teacher1表---------- imp "STUDENT/s123456@1.2.3.4:1521/orcl" ROWS=N tables=teacher1 BUFFER=1024000 IGNORE=y file=/data/oradata/dmp/backup.dmp log=/data/logs/imp.log IGNORE=y fromuser=TEACHER touser=STUDENT
然而导入到student用户下的teacher1表的表空间还是TBS_TEACHER, 上面的语句是不可能实现表空间的TEACHER表迁移的,原因参考下一小节.
- 使用UltraEdit CTRL+H来批量修改表空间------what a ball shit
- 指定fromuser touser shit 方法: 不启作用------what a ball shit
如果我们有dba权限,那这个时候最好使用expdp和impdp服务端程序,它可以满足要求.
imp和dmp导入导出用户表空间touser问题 #重点#
同一个dmp文件在导入生产库的时候导入的表空间是 a_tbs （目标a用户的默认表空间）,在导入测试环境的时候导入的表空间是 users .
使用的导入脚本也是完全相同的,所以这里就涉及了imp的导入机制
经过测试发现：
imp在使用touser进行制定用户导入的时候，导入哪个表空间不是由目标用户决定，
而是由导出用户fromuser决定的。
比如：
文件是由 s 用户导出，对应的表空间是 users , 导入到用户 a, 对应的表空间是 a_tbs
导入的时候如果目标环境有users表空间，就算使用了touser参数，也会导入到users表空间下，而如果目标环境没有users表空间，就会导入touser用户的默认表空间了。
主要参考链接: imp使用touser导入表空间问题
表空间其它相关辅助语句
-- 查看表空间的名称及大小 SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024)), 0) TS_SIZE FROM DBA_TABLESPACES T, DBA_DATA_FILES D WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME GROUP BY T.TABLESPACE_NAME; -- 查看表空间物理文件的名称及大小 SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME; -- 查看有哪些用户 SELECT USERNAME FROM DBA_USERS; -- 查看用户有哪些角色权限 SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'student'; -- 查看用户有哪些系统权限 SELECT * FROM DBA_SYS_PRIVS WHERE grantee = 'student' ; -- 查看当前用户下的表 SELECT * FROM USER_TABLES;
查看当前表空间
SELECT TBS 表空间名, SUM(TOTALM) 总共大小M, SUM(USEDM) 已使用空间M, SUM(REMAINEDM) 剩余空间M, SUM(USEDM) / SUM(TOTALM) * 100 已使用百分比, SUM(REMAINEDM) / SUM(TOTALM) * 100 剩余百分比 FROM (SELECT B.FILE_ID ID, B.TABLESPACE_NAME TBS, B.FILE_NAME NAME, B.BYTES / 1024 / 1024 TOTALM, (B.BYTES - SUM(NVL(A.BYTES, 0))) / 1024 / 1024 USEDM, SUM(NVL(A.BYTES, 0) / 1024 / 1024) REMAINEDM, SUM(NVL(A.BYTES, 0) / (B.BYTES) * 100), (100 - (SUM(NVL(A.BYTES, 0)) / (B.BYTES) * 100)) FROM DBA_FREE_SPACE A, DBA_DATA_FILES B WHERE A.FILE_ID = B.FILE_ID GROUP BY B.TABLESPACE_NAME, B.FILE_NAME, B.FILE_ID, B.BYTES ORDER BY B.TABLESPACE_NAME) GROUP BY TBS;