Feed aggregator

Creating a Web App – One Year On

Complete IT Professional - Mon, 2016-09-19 06:00
One year ago I decided to create a web app for an organisation a friend of mine works with. I’d like to share where it’s at and what I’ve learnt. One Year On I wrote a post where I decided to create a web app. It’s a customised reporting tool for a group of sports […]
Categories: Development

connecting subnets

Pat Shuff - Mon, 2016-09-19 02:07
This week we are going to focus on connecting computers. It seems like we have been doing this for a while. We have looked at connecting our desktop to a cloud server (Linux and Windows). We have also looked at hiding a server in the cloud so that you can only get to it from a proxy host and not from our desktop or anywhere on the cloud. In this blog we are going to start talking about changing the configuration so that we create a new network interface on our desktop and use this new network interface to connect through a secure tunnel to a second network interface on a compute cloud. A good diagram of what we are trying to accomplish can be seen below.

Note that there are three components to this. The first is Corente Gateway running in your data center. The second is Corente Gateway running in the Oracle Cloud. The third is the Corente App Net Manager Service Portal. The basic idea behind this is that we are going to create a second network as we did with hiding a server in the cloud. We initially setup one instance so that we could access it through a public IP address 140.86.14.242. It also has a private IP address for inside the Oracle Cloud of 10.196.135.XXX. I didn't record the internal IP address because I rarely use it for anything. The key here is that by default a subnet range of 10.196.135/24 was configured. We were able to connect to our second server at 10.196.135.74 because we allowed ssh access on the private subnet but not on the public network. When this blog was initially written Oracle did not support defining your own subnet and assigned you to a private network based on the rack that you got provisioned into inside the Cloud data center. As of OpenWorld, subnet support was announced so that you could define your own network range. One of the key feedbacks that Oracle got was that customers did not like creating a new subnet in their data center to match the Oracle subnet. They would rather define their own subnet in the Oracle Cloud to match the subnets that they have in their own data center.

Let's take each of these components one at a time. First the Corente Gateway running in your data center. This is a virtual image that you download or software components that you install on a Linux system and run in your data center. The concept here is that you have a (virtual) computer that runs in your data center. The system has two network interfaces attached. The first can connect to the public internet through network address translation or is directly connected to the internet or through a router. The second network interface connects to your private subnet. This IP address is typically not routable like a 10. or 192.168. network. There is not mistake that your are hoping to get to a machine on the internet because these networks are non-routable. The key is that the Corente Gateway actually has a listener that looks for communications intended for this non-routable network and replicates the packets through a secure tunnel to the Corente Gateway running in the Oracle Cloud. All of the traffic passes from your local network which is non-routable to another network hundreds or thousands of miles away and gives you the ability to talk to more computers on that network. This effectively gives you a private virtual network from your data center to a cloud data center.

Rather than using a software virtual gateway you can use a hardware router to establish this same connection. We are not going to talk about this as we go through our setup exercises but realize that it can be done. This is typically what a corporation does to extend resources to another data center, another office, or a cloud vendor for seasonal peak periods or cheaper resources. The benefit to this configuration is that it can be done by corporate IT and not by an individual.

The key things that get setup during this virtual private network connection are name parsing (DNS), ip routing (gateways and routers), and broadcast/multicast of messages. Most VPN configurations support layer 3 and above. If you do a arp request the arp is not passed through the VPN and never reaches the other data center. With Corente it uses the GRE tunneling protocol which is a layer 2 option. Supporting layer 2 allows you to route ping requests, multicast requests, and additional tunnel requests at a much lower and faster level. As we discussed in an earlier blog, Microsoft does not allow layer 2 to go into or out of their Azure cloud. Amazon allows layer 2 inside their cloud but not into and out of their cloud. This is a key differentiator between the AWS, Azure, and Oracle clouds.

The second component of the virtual private network is the Oracle Cloud Corente Gateway. This is the target side where the gateway in your data center is the initiator. Both gateways allow traffic to go between the two networks on the designated subnet. Both gateways allow for communication between servers in the data center and servers in the Oracle cloud. When you combine the VPN gateways with the Security Lists and Security Rules you get a secure network that allows you to share a rack of server and not worry about someone else who is using the Oracle Cloud from accessing your data center even if they are assigned an IP address on the same subnet. When you define a Security List or Security Rule, these exceptions and holes allow for traffic from computers in your account to access the VPN. No one else in the same rack or same cloud data center can access your VPN or your data center.

The third component is the app net management service portal. This portal establishes connections and rules for the other two components. When you install each of the components it communicates with the admin portal to get configuration information. If you need to change a configuration or keys or some aspect of the communication protocol this is done in the admin portal and it communicates to the other two components to update the configuration. This service also allows you to monitor traffic and record traffic between the Oracle Cloud and your data center.

The network resources for your data center installed service will look like

with br0 being your public facing connection and br1 connecting to your subnet in your data center. A similar configuration is done in the Oracle Cloud but this is pre-configured and can be provisioned as a public image. The only thing that you need to configure is the subnet address range and relationship with the app net management service portal.

Today was a lot of theory and high level discussions. Tomorrow we will dive into configuration of the gateway in your data center. The day after that we will look at provisioning the gateway in the Oracle Cloud and connecting the two. Just a quick reminder, we talked about how to establish a connection between your desktop and a cloud server. By going the a VPN configuration we will get around having to hide a server in the cloud. We can setup all of our servers to have private network links and only open up web servers or secure web servers to talk to the public internet. We can use ssh and rdp from our desktops at home or in our offices to communicate to the cloud servers. Setting up the VPN is typically a corporate responsibility and giving you access to the resources. What you need to know are what cloud resources you have access to and how much money you have in your budget to solve your business problem.

Oracle 12cR2 SQL new feature: LISTAGG overflow

Yann Neuhaus - Mon, 2016-09-19 02:00

LISTAGG was a great feature introduced in 11g: put rows into line with a simple aggregate function. 12cR2 adds an overflow clause to it.

What happens when you have so many rows that the LISTAGG result is too long?

SQL> select listagg(rownum,',')within group(order by rownum) from xmltable('1 to 10000');
select listagg(rownum,',')within group(order by rownum) from xmltable('1 to 10000')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

An error at runtime, and we don’t like runtime errors.

If you want to manage the overflow, it’s not easy: run a first query that sums the length and then calculate how much can fit

SQL> select v.*,4000-size_current from (
2 select n,
3 sum(length(n||',')) over(order by n rows between unbounded preceding and current row)-1 size_current,
4 sum(length(n||',')) over(order by n rows between unbounded preceding and 1 following)-1 size_next
5 from (select rownum n from xmltable('1 to 10000'))
6 ) v
7 where size_current between 4000-50 and 4000;
 
N SIZE_CURRENT SIZE_NEXT 4000-SIZE_CURRENT
---------- ------------ ---------- -----------------
1012 3952 3957 48
1013 3957 3962 43
1014 3962 3967 38
1015 3967 3972 33
1016 3972 3977 28
1017 3977 3982 23
1018 3982 3987 18
1019 3987 3992 13
1020 3992 3997 8
1021 3997 4002 3
 
9 rows selected.

Here you can see that values above 1020 will not fit in a VARCHAR2(4000).

In 12.2 you can manage the overflow in two ways

You can choose to raise a runtime error:

SQL> select listagg(rownum, ',' on overflow error)within group(order by rownum) from xmltable('1 to 10000');
select listagg(rownum, ',' on overflow error)within group(order by rownum) from xmltable('1 to 10000')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

But you can also choose to truncate the result:

SQL> select listagg(rownum, ',' on overflow truncate '' without count)within group(order by rownum) from xmltable('1 to 10000');
 
LISTAGG(ROWNUM,','ONOVERFLOWTRUNCATE''WITHOUTCOUNT)WITHINGROUP(ORDERBYROWNUM)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,
103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,
178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,
328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,
403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,
478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,
553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,
628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,
703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,
778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,
853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,
928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,10
02,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,

You may want to add some characters to show that it has been truncated:


SQL> select listagg(rownum, ',' on overflow truncate '...' without count)within group(order by rownum) from xmltable('1 to 10000');
 
LISTAGG(ROWNUM,','ONOVERFLOWTRUNCATE'...'WITHOUTCOUNT)WITHINGROUP(ORDERBYROWNUM)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,
103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,
178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,
328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,
403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,
478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,
553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,
628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,
703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,
778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,
853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,
928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,10
02,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,...

And you may even show the number of values that are not displayed:


SQL> select listagg(rownum, ',' on overflow truncate '...' with count)within group(order by rownum) from xmltable('1 to 10000');
 
LISTAGG(ROWNUM,','ONOVERFLOWTRUNCATE'...'WITHCOUNT)WITHINGROUP(ORDERBYROWNUM)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,
103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,
178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,
328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,
403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,
478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,
553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,
628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,
703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,
778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,
853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,
928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,10
02,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,...(8985)

The nice thing is that the truncation is adapted to the information displayed:


SQL> select listagg(rownum, ',' on overflow truncate 'blah blah blah...' with count)within group(order by rownum) from xmltable('1 to 10000');
 
LISTAGG(ROWNUM,','ONOVERFLOWTRUNCATE'BLAHBLAHBLAH...'WITHCOUNT)WITHINGROUP(ORDERBYROWNUM)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,
103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,
178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,
328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,
403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,
478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,
553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,
628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,
703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,
778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,
853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,
928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,10
02,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,blah blah blah...(8988)

The “,blah blah blah…()” takes 20 characters, the count may take up to 24 characters, so the truncated value cannot be larger than 4000-20-24=3956. From the first query we run we see that we have to truncate after the value “2012”. There’s no dynamic evaluation of the count size.

If all the values fit, then it’s not truncated. In the first query we have seen that values up to 1021 takes 3997 characters:


SQL> select listagg(rownum, ',' on overflow truncate 'blah blah blah...' with count)within group(order by rownum) from xmltable('1 to 1021');
 
LISTAGG(ROWNUM,','ONOVERFLOWTRUNCATE'BLAHBLAHBLAH...'WITHCOUNT)WITHINGROUP(ORDERBYROWNUM)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,
103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,
178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,
253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,
328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,
403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,
478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,
553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,
628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,
703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,
778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,
853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,
928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,10
02,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021

In summary

After the delimiter string you can add:

  • ON OVERFLOW ERROR which is the default. Same behavior as in previous releases.
  • ON OVERFLOW TRUNCATE with a string that is added in case of truncation (default: ‘…’) and optionally WITH COUNT (or WITHOUT COUNT which is the default)

The full syntax is in the documentation

 

Cet article Oracle 12cR2 SQL new feature: LISTAGG overflow est apparu en premier sur Blog dbi services.

Les 24 heures du PASS (24HOP) – édition francophone

Yann Neuhaus - Mon, 2016-09-19 01:46

24hour_pass

Je n’ai pas encore blogué sur le sujet mais il est temps de le faire. Cette année, aura lieu la première édition francophone des 24h du PASS  les 20 et 21 septembre 2016. L’idée est plutôt simple: proposer une série de 24 webinaires gratuits de 10h jusqu’à 22h (heure française)  pendant 2 jours. C’est l’occasion de recevoir et d’échanger les dernières informations autour de l’administration et du développement des bases de données, des nouvelles tendances côté Business Intelligence et du Cloud.

Qui dit événement Francophone dit aussi speakers de la communauté francophone avec du contenu en Français. Pour ma part, c’est avec plaisir que j’échangerais avec vous autour de 2 sujets: tempdb et bonnes pratiques ainsi que des columnstore et leur implication dans les nouvelles tendances d’architecture BI avec Thoi Dung TSP Microsoft Switzerland.

C’est aussi l’occasion de remercier les sponsors et Isabelle sans qui ce type d’événement n’aurait certainement pas lieu.

Aucune raisons de ne pas vous inscrire!

Au plaisir d’échanger avec vous au cours de cet événement!

 

Cet article Les 24 heures du PASS (24HOP) – édition francophone est apparu en premier sur Blog dbi services.

Video Tutorial: XPLAN_ASH Active Session History - Part 11

Randolf Geist - Sun, 2016-09-18 15:00
The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.


SQL Plan changes

Tom Kyte - Sun, 2016-09-18 14:46
I'm in a large organization, and we have many environments (Dev, Sys Int, User Acc and Prod). Many times, our code is moved from one environment to the next, and somehow the execution times skyrocket. (Example, 21 seconds to over an hour). We some...
Categories: DBA Blogs

