Hierarchical Query and With clause

1. How to convert hierarchical query to With clause query

1.1 Hierarchy query

SELECT LEVEL,
LPAD(' ', 4*(LEVEL -1)) || first_name || ' ' || last_name
FROM employees
START WITH MANAGER_ID IS NULL
CONNECT BY manager_id = PRIOR employee_id;

1.2 converted With clause

WITH roots AS ( SELECT employee_id, manager_id,first_name || ' ' || last_name names,
1 level_grd, department_id, 10 sortnum, 1001 sortnum2
FROM employees
WHERE manager_id IS NULL ),
level2 AS ( SELECT a.employee_id, b.employee_id manager_id, a.first_name || ' ' || a.last_name names,
2 level_grd, a.department_id, a.employee_id sortnum, 100 sortnum2
FROM employees a, roots b
WHERE a.manager_id = b.employee_id ),
level3 AS ( SELECT a.employee_id, b.employee_id manager_id, a.first_name || ' ' || a.last_name names,
3 level_grd, a.department_id, b.employee_id sortnum, a.employee_id sortnum2
FROM employees a, level2 b
WHERE a.manager_id = b.employee_id ),
level4 AS ( SELECT a.employee_id, b.employee_id manager_id, a.first_name || ' ' || a.last_name names,
4 level_grd, a.department_id, c.employee_id sortnum, b.employee_id sortnum2
FROM employees a, level3 b, level2 c
WHERE a.manager_id = b.employee_id
AND b.manager_id = c.employee_id )
SELECT LPAD(' ', 4 * (level_grd - 1)) || names, level_grd, employee_id, manager_id, sortnum, sortnum2
FROM roots
UNION ALL
SELECT LPAD(' ', 4 * (level_grd - 1)) || names, level_grd, employee_id, manager_id, sortnum, sortnum2
FROM level2
UNION ALL
SELECT LPAD(' ', 4 * (level_grd - 1)) || names, level_grd, employee_id, manager_id, sortnum, sortnum2
FROM level3
UNION ALL
SELECT LPAD(' ', 4 * (level_grd - 1)) || names, level_grd, employee_id, manager_id, sortnum, sortnum2
FROM level4
ORDER BY 5, 6, 2;

How to figure out space used by all databases under ASM Disk Group.

Below script will show space usage used by all databases under ASM Diskgroup.

echo “Database Used_MB Mirror_used_MB”

for i in `asmcmd ls +vg1/*|grep +vg1|grep “:”|awk -F: ‘{ print $1}’`
do
printf $i
asmcmd du $i|grep -v Used
done;