This discussion is archived
1 Reply Latest reply: Jan 29, 2013 7:43 AM by Barry McGillin RSS

read and write info from/to file available at client side.

987728 Newbie
Currently Being Moderated
Hi,

I have some table name in one CSV file at client side.
once you get list of tables name from input CSV file at client side.
Need to run one query to know total rows and size of table in MB , this i need to repeat for all tables which i got from input file.
finally write 'table name | total rows | size of table in MB' whole info in another CSV file at client side. this output CSV file name will contain timestamp.

Please guide me in detail how to read and write file avail at client side.


I am using sql developer at client side.

version : oracle11g

Thanks in advance.
  • 1. Re: read and write info from/to file available at client side.
    Barry McGillin Journeyer
    Currently Being Moderated
    This is a simple SQL question and this forum is for SQLDEveloper, however, this is a question for our SQL*Plus support which can help with this.

    clear SCREEN
    set FEEDBACK off
    set head off

    --Gather stats to populate rownums and avg length of rows.
    --These are not exact sizes (obviously) but you get the idea.
    begin
    dbms_stats.gather_schema_stats ('<YOUR_SCHEMA');
    end;
    /
    select TABLE_NAME||','||NUM_ROWS||','||ROUND((NUM_ROWS*AVG_ROW_LEN)/(1024*1024)) CSV
    from USER_TABLES
    where num_rows is not null order by num_rows desc;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points