数据库表空间 增删改查

--完整删除表空间(包括.dbf文件)

DROP TABLESPACE  space1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS  

 

 

-------------------------------------

--查询所有表空间

select dbf.tablespace_name,
dbf.totalspace "总量(M)",
dbf.totalblocks as 总块数,
dfs.freespace "剩余总量(M)",
dfs.freeblocks "剩余块数",
(dfs.freespace / dbf.totalspace) * 100 "空闲比例"
from (select t.tablespace_name,
sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

 

 

---------------------------------------------------

--创建表空间

create tablespace space1
logging
datafile 'E:\ORACLE\ORADATA\ORA9I\mysapce.dbf'
size 100m
autoextend on
next 50m maxsize 8092m
extent management local;

 

------------------------

-- 创建用户myuser并指定表空间space1
  create user myuser identified by myuserPwd
  default tablespace space1
  temporary tablespace temp;

 

--为用户myuser分配DBA权限
 grant DBA to myuser;
 commit; -- 提交

 

 

-- 修改用户myuser的默认表空间为 space1
--将myuser默认的表空间设置为space1，这样在使用myuser用户进行imp导入dmp文件的时候，数据自然会存储在apace1表空间中
alter user myuser default tablespace space1;