Union dba_indexes and dba_tables : UNION « Result Set « Oracle PL / SQL






Union dba_indexes and dba_tables

  

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

OWNER                           TABLESPACE_NAME                 OBJECTS
------------------------------  ------------------------------  ------------------------------------------------
CTXSYS                          SYSAUX                          26 tables
CTXSYS                          SYSAUX                          47 indexes
CTXSYS                                                          11 tables
DBSNMP                          SYSAUX                          17 tables
DBSNMP                          SYSAUX                          8 indexes
DBSNMP                                                          2 indexes
DBSNMP                                                          4 tables
DEFINER                         SYSTEM                          1 indexes
DEFINER                                                         1 tables
FLOWS_020100                    SYSAUX                          160 tables
FLOWS_020100                    SYSAUX                          422 indexes
FLOWS_020100                                                    4 tables
FLOWS_FILES                     SYSAUX                          1 tables
FLOWS_FILES                     SYSAUX                          5 indexes
HR                              USERS                           19 indexes
HR                              USERS                           6 tables
HR                                                              1 tables
INV10                           SYSTEM                          1 indexes
INV10                                                           1 tables
INV11                           SYSTEM                          1 indexes
INV11                                                           1 tables
INV12                           SYSTEM                          1 indexes
INV12                                                           1 tables
INV13                           SYSTEM                          1 indexes
INV13                                                           1 tables
INV14                           SYSTEM                          1 indexes
INV14                                                           1 tables
INV15                           SYSTEM                          1 indexes
INV15                                                           1 tables
INV16                           SYSTEM                          1 indexes
INV16                                                           1 tables
INV17                           SYSTEM                          1 indexes
INV17                                                           1 tables
INV18                           SYSTEM                          1 indexes
INV18                                                           1 tables
INV19                           SYSTEM                          1 indexes
INV19                                                           1 tables
INV20                           SYSTEM                          1 indexes
INV20                                                           1 tables
JAVA2S                          SYSTEM                          12 indexes
JAVA2S                          SYSTEM                          14 tables
JAVA2S                                                          3 indexes
JAVA2S                                                          9 tables
MDSYS                           SYSTEM                          30 tables
MDSYS                           SYSTEM                          51 indexes
MDSYS                                                           3 indexes
MDSYS                                                           7 tables
OUTLN                           SYSTEM                          3 tables
OUTLN                           SYSTEM                          4 indexes
SYS                             SYSAUX                          175 tables
SYS                             SYSAUX                          247 indexes
SYS                             SYSTEM                          402 tables
SYS                             SYSTEM                          513 indexes
SYS                                                             28 indexes
SYS                                                             92 tables
SYSTEM                          SYSAUX                          21 indexes
SYSTEM                          SYSAUX                          22 tables
SYSTEM                          SYSTEM                          145 indexes
SYSTEM                          SYSTEM                          85 tables
SYSTEM                                                          31 tables
SYSTEM                                                          36 indexes
TSMSYS                          SYSAUX                          1 tables
TSMSYS                          SYSAUX                          2 indexes
XDB                             SYSAUX                          11 tables
XDB                             SYSAUX                          384 indexes
XDB                                                             1 indexes
                    
SQL>
SQL>
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 DBA_TABLES and DBA_INDEXES
8.Union date column
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