PL\SQL - Get counts of all tables in a schema - errors
I want to get a result: table name, count amount for each table from AS_TABLE_LIST.
create procedure AtRowCount as declare TableCount NUMBER(1); TableName VARCHAR2(100); BEGIN SelectQuery1:= 'SELECT count(*) FROM ' || TableName || ' INTO ' || TableCount; FOR TableName IN (select table_name from AS_TABLE_LIST) LOOP EXECUTE IMMEDIATE SelectQuery1; END LOOP; select TableName, TableCount into AT_ROW_COUNT from dual; END AtRowCount;
I get two errors:
[Error] PLS-00306 (7: 19): PLS-00306: wrong number or types of arguments in call to '||'
[Error] ORA-00904 (9: 8): PL/SQL: ORA-00904: "TABLENAME": invalid identifier
I've been trying many times to fix this but still got same errors. any advice?
I'm not exactly sure what you're trying todo, but it might be the following:
CREATE PROCEDURE AtRowCount AS DECLARE l_count NUMBER; BEGIN FOR c IN (SELECT table_name from AS_TABLE_LIST) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||c.table_name INTO l_count; INSERT INTO AT_ROW_COUNT(TableName, TableCount ) VALUES (c.table_name, l_count); END LOOP; END AtRowCount;
If you want you're procedure to return the list instead of inserting the result in a new table, you have to use a pipelined function instead (see https://oracle-base.com/articles/misc/pipelined-table-functions for an example on how to use it).
PL\SQL - Get counts of all tables in a schema - errors, I'm not exactly sure what you're trying todo, but it might be the following: CREATE PROCEDURE AtRowCount AS DECLARE l_count NUMBER; So we can use below set of statements to find the count of rows of all the tables at once and store them in one permanent table for analysis purpose. Run the below PL/SQL code: You should get ‘PL/SQL procedure successfully completed.’ message from the editor. Now the table names and rows count is stored in our newly created table. METHOD-2:
You can get all rows count against table name in oracle by:
select owner, table_name, nvl(num_rows,-1) from all_tables order by nvl(num_rows,-1) desc
Oracle row count for all tables in schema, select 'select count(*) from '||table_name||';' from dba_tables where owner = 'XXX'; spool off @runme. Here is a PL/SQL approach for counting all tables in a You can also write PL/SQL procedures to count up the number of rows in a schema. Count each number of table rows Oracle ACE Laurent Schneider has a more elegant solution for counting tables, using dbms_xmlgen to store the row counts for multiple tables in a single SQL query list:
Hope this will work for you
CREATE PROCEDURE atrowcount IS selectquery1 VARCHAR2(2000); tablecount NUMBER; BEGIN selectquery1:= 'SELECT count(1) FROM :TableName'; FOR i IN (select table_name from AS_TABLE_LIST) LOOP EXECUTE IMMEDIATE selectquery1 INTO tablecount USING i.table_name; INSERT INTO table_list VALUES(i.table_name,TableCount); END LOOP; COMMIT; END AtRowCount;
count of all tables in a schema, PJ sirisha.yada via oracle-sql-l wrote: > > > > > hi all, > i need to write a procedure to get count of all tables in a schema. > plz help me proceed further. > thanks, I want to get a result: table name, count amount for each table from AS_TABLE_LIST. Get counts of all tables in a schema - errors. Simple PL/SQL to check if
Record Count of all tables in a schema, I want to know the record count of all tables starting with ABC_ in my schema In toad I have an option which count(*) Record_count From ABC_1 UNION ALL Select 'ABC_2' Table_name, count(*) Record_count From On Tue, Mar 11, 2008 at 5:52 PM, ahans via oracle-sql-l Error Logging in ODI- Oracle Data Integrator. Prefix all your table_names with some string or character say 'B'.. then it will be very useful here is a pl/sql program for your requirement..
ORACLE : GET RECORD COUNTS OF ALL TABLES IN A SCHEMA , You should get 'PL/SQL procedure successfully completed.' message from the editor. Now the table names and rows count is stored in our newly The simple answer would be: select count (*) from table_name ; But, then you can investigate dba_tables for num_rows for steady-state info assuming you update your data. However, if your catalog is being hammered with locks your DBA team may have an alternative schema to use.
count(*) for all tables, SQL> select table_name, xmlcast (xmlquery ( ('count(ora:view("' || table_name Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 select table_name from user_tables; doesn't list my tables from PM_CMP_MODEL schema. 2. Although in this the code I posted I'm trying to only get count(*), I would later want to get a dump of the actual contents.