Skip to main content

Number of Tables by The Number of Rows in Oracle Database

Here I am giving an example to find out number of tables by the number of rows in oracle database.

Query 1: Number of all tables accessible to the current user in Oracle database by the number of rows grouped into predefined intervals.

SELECT row_interval,
       count(*) AS no_of_tables
  FROM (SELECT owner,
               table_name,
               num_rows,
               CASE
                 WHEN num_rows > 1000000000
                 THEN '1b rows and more'
                 WHEN num_rows > 1000000
                 THEN '1m - 1b rows'
                 WHEN num_rows > 1000
                 THEN '1k - 1m rows'
                 WHEN num_rows > 100
                 THEN '100 - 1k rows'
                 WHEN num_rows > 10
                 THEN '10 - 100 rows'
                 ELSE '0 - 10 rows'
               END AS row_interval
          FROM sys.all_tables
-- excluding some Oracle maintained schemas
         WHERE owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
                             'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS',
                             'OWBSYS','ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',
                             'SYS','SYSMAN','SYSTEM', 'TSMSYS','DIP', 
                             'WKPROXY','WMSYS','XDB','APEX_040000', 
                             'APEX_PUBLIC_USER', 'FLOWS_30000','FLOWS_FILES',
                             'MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 
                             'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WK_TEST','WKSYS', 
                             'OUTLN')
       )
 GROUP BY row_interval
 ORDER BY row_interval;

Query 2: Number of all tables in Oracle database by the number of  rows grouped into predefined intervals. (If you have privilege on dba_tables)

SELECT row_interval,
       count(*) AS no_of_tables
  FROM (SELECT owner,
               table_name,
               num_rows,
               CASE
                 WHEN num_rows > 1000000000
                 THEN '1b rows and more'
                 WHEN num_rows > 1000000
                 THEN '1m - 1b rows'
                 WHEN num_rows > 1000
                 THEN '1k - 1m rows'
                 WHEN num_rows > 100
                 THEN '100 - 1k rows'
                 WHEN num_rows > 10
                 THEN '10 - 100 rows'
                 ELSE '0 - 10 rows'
               END AS row_interval
          FROM sys.dba_tables
-- excluding some Oracle maintained schemas
         WHERE owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
                             'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS',
                             'OWBSYS','ORDPLUGINS', 'ORDSYS', 'SI_INFORMTN_SCHEMA',
                             'SYS','SYSMAN','SYSTEM', 'TSMSYS','DIP', 
                             'WKPROXY','WMSYS','XDB','APEX_040000', 
                             'APEX_PUBLIC_USER', 'FLOWS_30000','FLOWS_FILES',
                             'MDDATA', 'ORACLE_OCM', 'XS$NULL', 'SPATIAL_CSW_ADMIN_USR', 
                             'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WK_TEST','WKSYS', 
                             'OUTLN')
       )
 GROUP BY row_interval
 ORDER BY row_interval;

Output:

Reference:

Happy APEXing!!!...

Comments

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template

Generating the report with APEX_DATA_EXPORT

With the APEX_DATA_EXPORT package, you are able to export data from Oracle Application Express in the following file types: PDF, XLSX, HTML, CSV, XML, and JSON. Step 1: Create a table and populate it with some sample records. CREATE TABLE emp   (     empno        NUMBER,     first_name   VARCHAR2(240),     last_name    VARCHAR2(240),     mgr          NUMBER,     deptno       NUMBER,     sal          NUMBER,     created_date TIMESTAMP (6),     comm         NUMBER,     hiredate     DATE,     JOB          VARCHAR2(240),     ename        VARCHAR2(240),     PRIMARY KEY (empno) USING INDEX ENABLE   ); /    INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (1, 'Larry', 'Ellison', ,                  10, 5000, LOCALTIMESTAMP);   INSERT INTO emp (empno, first_name, last_name, mgr,                   deptno, sal, created_date)         VALUES                 (2, 'Juan', 'Juan', 1,  

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  manager ,                emp . hiredate ,                 nvl ( emp . sal , 0 )  salary ,                 nvl ( emp . comm , 0 )  commission            FROM  eba_demo_chart_emp emp ,                eba_demo_chart_dept dept ,                eba_demo_chart_emp mgr           WHERE  emp . deptno = dept . deptno             AND  emp . mgr      = mgr . empno  ( + )           ORDER   BY  emp . ename