Oracle Useful scripts

select lo.*, 
       a.sql_text
from gv$sqlarea a, gv$session_longops lo
where lo.sql_id = a.sql_id
order by lo.start_time;


select s.sid, 
       s.username,
       s.machine,
       s.osuser, 
       cpu_time,
       (elapsed_time/1000000)/60 as minutes,
       sql_text
from gv$sqlarea a, gv$session s
where s.sql_id = a.sql_id
and s.machine  ='AMERICAS\D6W0014G';
SELECT t.used_ublk, t.used_urec, sa.*
      FROM gv$transaction t, gv$session s, gv$sqlarea sa
     WHERE t.ses_addr = s.saddr
       AND s.sql_id = sa.sql_id  ;
select * form gV$SQL_MONITOR;
select tr.used_ublk, tr.used_urec, tr.* from gv$transaction tr;
SQL> select count(*), inst_id from gv$session group by inst_id;
 
select  p.table_name parent_table, c.table_name child_table
 from all_constraints c, all_constraints p
where c.r_constraint_name = p.constraint_name
and  p.table_name ='HP_UNIT_SERIAL_NUMBER'
and c.constraint_type in ('R','F');

create indexes in parallel:
alter session enable parallel ddl;
CREATE <index name>
ON table.column parallel 4;
alter index <index name> noparallel;
alter session disable parallel ddl;

Flashback talbe <tableName> to before drop;
Analytic Functions:
select pl_hpe, ct, sum(ct) over(order by pl_hpe), sum(ct) over() from (
 select pl_hpe,count(*) ct from <tableName> where pl_hpe is not null
 group by pl_hpe having count(*)>1 order by 2 desc);
Rows to LIST
select listagg((''''||pl_hpe||'''') ,',') within group (order by pl_hpe) from <tableName> WHERE PL_HPE IS NOT  NULL;-------------------------------------------------------------------------------------------------
select owner,sum(bytes)/1024/1024/1024 size_in_GB from dba_segments group by rollup( owner) order by 2 desc;

SELECT * FROM
(SELECT *, RANK() over (partition by sessionID,order by timestamp desc) as rank FROM clicks) ranked_clicks
WHERE ranked_clicks.rank=1;