There is no join, so Oracle will form a cartesian product of rows in the 2 tables. These 2 tables have no columns named the same, so there is no need to use a table name (or alias) to distinguish which table the column comes from, so the alias is likely never used, avoiding any "ORA-00918: column ambiguously defined".
It executes, but is the result meaningful?
I don't think it is useful... I think you "got lucky".
Ok, possibly relevant, so I went back and made the example non-Cartesian as such:
SELECT owner, Table_Name, COLUMN_name
FROM All_Tab_Cols a,
WHERE owner = username
AND Column_Name LIKE 'R%';
and still Oracle doesn't squawk about using the table alias 'a' for two different tables.
It looks like Oracle allows duplicate table alaises as long as it doesn't really need any alias at all. For example, this works
FROM scott.emp a
JOIN scott.dept a ON a.ename > a.loc
and it would work the same if you removed all the aliases. Oracle can figure out that dname and loc must belong to the dept table, and that ename must belong to emp, without the aliases.
Like Brian, I don't think this a useful feature. I think it's confusing to use the same table alias twice in the same statement (e.g., if separate sub-queries); using the same alais twice in the same query is even worse.