'Database Object Mappings joining views

For each of your tables (use user_tables), list its tablename, tablespace, creation date, number of rows and average row length. (Note: creation date is in a different view-do not do a Cartesian join!).

How do I pull the creation date when it is in a different view? here is what I have so far. everything is user_tables except created, it is in user_objects view.

SELECT TABLE_NAME, TABLESPACE_NAME, CREATED, NUM_ROWS, AVG_ROW_LEN FROM USER_TABLES;

sqldatabaseoracle


Solution 1:[1]

Once you join user_tables and user_objects on a common column (that's table/object name), you get

SQL> SELECT
  2      t.table_name,
  3      t.tablespace_name,
  4      o.created,
  5      t.num_rows,
  6      t.avg_row_len
  7  FROM
  8           user_tables t
  9      JOIN user_objects o ON o.object_name = t.table_name
 10  WHERE
 11      ROWNUM <= 3;

TABLE_NAME      TABLESPACE_NAME CREATED               NUM_ROWS AVG_ROW_LEN
--------------- --------------- ------------------- ---------- -----------
GTT                             06.03.2022 08:35:00
SALESMAN        USERS           06.03.2022 08:43:40          2          26
STATES_TAB      USERS           07.03.2022 19:24:43          7          28

SQL>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot

Related Questions