统计 oracle 下某个表下的表和索引的总大小

##for oracle

问题目的是，找到oracle 下所有大表的表和该表的索引总大小。这样可以方便开发统计该表实际占用大小。

参考文档

http://www.51testing.com/html/60/n-846960-2.html

感谢

set linesize 140
set pagesize 160

clear breaks
clear computes

break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2

SET TIMI OFF
set linesize 140
set pagesize 10000
set verify off
col OWNER noprint
col TABLE_NAME for a30 heading 'Table name'
col INDEX_NAME for a30 heading 'Index name'
col INDEX_TYPE for a15 heading 'Index type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990D90 Heading 'Index|Size MB' justify right

SELECT
S.OWNER A_OWNER,
TABLE_NAME A_TABLE_NAME,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE S.OWNER = 'DBMGR'
AND I.OWNER = 'DBMGR'
AND INDEX_NAME = SEGMENT_NAME
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
HAVING SUM(S.BYTES) > 1048576 * 100

#########sample 0.1

单独对某个表ABLE_NAME='d0_BILL' 进行分析，统计这个表的大小和他对应的索引大小，但是不包括总大小，缺点是没计算所以是否是分区索引，

SELECT
S.OWNER A_OWNER,
TABLE_NAME A_TABLE_NAME,
INDEX_NAME A_INDEX_NAME,
INDEX_TYPE A_INDEX_TYPE,
SUM(S.bytes) / 1048576 A_MB
FROM DBA_SEGMENTS S,
DBA_INDEXES I
WHERE INDEX_NAME = SEGMENT_NAME and s.owner=i.owner
GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
HAVING SUM(S.BYTES) > 1048576 * 100 AND TABLE_NAME='d0_BILL'
union
SELECT
DS.OWNER A_OWNER,
TABLE_NAME A_TABLE_NAME,
' ',
' ',
SUM(DS.bytes) / 1048576 A_MB
from DBA_SEGMENTS DS,
DBA_tables T
WHERE t.TABLE_NAME = ds.SEGMENT_NAME and Ds.owner=T.owner
GROUP BY dS.OWNER, TABLE_NAME
HAVING SUM(dS.BYTES) > 1048576 * 100 and TABLE_NAME='d0_BILL'

###############

##sample 0 这个报表通过计算最大50个表的数据，（当然也可以把表的数量从50 改成 100）

然后按照每个对象分别输出如下信息： s_owner(用户名) ，s_sgement_name（表名字），sgment_size （表的大小）和 sgment_total_size （表加这个表对应的索引大小总共大小）

DECLARE
x NUMBER := 0;
s_owner varchar2(32);
s_sgement_name varchar2(200);
s_sgement_size varchar2(200);
s_total_size varchar2(200);

CURSOR get_50rows IS SELECT *
FROM (SELECT owner,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
GROUP BY owner,SEGMENT_NAME, segment_type
having segment_type like '%TABLE%'
ORDER BY 3 DESC)
WHERE ROWNUM < 50;
BEGIN
--DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');

---FOR irec IN get_50rows LOOP -- After CONTINUE statement, control should resume here

OPEN get_50rows;
LOOP
FETCH get_50rows INTO s_owner,s_sgement_name,s_sgement_size;

SELECT
(SELECT SUM(S.BYTES/1024/1024) -- The table segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = s_owner AND
(S.SEGMENT_NAME = s_sgement_name)) +
(SELECT NVL(SUM(S.BYTES/1024/1024), 0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = s_owner AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = s_sgement_name AND L.OWNER = s_owner)) +
(SELECT SUM(S.BYTES/1024/1024) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = s_owner AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = s_sgement_name AND I.OWNER = s_owner))
"TOTAL TABLE SIZE" into s_total_size
FROM DUAL;

