How to query all tables under a specified user in Oracle

In Oracle, you can use the "select" statement to query all tables under a specified user, the syntax is "select * from all_tables a where a.OWNER = upper('database user name');".

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

1. Query what tables are available under the current user

Standard query statement:

select * from all_tables a where a.OWNER = upper('数据库用户名');

Example: (Note: HDRV2 is the database username I use. You can change your username here. Remember to capitalize the username. After the query is successful, you can understand the role of each field in the
all_tables table)

---

2. Query all field information of all tables under the current user

Standard query statement:

select * from all_tab_columns c where c.OWNER = upper('数据库用户名');

Example: (Note: HDRV2 is the database username I use. You can modify your username here. Remember to capitalize the username; then use and to make a conditional query)

---

3. View the table space to which the current user belongs

Standard query statement (Username must be in uppercase letters, Oracle is case-sensitive):

select * from dba_users where username=upper('用户名');

Example:

select default_tablespace from dba_users where username='HDRV2';

4. Query the number of data items (number not found), table name, and Chinese table name of the table under the current user

select
      a.num_rows as '数据条数', a.TABLE_NAME as '表名', b.COMMENTS as '中文表名'
from 
      user_tables a, user_tab_comments b
where
      a.TABLE_NAME = b.TABLE_NAME
order by 
      TABLE_NAME;

5. Query the current user All table names under:

select t.table_name from user_tables t;

6. Query the field names of all tables under the current user:

select t.column_name from user_col_comments t;

7. Query the table names and table descriptions of all tables under the current user:

select t.table_name,f.comments from user_tables t 
inner join user_tab_comments f on t.table_name = f.table_name;

Recommended tutorial: "Oracle Tutorial"

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