Oralce创建表用户和导入导出dmp文件

1、查看表空间名字：

select distinct TABLESPACE_NAME from tabs

2、查看几个表空间：

select count(distinct TABLESPACE_NAME) from tabs

3、创建表空间

create tablespace tj_apartmentorasys logging datafile 
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\RESOURCE\tj_apartmentorasys.dbf' 
size 1024m autoextend on next 50m maxsize 
unlimited extent management local segment space management auto;

4、创建用户并赋权限

create user integration
default tablespace sdzjypt
identified by integration;
grant dba to integration;
grant select any table to integration;
grant insert any table to integration;
grant update any table to integration;
grant delete any table to integration;
alter user integration account unlock;

查询用户表空间

select * from dba_users;
select username,default_tablespace from dba_users;

查询现有连接用户
select * from v$session;
查看表空间信息
select file_name,tablespace_name from dba_data_files;
select name from v$datafile;

数据库导出
exp test/test@192.168.4.118/resource file=C:\1\test.dmp
导出数据表
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)

数据库导入
imp test/test@192.168.4.118/resource file=C:\test.dmp full=y

/***************************************************************/
连接到sqlplus
sqlplus sys/sys123@zjl_db_rac as sysdba

删除表空间
Drop TABLESPACE NEWLIMS INCLUDING CONTENTS and DATAFILES;
创建表空间
create smallfile tablespace xtz logging  datafile '+DATA/shitan/datafile/xtz.dbf' size 1024m autoextend on next 50m maxsize unlimited extent management local segment space management auto;
创建用户
Create user xtz Default tablespace xtz Identified by xtz123;

给新创建的用户赋权限
grant dba,create table to xtz;

测试连接
conn tsmx/tsmx123@resource

删除用户
drop user zzjw1214 cascade;

更改用户的默认表空间
alter user lims default tablespace lims; 

导入
set oracle_sid=CSLIMS

imp tsmx/tsmx123@resource  file=  fromuser=exercise touser=tsmx 

grant select any table to user;(命令窗口)

导出
exp tsmx/tsmx123@research file=D:\tsmx20160107.dmp