Union DBA_TABLES and DBA_INDEXES : UNION « Result Set « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Result Set
  3. UNION

Union DBA_TABLES and DBA_INDEXES

  

SQL> select
  2        Tablespace_Name,
  3        Owner,
  4        COUNT(*)||' tables' Objects
  5   from DBA_TABLES
  6  where Owner <> 'SYS'
  7  group by
  8        Tablespace_Name,
  9        Owner
 10  union
 11  select
 12        Tablespace_Name,
 13        Owner,
 14        COUNT(*)||' indexes' Objects
 15   from DBA_INDEXES
 16  where Owner <> 'SYS'
 17  group by
 18        Tablespace_Name,
 19        Owner;
                                                              

TABLESPACE_NAME                 OWNER         OBJECTS
------------------------------  ------------  --------------------
SYSAUX                          CTXSYS        26 tables
SYSAUX                                        47 indexes
SYSAUX                          DBSNMP        17 tables
SYSAUX                                        8 indexes
SYSAUX                          FLOWS_020100  160 tables
SYSAUX                                        422 indexes
SYSAUX                          FLOWS_FILES   1 tables
SYSAUX                                        5 indexes
SYSAUX                          SYSTEM        21 indexes
SYSAUX                                        22 tables
SYSAUX                          TSMSYS        1 tables
SYSAUX                                        2 indexes
SYSAUX                          XDB           11 tables
SYSAUX                                        384 indexes
SYSTEM                          DEFINER       1 indexes
SYSTEM                          INV10         1 indexes
SYSTEM                          INV11         1 indexes
SYSTEM                          INV12         1 indexes
SYSTEM                          INV13         1 indexes
SYSTEM                          INV14         1 indexes
SYSTEM                          INV15         1 indexes
SYSTEM                          INV16         1 indexes
SYSTEM                          INV17         1 indexes
SYSTEM                          INV18         1 indexes
SYSTEM                          INV19         1 indexes
SYSTEM                          INV20         1 indexes
SYSTEM                          JAVA2S        12 indexes
SYSTEM                                        14 tables
SYSTEM                          MDSYS         30 tables
SYSTEM                                        51 indexes
SYSTEM                          OUTLN         3 tables
SYSTEM                                        4 indexes
SYSTEM                          SYSTEM        145 indexes
SYSTEM                                        85 tables
USERS                           HR            19 indexes
USERS                                         6 tables
                                CTXSYS        11 tables
                                DBSNMP        2 indexes
                                              4 tables
                                DEFINER       1 tables
                                FLOWS_020100  4 tables
                                HR            1 tables
                                INV10         1 tables
                                INV11         1 tables
                                INV12         1 tables
                                INV13         1 tables
                                INV14         1 tables
                                INV15         1 tables
                                INV16         1 tables
                                INV17         1 tables
                                INV18         1 tables
                                INV19         1 tables
                                INV20         1 tables
                                JAVA2S        3 indexes
                                              9 tables
                                MDSYS         3 indexes
                                              7 tables
                                SYSTEM        31 tables
                                              36 indexes
                                XDB           1 indexes

SQL>








Related examples in the same category

1.Union result set in action
2.union with like operator
3.UNION: adds all rows from the first and the second query, eliminate any duplicates, and return the results
4.A UNION query that uses an ORDER BY clause
5.Combining Data with UNION and subquery
6.Combining Data with UNION for single column
7.Union date column
8.Union dba_indexes and dba_tables
9.Union two complex queries and subquery
10.Eliminate the duplication and mimic a UNION operator by appending the DISTINCT operator
11.Take the result of the MULTISET UNION DISTINCT operation and pass it as an argument to the SET operator to eliminate duplicates.
12.Using union in subquery