Query to count the number of rows in each table and extract a txt file using SQL developer

Question

I'm new to using the oracle database and advanced query concepts and even PL / SQL.

I'm trying to create a query where I can see the number of rows in each table in my database and write them in a txt file. The goal is to have a kind of history of how this database is updating every day.

Until then I proceeded using this code below, which works correctly.

spool C:\Logs\LOGs.txt
select
   table_name,
   to_number(
   extractvalue(
      xmltype(
         dbms_xmlgen.getxml('select count(*) c from '||table_name))
    ,'/ROWSET/ROW/C')) count
from 
   user_tables
order by 
   table_name;

spool off;

Now I am facing two new challenges:

1 - I can't schedule this query correctly in SQL Developer because they only accept procedures and not queries or I can't find a way to schedule this query to run, for example, in task scheduler on windows.

2 - I wanted a way to write the progressive history of the database in the txt file, this query of mine only writes about the current day. I thought of a solution where the name of the file would change according to the date, that way I wouldn't replace the file but I still couldn't.

I appreciate everyone's attention and availability

Answer 1

You could wrap the query in a procedure and schedule that, but spool is a client command, so you would need to use utl_file to write to a file on the server instead.

You could, instead, save this as a script and write a batch file that runs it through SQL*Plus or SQLcl instead of SQL Developer; and then use Windows scheduling to run that batch file.

By default spool will overwrite an existing file, but you can add append to change the behaviour, as described in the documentation:

spool C:\Logs\LOGs.txt append

Having data in one file might be confusing though, even if you include the current date as another column in your query. You could change the file name each day instead.

Another option is to write the counts to a logging table within the database, which you could then query and compare easily. The insert - using your existing query plus a date - could then easily be wrapped in a procedure, and that procedure can be scheduled.