| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> re: row count in table ?
Run the following script:
schema"
--Date: 19th May, 2000
--Author: Ramesh Papnoi
--Subject : TIP for finding out the no. of rows in each tables
in User's Schema on which user have access.
SET PAGESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SPOOL C:\NO_OF_ROWS.SQL;
SELECT 'SELECT '||CHR(39)||RPAD(TABLE_NAME,30,' ')||' :
'||CHR(39)||'||'||' COUNT(*) FROM '||TABLE_NAME ||';' FROM USER_TABLES;
SPOOL OFF;
@C:\NO_OF_ROWS.SQL;
SET PAGESIZE 24
SET HEADING ON
SET FEEDBACK ON
CONTENT OF C:\NO_OF_ROWS.SQL:
----------------------------------------------
SELECT 'AREA_MST : '|| COUNT(*) FROM AREA_MST;
SELECT 'BASIC_MATL : '|| COUNT(*) FROM BASIC_MATL;
SELECT 'BULKMTO : '|| COUNT(*) FROM BULKMTO;
SELECT 'CODEMAPO : '|| COUNT(*) FROM CODEMAPO;
SELECT 'CODEMAPP : '|| COUNT(*) FROM CODEMAPP;
SELECT 'COPY_OF_SHTBOM : '|| COUNT(*) FROM
COPY_OF_SHTBOM;
SELECT 'DUPLGNS : '|| COUNT(*) FROM DUPLGNS;
SELECT 'ESC_MST : '|| COUNT(*) FROM ESC_MST;
SELECT 'ISO_MTO : '|| COUNT(*) FROM ISO_MTO;
SELECT 'ISO_MTOT : '|| COUNT(*) FROM ISO_MTOT;
SELECT 'ITEMTYPE : '|| COUNT(*) FROM ITEMTYPE;
SELECT 'LBULKMTO : '|| COUNT(*) FROM LBULKMTO;
SELECT 'LINEMTO : '|| COUNT(*) FROM LINEMTO;
SELECT 'LINETEMP : '|| COUNT(*) FROM LINETEMP;
SELECT 'MSR : '|| COUNT(*) FROM MSR;
SELECT 'NIPPLES : '|| COUNT(*) FROM NIPPLES;
SELECT 'PASSWORD : '|| COUNT(*) FROM PASSWORD;
SELECT 'PIP_CODE : '|| COUNT(*) FROM PIP_CODE;
SELECT 'PROJ_CODEMAPP : '|| COUNT(*) FROM
PROJ_CODEMAPP;
SELECT 'PROJ_JACKET_PCLASS : '|| COUNT(*) FROM
PROJ_JACKET_PCLASS;
SELECT 'PROJ_SHT_MATL : '|| COUNT(*) FROM
PROJ_SHT_MATL;
SELECT 'SCOPE : '|| COUNT(*) FROM SCOPE;
SELECT 'SHT_MATL : '|| COUNT(*) FROM SHT_MATL;
SELECT 'SHT_MATT : '|| COUNT(*) FROM SHT_MATT;
SELECT 'SIZEMST : '|| COUNT(*) FROM SIZEMST;
SELECT 'SPECMAST : '|| COUNT(*) FROM SPECMAST;
SELECT 'SPECS : '|| COUNT(*) FROM SPECS;
SELECT 'SPECSTMP : '|| COUNT(*) FROM SPECSTMP;
SELECT 'SPECS_AUDIT : '|| COUNT(*) FROM SPECS_AUDIT;
SELECT 'SPEC_CRITERIA : '|| COUNT(*) FROM
SPEC_CRITERIA;
SELECT 'USERROLL : '|| COUNT(*) FROM USERROLL;
SQL>
OUTPUT :
----------------------------------------------
AREA_MST : 17
BASIC_MATL : 4
BULKMTO : 0
CODEMAPO : 3460
CODEMAPP : 5282
COPY_OF_SHTBOM : 940
DUPLGNS : 0
ESC_MST : 1174
ISO_MTO : 285
ISO_MTOT : 36
ITEMTYPE : 123
LBULKMTO : 942
LINEMTO : 31561
LINETEMP : 1
MSR : 0
NIPPLES : 45
PASSWORD : 7
PIP_CODE : 2437
PROJ_CODEMAPP : 68
PROJ_JACKET_PCLASS : 16
PROJ_SHT_MATL : 35
SCOPE : 6
SHT_MATL : 2828
SHT_MATT : 2685
SIZEMST : 99
SPECMAST : 58
SPECS : 2654
SPECSTMP : 0
SPECS_AUDIT : 19
SPEC_CRITERIA : 54
USERROLL : 12
SQL>
Regards,
Ramesh D Papnoi
(BrainBench Certified Oracle 8 DBA & Developer)
(Brainbuzz Certified Oracle 8i DBA & 8i Pl-Sql)
"Kites rise against the wind - not with it."
To: internet["Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>]
All,
Is there an easy way to find out how many rows are there is each table for a schema? Thank you.
Andrea
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle INET: andreaoracle_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Apr 19 2001 - 09:27:43 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: rpapnoi_at_chemtex.co.in Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |