Row Count for Tables in the database

View All Scripts Login to Run Script

Script Name Row Count for Tables in the database
Description Row Counts from tables in database in descending order
Area SQL General / SQL Query
Contributor Ramesh Doraiswamy
Created Tuesday March 14, 2017

Statement 1

select owner, table_name, nvl(num_rows,-1) 
from all_tables 
order by nvl(num_rows,-1) desc


OWNER TABLE_NAME NVL(NUM_ROWS,-1)
SH SALES 918843
AV SALES_FACT 86880
SH COSTS 82112
SH CUSTOMERS 55500
OLYM OLYM_MEDALS 29215
OLYM OLYM_ATHLETE_GAMES 26006
OLYM OLYM_ATHLETES 21415
MDSYS SDO_COORD_OP_PARAM_VALS 15082
SH FWEEK_PSCAT_SALES_MV 11266
MDSYS SDO_CS_SRS 5982
MDSYS SDO_COORD_REF_SYS 5982
DD DD_MEMBERS 5041
SH SUPPLEMENTARY_DEMOGRAPHICS 4500
MDSYS SDO_COORD_OPS 3655
CTXSYS DR$NUMBER_SEQUENCE 2048
SH TIMES 1826
MDSYS SDO_COORD_OP_PARAM_USE 876
OLYM OLYM_EVENTS 750
MDSYS SDO_DATUMS 722
OLYM OLYM_BASE_EVENTS 648
SH PROMOTIONS 503
DD DD_SEED_DATA 500
CTXSYS DR$OBJECT_ATTRIBUTE 474
MDSYS SDO_COORD_OP_PATHS 392
MDSYS SDO_COORD_AXES 292
SYS AUDIT_ACTIONS 239
WORLD WORLD_POPULATION 215
AV GEOGRAPHY_DIM 181
MDSYS SDO_COORD_OP_PARAMS 179
MDSYS SDO_UNITS_OF_MEASURE 141
MDSYS SDO_COORD_SYS 139
OLYM OLYM_NATIONS 138
MDSYS SDO_CRS_GEOGRAPHIC_PLUS_HEIGHT 132
MDSYS SDO_COORD_OP_METHODS 129
MDSYS SDO_DATUMS_OLD_SNAPSHOT 118
HR EMPLOYEES 107
MDSYS SDO_ELLIPSOIDS 105
SH PRODUCTS 72
AV TIME_DIM 60
OLYM OLYM_DISCIPLINES 56
SH CAL_MONTH_SALES_MV 48
MDSYS SDO_ELLIPSOIDS_OLD_SNAPSHOT 47
OLYM OLYM_SPORTS 42
MDSYS SDO_PROJECTIONS_OLD_SNAPSHOT 42
DD DD_MESSAGES 40
MDSYS SDO_COORD_AXIS_NAMES 31
HR DEPARTMENTS 27
OLYM OLYM_GAMES 26
HR COUNTRIES 25
SH COUNTRIES 23
HR LOCATIONS 23
DD DD_LOCATIONS 20
HR JOBS 19
MDSYS SDO_FEATURE_USAGE 19
MDSYS SDO_PRIME_MERIDIANS 17
SCOTT EMP 14
HR JOB_HISTORY 10
AV PRODUCT_DIM 8
DD DD_DINOSAURS 8
MDSYS SDO_XML_SCHEMAS 6
SH CHANNELS 5
HR REGIONS 4
SYS USER_PRIVILEGE_MAP 4
SCOTT DEPT 4
MDSYS RDF_PARAMETER 3
MDSYS SDO_PREFERRED_OPS_SYSTEM 2
SYS DUAL 1
DD DD_SETTINGS 1
MDSYS SDO_GEOR_XMLSCHEMA_TABLE 1
CTXSYS DR$POLICY_TAB 0
MDSYS SDO_SRIDS_BY_URN_PATTERN 0
CTXSYS DR$THS_PHRASE 0
SH DIMENSION_EXCEPTIONS 0
DD DD_TEMPLATES 0
DD DD_DEQUEUE_ERRORS 0
MDSYS SRSNAMESPACE_TABLE 0
MDSYS OGIS_SPATIAL_REFERENCE_SYSTEMS 0
MDSYS OGIS_GEOMETRY_COLUMNS 0
MDSYS SDO_PREFERRED_OPS_USER 0
MDSYS SDO_GEOR_PLUGIN_REGISTRY 0
MDSYS SDO_WS_CONFERENCE 0
MDSYS SDO_WS_CONFERENCE_RESULTS 0
MDSYS SDO_WS_CONFERENCE_PARTICIPANTS 0
MDSYS SDO_TIN_PC_SEQ 0
MDSYS SDO_TIN_PC_SYSDATA_TABLE 0
MDSYS SDO_INDEX_HISTOGRAM_TABLE 0
MDSYS NTV2_XML_DATA 0
MDSYS SDO_SRIDS_BY_URN 0
CTXSYS DR$THS 0
SQL_MRBUNJPRVGJFZTCHILNFOIMZG RD_TEST -1
MDSYS SDO_WFS_LOCAL_TXNS -1
SYS ODCI_SECOBJ$ -1
SYS ODCI_WARNINGS$ -1
SYS PLAN_TABLE$ -1
MDSYS SDO_CS_CONTEXT_INFORMATION -1
MDSYS SDO_ST_TOLERANCE -1
MDSYS SDO_TXN_JOURNAL -1
MDSYS SDO_TXN_IDX_EXP_UPD_RGN -1
MDSYS SDO_TOPO_TRANSACT_DATA -1
MDSYS SDO_TOPO_RELATION_DATA -1
MDSYS SDO_TOPO_DATA$ -1
MDSYS SDO_GR_MOSAIC_0 -1
MDSYS SDO_GR_MOSAIC_1 -1
MDSYS SDO_GR_MOSAIC_2 -1
MDSYS SDO_GR_MOSAIC_3 -1
MDSYS SDO_GR_MOSAIC_CB -1
MDSYS SDO_GR_PARALLEL -1
MDSYS SDO_GR_RDT_1 -1
SYS ODCI_PMO_ROWIDS$ -1