--DBMS_OUTPUT.PUT_LINE ('Inside loop: x = ' || TO_CHAR(x));
x := x + 1;
--IF x < 3 THEN
--CONTINUE;
---END IF;
--DBMS_OUTPUT.PUT_LINE ('Inside loop, after CONTINUE: x = '||TO_CHAR(x));
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('owner_name: '||s_owner);
---DBMS_OUTPUT.PUT_LINE('---------------------------');
---DBMS_OUTPUT.PUT_LINE(CHR(10));
---DBMS_OUTPUT.PUT_LINE('Summary');
DBMS_OUTPUT.PUT_LINE('========');
DBMS_OUTPUT.PUT_LINE('sgment_name: '||s_sgement_name);
DBMS_OUTPUT.PUT_LINE('sgment_size(MB): '||s_sgement_size);
DBMS_OUTPUT.PUT_LINE('sgment_total_size(MB): '||s_total_size);
DBMS_OUTPUT.PUT_LINE('========');
EXIT WHEN x = 51;

END LOOP;
end;
/

##sample 0.2 将top 100 segment 存入数据库对象，方便做excel 统计。也方便做清理前和清理后的数据对比

step 1: create table: use store date of top segment size t
create table dbmgr.seg_size_summary
(
time VARCHAR2(30),
owner VARCHAR2(30),
segment_name VARCHAR2(30),
segment_size NUMBER,
segment_tot_size NUMBER
);

COMMENT ON TABLE dbmgr.seg_size_summary IS 'seg_size_summary include top 100 table and index size';

step 2: run procedure
set serveroutput on

DECLARE
x NUMBER := 0;
s_owner varchar2(32);
s_sgement_name varchar2(200);
s_sgement_size varchar2(200);
s_total_size varchar2(200);
v_sysdate varchar2(12);

CURSOR get_50rows IS SELECT *
FROM (SELECT owner,SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
GROUP BY owner,SEGMENT_NAME, segment_type
having segment_type like '%TABLE%'
ORDER BY 3 DESC)
WHERE ROWNUM < 100;
BEGIN
--DBMS_OUTPUT.PUT_LINE ('First a basic loop without CURSOR FOR LOOP running fine');
DBMS_OUTPUT.ENABLE (buffer_size=>null);
---FOR irec IN get_50rows LOOP -- After CONTINUE statement, control should resume here
select to_char(sysdate,'yyyymmdd') into v_sysdate from dual ; --get sysdate

OPEN get_50rows;
LOOP
FETCH get_50rows INTO s_owner,s_sgement_name,s_sgement_size;

