sql语句:
select
t.table_name as table_name, //表名
s.bytes as total_size, //表大小
table_rowcount(t.owner,t.table_name) as rowCounts //表行数
from dba_tables t
left join dba_segments s
on t.table_name=s.segment_name
where t.owner='' //数据库名称
and t.table_name=s.segment_name
and t.owner=s.owner;
java 代码:
StringBuilder sql = new StringBuilder("");
if (StringUtils.isNotBlank(schemaName)) {
sql.append("select ")
.append("t.table_name as table_name, ")
.append("s.bytes as total_size, ")
.append("table_rowcount(t.owner,t.table_name) as rowCounts ")
.append("from dba_tables t ")
.append("left join dba_segments s ")
.append("on t.table_name=s.segment_name ")
.append("where t.owner='").append(schemaName)
.append("' ")
.append("and t.table_name=s.segment_name ")
.append("and t.owner=s.owner;");
try (Connection conn = getDataSource().getConnection()) {
conn.setAutoCommit(false);
try (PreparedStatement preparedStatement = conn.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
preparedStatement.setFetchSize(Integer.MIN_VALUE);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
String tableName = rs.getString("table_name");
if (!isNameAccepted(tableName)) {
continue;
}
String tableSize = rs.getString("total_size") == null ? "0" : rs.getString("total_size");
String tableRows = rs.getString("rowCounts") == null ? "0" : rs.getString("rowCounts");
TableSize tableSizeObject = new TableSize(tableSize, tableRows);
tablesSizeInfoMap.put(tableName, tableSizeObject);
}
}
}
conn.setAutoCommit(true);
}
}
} catch (Exception e) {
LogManager.logError(LogConstants.CTX_JDBC, e, "get table size error:");
}