DB version: 19c
v$datafile view's creation_time column stores the creation date of each Oracle DB's datafile.
SQL> select to_char(creation_time, 'DD-MON-YYYY HH24:MI') from v$datafile where ts# = 36 ORDER BY CREATION_TIME ASC;
TO_CHAR(CREATION_TIME,'DD-
--------------------------
15-APR-2022 17:10
15-APR-2022 17:12
15-APR-2022 17:13
15-APR-2022 13:10
19-APR-2022 10:45
21-APR-2022 16:37
28-APR-2022 10:29
29-APR-2022 16:13
04-MAY-2022 13:59
10-MAY-2022 08:44
11-MAY-2022 18:27
<snipped>
I want to know how many datafiles got added each month. And I want to sort it in ASC order for creation_time column.
Query1 and Query3 shown below got errors.
Query2 shown below worked. But, as you can see in the output, the ASCENDING sort is not working well, most probably because to_char conversion is messing it up.
Any idea how I can sort the output based on creation_time column in ASCENDING order ?
Query1.
select trunc(creation_time, 'MON-YYYY'), count(*) from v$datafile
where ts#=(select ts# from ts$ where name = 'TBS_WMS_DATA')
group by trunc(creation_time,'MON-YYYY')
order by 1;
ERROR at line 3:
ORA-01898: too many precision specifiers
Query2.
select to_char(creation_time, 'MON-YYYY'), count(*) from v$datafile
where ts#=(select ts# from ts$ where name = 'TBS_WMS_DATA')
group by to_char(creation_time, 'MON-YYYY')
order by 1 asc;
TO_CHAR(CREATION_ COUNT(*)
----------------- ----------
APR-2022 8
AUG-2022 10
DEC-2022 4
JAN-2023 3
JUL-2022 11
JUN-2022 10
JUN-2023 5
MAY-2022 8
NOV-2022 7
OCT-2022 14
SEP-2022 22
11 rows selected.
Query3.
select to_char(creation_time, 'MON-YYYY'), count(*) from v$datafile
where ts#=(select ts# from ts$ where name = 'TBS_WMS_DATA')
group by to_char(creation_time, 'MON-YYYY')
order by creation_time asc;
ERROR at line 4:
ORA-00979: not a GROUP BY expression