DBA related queries & scripts

select * from v$system_event;  -- query to get the oracle system event

select * from ( select d.*,rank() over(order by cpu_time desc) rk from v$sqlstats d ) where rk <=5; -- SQL to get the largest time taking process

SELECT value  FROM sys.v_$parameter WHERE name = 'user_dump_dest'; -- To get the location of SQL dump


Oracle Segment Advisor, can identify objects that have space for reclamation or objects that have too much row chaining. The function dbms_space.asa_recommendations verify if there are recommendations for improvement in a particular segment.

SELECT * FROM table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));


To get the size of tablespace

DECLARE
 ub NUMBER;
 ab NUMBER;
BEGIN
  dbms_space.create_table_cost('DYNAMIC',28,250000,0,ub,ab);
  dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub)||' | Alloc Bytes: ' || TO_CHAR(ab));
END;

Determines the cost of creating an index on an existing table

CREATE TABLE t ( person_id  NUMBER(5), first_name VARCHAR2(30), last_name  VARCHAR2(30));


 DECLARE
  ub  NUMBER;
  ab  NUMBER;
 BEGIN
   dbms_space.create_index_cost('CREATE INDEX t_pid ON t(person_id)', ub, ab);
   dbms_output.put_line('Used Bytes: ' || TO_CHAR(ub));
   dbms_output.put_line('Alloc Bytes: ' || TO_CHAR(ab));
 END;