Select count(*) from multiple tables

You can use the UNION operator to combine the results of multiple SELECT COUNT(*) statements to get the total count of rows across multiple tables. The basic syntax is as follows:

SELECT COUNT(*) FROM table1

UNION

SELECT COUNT(*) FROM table2

UNION

SELECT COUNT(*) FROM table3

For example:

SELECT COUNT(*) FROM employees

UNION

SELECT COUNT(*) FROM departments

UNION

SELECT COUNT(*) FROM salaries

This will return the total number of rows in the "employees", "departments", and "salaries" tables. Alternatively, you can use UNION ALL instead of UNION to include duplicate rows in the results. You can also use a subquery in the from clause.

SELECT (SELECT COUNT(*) FROM employees) as employees_count,

       (SELECT COUNT(*) FROM departments) as departments_count,

       (SELECT COUNT(*) FROM salaries) as salaries_count

FROM DUAL;

This will return the total number of rows in each table in separate columns.

It is also worth noting that starting from Oracle 12c, you can use the COUNT(*) OVER() function to count rows in multiple tables, but it needs to be executed separately for each table. It is important to note that, when running the query, you should replace "table1", "table2", etc., with the actual names of the tables whose rows you want to count.