oracle_查看表字段与长度

SELECT A.OWNER || '.' || A.TABLE_NAME,
B.COLUMN_NAME,
B.DATA_TYPE,
B.DATA_LENGTH
FROM ALL_ALL_TABLES A, ALL_TAB_COLUMNS B
WHERE A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND B.COLUMN_NAME LIKE '%BANKACCO%'

 

--通过占用内存大小删除无用表

SELECT AA.OWNER,
SEGMENT_NAME,segment_type,
'drop table ' || OWNER || '.' || SEGMENT_NAME || ';',
ROUND(SUM(BYTES) / 1024 / 1024, 2) || 'M'
FROM DBA_SEGMENTS AA
WHERE OWNER = 'DM'
AND aa.segment_type='TABLE'
AND SEGMENT_NAME LIKE '%_17%0%'
AND SEGMENT_NAME NOT LIKE '%_220%'
GROUP BY SEGMENT_NAME,segment_type, AA.OWNER
ORDER BY ROUND(SUM(BYTES) / 1024 / 1024, 2) DESC, SEGMENT_NAME