Dec 30, 2016
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
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:
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.
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.
Subscribe to:
Posts (Atom)