Preparations for clearing historical Oracle Data

The customer requested that the historical data in the database of the two projects on the current network be cleaned up recently, so he sorted out the section usage in the next application, mainly to view the table section. The script used is as follows.

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
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-

Gtlions @ CSDNStudy... Practice