oracel创建新用户并将数据导入数据库语句

--system使用sysdba登陆报权限不够，登陆sys给system赋予sysdba的权限即可登陆了
grant sysdba to system;

--删除用户
drop user cemapp3 cascade;
--删除表空间
drop tablespace CEM_DATA including contents and datafiles CASCADE CONSTRAINTS;
-- 查询表空间
select * from dba_tablespaces;

--查看所有用户
select * from all_users
--创建用户
 create user ldgsTest identified by ldgsTest;
--创建表空间
create tablespace LDGS_DATA datafile 'D:\app\june\oracle\LDGS_DATA.DBF' size 1024m autoextend on next 2000m extent management local segment space management auto;
 --先查看用户的表空间属性
 select username,default_tablespace,temporary_tablespace from dba_users
 --用户当前的对象及所在位置
  select object_name,object_type,status from user_objects;
 
  select segment_name,segment_type,tablespace_name from user_extents ;

 --表空间扩容
 alter database datafile 'D:\app\june\oracle\LDGS_DATA.DBF' autoextend on next 200m maxsize 5000m;  
--为用户分配表空间
 alter user ldgsTest default tablespace LDGS_DATA;
--授权
grant dba to ldgsTest;
grant connect to ldgsTest;
grant resource to ldgsTest;
--执行存储过程报错
grant resource to ldgsTest;
grant create any procedure to ldgsTest;
grant create any procedure to ldgsTest;
grant execute any procedure to ldgsTest;
grant exp_full_database,imp_full_database to ldgsTest;
--报必须声明标识符，解决：sys用户给cemapp3加上dbms_crypto得权限就可以成功编译存储过程

---建立逻辑目录
create or replace directory data as 'D:\app\june\oracle\dmp';
select * from dba_directories;
--给目标用户授权
grant read,write on directory data to ldgsTest;

exp fkuser/FK129user!@10.10.1.129/fkdb owner=fkuser file=D:/dmp/cemprd_zhongyi_exp_20180525.dmp
--导入语句
imp ldgsTest/ldgsTest@fmc1 file=D:/dmp/fmc0608.dmp ignore=y full=y;
impdp cemapp3/cemapp3@cemapp3 directory=data dumpfile=cemapp.dmp logfile=cemapp3.log  remap_schema=cemapp:cemapp3 transform=OID:N
exp fkuser/FK129user!@10.10.1.129/fkdb owner=fkuser file=D:/dmp/cemprd_zhongyi_exp_20180525.dmp