109 rows selected.

OWNER	TABLE_NAME	NVL(NUM_ROWS,-1)
SH	SALES	918843
AV	SALES_FACT	86880
SH	COSTS	82112
SH	CUSTOMERS	55500
OLYM	OLYM_MEDALS	29215
OLYM	OLYM_ATHLETE_GAMES	26006
OLYM	OLYM_ATHLETES	21415
MDSYS	SDO_COORD_OP_PARAM_VALS	15082
SH	FWEEK_PSCAT_SALES_MV	11266
MDSYS	SDO_CS_SRS	5982
MDSYS	SDO_COORD_REF_SYS	5982
DD	DD_MEMBERS	5041
SH	SUPPLEMENTARY_DEMOGRAPHICS	4500
MDSYS	SDO_COORD_OPS	3655
CTXSYS	DR$NUMBER_SEQUENCE	2048
SH	TIMES	1826
MDSYS	SDO_COORD_OP_PARAM_USE	876
OLYM	OLYM_EVENTS	750
MDSYS	SDO_DATUMS	722
OLYM	OLYM_BASE_EVENTS	648
SH	PROMOTIONS	503
DD	DD_SEED_DATA	500
CTXSYS	DR$OBJECT_ATTRIBUTE	474
MDSYS	SDO_COORD_OP_PATHS	392
MDSYS	SDO_COORD_AXES	292
SYS	AUDIT_ACTIONS	239
WORLD	WORLD_POPULATION	215
AV	GEOGRAPHY_DIM	181
MDSYS	SDO_COORD_OP_PARAMS	179
MDSYS	SDO_UNITS_OF_MEASURE	141
MDSYS	SDO_COORD_SYS	139
OLYM	OLYM_NATIONS	138
MDSYS	SDO_CRS_GEOGRAPHIC_PLUS_HEIGHT	132
MDSYS	SDO_COORD_OP_METHODS	129
MDSYS	SDO_DATUMS_OLD_SNAPSHOT	118
HR	EMPLOYEES	107
MDSYS	SDO_ELLIPSOIDS	105
SH	PRODUCTS	72
AV	TIME_DIM	60
OLYM	OLYM_DISCIPLINES	56
SH	CAL_MONTH_SALES_MV	48
MDSYS	SDO_ELLIPSOIDS_OLD_SNAPSHOT	47
OLYM	OLYM_SPORTS	42
MDSYS	SDO_PROJECTIONS_OLD_SNAPSHOT	42
DD	DD_MESSAGES	40
MDSYS	SDO_COORD_AXIS_NAMES	31
HR	DEPARTMENTS	27
OLYM	OLYM_GAMES	26
HR	COUNTRIES	25
SH	COUNTRIES	23
HR	LOCATIONS	23
DD	DD_LOCATIONS	20
HR	JOBS	19
MDSYS	SDO_FEATURE_USAGE	19
MDSYS	SDO_PRIME_MERIDIANS	17
SCOTT	EMP	14
HR	JOB_HISTORY	10
AV	PRODUCT_DIM	8
DD	DD_DINOSAURS	8
MDSYS	SDO_XML_SCHEMAS	6
SH	CHANNELS	5
HR	REGIONS	4
SYS	USER_PRIVILEGE_MAP	4
SCOTT	DEPT	4
MDSYS	RDF_PARAMETER	3
MDSYS	SDO_PREFERRED_OPS_SYSTEM	2
SYS	DUAL	1
DD	DD_SETTINGS	1
MDSYS	SDO_GEOR_XMLSCHEMA_TABLE	1
CTXSYS	DR$POLICY_TAB	0
MDSYS	SDO_SRIDS_BY_URN_PATTERN	0
CTXSYS	DR$THS_PHRASE	0
SH	DIMENSION_EXCEPTIONS	0
DD	DD_TEMPLATES	0
DD	DD_DEQUEUE_ERRORS	0
MDSYS	SRSNAMESPACE_TABLE	0
MDSYS	OGIS_SPATIAL_REFERENCE_SYSTEMS	0
MDSYS	OGIS_GEOMETRY_COLUMNS	0
MDSYS	SDO_PREFERRED_OPS_USER	0
MDSYS	SDO_GEOR_PLUGIN_REGISTRY	0
MDSYS	SDO_WS_CONFERENCE	0
MDSYS	SDO_WS_CONFERENCE_RESULTS	0
MDSYS	SDO_WS_CONFERENCE_PARTICIPANTS	0
MDSYS	SDO_TIN_PC_SEQ	0
MDSYS	SDO_TIN_PC_SYSDATA_TABLE	0
MDSYS	SDO_INDEX_HISTOGRAM_TABLE	0
MDSYS	NTV2_XML_DATA	0
MDSYS	SDO_SRIDS_BY_URN	0
CTXSYS	DR$THS	0
SQL_MRBUNJPRVGJFZTCHILNFOIMZG	RD_TEST	-1
MDSYS	SDO_WFS_LOCAL_TXNS	-1
SYS	ODCI_SECOBJ$	-1
SYS	ODCI_WARNINGS$	-1
SYS	PLAN_TABLE$	-1
MDSYS	SDO_CS_CONTEXT_INFORMATION	-1
MDSYS	SDO_ST_TOLERANCE	-1
MDSYS	SDO_TXN_JOURNAL	-1
MDSYS	SDO_TXN_IDX_EXP_UPD_RGN	-1
MDSYS	SDO_TOPO_TRANSACT_DATA	-1
MDSYS	SDO_TOPO_RELATION_DATA	-1
MDSYS	SDO_TOPO_DATA$	-1
MDSYS	SDO_GR_MOSAIC_0	-1
MDSYS	SDO_GR_MOSAIC_1	-1
MDSYS	SDO_GR_MOSAIC_2	-1
MDSYS	SDO_GR_MOSAIC_3	-1
MDSYS	SDO_GR_MOSAIC_CB	-1
MDSYS	SDO_GR_PARALLEL	-1
MDSYS	SDO_GR_RDT_1	-1
SYS	ODCI_PMO_ROWIDS$	-1

