I have created a temporary table which has "Table_Name" and "Count" columns.
What I want to do is automatically populate this table with each table name in the database (not sys or system) and it's row count, from that I can create a truncate statement easily.
The statement I am trying to get to work is:
'select 'select count(*), table_name from '||owner||'.'||table_name||';' from dba_tables
where owner not in ('SYS','SYSTEM');
I know that eventually this will turn into an 'update table ....' statement but at the moment I am just trying to get this bit to work.
I'm sure that this could be done using PLSQL but I want to do it with pure SQL
Any advice would be greatly appreciated
SELECT 'INSERT INTO Temp_Table (TableName, Count) SELECT '''||Owner||'.'||Table_Name||''', COUNT(*) FROM '||OWNER||'.'||TABLE_NAME||';'
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY Table_Name
This would give you output like:
INSERT INTO Temp_Table (TableName, Count) SELECT 'OWNER1.TABLENAME1', COUNT(*) FROM OWNER1.TABLENAME1;
INSERT INTO Temp_Table (TableName, Count) SELECT 'OWNER2.TABLENAME2', COUNT(*) FROM OWNER2.TABLENAME2;
Just spool and run
Many thanks for you help..
That worked a treat ;-)
Take a look at ALL_TABLES.
SELECT TABLE_NAME, NUM_ROWS
WHERE OWNER NOT IN ('SYS','SYSTEM');
As far as I know this method only works if the tables are ANALYZED.
I did originally try and do it the way you suggested, however the schema which I would have to analyze has over 5000 tables in it (the overhead could be a bit large)
The other suggestion works well, if a bit messy.
Could anybody help me out with a PLSQL script which could do a similar job.