oracle 查看用户的表空间，表空间路径，角色，权限

 

查看当前用户的缺省表空间
select username,default_tablespace from user_users;
--XUSER XUSER_DATA

 

查询表空间 含临时表空间
select tablespace_name from dba_tablespaces;
--XUSER_TEMP
--XUSER_DATA

 

查询表空间的路径
select name from v$datafile
--/u01/app/oracle/oradata/orcl/xuser_data.dbf

 

查询表空间的路径
select * from DBA_DATA_FILES;
--/u01/app/oracle/oradata/orcl/xuser_data.dbf

 

查询临时表空间的路径
select * from v_$tempfile --sys用户查看

 

查询临时表空间的路径
select * from dba_temp_files;
--/u01/app/oracle/oradata/orcl/xuser_temp.dbf

 

查询用户的角色
select * from user_role_privs;
--XUSER CONNECT
--XUSER DBA
--XUSER RESOURCE

 

查询用户的系统权限
select * from user_sys_privs;
--XUSER UNLIMITED TABLESPACE

查询表空的大小，使用量
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)

查看临时表空间的大小，使用量
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

 

windows下oracle 创建表空间，创建用户

http://happyqing.iteye.com/blog/1858287

 

linux下oracle创建表空间，创建用户

http://happyqing.iteye.com/blog/2024902