| need to get row count for database with 20 k tables [message #650297] |
Thu, 21 April 2016 22:26  |
TorontoTrader
Messages: 8
Registered: January 2007
|
Junior Member |
|
|
i built a p/l sql script but i am guessing that there is a better way to do it than what i am trying to do. my script loops thro all the tables in all schemas except system tables and does a select count(1) and stores it another table. do you have any ideas or suggestions. we need to do this as we are loading data daily and need to keep some metrics on the # of rows daily
declare
-- Local variables here
i integer;
query_str VARCHAR2(1000);
tablenames varchar2(200);
err_msg VARCHAR2(100);
c integer;
begin
-- Test statements here
execute immediate 'truncate table TABLE_LIST ';
for tab_name in (select distinct owner,table_name from dba_tables where owner not in ('SYS' ,'SYSTEM') AND TABLESPACE_NAME NOT IN ('SYSTEM') AND TABLE_NAME not like '%$%')
loop
begin
tablenames:= tab_name.owner|| '.'|| tab_name.table_name;
query_str := ' select count(1) from ' || tablenames;
--dbms_output.put_line( query_str ) ;
EXECUTE IMMEDIATE query_str INTO c ;
insert into TABLE_LIST
(table_name,row_count)
values
(tablenames,c);
commit;
exception
when others then
dbms_output.put_line(SQLCODE);
dbms_output.put_line( SUBSTR(SQLERRM, 1, 100));
dbms_output.put_line( tablenames|| ','|| c );
end;
--
end loop;
end;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: need to get row count for database with 20 k tables [message #650318 is a reply to message #650306] |
Fri, 22 April 2016 06:28  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
select distinct owner,table_name from dba_tables where owner not in ('SYS' ,'SYSTEM') AND TABLESPACE_NAME NOT IN ('SYSTEM') AND TABLE_NAME not like '%$%'
Get rid of distinct. Owner, table name combination is unique. There is ton of Oracle supplied tables that match your SQL:
SQL> select nvl(owner,'Total') owner,
2 count(*)
3 from dba_tables
4 where owner not in ('SYS' ,'SYSTEM')
5 AND TABLESPACE_NAME NOT IN ('SYSTEM')
6 AND TABLE_NAME not like '%$%'
7 AND OWNER NOT IN ('SCOTT','HR')
8 group by rollup(owner)
9 /
OWNER COUNT(*)
------------------------------ ----------
APEX_040200 404
APPQOSSYS 4
CTXSYS 2
DBSNMP 16
GSMADMIN_INTERNAL 13
MDSYS 82
OLAPSYS 2
ORDDATA 71
ORDSYS 5
WMSYS 1
XDB 5
Total 605
12 rows selected.
SQL>
Also, your query will return (if you have any) auxiliary tables like materialized view logs, IOT overflow tables, Oracle text index tables, etc. you need to exclude.
Do you gather stats on your tables? If so, selecting NUM_ROWS from DBA_TABLES right after collecting stats will give you what you need.
SY.
[Updated on: Fri, 22 April 2016 06:38] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: need to get row count for database with 20 k tables [message #650332 is a reply to message #650327] |
Fri, 22 April 2016 10:16 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
I would do the following steps.
1) do NOT truncate the table, keep the history. Make your table TABLE_LIST with the following columns
owner varchar2(30)
table_name varchar2(30)
tablespace_name varchar2(30)
num_rows number
create_date date
create a unique index on owner,table_name,create_date
2) Run your daily database wide statistics run, this should be done and will populate the num_rows with the correct values
3) Run the following query. I would put it in the same job that ran the statistics update
insert into TABLE_LIST
select A.owner,A.table_name,a.tablespace_name,NUM_ROWS,TRUNC(SYSDATE) RUN_DATE
from DBA_OBJECTS B,dba_tables A
where A.owner not in ('SYS' ,'SYSTEM')
AND A.TABLE_NAME = B.OBJECT_NAME
AND A.OWNER = B.OWNER
AND B.OBJECT_TYPE = 'TABLE'
AND A.TABLESPACE_NAME NOT IN ('SYSTEM')
AND A.TABLE_NAME not like '%$%';
commit;
[Updated on: Fri, 22 April 2016 11:50] Report message to a moderator |
|
|
|
| Re: need to get row count for database with 20 k tables [message #650342 is a reply to message #650332] |
Fri, 22 April 2016 15:14 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
This will not eliminate materialized views, just materialized view logs:
SQL> create materialized view emp_mv as select * from emp;
Materialized view created.
SQL> select object_type
2 from user_objects
3 where object_name = 'EMP_MV'
4 /
OBJECT_TYPE
-----------------------
MATERIALIZED VIEW
TABLE
SQL>
It should be something like:
AND (OWNER,TABLE_NAME) IN (
SELECT B.OWNER,
B.TABLE_NAME
FROM DBA_OBJECTS B
WHERE B.OBJECT_TYPE != 'MATERIALIZED VIEW'
)
Also, OP needs to add
AND A.SECONDARY != 'Y' -- This will eliminate domain index tables
AND NVL(A.IOT_TYPE,'NOT_IOT') NOT IN ('IOT_OVERFLOW','IOT_MAPPING') -- This will eliminate IOT overflow and mapping tables
AND A.TEMPORARY != 'Y' -- This will eliminate GTT
to main query.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
