http://space.itpub.net/7104/viewspace-133950
Statistics of records for all tables in Oracle
CREATE TABLE A
(
USERNAME VARCHAR2 (20),
TableName VARCHAR2 (50),
JS VARCHAR2 (10)
)
Create or replace procedure P_getcount
As
Micount INTEGER;
T_owner VARCHAR2 (40);
T_table VARCHAR2 (40);
BEGIN
For C_tab in (SELECT owner,table_name to Dba_tables where owner not in (' SYS ', ' SYSTEM ', ' Ctxsys ', ' perfstat ', ' Wmsys ', ' exf ') SYS ', ' Sysman ') LOOP
--dbms_output.put_line (' Select COUNT (*) from ' | | | c_tab.owner| | '. ' | | C_tab.table_name | | '');
--dbms_output.put_line (c_tab.owner| | | C_tab.table_name);
T_owner: = C_tab.owner;
T_table: = C_tab.table_name;
EXECUTE IMMEDIATE ' SELECT COUNT (*) from ' | | c_tab.owner| | ' | | | c_tab.table_name| | ' "' | | ' Into Micount;
--dbms_output.put_line (Rpad (c_tab.table_name,30, '. ') | | lpad (micount,10, '. '));
INSERT into A VALUES (c_tab. Owner,c_tab. Table_name,micount);
End LOOP;
EXCEPTION
When others THEN
Dbms_output.put_line (t_owner| | | t_table);
RAISE;
Commit
End