登录生产数据库
-- 空表分配空间
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 and TABLESPACE_NAME ='AAAAAAAAAA';-- 导出表结构和数据
exp 用户名/密码@192.168./oracle file=d:\AAAAAAAAAA.dmp owner=AAAAAAAAAA
登录开发数据库
sqlplus /nolog 然后 connect user/password @192.168.0.30:1521/orcl(或者 sqlplus system/manager@192.168.0.120:1521/orcl)
创建表空间
删除已经存在的表空间
drop user AAAAAAAAAA cascade;drop tablespace AAAAAAAAAA including contents AND DATAFILES;
create tablespace AAAAAAAAAA datafile 'C:\app\oradata\oracle\XXXXXX.ora' size 200m autoextend on next 10M maxsize unlimited;
创建用户
CREATE USER AAAAAAAAAA PROFILE DEFAULT IDENTIFIED BY AAAAAAAAAA DEFAULT TABLESPACE AAAAAAAAAA TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
授权
GRANT CONNECT TO AAAAAAAAAA;GRANT IMP_FULL_DATABASE TO AAAAAAAAAA;
GRANT RESOURCE TO AAAAAAAAAA;
--回收用户无限制的权限;
revoke unlimited tablespace from AAAAAAAAAA;
--在USERS表空间的配额为0
alter user AAAAAAAAAA quota 0 on USERS;
--在用户自己的缺省表空间内配额为无限的
alter user AAAAAAAAAA quota unlimited on AAAAAAAAAA;
--使用用户导入
imp AAAAAAAAAA/AAAAAAAAAA@oracle fromuser=AAAAAAAAAA touser=AAAAAAAAAA file=D:\AAAAAAAAAA.dmp
-- log=d:\log.log ignore=y;