Oracle操作表空间

查看用户对应的表空间
select username, default_tablespace, temporary_tablespace from dba_users;
select username,default_tablespace from user_users;

以system用户登录，查找需要删除的用户：
–查找用户
select *　from dba_users;
–查找工作空间的路径
select * from dba_data_files;
–删除用户
drop user 用户名称 cascade;
–删除表空间
drop tablespace 表空间名称 including contents and datafiles cascade constraint;
例如：删除用户名成为LYK，表空间名称为LYK
–删除用户，及级联关系也删除掉
drop user LYK cascade;
–删除表空间，及对应的表空间文件也删除掉
drop tablespace LYK including contents and datafiles cascade constraint;

–1：创建临时表空间
create temporary tablespace qifeng_temp
tempfile ‘/data/oracle/oradata/orcl/qifeng_temp.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

–2：创建数据表空间
create tablespace qifeng_tablespaces
logging
datafile ‘/data/oracle/oradata/orcl/qifeng.dbf’
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;

–第3步：创建用户并指定表空间
create user amoy identified by amoy123456
default tablespace amoy_tablespaces
temporary tablespace amoy_temp;

–第4步：给用户授予权限
– grant connect,resource,dba to amoy;
grant connect,create view,resource to amoy;

–索引表空间
CREATE TABLESPACE AMOYSPACE_INDEX
DATAFILE ‘E:\oracle_tablespaces\AMOYSPACE_INDEX.dbf’
size 4096M
autoextend on next 50M maxsize 30000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

–导入导出命令
ip导出方式： exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y
exp demo/demo@orcl file=f:/f.dmp full=y
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y