SELECT
(SELECT SUM(S.BYTES/1024/1024) -- The table segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = s_owner AND
(S.SEGMENT_NAME = s_sgement_name)) +
(SELECT NVL(SUM(S.BYTES/1024/1024), 0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = s_owner AND
(L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = s_sgement_name AND L.OWNER = s_owner)) +
(SELECT NVL(SUM(S.BYTES/1024/1024), 0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = s_owner AND
(I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = s_sgement_name AND I.OWNER = s_owner))
"TOTAL TABLE SIZE" into s_total_size
FROM DUAL;

END LOOP;
commit;
end;
/

step 3: view data
select time,owner,segment_name,segment_size "表大小",segment_tot_size "表和索引总大小" from dbmgr.seg_size_summary where time=to_char(sysdate,'yyyymmdd');

###sampe 0.3 不区分表和索引大小，只是统计排名在前几位的对象，包括对象类型，比如是否是分区索引和分区表。

检查10个大对象
select owner, name, type, mega, tbs
from (select owner,
case
when segment_type like 'LOB%' then
logical_name
else
segment_name
end as name,
segment_type as type,
round(bytes / 1024 / 1024) as mega,
tablespace_name as tbs
from (select a.owner,
a.segment_name,
a.segment_type,
b.table_name || '.' || b.column_name as logical_name,
bytes,
a.tablespace_name
from (select owner,
segment_name,
segment_type,
sum(bytes) as bytes,
max(tablespace_name) as tablespace_name
from dba_segments
group by owner, segment_name, segment_type) a,
dba_lobs b
where a.owner = b.owner(+)
and a.segment_name = b.segment_name(+)
and a.bytes > 1024 * 1024 * 1024)
order by bytes desc);

##sample 1 for mysql

How to find out each table size

GOAL

Find out each Schema size by Engine from the MySQL Server

SOLUTION

Extract metadata information about each Database Schema size by Engine utilization and total, for both indexes and data:

SELECT
  IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Instance Total',
  CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) " Statistic",
  LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
  SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
  LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
  SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
  LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
  SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM
(
  SELECT DB,ENGINE,DAT,NDX,TBL,
  IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
  FROM
  (SELECT *,
  FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
  FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
  FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
  FROM
  (SELECT
  DB,ENGINE,
  SUM(data_length) DAT,
  SUM(index_length) NDX,
  SUM(data_length+index_length) TBL
  FROM
  (
  SELECT table_schema DB,ENGINE,data_length,index_length FROM
  information_schema.tables WHERE table_schema NOT IN
  ('information_schema','performance_schema','mysql')
  AND ENGINE IS NOT NULL
  ) AAA GROUP BY DB,ENGINE WITH ROLLUP
) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

+-------------------+-----------+------------+------------+
| Statistic | Data Size | Index Size | Total Size |
+-------------------+-------------------+-----------------+
| database1 InnoDB | 64.00 KB | 416.00 KB | 480.00 KB |
| database1 Total   | 64.00 KB | 416.00 KB | 480.00 KB |
| database2 InnoDB | 1.22 MB   | 2.81 MB    | 4.03 MB    |
| database2 Total   | 1.22 MB   | 2.81 MB    | 4.03 MB    |
| database3 InnoDB | 32.00 KB | 0.00 B     | 32.00 KB   |
| database3 Total   | 32.00 KB | 0.00 B     | 32.00 KB   |
| database4 InnoDB | 32.00 KB | 48.00 KB   | 80.00 KB   |
| database4 Total | 32.00 KB | 48.00 KB   | 80.00 KB   |
| database5 InnoDB | 176.00 KB | 496.00 KB | 672.00 KB |
| database5 MyISAM | 0.00 B    | 1.00 KB    | 1.00 KB    |
| database5 Total   | 176.00 KB | 497.00 KB | 673.00 KB |
| database6 InnoDB | 32.00 KB | 304.00 KB | 336.00 KB |
| database6 Total | 32.00 KB | 304.00 KB | 336.00 KB |
| sys InnoDB      | 16.00 KB | 0.00 B     | 16.00 KB   |
| sys Total       | 16.00 KB | 0.00 B     | 16.00 KB   |
| database7 ARCHIVE | 88.00 B   | 0.00 B     | 88.00 B    |
| database7 InnoDB | 1.62 MB   | 2.50 MB    | 4.12 MB    |
| database7 Total   | 1.63 MB   | 2.50 MB    | 4.13 MB    |
| Instance Total    | 3.19 MB   | 6.55 MB    | 9.74 MB    |
+-------------------+-----------+------------+------------+
19 rows in set (0.45 sec)

########sample 5

https://www.cnblogs.com/chshnan/archive/2012/02/07/2341694.html

ORACLE删除当前用户下所有的表的方法

使用业务用户登陆，如果没有删除用户的权限，则可以执行：

select 'drop table '||table_name||';'
from cat
where table_type='TABLE'

将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了。（需要有drop table的权限）

select 'drop SEQUENCE '||table_name||';'
from cat
where table_type='SEQUENCE';

select 'drop VIEW '||table_name||';'
from cat
where table_type='VIEW';

posted @ 2021-02-24 17:37 feiyun8616 阅读(1256) 评论(0) 编辑收藏举报

刷新页面返回顶部

feiyun8616的作坊 (半个程序员and dba) 脚本化 python

统计 oracle 下某个表下的表和索引的总大小

GOAL

SOLUTION

ORACLE删除当前用户下所有的表的方法

公告