How to query the table space of a specified user in Oracle

In Oracle, you can use the "select" statement to query the table space of a specified user, with the syntax "select default_tablespace from dba_users where username='username'".

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

How to query the table space name of the current user? Because Oracle needs to know the current user's table space to create an index, I looked up the information

--查询语法--
 select default_tablespace from dba_users where username='登录用户'

. For example, my login user is TMS21, then the query syntax is

/* 查看用户所属的表空间 */
 select default_tablespace from dba_users where username='TMS21';

Please also record the usage of related queries

1) Query the current user table space

/* 查看用户所属的表空间 */
 select default_tablespace from dba_users where username='TMS21';

2) Query all table spaces

  /*查看所有的表空间 */
   -- 1 ）方式1：dba_tablespaces --
   select * from dba_tablespaces;
    --2 ）方式2：v$tablespace --
   select * from v$tablespace;

3) Query all tables under the user

  /* 查看用户下面的所有的表 */ 
  -- 1 ）方式1：user_tables --
   select * from user_tables;
   --2 ）方式2: dba_tables --
   select * from dba_tables where owner='TMS21';

4) Query users under the table space

 /*查看表空间下有多少用户，tablespace_name表空间 的名字一定要大写 */
   select distinct s.owner from dba_segments s where s.tablespace_name ='TMS21';

PS: Because my table space name is the same as the user, the table spaces in 4) are all TMS21 (based on query results)

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to query the table space of a specified user in Oracle. For more information, please follow other related articles on the PHP Chinese website!