Statement 2

select owner, table_name, nvl(num_rows,-1) rowcount 
from all_tables 
order by nvl(num_rows,-1) desc


OWNER TABLE_NAME ROWCOUNT
SH SALES 918843
AV SALES_FACT 86880
SH COSTS 82112
SH CUSTOMERS 55500
OLYM OLYM_MEDALS 29215
OLYM OLYM_ATHLETE_GAMES 26006
OLYM OLYM_ATHLETES 21415
MDSYS SDO_COORD_OP_PARAM_VALS 15082
SH FWEEK_PSCAT_SALES_MV 11266
MDSYS SDO_CS_SRS 5982
MDSYS SDO_COORD_REF_SYS 5982
DD DD_MEMBERS 5041
SH SUPPLEMENTARY_DEMOGRAPHICS 4500
MDSYS SDO_COORD_OPS 3655
CTXSYS DR$NUMBER_SEQUENCE 2048
SH TIMES 1826
MDSYS SDO_COORD_OP_PARAM_USE 876
OLYM OLYM_EVENTS 750
MDSYS SDO_DATUMS 722
OLYM OLYM_BASE_EVENTS 648
SH PROMOTIONS 503
DD DD_SEED_DATA 500
CTXSYS DR$OBJECT_ATTRIBUTE 474
MDSYS SDO_COORD_OP_PATHS 392
MDSYS SDO_COORD_AXES 292
SYS AUDIT_ACTIONS 239
WORLD WORLD_POPULATION 215
AV GEOGRAPHY_DIM 181
MDSYS SDO_COORD_OP_PARAMS 179
MDSYS SDO_UNITS_OF_MEASURE 141
MDSYS SDO_COORD_SYS 139
OLYM OLYM_NATIONS 138
MDSYS SDO_CRS_GEOGRAPHIC_PLUS_HEIGHT 132
MDSYS SDO_COORD_OP_METHODS 129
MDSYS SDO_DATUMS_OLD_SNAPSHOT 118
HR EMPLOYEES 107
MDSYS SDO_ELLIPSOIDS 105
SH PRODUCTS 72
AV TIME_DIM 60
OLYM OLYM_DISCIPLINES 56
SH CAL_MONTH_SALES_MV 48
MDSYS SDO_ELLIPSOIDS_OLD_SNAPSHOT 47
OLYM OLYM_SPORTS 42
MDSYS SDO_PROJECTIONS_OLD_SNAPSHOT 42
DD DD_MESSAGES 40
MDSYS SDO_COORD_AXIS_NAMES 31
HR DEPARTMENTS 27
OLYM OLYM_GAMES 26
HR COUNTRIES 25
SH COUNTRIES 23
HR LOCATIONS 23
DD DD_LOCATIONS 20
HR JOBS 19
MDSYS SDO_FEATURE_USAGE 19
MDSYS SDO_PRIME_MERIDIANS 17
SCOTT EMP 14
HR JOB_HISTORY 10
AV PRODUCT_DIM 8
DD DD_DINOSAURS 8
MDSYS SDO_XML_SCHEMAS 6
SH CHANNELS 5
HR REGIONS 4
SYS USER_PRIVILEGE_MAP 4
SCOTT DEPT 4
MDSYS RDF_PARAMETER 3
MDSYS SDO_PREFERRED_OPS_SYSTEM 2
SYS DUAL 1
DD DD_SETTINGS 1
MDSYS SDO_GEOR_XMLSCHEMA_TABLE 1
CTXSYS DR$POLICY_TAB 0
MDSYS SDO_SRIDS_BY_URN_PATTERN 0
CTXSYS DR$THS_PHRASE 0
SH DIMENSION_EXCEPTIONS 0
DD DD_TEMPLATES 0
DD DD_DEQUEUE_ERRORS 0
MDSYS SRSNAMESPACE_TABLE 0
MDSYS OGIS_SPATIAL_REFERENCE_SYSTEMS 0
MDSYS OGIS_GEOMETRY_COLUMNS 0
MDSYS SDO_PREFERRED_OPS_USER 0
MDSYS SDO_GEOR_PLUGIN_REGISTRY 0
MDSYS SDO_WS_CONFERENCE 0
MDSYS SDO_WS_CONFERENCE_RESULTS 0
MDSYS SDO_WS_CONFERENCE_PARTICIPANTS 0
MDSYS SDO_TIN_PC_SEQ 0
MDSYS SDO_TIN_PC_SYSDATA_TABLE 0
MDSYS SDO_INDEX_HISTOGRAM_TABLE 0
MDSYS NTV2_XML_DATA 0
MDSYS SDO_SRIDS_BY_URN 0
CTXSYS DR$THS 0
SQL_MRBUNJPRVGJFZTCHILNFOIMZG RD_TEST -1
MDSYS SDO_WFS_LOCAL_TXNS -1
SYS ODCI_SECOBJ$ -1
SYS ODCI_WARNINGS$ -1
SYS PLAN_TABLE$ -1
MDSYS SDO_CS_CONTEXT_INFORMATION -1
MDSYS SDO_ST_TOLERANCE -1
MDSYS SDO_TXN_JOURNAL -1
MDSYS SDO_TXN_IDX_EXP_UPD_RGN -1
MDSYS SDO_TOPO_TRANSACT_DATA -1
MDSYS SDO_TOPO_RELATION_DATA -1
MDSYS SDO_TOPO_DATA$ -1
MDSYS SDO_GR_MOSAIC_0 -1
MDSYS SDO_GR_MOSAIC_1 -1
MDSYS SDO_GR_MOSAIC_2 -1
MDSYS SDO_GR_MOSAIC_3 -1
MDSYS SDO_GR_MOSAIC_CB -1
MDSYS SDO_GR_PARALLEL -1
MDSYS SDO_GR_RDT_1 -1
SYS ODCI_PMO_ROWIDS$ -1

