Here is another option. It still doesn't use your CONNECT BY (probably a good thing), but does have fewer table scans. For the SQL Fiddle I used Phil's modified data.
SELECT usr, usrmax - (slice-1)*7 WindowEnd, count(*) FROM
(
SELECT usr, max(dt) OVER (PARTITION BY Usr) usrmax
, TRUNC((max(dt) OVER (PARTITION BY Usr) - dt)/7) + 1 Slice
FROM Sales s
) a
GROUP BY usr, usrmax, slice
ORDER BY usr, WindowEnd;
+1 For an excellent and interesting question.
A. Oracle searches for a init.ora to determine the configuration
parameters
without a parameterfile, you will get an LRM-00109, ORA-01078 error, and nothing gets started
E. oracle starts an instance
we could argue what exactly starting an instance means, I have just simply put it here because this is the "exact" message that appears first when starting an instance
C. memory for the SGA gets allocated and the background processes that are necessairy get started
background processes like LGWR, DBWR, CKPT, SMON, PMON start in NOMOUNT, way before doing anything with control files...
F. oracle searches for the control files with the parameter CONTROL_FILES
thats the next one when going to MOUNT
D. Oracle searches the database files and redo log files
now tell me, how could the database perform an instance recovery without knowing where the datafiles and redo logs are
B. The proces SMON sometimes executes an instance-recovery
finally an instance recovery is performed, and database gets opened
Best Answer
If you want the size of the redo log members (files on disk) size, then use the query below:
The query output should look like: