Oracel 获取表数据大小

Oracel 获取表数据大小

select ROUND(sum(BYTES)/1024/1024,2) from dba_segments where owner=dbname（数据库用户名）

 

Oracel 获取表总条数

SELECT ROUND(sum(t1.nrows),2) rowN from (
select t.table_name, count_rows(t.table_name) nrows from (
SELECT table_name FROM all_tables WHERE OWNER ='dbname（数据库用户名）' ORDER BY table_name ) t
) t1

 

String tablename="";
String dbtype="";
String ip="";
Integer port=0;
String user="";
String pwd="";
Integer count=0;
for(int j=0;j<list.size();j++){
Object a[]=list.get(j);
tablename=(String) a[0];
dbtype=(String) a[1];
ip=(String) a[2];
port=Integer.valueOf((String) a[3]);
user=(String) a[4];
pwd=(String) a[5];
//mysql 8a8a00c75d9b4857015d9b60a71a0000
//sqlserver 8a8a00c75d9b4857015d9b60f44b0001
//oracle 4028b8815d301c7d015d302f41860000
Connection conn=null;
String tmpName=null;
String sql="";
try{
// 1.加载驱动程序
if("8a8a00c75d9b4857015d9b60a71a0000".equals(dbtype)){
tmpName=tablename;
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection("jdbc:mysql://"+ip+":"+port+"/"+tablename, user, pwd);
}else if("8a8a00c75d9b4857015d9b60f44b0001".equals(dbtype)){
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
tmpName=tablename;
conn=DriverManager.getConnection("jdbc:sqlserver://"+ip+":"+port+";DatabaseName="+tablename, user, pwd);
}else if("4028b8815d301c7d015d302f41860000".equals(dbtype)){
Class.forName("oracle.jdbc.OracleDriver");
conn=DriverManager.getConnection("jdbc:oracle:thin:@"+ip+":"+port+":orcl", user, pwd);
}
String[] types = { "TABLE" };

ResultSet resultSet = conn.getMetaData()
.getTables(tmpName, null, "%", types);

//如果是oracle     getTables()第一个参数是数据库名称,如果是oracle为null ,mysql传数据库名称
if("4028b8815d301c7d015d302f41860000".equals(dbtype)){
resultSet = conn.getMetaData()
.getTables(null, user.toUpperCase(), "%", types);
}

List<String> tablist=new ArrayList<>();
String tableName = "";
//获取表名
while (resultSet.next()) {
tableName = resultSet.getString(3);
tablist.add(tableName);
}
Statement stat = conn.createStatement();
for(String aa : tablist){
sql="select count(*) from "+aa+" ";
ResultSet rs = stat.executeQuery(sql);
Integer counts=0;
while (rs.next()) {
counts=rs.getInt(1);
}
count+=counts;
rs.close();
}
resultSet.close();
}catch(Exception e){
e.printStackTrace();
}
}

 

//获取表列 名
DatabaseMetaData meta = conn.getMetaData();
for(String list:tablist){

if(oracle){
resultSet = meta.getColumns(null, userName.toUpperCase(), tableName,"%");
}else{
resultSet = meta.getColumns(database.getDbName(), null, tableName, "%");
}

while (resultSet.next()) {
String col=resultSet.getString(4);
String type=resultSet.getString(6);
// System.out.println("Column Name of table " + tableName + " = "
// + resultSet.getString(4));
}
}

 

 

 

存储过程

CREATE OR REPLACE
function "COUNT_ROWS"(table_name in varchar2,
owner in varchar2 default null)
return number
authid current_user
IS
num_rows number;
stmt varchar2(2000);
begin
if owner is null then
stmt := 'select count(*) from "'||table_name||'"';
else
stmt := 'select count(*) from "'||owner||'"."'||table_name||'"';
end if;
execute immediate stmt into num_rows;
return num_rows;
end;