Oracle/SQLServer/达梦/mysql查看最大表和清理的方法

oracle

1. 查看表空间:
SELECT
  a.tablespace_name "表空间名",
  total "表空间大小",
  free "表空间剩余大小",
  ( total - free ) "表占用空间大小",
  ROUND( ( total - free )  total * 100, 2 ) || '%' "已使用空间百分比" 
FROM
  ( SELECT tablespace_name, SUM( bytes )  1024  1024 total FROM dba_data_files GROUP BY tablespace_name ) a,
  ( SELECT tablespace_name, SUM( bytes )  1024  1024 free FROM dba_free_space GROUP BY tablespace_name ) b 
WHERE
  a.tablespace_name = b.tablespace_name 
ORDER BY
  ( total - free ) DESC;


2. 查看最大的100张表
SELECT
    x.table_name AS table_nameinfo,
    x.表行数 AS table_rownum,
    x.表列数 AS table_colnum,
    y.表大小 AS table_size ,
    x.table_tablespace as table_tablespace
FROM
    (
    SELECT
        b.table_name,
        a.num_rows AS 表行数,
        b.count1 AS 表列数 ,
                                a.TABLESPACE_name as table_tablespace
    FROM
        dba_tables a
        INNER JOIN ( SELECT table_name, count( column_name ) AS count1 FROM dba_tab_columns GROUP BY table_name ) b ON a.table_name = b.table_name
    ORDER BY
        b.table_name
    ) x LEFT outer
    JOIN (
    SELECT
        sum( tablesize ) AS 表大小,
        tablename
    FROM
        (
        SELECT
            sum( C.bytes )  1024  1024 AS tablesize,
            C.table_name AS tablename
        FROM
            ( SELECT A.table_name, B.bytes FROM dba_lobs A, dba_extents B WHERE A.segment_name = B.segment_name ) C
        GROUP BY
            C.table_name UNION ALL
        SELECT
            sum( bytes )  1024  1024 AS tablesize,
            segment_name AS tablename
        FROM
            dba_extents
        WHERE
            segment_type = 'TABLE'
        GROUP BY
            segment_name
        )
    GROUP BY
        tablename
    ORDER BY
        1 DESC
    ) y ON x.table_name = y.tablename
ORDER BY
    nvl(y.表大小,0) desc FETCH NEXT 100 ROWS ONLY

3. 查看数据文件和表空间
select * from v$datafile;
select * from dba_tablespaces

4. 删除用户和表空间
drop user xxxx cascade 
drop tablespace xxxx including contents and datafile; 

MySQL

1. 查看表大小:
SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND(( DATA_LENGTH + INDEX_LENGTH )  1024  1024,
  2 
) AS `Size (MB)` 
FROM
  INFORMATION_SCHEMA.TABLES 
ORDER BY
  ( DATA_LENGTH + INDEX_LENGTH ) DESC;

2. 按表前缀查看表的数量
SELECT LEFT
  ( TABLE_NAME, 3 ),
  count( 1 ) 
FROM
  INFORMATION_SCHEMA.TABLES 
GROUP BY
  LEFT ( TABLE_NAME, 3 ) 
ORDER BY
  2 DESC

达梦

create table table_count (owner varchar(100),table_name varchar(100),used_space_MB varchar(100));
truncate table table_count ;
declare
begin
for rec in
(
select owner, table_name from all_tables order by 1, 2
)
loop
insert into table_count
select
rec.owner ,
rec.table_name,
table_used_pages(rec.owner, rec.table_name)*(page()/1024)/1024
from
dual;
end loop;
end;
select * from table_count  order by to_number(used_space_mb) desc;

SQLServer

使用查询分析器
点击数据库->报表->按磁盘使用情况进行分析

PG

1. 查看最大的100张表
SELECT 
    nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM 
    pg_class C
LEFT JOIN 
    pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
    nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
ORDER BY 
    pg_total_relation_size(C.oid) DESC
LIMIT 10;