Query the table space and usage of the logon user name in the Oracle database

Oracle DatabaseYou can query the name of the logon user.TablespaceAnd table space usage? The answer is yes, but executing select username, default_tablespace from dba_users order by username requires the dba permission. This article introduces this implementation method, next let's take a look at it.

1. view the user's default tablespace name

First, you need to know the user name you are logged on to, and then log on with sysdba, and then execute the following statement:

 
 

  
  
sqlplus / as sysdba  
  
  
 
  
  
select username,default_tablespace from dba_users; 
 
 

2. view the total table space size and the used table space size.

 
 

  
  
select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",  
  
  
 
  
  
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  
  
  
 
  
  
from  
  
  
 
  
  
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,  
  
  
 
  
  
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b  
  
  
 
  
  
where a.tablespace_name=b.tablespace_name  
  
  
 
  
  
order by ((a.bytes-b.bytes)/a.bytes) desc; 
 
 

This article introduces how to view the tablespace to which the logon username belongs to the Oracle database. For more information about Oracle databases, see the article http://database.51cto.com/oracle/. it is a reliable solution.