Oracle数据库实现查询所有表_Oracle

1、查询当前数据库下的所有表

select * from all_tables where owner = 'test';

注：all_tables查出来是查得所有用户下的表，当然也包括你登录的用下的表，然后加一个where你要查的那个用户名就可以了。（记得用户名要大写）

模糊查询该条件的表名称：

select * from all_tables where owner = 'test' and table_name like '%s%';

2、查询当前登录用户的所有表

select * from user_tables;

简写：

select * from tabs;

模糊查询该条件的表名称:

select * from user_tables where table_name like '%s%';

3、查询所有用户的表,视图等

select * from all_tab_comments;

4、查询本用户的表,视图等

select * from user_tab_comments;

5、查询所有用户的表的列名和注释

select * from all_col_comments;

6、查询本用户的表的列名和注释

select * from user_col_comments;

7、查询所有用户的表的列名等信息

select * from all_tab_columns;

8、查询本用户的表的列名等信息

select * from user_tab_columns;

注：

3-8号的都可以在后面加一个 where owner = 'test'
就是你要查的那个用户名下的信息

9、查询一个数据库中所有表的大小并排序

select *
  from (select t1.owner,
               t1.table_name,
               round(t1.table_size / 1024 / 1024) +
               round(nvl(t3.lob_data_size, 0) / 1024 / 1024) table_size_mb,
               round(nvl(t2.index_size, 0) / 1024 / 1024) +
               round(nvl(t3.lob_index_size, 0) / 1024 / 1024) index_size_mb,
               round(t1.table_size / 1024 / 1024) +
               round(nvl(t3.lob_data_size, 0) / 1024 / 1024) +
               round(nvl(t2.index_size, 0) / 1024 / 1024) +
               round(nvl(t3.lob_index_size, 0) / 1024 / 1024 / 1024) size_mb
          from (select owner,
                       segment_name table_name,
                       round(sum(bytes)) table_size
                  from dba_segments
                 where segment_type like 'table%'
                 group by owner, segment_name) t1,
               (select a.owner, a.table_name, sum(b.bytes) index_size
                  from (select owner, table_name, index_name
                          from dba_indexes
                         where index_type <> 'lob') a,
                       (select owner, segment_name, bytes from dba_segments) b
                 where a.owner = b.owner
                   and a.index_name = b.segment_name
                 group by a.owner, a.table_name) t2,
               (select a.owner,
                       a.table_name,
                       sum(b.lob_data_size) lob_data_size,
                       sum(c.lob_index_size) lob_index_size
                  from (select owner, table_name, segment_name, index_name
                          from dba_lobs) a,
                       (select owner, segment_name, sum(bytes) lob_data_size
                          from dba_segments
                         group by owner, segment_name) b,
                       (select owner, segment_name, sum(bytes) lob_index_size
                          from dba_segments
                         group by owner, segment_name) c
                 where a.owner = b.owner
                   and a.segment_name = b.segment_name
                   and a.owner = c.owner
                   and a.index_name = c.segment_name
                 group by a.owner, a.table_name) t3
         where t1.owner = t2.owner(+)
           and t1.table_name = t2.table_name(+)
           and t1.owner = t3.owner(+)
           and t1.table_name = t3.table_name(+)
           and t1.owner = upper('test')
         order by 5 desc) x;