我要评论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;总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论