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;
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持IT俱乐部。
