Script to Find out Row Count of All the Tables in a schema



The Eucharistic Miracles of the World
To retrieve the row count of all the tables in a schema, you can use the following SQL script. This script queries the DBA_TABLES view to get the row count for each table in the specified schema

col table_name for a40 
  owner = 'YOUR_SCHEMA_NAME';
Replace 'YOUR_SCHEMA_NAME' with the name of the schema for which you want to get the row counts. Explanation: The script queries the all_tables view, which contains information about all tables accessible to the current user. Here's what the script does: table_name: This column represents the name of each table. num_rows: This column provides the row count for each table. The WHERE clause filters the results to include only tables from the specified schema. 


 Knowing the row count of tables in a schema is useful for various purposes, including performance tuning, capacity planning, and data analysis. It helps you understand the volume of data stored in each table and can assist in identifying potential bottlenecks or areas for optimization. It's important to note that the num_rows value in the all_tables view may not always be accurate, as it depends on when statistics were last gathered. If you need precise row counts, you may consider using the COUNT function on the table itself, but this could be resource-intensive for large tables. 

 Sample Output:
---------------------------------------- ----------
ICOL$					       3996
COL$					     113394
IND$					       1865
TAB$					       1713
CLU$						 10
LOB$						259
COLTYPE$				       2353
NTAB$						443

In this sample output, you see a list of tables in the specified schema along with their corresponding row counts. Please ensure you have the necessary privileges to query the all_tables view and access the specified schema's tables. Test the script in a controlled environment before running it in a production database.


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer