- it has a specification
- the results are reproducible
- alternative explanations have been eliminated
- it is published
- it survives peer-group review
Reference
“The Burden of Proof” presentation by Jonathan Lewis -> http://jonathanlewis.wordpress.com/
SR Number 5284373.993 Support Identifier 13486073
QUESTION
=========
TNSPING WITH 9I CLIENT WORKS BUT FAILS WITH 10G WITH THE SAME ORA FILES TNS-03505
ANSWER
=======
From documentation of Oracle9i Net Services Administrator’s Guide Release 2 (9.2) October 2002 Part No. A96580-02:
Chapter 3 Configuration Management Concepts, section: Oracle Names Support
Oracle Names Support
Like directory naming support, Oracle Names provides a distributed naming
service to help simplify the setup and administration of global, client/server
computing networks. Directory naming uses an LDAP-compliant directory server,
which can have other functions in addition to supporting Oracle services. Oracle
Names, however, is Oracle proprietary, providing support only for Oracle services.
Note: In future releases, Oracle Names will not be supported as a
centralized naming method. Because no new enhancements are
being added to Oracle Names, consider using directory naming or
migrating an existing Oracle Names configuration to directory
naming, as described in Chapter 10, “Exporting Naming Data to a
Directory Server”. The material presented here is primarily for
reference to enable you to maintain your current Oracle Names environment.
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-NET
- Allows you to rank data
- RANK – leaves gaps (1,2,2,4)
- DENSE_RANK – no gaps (1,2,2,3)
- ASC is default order
- Partitions can set boundaries
- Can define NULLs as first or last in the order, default is last
SELECT channel_desc, calendar_month_desc,
TO_CHAR(TRUNC(SUM(amount_sold),-6), ‘9,999,999,999′) SALES$,
RANK() OVER (ORDER BY trunc(SUM(amount_sold),-6) DESC) AS RANK,
DENSE_RANK() OVER (ORDER BY TRUNC(SUM(amount_sold),-6) DESC) AS DENSE_RANK
FROM sales, products, customers, times, channels
WHERE sales.prod_id=products.prod_id
AND sales.cust_id=customers.cust_id
AND sales.time_id=times.time_id
AND sales.channel_id=channels.channel_id
AND times.calendar_month_desc IN (‘2003-09′, ‘2003-10′) AND channels.channel_desc<>‘Tele Sales’
GROUP BY channel_desc, calendar_month_desc;
CHANNEL_DESC CALENDAR SALES$ RANK DENSE_RANK
———— ——– ——— —— ———-
Direct Sales 2003-10 10,000,000 1 1
Direct Sales 2003-09 9,000,000 2 2
Internet 2003-09 6,000,000 3 3
Internet 2003-10 6,000,000 3 3
Catalog 2003-09 3,000,000 5 4
Catalog 2003-10 3,000,000 5 4
Partners 2003-09 2,000,000 7 5
Partners 2003-10 2,000,000 7 5
If from this result set you wanted to see the top ten PRODUCT_ID’s, you would use the original query as a subquery, as in the following listing:
select * from
(select prod_id,
sum(quantity_sold),
rank () over (order by sum(quantity_sold) desc) as rank,
dense_rank () over (order by sum(quantity_sold) desc) as dense_rank
from sales
where to_char(time_id, ‘yyyy-mm’) = ‘2001-06′
group by prod_id)
where rank < 11;
Reference; Oracle Database Data Warehousing Guide 10g Release 2 (10.2), Chapter 21 SQL for Analysis and Reporting, http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
Ian Abramson, http://www.redskydata.com/Presentations/2004_presentation_404.zip
– kullanıcı kurulumu
connect “/ as sysdba”
DROP ROLE aq_adm_role;
CREATE ROLE aq_adm_role;
GRANT CONNECT, RESOURCE, aq_administrator_role TO aq_adm_role;
EXEC SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => ‘ENQUEUE_ANY’,grantee => ‘aq_adm_role’,admin_option => FALSE);
EXEC SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => ‘DEQUEUE_ANY’,grantee => ‘aq_adm_role’,admin_option => FALSE);
commit;
DROP USER AQ_ADM ;
CREATE USER AQ_ADM IDENTIFIED BY yyy
DEFAULT TABLESPACE CLON TEMPORARY TABLESPACE TEMP5;
GRANT aq_adm_role, alter session, unlimited tablespace TO AQ_ADM ;
grant execute on SYS.dbms_aqadm to AQ_USER ;
grant execute on SYS.dbms_aq to AQ_USER ;
grant execute on SYS.dbms_aq to AQ_ADM ;
grant execute on SYS.dbms_aqadm to AQ_ADM ;
– kuyruk tabloları kurulum
connect aq_adm/yyy@xxx
– ileti tipi
CREATE type aq_adm.Message_typ as object(subject VARCHAR2(30), text VARCHAR2(80));
/
– ileti kuyruğu
begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => ‘aq_adm.objmsgs80_qtab’,
queue_payload_type => ‘aq_adm.Message_typ’,
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => ‘MSG_QUEUE’,
queue_table => ‘aq_adm.objmsgs80_qtab’);
DBMS_AQADM.START_QUEUE(queue_name => ‘MSG_QUEUE’);
end;
/
– yayılım(propagate) kuyruğu
begin
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => ‘aq_adm.objmsgs80_qtabX’,
queue_payload_type => ‘aq_adm.Message_typ’,
multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(queue_name => ‘MSG_QUEUEX’,
queue_table => ‘aq_adm.objmsgs80_qtabX’);
DBMS_AQADM.START_QUEUE(queue_name => ‘MSG_QUEUEX’);
end;
/
– kuyruğa ileti gönderen işlev
create or replace procedure enqueue_msg(p_msg in varchar2,
p_add in varchar2 default null)
as
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
message_handle RAW(16);
message aq_adm.message_typ;
recipients DBMS_AQ.aq$_recipient_list_t;
BEGIN
recipients(1) := SYS.aq$_agent(‘RECIPIENT’, p_add, null);
message_properties.recipient_list := recipients;
message := message_typ(‘NORMAL MESSAGE’, p_msg );
dbms_aq.enqueue(queue_name => ‘msg_queue’,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
end;
/
– yayılıma girmeyecek bir ileti denemesi
begin enqueue_msg(‘This is a test….’); commit; end;
/
– yayılım için gereken ilmik veritabanı bağlantısı
create database link aq_adm.LoopBack connect to aq_adm identified by admin
using ’shf’;
– yayılım kurulumu
begin DBMS_AQADM.Schedule_Propagation(Queue_Name => ‘MSG_QUEUE’,
Destination => ‘aq_adm.LoopBack’,
Start_Time => sysdate,
Latency => 0);
end;
/
– yayılım çizelgesi doğru tanımlandı mı
select * from user_queue_schedules;
– yayılıma uğramak üzere deneme iletisi gönder
begin enqueue_msg(‘This should be propagated.’,
‘aq_adm.MSG_QUEUEX@aq_adm.LoopBack’); commit; end;
/
– yayılım çizelgesinde hata oluştu mu denetle
select * from user_queue_schedules;
– yayılım başarılı oldu mu denetle
select t1.cnt, t2.cnt
from (select count(*) cnt from objmsgs80_qtab) t1,
(select count(*) cnt from objmsgs80_qtabx) t2
/
Kaynakça; http://asktom.oracle.com/pls/ask/f?p=4950:8:4484488848005704708::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8760267539329
Analytic Concepts;
- Processing order
- Result set partitions
Allows for separation of rows into groups
Based on columns or expressions
- Windowing
Defined for each row and defines range
May be fixed or variable
- Current row
Serves as data set reference point
Calculations performed around row
Types of Analytic Functions;
- Ranking
Calculate ranks, percentiles in a result set
- Windowing
Calculate cumulative or moving aggregrates
Supports: sum , avg, min, max, count, variance, stddev, first_value, last_value
- Reporting
Calculate share within a data set
Supports: as above, RATIO_TO_REPORT
How Oracle Processes Analytics;
1- Performs: Joins, WHERE, GROUP, HAVING,
2- Creates partitions, Analytics applied to each row,
3- Order by
Analytic SQL Features;
- Analytic SQL Capabilities
Rank, N-tile
Moving averages
Cumulative sums
Lag/Lead
Ratio-to-Report
Inverse Percentiles
FIRST/LAST values
- Dimensions and hierarchies
- Aggregation Capabilities
CUBE and ROLLUP
Grouping sets
Concatenated grouping sets
- Models, The new paradigm 
Reference; Oracle Database Data Warehousing Guide 10g Release 2 (10.2), Chapter 21 SQL for Analysis and Reporting, http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
Ian Abramson, http://www.redskydata.com/Presentations/2004_presentation_404.zip
The cube extension takes rollup a step further by generating subtotals for each combination of the group by attributes,
totals by attribute and the grand total. The following is an example of the cube extension, using the same query we used for rollup:
select c.cust_gender gender,
b.channel_class channel_class,
to_char(a.time_id, ‘yyyy-mm’) month,
count(*) unit_count,
sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and a.cust_id = c.cust_id
and to_char(a.time_id, ‘yyyy-mm’) between ‘2001-01′ and ‘2001-02′
group by cube(c.cust_gender,
b.channel_class,
to_char(a.time_id, ‘yyyy-mm’));
GENDER CHANNEL_CLASS MONTH UNIT_COUNT AMOUNT_SOLD
—— ——————– ——- ———- ———–
39924 4216833.67
2001-01 20739 2113709.13
2001-02 19185 2103124.54
Direct 20427 2113199.92
Direct 2001-01 11039 1106147.18
Direct 2001-02 9388 1007052.74
Others 12603 1278645.06
Others 2001-01 6796 657218.93
Others 2001-02 5807 621426.13
Indirect 6894 824988.69
Indirect 2001-01 2904 350343.02
Indirect 2001-02 3990 474645.67
F 14274 1552931.54
F 2001-01 7540 768012.01
F 2001-02 6734 784919.53
F Direct 7209 752861.03
F Direct 2001-01 4001 387000.9
F Direct 2001-02 3208 365860.13
F Others 4542 472249.42
F Others 2001-01 2486 242615.9
F Others 2001-02 2056 229633.52
F Indirect 2523 327821.09
F Indirect 2001-01 1053 138395.21
F Indirect 2001-02 1470 189425.88
M 25650 2663902.13
M 2001-01 13199 1345697.12
M 2001-02 12451 1318205.01
M Direct 13218 1360338.89
M Direct 2001-01 7038 719146.28
M Direct 2001-02 6180 641192.61
M Others 8061 806395.64
M Others 2001-01 4310 414603.03
M Others 2001-02 3751 391792.61
M Indirect 4371 497167.6
M Indirect 2001-01 1851 211947.81
M Indirect 2001-02 2520 285219.79
In the results, we can see that counts and sums of AMOUNT_SOLD are returned at the following levels:
• By GENDER, CHANNEL_CLASS and MONTH
• Subtotals by MONTH within CHANNEL_CLASS
• Subtotals by MONTH within GENDER
• Subtotals by CHANNEL_CLASS within GENDER
• Subtotals by MONTH
• Subtotals by CHANNEL_CLASS
• Subtotals by GENDER
• Grand total
Reference; Oracle Database Data Warehousing Guide 10g Release 2 (10.2), Chapter 21 SQL for Analysis and Reporting, http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
Ian Abramson, http://www.redskydata.com/Presentations/2004_presentation_404.zip
The rollup extension generates subtotals for attributes specified in the group by clause, plus another row representing the grand total.
The following is an example of the rollup extension, using the SALES table we have seen throughout this module:
select c.cust_gender gender,
b.channel_class channel_class,
to_char(a.time_id, ‘yyyy-mm’) month,
count(*) unit_count,
sum(a.amount_sold) amount_sold
from sales a, channels b, customers c
where a.channel_id = b.channel_id
and a.cust_id = c.cust_id
and to_char(a.time_id, ‘yyyy-mm’) between ‘2001-01′ and ‘2001-02′
group by rollup(c.cust_gender,
b.channel_class,
to_char(a.time_id, ‘yyyy-mm’));
GENDER CHANNEL_CLASS MONTH UNIT_COUNT AMOUNT_SOLD
—— ——————– ——- ———- ———–
F Direct 2001-01 4001 387000.9
F Direct 2001-02 3208 365860.13
F Direct 7209 752861.03
F Others 2001-01 2486 242615.9
F Others 2001-02 2056 229633.52
F Others 4542 472249.42
F Indirect 2001-01 1053 138395.21
F Indirect 2001-02 1470 189425.88
F Indirect 2523 327821.09
F 14274 1552931.54
M Direct 2001-01 7038 719146.28
M Direct 2001-02 6180 641192.61
M Direct 13218 1360338.89
M Others 2001-01 4310 414603.03
M Others 2001-02 3751 391792.61
M Others 8061 806395.64
M Indirect 2001-01 1851 211947.81
M Indirect 2001-02 2520 285219.79
M Indirect 4371 497167.6
M 25650 2663902.13
39924 4216833.67
In the results, we can see that counts and sums of AMOUNT_SOLD are returned at the following levels:
• By GENDER, CHANNEL_CLASS and MONTH
• Subtotals by CHANNEL_CLASS within GENDER
• Subtotals by GENDER
• Grand total
Reference; Oracle Database Data Warehousing Guide 10g Release 2 (10.2), Chapter 21 SQL for Analysis and Reporting, http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
Ian Abramson, http://www.redskydata.com/Presentations/2004_presentation_404.zip
One option is as follows:
(1) Turn the auditing on: AUDIT_TRAIL = true in init.ora
(2) Restart the instance if its running.
(3) Audit the table:
AUDIT INSERT,SELECT,DELETE,UPDATE on TableName
by ACCESS WHENEVER SUCCESSFUL
(4) Get the desired information using :
SELECT OBJ_NAME,ACTION_NAME ,to_char(timestamp,’dd/mm/yyyy , HH:MM:SS’)
from sys.dba_audit_object.
Hierarchical Queries; If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.
create table corporate_slaves (
slave_id integer primary key,
supervisor_id references corporate_slaves,
name varchar(100)
);
insert into corporate_slaves values (1, NULL, ‘Big Boss Man’);
insert into corporate_slaves values (2, 1, ‘VP Marketing’);
insert into corporate_slaves values (3, 1, ‘VP Sales’);
insert into corporate_slaves values (4, 3, ‘Joe Sales Guy’);
insert into corporate_slaves values (5, 4, ‘Bill Sales Assistant’);
insert into corporate_slaves values (6, 1, ‘VP Engineering’);
insert into corporate_slaves values (7, 6, ‘Jane Nerd’);
insert into corporate_slaves values (8, 6, ‘Bob Nerd’);
column name format a20
select * from corporate_slaves;
SLAVE_ID SUPERVISOR_ID NAME
———- ————- ——————–
1 Big Boss Man
2 1 VP Marketing
3 1 VP Sales
4 3 Joe Sales Guy
5 4 Bill Sales Assistant
6 1 VP Engineering
7 6 Jane Nerd
8 6 Bob Nerd
8 rows selected.
– With the Oracle CONNECT BY clause, you can get all the rows out at once:
set pagesize 100
select name, slave_id, supervisor_id
from corporate_slaves
connect by prior slave_id = supervisor_id;
NAME SLAVE_ID SUPERVISOR_ID
——————– ———- ————-
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
Bill Sales Assistant 5 4
Jane Nerd 7 6
Bob Nerd 8 6
Big Boss Man 1
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
20 rows selected.
– This seems a little strange. It looks as though Oracle has produced all possible trees and subtrees. Let’s add a START WITH clause:
select name, slave_id, supervisor_id
from corporate_slaves
connect by prior slave_id = supervisor_id
start with slave_id in (select slave_id
from corporate_slaves
where supervisor_id is null);
NAME SLAVE_ID SUPERVISOR_ID
——————– ———- ————-
Big Boss Man 1
VP Marketing 2 1
VP Sales 3 1
Joe Sales Guy 4 3
Bill Sales Assistant 5 4
VP Engineering 6 1
Jane Nerd 7 6
Bob Nerd 8 6
8 rows selected.
SQL> SELECT sys_connect_by_path(name, ‘/’) Path
2 FROM corporate_slaves
3 CONNECT BY PRiOR slave_id = supervisor_id
4 START WiTH slave_id iN
5 (SELECT slave_id
6 FROM corporate_slaves
7 WHERE supervisor_id iS NULL) ;
PATH
————————————————–
/Big Boss Man
/Big Boss Man/VP Marketing
/Big Boss Man/VP Sales
/Big Boss Man/VP Sales/Joe Sales Guy
/Big Boss Man/VP Sales/Joe Sales Guy/Bill Sales Assistant
/Big Boss Man/VP Engineering
/Big Boss Man/VP Engineering/Jane Nerd
/Big Boss Man/VP Engineering/Bob Nerd
SQL> SELECT sys_connect_by_path(name, ‘/’) Path
2 FROM corporate_slaves
3 CONNECT BY slave_id = PRiOR supervisor_id
4 START WiTH slave_id iN
5 (SELECT slave_id
6 FROM corporate_slaves
7 WHERE supervisor_id iS NULL) ;
PATH
————————————————–
/Big Boss Man
SQL> SELECT sys_connect_by_path(name, ‘/’) Path
2 FROM corporate_slaves
3 CONNECT BY PRiOR slave_id = supervisor_id
4 START WiTH slave_id = 3 ;
PATH
————————————————–
/VP Sales
/VP Sales/Joe Sales Guy
/VP Sales/Joe Sales Guy/Bill Sales Assistant
– some functions cant hurt
SELECT name,
slave_id,
supervisor_id,
LEVEL,
sys_connect_by_path(slave_id, ‘/’) Path,
connect_by_isleaf isLeaf
FROM corporate_slaves
CONNECT BY PRiOR slave_id = supervisor_id
START WiTH slave_id iN
(SELECT slave_id
FROM corporate_slaves
WHERE supervisor_id iS NULL)
ORDER BY LEVEL, NAME;
NAME SLAVE_ID SUPERVISOR_ID LEVEL PATH ISLEAF
——————– ———- ————- ———- ——– ———-
Big Boss Man 1 1 /1 0
VP Engineering 6 1 2 /1/6 0
VP Marketing 2 1 2 /1/2 1
VP Sales 3 1 2 /1/3 0
Bob Nerd 8 6 3 /1/6/8 1
Jane Nerd 7 6 3 /1/6/7 1
Joe Sales Guy 4 3 3 /1/3/4 0
Bill Sales Assistant 5 4 4 /1/3/4/5 1
8 rows selected.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935
http://philip.greenspun.com/sql/trees
http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/gennick_connectby_10g.html
- satır seviyesinde örnekleme;
SQL> SELECT count(*) FROM emp SAMPLE (1);
COUNT(*)
———-
0
SQL> SELECT count(*) FROM emp SAMPLE (10);
COUNT(*)
———-
31
SQL> SELECT count(*) FROM emp SAMPLE (50);
COUNT(*)
———-
107
- blok seviyesinde örnekleme;
SQL> SELECT count(*) FROM emp SAMPLE BLOCK (1);
COUNT(*)
———-
0
SQL> SELECT count(*) FROM emp SAMPLE BLOCK (10);
COUNT(*)
———-
0
SQL> SELECT count(*) FROM emp SAMPLE BLOCK (50);
COUNT(*)
———-
214
Kısıtlar; join, db link, rbo, pl/sql için kullanılamıyor.