-- 查看当前用户使用的表空间情况
select * from user_users;
-- 查看oracle下面所有的表空间
select * from Dba_Tablespaces;
-- 查看文件位置与大小等
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
第1步:创建临时表空间
create temporary tablespace dj_dsf_temp
tempfile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\dj_dsf_temp.DBF'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
第2步:创建数据表空间 代码如下:
create tablespace dj_dsf_data
logging
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\dj_dsf_data.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
第3步:创建用户并指定表空间
create user dj_dsf identified by 111111
default tablespace dj_dsf_data
temporary tablespace dj_dsf_temp;
-- 修改密码
alter user DJ_DSF
identified by "111111";
4、授权用户
-- Grant/Revoke role privileges
grant connect to DJ_DSF;
grant dba to DJ_DSF;
grant resource to DJ_DSF;
-- Grant/Revoke system privileges
grant create any table to DJ_DSF;
grant drop any table to DJ_DSF;
grant alter any table to DJ_DSF;
grant insert any table to DJ_DSF;
grant delete any table to DJ_DSF;
grant select any table to DJ_DSF;
grant update any table to DJ_DSF;
grant unlimited tablespace to DJ_DSF;