客戶要求近期對(duì)現(xiàn)網(wǎng)的2個(gè)項(xiàng)目的數(shù)據(jù)庫(kù)中的歷史數(shù)據(jù)進(jìn)行清理，于是整理了下應(yīng)用中的段使用情況，主要是看表段情況，使用的腳本如下

客戶要求近期對(duì)現(xiàn)網(wǎng)的2個(gè)項(xiàng)目的數(shù)據(jù)庫(kù)中的歷史數(shù)據(jù)進(jìn)行清理，，于是整理了下應(yīng)用中的段使用情況，主要是看表段情況，使用的腳本如下。

create or replace procedure get_tab_rows
as
--Drop Table tabsrow
--Create Global Temporary Table tabsrow (Name Varchar2(30),Rowsnum Number) On Commit Delete Rows;
--Create Table tabsrow (Name Varchar2(30),Rowsnum Number) ;
V_SQL Varchar2(300);
Begin
Execute Immediate 'truncate table tabsrow';
For x In (Select OWNER,table_name From dba_tables Where owner='XXX' and table_name<>'test') Loop
V_SQL:='insert into tabsrow Select '''||x.table_name||''' ,Count(1) From '||X.OWNER||'.'||x.table_name;
Execute Immediate V_SQL;
End Loop;
commit;
End;
/
exec get_tab_rows;
Select a.Owner,
a.Table_Name,
--b.Segment_Name,
a.Tablespace_Name,
b.Bytes/1024/1024 as "size(Mb)",e.rowsnum,
--b.Blocks,
--c.Table_Name,
c.Partitioning_Type,
d.column_name
From Dba_Tables a
Left Join (Select b.Segment_Name, Sum(b.Bytes) Bytes, Sum(b.Blocks) Blocks
From Dba_Segments b
WHERE B.OWNER = 'XXX'
AND B.segment_type NOT IN ('INDEX')
Group By b.Segment_Name) B on a.Table_Name = b.Segment_Name
Left Join Dba_Part_Tables c On a.Table_Name = c.Table_Name
left join Dba_Part_Key_Columns d on d.owner = 'XXX'
and a.table_name = d.name
left join tabsrow e on a.table_name=e.name
Where a.Owner = 'XXX' order by "size(Mb)" desc;
drop procedure get_tab_rows;
Drop Table tabsrow;

-The End-

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com