Could you let us now whether there are possibiities to optimize this query?. Due to space issue we have taken (*) in select statements instead of column names. we wil give a query plan in the next post..

Tom Kyte - Sun, 2016-09-18 14:46
WITH tgt AS ( SELECT TRIM ( REGEXP_SUBSTR ( REPLACE (MS.tgt_column_name, CHR (10), ','), '[^,]+', 1, LEVEL)) ...
Categories: DBA Blogs

Which algorithm uses dbms_random.random

Tom Kyte - Sun, 2016-09-18 14:46
Hi, I could not find in documentation. Do you know which algorithm uses dbms_random.random? Thanks
Categories: DBA Blogs

Oracle Kicks off Oracle OpenWorld 2016 by Showcasing Industry-First Cloud Innovations

Oracle Press Releases - Sun, 2016-09-18 14:00
Press Release
Oracle Kicks off Oracle OpenWorld 2016 by Showcasing Industry-First Cloud Innovations

Oracle OpenWorld, San Francisco, Calif.—Sep 18, 2016

Oracle Executive Chairman and CTO Larry Ellison showcased innovations across the world’s most complete, open and integrated Cloud computing platform in his opening keynote presentation at Oracle OpenWorld 2016 in San Francisco’s Moscone Center.

Ellison introduced Oracle Database 12c Release 2, as well as more than 20 new Oracle Cloud Platform and Application services that extend choice and access for customers and partners globally. Industry-first innovations highlighted include a new Database-as-a-Service (DBaaS) that is orders of magnitudes faster and more scalable than other Cloud database services and new Adaptive Intelligence Applications that use machine learning to power the next generation of Cloud applications.

The release of Oracle’s world’s number one database, Oracle Database 12c Release 2, coming first in the Cloud, marks a new era of magnitude and innovation for the enterprise. Building on its unique multi-tenant architecture and in-memory technology, Oracle’s flagship database is able to drive robust enterprise workloads faster than the nearest competitor.

Additional innovations unveiled across the Oracle Cloud include:

  • New public sector and revenue management Cloud services as part of Oracle ERP Cloud
  • Modern employee engagement capabilities as part of Oracle HCM Cloud
  • Expanded packaged industry solutions, new blended agent capabilities for sales and service professionals and expanded B2B commerce functionality within Oracle CX Cloud
  • New financial consolidation and close and profitability and cost management capabilities within Oracle EPM Cloud
  • Exadata Express Cloud Service
  • Container Cloud service to support Cloud developers and make managing containers easy on the public Cloud
  • API Cloud service to help developers create, secure, manage and analyze APIs
  • Enhanced mobile Cloud services that include support for chatbots
  • Low-code development platform that enables business users and developers quickly and easily to extend services and build new applications
  • Integration Cloud service to support integration of Cloud and on-premises applications
  • New analytics Cloud suite to connect data sources and instantly visualize any data on any device
  • Big data discovery Cloud service to visualize and discover hidden value in all data
  • Internet of Things applications for asset management, production insights, connected worker and fleet management
  • Management Cloud services that use machine learning to identify threats and provide early warnings

Quotes from Larry Ellison’s keynote presentation:

  • “Amazon’s lead is over. Amazon is going to have serious competition going forward. And we’re very proud of our second generation of Infrastructure as a Service. We’re going to be focusing on it and aggressively featuring it not only during Oracle OpenWorld but for the remainder of this fiscal year and next fiscal year and the year after that.”
  • “We have more SaaS applications by a huge margin than any other cloud services provider. We have HCM suite, CRM Suite, Customer Experience Suite plus lots and lots of industry suites as well. And we’re constantly, constantly adding to our footprint.”
  • “There are two big databases that keep track of consumers, if you will, and have a lot of information about consumers. One is very famous. It’s called Facebook. The other one is less well known. It’s Oracle’s Data Cloud. We actually have more consumers in our data cloud than they have in theirs.”
  • “You will see as we develop features for the cloud, we’ll also start delivering our software in the cloud first. Clearly it’s going to go on-premises, but the first deployment of our database and a lot of our software now is going to go to the cloud first.”
  • “We have toolsets for developers who want to extend and migrate their existing enterprise applications to the cloud. We have toolsets for cloud-centric developers who are developing all new applications that will only be in the cloud. And we have toolsets for business users, where they can easily, without writing code, easily create extensions to our SaaS applications and even build something.”
  • “Our new second-generation datacenters offer twice as many cores as Amazon, twice as much memory as Amazon, four times as much storage as Amazon, and more than 10 times the I/O capacity of Amazon. But you have to be willing to pay less. If you’re not willing to pay less, you can’t place the order.”
  • “Oracle competes at all three levels of the cloud, all three tiers of the cloud.”
Contact Info
Simon Jones
Oracle PR
+1 (415) 856-5155
sjones@blancandotus.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Simon Jones

  • +1 (415) 856-5155

What's Happening on Sunday: Oracle OpenWorld and JavaOne Update

OTN TechBlog - Sun, 2016-09-18 13:49
We have some great activities going on in the OTN Lounge in Moscone South and the Java Hub in the Hilton this week.  It all kicks off with the OTN Welcome Event, today at 3 pm in the OTN Lounge. Come by to start the week off on the right foot and get to preview of all we have going on for for the rest of the week with:
  • HearingNest Community Art Experience
 - Attendees can draw shapes on the JavaOne Community Application by Gluon, and those images are then uploaded to the Oracle Cloud and transferred to CNC machines to be downloaded and cut into your unique design. These are both at the OTN Lounge and the Java Hub. Download the app here.
  • OTN Cloud Coffee Service
 - Watch baristas serve coffee using a Raspberry Pi and Java. Chat with them about anything related to Oracle University and OTN. 
  • IoT Workshop
 - Get your own wi-fi microcontroller and connect it to Oracle IoT Cloud Service. OTN is partnering with the AppsLab, the OAUX emerging technologies team to make this happen. Schedule your session here.
In addition to OTN’s activities throughout the week, we have several activities today.
  • Just Wrapping up : Oracle Cloud Experience 
Oracle - ACEs and experts from the Cloud SIG present a 4-hour introduction to the Oracle Database Cloud capabilities.
  • 3 pm – 4 pm : Women in Technology (WIT)
 - Join us at the OTN Lounge to attend a panel of women technologists and stick around to meet with the WIT panel.


  • 3:30 pm – 4 pm : Ask Tom - 
The Ask Tom team is here to answer your questions and issues at work. 


Along with our own events, make sure to check out other schedles in both OpenWorld and Java One.

BI without a data warehouse

Dylan's BI Notes - Sun, 2016-09-18 11:36
When we think of a data warehouse, we are typically thinking of having a database that store the data and need to design the schema and the ETL program to populate the database.  If we take out the “database” from the above description, will it still be called a data warehouse? I think that a […]
Categories: BI & Warehousing

APEX 5.1 Early Adopter 2 Available

John Scott - Sun, 2016-09-18 10:10

2016-11-10_15-14-47.png

Oracle have updated the Application Express Early Adopter instance to EA2, you can try it out here – https://apexea.oracle.com/

There are some fairly significant changes and enhancements in EA2 versus the EA1 release, particularly around the Interactive Grid feature (which every APEX developer can’t wait to get their hands on!). For example, the list below shows some very cool additions.

2016-11-10_15-17-52.png

There seem to have been some improvements to the Jet Charts too, this is shaping up to be the best APEX release ever IMHO.

 


SQL*Net Message Waits

Hemant K Chitale - Sun, 2016-09-18 10:10
Here are some extracts from an 11.2.0.4 AWR Report  I ran a simulated workload on this server for about 40minutes and generated this report.  I understand that some DBAs may be misinterpreting SQL*Net message time.

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
ora11204 Linux x86 64-bit 2 2 1 3.04

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 158 18-Sep-16 21:42:34 36 1.0
End Snap: 159 18-Sep-16 22:23:01 33 1.0
Elapsed: 40.45 (mins)
DB Time: 42.04 (mins)

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 1.0 10.2 0.12 0.07
DB CPU(s): 0.0 0.1 0.00 0.00
Redo size (bytes): 2,523.6 24,793.8
Logical read (blocks): 41.7 409.6


Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
log file sync 1,095 2508 2291 99.5 Commit
db file sequential read 162 37.5 231 1.5 User I/O
DB CPU 19.1 .8
SQL*Net message to client 31,579 .1 0 .0 Network
Disk file operations I/O 103 0 0 .0 User I/O


Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
Commit 1,095 2,509 2291 99.5 1.0
System I/O 12,899 2,210 171 87.6 0.9
User I/O 1,866 38 20 1.5 0.0
DB CPU 19 .8 0.0
Network 33,651 9 0 .4 0.0


Foreground Wait Class DB/Inst: ORCL/orcl Snaps: 158-159
-> s - second, ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Captured Time accounts for 101.7% of Total DB time 2,522.36 (s)
-> Total FG Wait Time: 2,546.18 (s) DB CPU time: 19.14 (s)


Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
Commit 1,095 0 2,509 2291 99.5
User I/O 276 0 38 136 1.5
DB CPU 19 0.8
Network 31,579 0 0 0 0.0
Concurrency 21 0 0 0 0.0

Foreground Wait Events DB/Inst: ORCL/orcl Snaps: 158-159
-> s - second, ms - millisecond - 1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0


Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
log file sync 1,095 0 2,509 2291 4.4 99.5
db file sequential read 162 0 37 231 0.7 1.5
SQL*Net message to client 31,579 0 0 0 127.9 .0
Disk file operations I/O 103 0 0 0 0.4 .0
latch: shared pool 2 0 0 4 0.0 .0
direct path sync 2 0 0 2 0.0 .0
db file scattered read 6 0 0 0 0.0 .0
jobq slave wait 5,522 100 2,770 502 22.4
SQL*Net message from clien 31,577 0 2,404 76 127.8


Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 158-159
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms>1s is truly >=1024ms
-> Ordered by Event (idle events last)

% of Waits
-----------------------------------------------
Total
Event Waits <1ms ms="" s="">1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ARCH wait on ATTACH 37 97.3 2.7
ARCH wait on DETACH 37 100.0
Disk file operations I/O 920 99.9 .1
LGWR wait for redo copy 54 100.0
Parameter File I/O 640 100.0
SQL*Net break/reset to cli 6 100.0
SQL*Net message to client 33.6K 100.0
...
SQL*Net message from clien 34.2K 82.7 9.5 2.7 1.6 .7 2.4 .3 .0


Other Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 158-159
-> Ordered by statistic name

Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
SQL*Net roundtrips to/from clien 31,579 13.0 127.9



Should I be worried about the SQL*Net message waits (to client, from client) ? How should I interpret them ?  I have more than 30K SQL*Net messages and roundtrips.  Apparently, messages from client are very slow -- at 76ms/message.  Apparently, round-trips are very slow -- at 13 round-trips per second.

SQL*Net message to client waits are not really measured in terms of the time it took for the message to reach the client.  Oracle doesn't know how long the transmission took.  It only knows the time taken to put the message onto the TCP stack on the database server itself.  Subsequent network transmission time is unknown. That is why SQL*Net message to client will always be an extremely low figure in terms of time -- because it isn't a true measure of elapsed time sending a message to a client.

SQL*Net message from client is not  just the time spent on the network.  It is the time between the last message to the client upto the next message from the client.  Thus, it also includes client overheads (like "think time", CPU time, network stack on the client) besides transmission over the network.  In most cases, the major component of SQL*Net message from the client is client "think time" -- how long the client spent before formatting and sending the next SQL call to the database server.   This should also mean that it is not always true that SQL*Net message from client is an idle event.

If I have a single client that is either or some or all of :
a. Loading data in sets of rows (e.g. an ETL client)
b. Extracting data in sets of rows
c. Retrieving results and formatting the results for presentation to a user
b. Running a batch job that majorly consists of SQL calls, not PLSQL
the SQL*Net message from client is majorly the time spent by the client  (--- unless you really have a high latency network.).  In most such cases, when tracing the individual session, this wait event is NOT an idle event.  However, when reading an AWR, you cannot isolate such a session from the other sessions that are doing a mix of activity -- some with real interactive end-users, some sending periodic "heart-beat" messages, some completely idle waiting for a request from a user.  In this AWR report, there are a mix of clients with different "think-times", some completely idle for 40minutes.  We can't identify them in the AWR report.

Can you use the SQL*Net roundtrips to/from client figure from the AWR ?  Not if you have a mix of different clients doing different forms of activity.  Idle clients will have very few roundtrips in the 40minutes while clients extracting data row-by-row (not in PLSQL, but SQL or some other client like Java) would have a high number of roundtrips.  So, you can't separate the two types of behaviour in an AWR.

If you are really concerned about identifying SQL*Net overheads and/or round-trips, you should *trace* the specific individual session of interest and extract figures from the trace file.
.
.
.


Categories: DBA Blogs

Last Minute Tips for Oracle OpenWorld 2016

WebCenter Team - Sun, 2016-09-18 05:00
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Ready for Oracle OpenWorld 2016? The conference is ready for you too! As you make your way to Moscone Center this coming week, we wanted to (first of all) thank you for tuning in. Over the last few weeks (months, really!), we have done our best efforts to give you a preview of what awaits you at Oracle OpenWorld in terms of key sessions, demos and showcases, hands-on-labs, resources, et al.

In fact, in our concerted effort to help you hone in on your areas of interest, we even created from scratch a brand new website – Oracle Content and Experience Management at OOW16. The site built using our very own solutions, Oracle Sites Cloud Service and Oracle Documents Cloud Services, was built in all of 3 days but is the one stop shop for all things Digital Experience that we have planned for you at Oracle OpenWorld.

We have sliced and diced our information and tried to present it to you in ways that have hopefully been useful for you to plan your OOW16 itinerary. Here are a few additional last minute tips that can perhaps save you a few extra steps (we all know how those add up at OOW….) and help you optimize your presence at this conference. Here we go with our Top 5 tips:

  1. Keep https://sites.oracle.com/oowdx/ handy on your mobile web browser. Not only does it have all the information you would need to traverse through Digital Experience sessions and activities, it will give you up to date information on what’s coming up and where. Handy, don’t you think?

  2. Make use of the My Schedule app on your mobile device. If you haven’t added the session you are looking to check out, please do so on My Schedule before you get to the conference. This post on must-attend sessions could be a helpful starting point.

  3. Don’t miss the conference keynotes. You know people would be talking about that in the social world for a few days and you don’t want to be left out of the conversation. Be there in person to catch it all live. But also, here are the 3 key sessions you don’t want to miss from Oracle executives, and need to have on My Schedule:

    Monday, September 19, 2016

    Content and Experience Management: Roadmap and Vision [CON7256]

    David Le Strat, Senior Director, Oracle
    11:00 a.m. | Moscone West 2014

    Oracle Integrated Cloud—Strategy/Vision for Journey to Cloud Transformation [GEN1806]
    Steve Daheb, Sr. Vice President, PaaS & IaaS Business Groups, Oracle
    12:30 p.m. | Moscone South 103

    Scaling Innovation with Oracle Cloud Platform for Middleware—Roadmap/Vision [GEN7006]

    Inderjeet Singh, EVP, Fusion Middleware Development, Oracle
    4:15 p.m. | Moscone South 103

  4. Play along on the Oracle Mobile Challenge game. Put your phone to use and you may end up winning some serious prizes, sponsored by our partner, Samsung. A quick preview, QR codes for download along with links to more information can be found on our site: https://sites.oracle.com/oowdx/

  5. Tweet along and we would tweet with you!

    Some common hashtags to use:
    #OOW16, #OracleDX, #OraclePaaS, #PaaS, #Cloud, #Mobile, #OracleDOCS, #OraclePCS, #OracleSCS

    Twitter handles that you may want to follow:
    @oraclewebcenter, @oraclemiddle, @oraclecloudzone, @oracleopenworld

    Use #OOW16 and #OracleDX and you will find your tweets show up on https://sites.oracle.com/oowdx/
See you at #OOW16 soon!
/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

Replacement for full outer join (Not Union)

Tom Kyte - Sat, 2016-09-17 20:26
Hi tom, i prepared a Query to Get the Details of Requisition, Purchase Order, Receipt and Invoice by giving any of Requisition Number, Purchase Order Number, Receipt Number and Invoice Number. IF all the modules are not complete then to get t...
Categories: DBA Blogs

v$ table access from stored procedures

Tom Kyte - Sat, 2016-09-17 20:26
Hi Tom, I want to select from v$ tables inside stored procedures. Before Oracle9 if I had "select any table" privileges there was no problem doing so. With Oracle9, "select any table" privileges no longer allow direct access to the v$ tables. ...
Categories: DBA Blogs

Database Migration

Tom Kyte - Sat, 2016-09-17 20:26
Oracle Database 9i to Oracle 12c Database Migration Kindly advise how to migrate a database developed and running on Oracle 9i to Oracle 12c, which is the best tool for the job and what are the requirements. Kindly list the steps involved.
Categories: DBA Blogs

Le programme Oracle ACE

Yann Neuhaus - Sat, 2016-09-17 19:55

Venant de passer les deux derniers jours au siège d’Oracle pour le ACE Director briefing, j’en profite pour expliquer ce que représente le programme Oracle ACE car il y a peu de ressources en français.

On ne le sent pas toujours, mais Oracle est à l’écoute des utilisateurs. Oracle encourage les spécialiste de la communauté d’utilisateurs à partager et communiquer sur ses produits. Ce n’est pas pour faire simplement de la publicité commerciale, mais d’échanger et de faire connaitre la technologie. C’est l’OTN (Oracle Technology Network) qui gère le programme ACE.

Les titres de ACE Associate, ACE, et ACE Director sont une reconnaissance du travail effectué par les spécialistes au travers de différent canaux d’information: blogs, articles, présentation, organisation des groupes d’utilisateurs, écriture de livres techniques, etc.

Mais c’est aussi le moyen pour Oracle d’être à l’écoute des utilisateurs finaux, en dehors du réseau commercial. Les ACEs ont un contact privilégié avec les Product Manager pour tester les produits, parfois en beta, ouvrir et suivre les bugs rencontrés et faire un retour sur l’utilisation des fonctionnalités.

Les ACE directors se réunissent au siège juste avant l’Oracle Open World pour un briefing sur ce qui va être annoncé à l’Open World, et sur la Road Map des futures releases. Cela permet aux products managers de mieux présenter les nouveautés en fonction de ce qu’attendent les utilisateurs, et aux ACEs de pouvoir répondre aux questions des utilisateurs qui apprennent ces nouveautés.

Pour donner une idée du nombre de ACE Directors présents au siège, voici l’équipe:
2016-09-16 17.08.58

Aujourd’hui, Oracle couvre un domaine beaucoup plus large que la base de donnée. Il y a du hardware, de la virtualisation, des systèmes d’exploitation, plusieurs bases de données, de la BI, ERP… L’objectif aujourd’hui d’Oracle est de s’imposer face à Amazon sur le IaaS, d’offrir la plus grande agilité en PaaS et une gamme très large en SaaS.

Mais heureusement, les bâtiments d’Oracle Corporation montrent toujours d’où ils sont partis: la base de donnée, stockée sur disque dur, toujours représentée par des cylindres rotatifs:

2016-09-16 07.35.12

Aujourd’hui, les disques ne ressemblent plus à cela et de sont plus l’élément critique de l’IT car on ne dépend plus de la latence mécanique. Mais le symbole reste.

Si vous êtes à l’Oracle Open World cette année, n’hésitez pas à passer à l’OTN Lounge au Moscone South. Vous pourrez vous renseigner sur la communauté autour de la technologie Oracle, et sur le programme ACE.

Si vous ne maîtrisez pas l’anglais, pas de problème la Délégation Française propose plusieurs points de rencontre.

Et n’hésitez pas à venir voir ma session sur le Multitenant et j-interviens aussi pour parler Transportable Tablespace dans la session de l’EMEA User Group Community (EOUC)

 

Cet article Le programme Oracle ACE est apparu en premier sur Blog dbi services.

Pro*C Multithread Extracting Table Data

Tom Kyte - Sat, 2016-09-17 02:06
Hi Tom, I have created pro*c multithreaded application to download data from oracle table to flatfile. Each thread has its own sql context, cursor and fetch statements. I am generating separate WHERE clause for each thread with MOD function on one...
Categories: DBA Blogs

Performance Tuning

Tom Kyte - Sat, 2016-09-17 02:06
I'm junior DBA and i join an organization in last month. we are using oracle 11g Standard Edition. we does not have licence of performance tuning so I have a performance tab disable in EM Manager . I want to find out Top SQL , Top Activity , To...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator