表空间

--完整删除表空间(包括.dbf文件)
DROP TABLESPACE  zxtf_space INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS 

--删除表空间
drop tablespace wsm_space INCLUDING CONTENTS and datafiles;

--删除用户
drop user hhdc cascade

 

 

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

--查询所有表空间

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)

 
--------创建NC表空间（只需要创建一次）
CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\ORACLE\ORADATA\nnc_data01.dbf' SIZE
500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;      
 
CREATE TABLESPACE NNC_DATA02 DATAFILE 'D:\ORACLE\ORADATA\nnc_data02.dbf' SIZE
300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;   
 
CREATE TABLESPACE NNC_DATA03 DATAFILE 'D:\ORACLE\ORADATA\nnc_data03.dbf' SIZE
500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K ;
 
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\ORACLE\ORADATA\nnc_index01.dbf'
SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;      
 
CREATE TABLESPACE NNC_INDEX02 DATAFILE 'D:\ORACLE\ORADATA\nnc_index02.dbf'
SIZE 300M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K ;

CREATE TABLESPACE NNC_INDEX03 DATAFILE 'D:\ORACLE\ORADATA\nnc_index03.dbf'
SIZE 500M AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
  
CREATE USER NC IDENTIFIED BY NC DEFAULT TABLESPACE NNC_DATA01 TEMPORARY
TABLESPACE temp;
 
GRANT connect,dba to NC;
 

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

--创建表空间

create tablespace hhdc_space
logging
datafile 'D:\ORACLE\ORADATA\zxtf_space.dbf'
size 100m
autoextend on
next 50m maxsize 8092m
extent management local;
 
 
------------------------

-- 创建用户myuser并指定表空间space1

  create user zxtf identified by zxtf
  default tablespace zxtf_space
  temporary tablespace temp;

 

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

 

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