Dear Community,
I want to create a summarised database backup report, which includes data from multiple database tables which has no relation to each other. For example, I want to include the name of the database from v$database view:
SQL> Select name from v$database;
And the size of the database:
SQL> SELECT sum(bytes) FROM v$datafile;
And consider there is no common column between those two views.
Beside that I want to include the database size in Bytes, KBs, MBs ,......, and so on.
I am using the following query:
SQL> SELECT
"Database", "DB_SIZE_IN_Bytes", "DB_SIZE_IN_KBs", "DB_SIZE_IN_MBs",
"DB_SIZE_IN_GBs", "DB_SIZE_IN_TBs"
FROM (
SELECT
(SELECT name FROM v$database ) "Database",
(SELECT sum(bytes) FROM v$datafile ) "DB_SIZE_IN_Bytes",
(SELECT sum(bytes)/1024 FROM v$datafile ) "DB_SIZE_IN_KBs",
(SELECT sum(bytes)/1024/1024 FROM v$datafile ) "DB_SIZE_IN_MBs",
(SELECT sum(bytes)/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_GBs",
(SELECT sum(bytes)/1024/1024/1024/1024 FROM v$datafile ) "DB_SIZE_IN_TBs"
FROM dual
);
In this way I am avoiding the join condition between v$database and v$datafile.
This is just an example, however, in my project I have more than 10 tables. Most of them without a common column.
The question is while converting bytes into kbs, mbs, and so on, I want to use the column references such as:
(SELECT DB_SIZE_IN_Bytes/1024 FROM dual ) "DB_SIZE_IN_KBs",
(SELECT DB_SIZE_IN_KBs /1024 FROM dual ) "DB_SIZE_IN_MBs",
(SELECT DB_SIZE_IN_MBs /1024 FROM dual ) "DB_SIZE_IN_GBs",
(SELECT DB_SIZE_IN_GBs /1024 FROM dual ) "DB_SIZE_IN_TBs"
instead of again and again calculations. Is there any way to use the column references such as mentioned above in the example instead of re-calculation?
Regards,
Ali