I need to export the records of some tables of the same schema in oracle , but due to limitations on the machine from which I am going to carry out this process, I need to know the number of rows of the tables of said schema to decide if I do it like this or I use another alternative to copy the records between one environment and another.
How can I quickly and effectively determine the number of records for all tables in the schema?
The easiest way to achieve it, that is, without procedures or complex things, is to run the following query:
SELECT owner, table_name, num_rows, sample_size, last_analyzed FROM all_tables WHERE owner = 'nombre_de_tu_esquema';
num_rows column contains the number of rows or records in each of the tables.
As @jpertejo highlights:
Keep in mind that the
NUM_ROWScolumn of the
ALL_TABLESview obtains the information from the table's statistics. If the statistics are not up-to-date, it may be that the number of rows returned by the
NUM_ROWSdoes not correspond to the real one. To know exactly the real number of rows, you would have to do a
COUNT(*). The query for the
NUM_ROWSis faster than the
COUNT(*), but it may not give correct data in some cases.
last_analyzed column is included in the query, the
last_analyzed which is to inform the date on which the most recent statistical analysis was made for each table.