Aug 12, 2016

Lists the arguments of procedures & functions

In ALL_ARGUMENTS table lists the arguments of the procedures and functions that are accessible to the current user


In DBA_ARGUMENTS table lists the arguments of the procedures and functions that are available in the database.


In USER_ARGUMENTS  table lists the arguments of the procedures and functions that are owned by the current user. This view does not display the OWNER column.


select distinct owner , package_name,object_name,   argument_name, data_type, in_out, data_length,
character_set_name, type_owner, type_name, type_subname,  pls_type, char_length, char_used from all_arguments

Jul 29, 2016

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;


Jun 6, 2016

SQL- avoid the logical duplicate


Query to avoid the logical duplicate records .(Technically these records are not duplicate ie: we can avoid records using the distinct keyword)
 
create table LOGICAL_DUP
( source      VARCHAR2(100),
  destination VARCHAR2(100),
  distance    NUMBER
) ;

insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'BB', 5000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('CC', 'DD', 1000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('FF', 'GG', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('AA', 'CC', 3000);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('GG', 'FF', 1500);
insert into LOGICAL_DUP (SOURCE, DESTINATION, DISTANCE) 
values ('BB', 'AA', 5000); 

commit;
 
Output required is 
 
 AA  BB  5000
 AA  CC  3000
 CC  DD  1000
 FF  GG  1500
select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source  in (select b.destination from logical_dup b where 
b.source=a.destination)group by a.distance

union

select min(a.source),max(a.destination),a.distance from LOGICAL_DUP a
where a.source not  in (select b.destination from logical_dup b 
where b.source=a.destination)group by a.distance ;
 
OR 
 
select source,
       destination,
       distance
from (select source, 
             destination, 
             distance,
             row_number() over(partition by greatest(source, destination),
 least(source, destination) order by source, destination) rn
      from logical_dup)
where rn = 1 ;
 

May 13, 2016

Oracle Partitioning 2

Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub- partitioned by the second data distribution method.

The following composite partitions are available:
  •  Range-hash partitioning was introduced in Oracle 8i
  • Range-list partitioning was introduced in Oracle 9i
  •  Range-range partitioning was introduced in Oracle 11g
  •  List-range partitioning was introduced in Oracle 11g
  •  List-hash partitioning was introduced in Oracle 11g
  •  List-list partitioning was introduced in Oracle 11g
  •  Interval-range partitioning was introduced in Oracle 11g
  •  Interval-list partitioning was introduced in Oracle 11g
  •  Interval-hash partitioning was introduced in Oracle 11g
  •  Hash-hash partitioning was introduced in Oracle 11gR2


For More details










SUB-PARTITIONS


Utilized most often when partition strategy does not provide small enough partition units to achieve maintenance goals. Sub-partitions further divide table based another column with partitions.