109 rows selected.

OWNER	TABLE_NAME	ROWCOUNT
SH	SALES	918843
AV	SALES_FACT	86880
SH	COSTS	82112
SH	CUSTOMERS	55500
OLYM	OLYM_MEDALS	29215
OLYM	OLYM_ATHLETE_GAMES	26006
OLYM	OLYM_ATHLETES	21415
MDSYS	SDO_COORD_OP_PARAM_VALS	15082
SH	FWEEK_PSCAT_SALES_MV	11266
MDSYS	SDO_CS_SRS	5982
MDSYS	SDO_COORD_REF_SYS	5982
DD	DD_MEMBERS	5041
SH	SUPPLEMENTARY_DEMOGRAPHICS	4500
MDSYS	SDO_COORD_OPS	3655
CTXSYS	DR$NUMBER_SEQUENCE	2048
SH	TIMES	1826
MDSYS	SDO_COORD_OP_PARAM_USE	876
OLYM	OLYM_EVENTS	750
MDSYS	SDO_DATUMS	722
OLYM	OLYM_BASE_EVENTS	648
SH	PROMOTIONS	503
DD	DD_SEED_DATA	500
CTXSYS	DR$OBJECT_ATTRIBUTE	474
MDSYS	SDO_COORD_OP_PATHS	392
MDSYS	SDO_COORD_AXES	292
SYS	AUDIT_ACTIONS	239
WORLD	WORLD_POPULATION	215
AV	GEOGRAPHY_DIM	181
MDSYS	SDO_COORD_OP_PARAMS	179
MDSYS	SDO_UNITS_OF_MEASURE	141
MDSYS	SDO_COORD_SYS	139
OLYM	OLYM_NATIONS	138
MDSYS	SDO_CRS_GEOGRAPHIC_PLUS_HEIGHT	132
MDSYS	SDO_COORD_OP_METHODS	129
MDSYS	SDO_DATUMS_OLD_SNAPSHOT	118
HR	EMPLOYEES	107
MDSYS	SDO_ELLIPSOIDS	105
SH	PRODUCTS	72
AV	TIME_DIM	60
OLYM	OLYM_DISCIPLINES	56
SH	CAL_MONTH_SALES_MV	48
MDSYS	SDO_ELLIPSOIDS_OLD_SNAPSHOT	47
OLYM	OLYM_SPORTS	42
MDSYS	SDO_PROJECTIONS_OLD_SNAPSHOT	42
DD	DD_MESSAGES	40
MDSYS	SDO_COORD_AXIS_NAMES	31
HR	DEPARTMENTS	27
OLYM	OLYM_GAMES	26
HR	COUNTRIES	25
SH	COUNTRIES	23
HR	LOCATIONS	23
DD	DD_LOCATIONS	20
HR	JOBS	19
MDSYS	SDO_FEATURE_USAGE	19
MDSYS	SDO_PRIME_MERIDIANS	17
SCOTT	EMP	14
HR	JOB_HISTORY	10
AV	PRODUCT_DIM	8
DD	DD_DINOSAURS	8
MDSYS	SDO_XML_SCHEMAS	6
SH	CHANNELS	5
HR	REGIONS	4
SYS	USER_PRIVILEGE_MAP	4
SCOTT	DEPT	4
MDSYS	RDF_PARAMETER	3
MDSYS	SDO_PREFERRED_OPS_SYSTEM	2
SYS	DUAL	1
DD	DD_SETTINGS	1
MDSYS	SDO_GEOR_XMLSCHEMA_TABLE	1
CTXSYS	DR$POLICY_TAB	0
MDSYS	SDO_SRIDS_BY_URN_PATTERN	0
CTXSYS	DR$THS_PHRASE	0
SH	DIMENSION_EXCEPTIONS	0
DD	DD_TEMPLATES	0
DD	DD_DEQUEUE_ERRORS	0
MDSYS	SRSNAMESPACE_TABLE	0
MDSYS	OGIS_SPATIAL_REFERENCE_SYSTEMS	0
MDSYS	OGIS_GEOMETRY_COLUMNS	0
MDSYS	SDO_PREFERRED_OPS_USER	0
MDSYS	SDO_GEOR_PLUGIN_REGISTRY	0
MDSYS	SDO_WS_CONFERENCE	0
MDSYS	SDO_WS_CONFERENCE_RESULTS	0
MDSYS	SDO_WS_CONFERENCE_PARTICIPANTS	0
MDSYS	SDO_TIN_PC_SEQ	0
MDSYS	SDO_TIN_PC_SYSDATA_TABLE	0
MDSYS	SDO_INDEX_HISTOGRAM_TABLE	0
MDSYS	NTV2_XML_DATA	0
MDSYS	SDO_SRIDS_BY_URN	0
CTXSYS	DR$THS	0
SQL_MRBUNJPRVGJFZTCHILNFOIMZG	RD_TEST	-1
MDSYS	SDO_WFS_LOCAL_TXNS	-1
SYS	ODCI_SECOBJ$	-1
SYS	ODCI_WARNINGS$	-1
SYS	PLAN_TABLE$	-1
MDSYS	SDO_CS_CONTEXT_INFORMATION	-1
MDSYS	SDO_ST_TOLERANCE	-1
MDSYS	SDO_TXN_JOURNAL	-1
MDSYS	SDO_TXN_IDX_EXP_UPD_RGN	-1
MDSYS	SDO_TOPO_TRANSACT_DATA	-1
MDSYS	SDO_TOPO_RELATION_DATA	-1
MDSYS	SDO_TOPO_DATA$	-1
MDSYS	SDO_GR_MOSAIC_0	-1
MDSYS	SDO_GR_MOSAIC_1	-1
MDSYS	SDO_GR_MOSAIC_2	-1
MDSYS	SDO_GR_MOSAIC_3	-1
MDSYS	SDO_GR_MOSAIC_CB	-1
MDSYS	SDO_GR_PARALLEL	-1
MDSYS	SDO_GR_RDT_1	-1
SYS	ODCI_PMO_ROWIDS$	-1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database