Feed aggregator

Date range grouping for same price and stop sale option

Tom Kyte - Sun, 2016-10-30 14:26
Hi Tom, Please disregard my previous ticket as I am not able to edit and the query requirement has changed. Thanks for the link by I am struggling a bit. I have a table of daily rates for a service that contains a price and an Y/N field to sto...
Categories: DBA Blogs

Performance issue with the Query

Tom Kyte - Sat, 2016-10-29 20:06
Dear Tom, We have recently Upgraded form 11g to 12c. We have a small application(it is exactly same as SQL Developer) through which we can select a particular table from the list and we can view the data. All tables has less than 10...
Categories: DBA Blogs

Blogs to read in the Oracle DBA/Developer world

RDBMS Insight - Sat, 2016-10-29 16:23

:Earlier this month, I conducted a totally unscientific survey on Twitter, asking where people got their Oracle news from. Twitter and the NoCOUG Journal were two popular sources, along with a wide range of blogs. Here are some of the blogs that the Oracle DBA & Dev superstars in my Twitter like to read:

BTW, I notice that David Fitzjarrell’s blog was named one of the top 50 SQL blogs in 2016 by Ben’s DatabaseStar blog, itself a good blog with a strong focus on Oracle SQL for developers. I recommend checking out Ben’s list for more SQL blogs!

I also learned about a couple of terrific Oracle blog aggregators:

I’ve got a couple of my own to add to the list.

I also follow the articles on orafaq.com:
http://www.orafaq.com/articles

And there are some official Oracle blogs I like to keep my eye on:

And of course, there’s Ask Tom, which has an RSS feed for newest questions.

I’m experimenting with Feedly as my new RSS reader. Do you have a favorite Oracle blog, magazine or RSS reader? Let me know in the comments!

Categories: DBA Blogs

Working With Interval Datatype

Michael Dinh - Sat, 2016-10-29 10:41

Here is the scope:

The job takes 42m to complete and monitoring threshold is 30m.

If 42m is the typical to complete, then we should increase the threshold to avoid noise.

If 42m is not the typical time to complete, then we should investigate to see if this is an anomaly.

From dba_scheduler_job_run_details, RUN_DURATION  data type is INTERVAL DAY(3) TO SECOND(0).

Querying dba_scheduler_job_run_details provides the following result.

This seems to be a lot of work trying to identify jobs running longer that 30m, plus there can be errors as a run could have been missed which I did not highlight.

This test case was for 10.2.0.4 but imagine it would for all versions higher.

-- dba_scheduler_jobs
Enter value for 1: PURGE_JOB

OWNER			        JOB_NAME 		      STATE	      REPEAT_INTERVAL						   NEXT_RUN_DATE
------------------------------ ------------------------------ --------------- ------------------------------------------------------------ --------------------------------------------------
DEMODB01		       PURGE_JOB	      SCHEDULED       Freq=Hourly						   28-OCT-16 02.00.00.000000 PM -05:00

-- dba_scheduler_job_run_details

LOG_DATE			    JOB_NAME			   STATUS     ACTUAL_START_DATE 				 RUN_DURATION
----------------------------------- ------------------------------ ---------- -------------------------------------------------- ---------------
28-OCT-16 01.18.34.766342 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.101332 PM -05:00		 +000 00:18:35
28-OCT-16 12.18.50.630375 PM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.128330 PM -05:00		 +000 00:18:50
28-OCT-16 11.19.39.302422 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 11.00.00.096290 AM -05:00		 +000 00:19:39
28-OCT-16 10.19.06.631086 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 10.00.00.098943 AM -05:00		 +000 00:19:07
28-OCT-16 09.21.55.042321 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 09.00.00.142871 AM -05:00		 +000 00:21:55
28-OCT-16 08.22.05.168894 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 08.00.00.114841 AM -05:00		 +000 00:22:05
28-OCT-16 07.20.58.795685 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 07.00.00.615018 AM -05:00		 +000 00:20:58
28-OCT-16 06.19.32.286364 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 06.00.04.355748 AM -05:00		 +000 00:19:28
28-OCT-16 05.22.12.990840 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 05.00.00.108447 AM -05:00		 +000 00:22:13
28-OCT-16 04.22.46.316064 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 04.00.00.822142 AM -05:00		 +000 00:22:45
28-OCT-16 03.23.49.518493 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 03.00.00.767310 AM -05:00		 +000 00:23:49
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 02.00.00.689275 AM -05:00		 +000 00:33:20
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 01.00.00.687059 AM -05:00		 +000 00:42:40
28-OCT-16 12.24.03.200350 AM -05:00 PURGE_JOB		   SUCCEEDED  28-OCT-16 12.00.00.687291 AM -05:00		 +000 00:24:03
27-OCT-16 11.21.45.310127 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.174274 PM -05:00		 +000 00:21:45
27-OCT-16 10.22.04.226755 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.100101 PM -05:00		 +000 00:22:04
27-OCT-16 09.20.46.491481 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.095188 PM -05:00		 +000 00:20:46
27-OCT-16 08.20.11.936472 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.01.162137 PM -05:00		 +000 00:20:11
27-OCT-16 07.16.30.836698 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.122310 PM -05:00		 +000 00:16:31
27-OCT-16 06.17.19.315011 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.683161 PM -05:00		 +000 00:17:19
27-OCT-16 05.17.40.010428 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093667 PM -05:00		 +000 00:17:40
27-OCT-16 04.18.08.064115 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.227937 PM -05:00		 +000 00:18:08
27-OCT-16 03.19.58.561961 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.136952 PM -05:00		 +000 00:19:58
27-OCT-16 02.17.30.492761 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.149822 PM -05:00		 +000 00:17:30
27-OCT-16 01.17.44.490772 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.084995 PM -05:00		 +000 00:17:44
27-OCT-16 12.19.29.486819 PM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.665087 PM -05:00		 +000 00:19:29
27-OCT-16 11.19.43.126793 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 11.00.00.095392 AM -05:00		 +000 00:19:43
27-OCT-16 10.20.28.096927 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 10.00.00.098227 AM -05:00		 +000 00:20:28
27-OCT-16 09.20.59.774236 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 09.00.00.087305 AM -05:00		 +000 00:21:00
27-OCT-16 08.20.11.194525 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 08.00.00.095362 AM -05:00		 +000 00:20:11
27-OCT-16 07.21.19.440710 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 07.00.00.093116 AM -05:00		 +000 00:21:19
27-OCT-16 06.21.21.777725 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 06.00.00.729176 AM -05:00		 +000 00:21:21
27-OCT-16 05.22.00.235810 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 05.00.00.093358 AM -05:00		 +000 00:22:00
27-OCT-16 04.22.41.035983 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 04.00.00.109282 AM -05:00		 +000 00:22:41
27-OCT-16 03.23.03.747790 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 03.00.00.740992 AM -05:00		 +000 00:23:03
27-OCT-16 02.28.28.902795 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 02.00.00.098180 AM -05:00		 +000 00:28:29
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 01.00.00.618087 AM -05:00		 +000 00:38:20
27-OCT-16 12.22.48.596935 AM -05:00 PURGE_JOB		   SUCCEEDED  27-OCT-16 12.00.00.134944 AM -05:00		 +000 00:22:48
26-OCT-16 11.22.39.417841 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.601951 PM -05:00		 +000 00:22:39
26-OCT-16 10.21.24.088692 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.700209 PM -05:00		 +000 00:21:23
26-OCT-16 09.21.48.083949 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.108737 PM -05:00		 +000 00:21:48
26-OCT-16 08.20.36.600056 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.599238 PM -05:00		 +000 00:20:36
26-OCT-16 07.21.26.973000 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.170795 PM -05:00		 +000 00:21:27
26-OCT-16 06.21.13.513245 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.661959 PM -05:00		 +000 00:21:13
26-OCT-16 05.20.38.611174 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.095111 PM -05:00		 +000 00:20:39
26-OCT-16 04.19.43.026097 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.647115 PM -05:00		 +000 00:19:42
26-OCT-16 03.22.25.930388 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.099210 PM -05:00		 +000 00:22:26
26-OCT-16 02.22.28.929046 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.084756 PM -05:00		 +000 00:22:29
26-OCT-16 01.22.35.673399 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.092354 PM -05:00		 +000 00:22:36
26-OCT-16 12.22.51.518382 PM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.098155 PM -05:00		 +000 00:22:51
26-OCT-16 11.25.12.885694 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 11.00.00.097514 AM -05:00		 +000 00:25:13
26-OCT-16 10.25.30.697623 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 10.00.00.109984 AM -05:00		 +000 00:25:31
26-OCT-16 09.25.48.331970 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 09.00.00.650305 AM -05:00		 +000 00:25:48
26-OCT-16 08.23.00.786181 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 08.00.00.092182 AM -05:00		 +000 00:23:01
26-OCT-16 07.23.09.292134 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 07.00.00.706906 AM -05:00		 +000 00:23:09
26-OCT-16 06.23.26.838110 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 06.00.00.737269 AM -05:00		 +000 00:23:26
26-OCT-16 05.23.46.628555 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 05.00.00.779244 AM -05:00		 +000 00:23:46
26-OCT-16 04.26.10.248261 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 04.00.00.118811 AM -05:00		 +000 00:26:10
26-OCT-16 03.26.45.574405 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 03.00.00.868199 AM -05:00		 +000 00:26:45
26-OCT-16 02.29.59.220470 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 02.00.00.716143 AM -05:00		 +000 00:29:59
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 01.00.00.671400 AM -05:00		 +000 00:41:19
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB		   SUCCEEDED  26-OCT-16 12.00.00.686242 AM -05:00		 +000 00:40:14
25-OCT-16 11.25.01.222486 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.094405 PM -05:00		 +000 00:25:01
25-OCT-16 10.25.16.188272 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.093149 PM -05:00		 +000 00:25:16
25-OCT-16 09.22.50.957227 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.685572 PM -05:00		 +000 00:22:50
25-OCT-16 08.23.24.366195 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.681146 PM -05:00		 +000 00:23:24
25-OCT-16 07.21.34.877025 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.679188 PM -05:00		 +000 00:21:34
25-OCT-16 06.22.01.687270 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.705931 PM -05:00		 +000 00:22:01
25-OCT-16 05.22.59.985410 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.058401 PM -05:00		 +000 00:23:00
25-OCT-16 04.23.47.510414 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.117113 PM -05:00		 +000 00:23:47
25-OCT-16 03.23.04.877802 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.099222 PM -05:00		 +000 00:23:05
25-OCT-16 02.26.03.301910 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.116152 PM -05:00		 +000 00:26:03
25-OCT-16 01.24.27.430723 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.117626 PM -05:00		 +000 00:24:27
25-OCT-16 12.24.36.971637 PM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.664118 PM -05:00		 +000 00:24:36
25-OCT-16 11.23.58.307402 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 11.00.00.096260 AM -05:00		 +000 00:23:58
25-OCT-16 10.24.31.716126 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 10.00.00.092256 AM -05:00		 +000 00:24:32
25-OCT-16 09.24.10.974503 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 09.00.00.100348 AM -05:00		 +000 00:24:11
25-OCT-16 08.22.53.833215 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 08.00.00.082274 AM -05:00		 +000 00:22:54
25-OCT-16 07.24.30.938431 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 07.00.00.692159 AM -05:00		 +000 00:24:30
25-OCT-16 06.23.52.431531 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 06.00.00.093246 AM -05:00		 +000 00:23:52
25-OCT-16 05.24.41.532133 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 05.00.00.729381 AM -05:00		 +000 00:24:41
25-OCT-16 04.23.25.958305 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 04.00.00.208211 AM -05:00		 +000 00:23:26
25-OCT-16 03.25.01.192060 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 03.00.00.106130 AM -05:00		 +000 00:25:01
25-OCT-16 02.28.53.376223 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 02.00.00.662053 AM -05:00		 +000 00:28:53
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 01.00.00.690348 AM -05:00		 +000 00:40:10
25-OCT-16 12.26.06.567009 AM -05:00 PURGE_JOB		   SUCCEEDED  25-OCT-16 12.00.00.101229 AM -05:00		 +000 00:26:06
24-OCT-16 11.23.44.968956 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 11.00.00.112954 PM -05:00		 +000 00:23:45
24-OCT-16 10.25.27.790486 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 10.00.02.818356 PM -05:00		 +000 00:25:25
24-OCT-16 09.21.43.329295 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 09.00.00.143735 PM -05:00		 +000 00:21:43
24-OCT-16 08.23.53.456424 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 08.00.00.699357 PM -05:00		 +000 00:23:53
24-OCT-16 07.22.46.201840 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 07.00.00.108008 PM -05:00		 +000 00:22:46
24-OCT-16 06.24.31.090402 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 06.00.00.712135 PM -05:00		 +000 00:24:30
24-OCT-16 05.22.41.748800 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 05.00.00.092252 PM -05:00		 +000 00:22:42
24-OCT-16 04.24.25.687414 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 04.00.00.093241 PM -05:00		 +000 00:24:26
24-OCT-16 03.24.51.693561 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 03.00.00.734264 PM -05:00		 +000 00:24:51
24-OCT-16 02.23.25.886242 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 02.00.00.689991 PM -05:00		 +000 00:23:25
24-OCT-16 01.26.34.238241 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 01.00.00.097179 PM -05:00		 +000 00:26:34
24-OCT-16 12.26.56.946893 PM -05:00 PURGE_JOB		   SUCCEEDED  24-OCT-16 12.00.00.097951 PM -05:00		 +000 00:26:57

98 rows selected.

SQL >

Here’s the better way for  doing this using extract function.

Datatype is data type is INTERVAL DAY(3) TO SECOND(0)..

24 hours per day and 60 minutes per hour.

SQL > r
  1  SELECT log_date, job_name, extract(day from 24*60*RUN_DURATION) mins
  2  FROM dba_scheduler_job_run_details
  3  WHERE extract(day from 24*60*RUN_DURATION)>30
  4  AND job_name='PURGE_JOB'
  5  and log_date>sysdate-90
  6  ORDER BY 1 DESC
  7*

LOG_DATE			    JOB_NAME				 MINS
----------------------------------- ------------------------------ ----------
28-OCT-16 02.33.20.747873 AM -05:00 PURGE_JOB			   33
28-OCT-16 01.42.40.477522 AM -05:00 PURGE_JOB			   42
27-OCT-16 01.38.20.999365 AM -05:00 PURGE_JOB			   38
26-OCT-16 01.41.19.190372 AM -05:00 PURGE_JOB			   41
26-OCT-16 12.40.14.668943 AM -05:00 PURGE_JOB			   40
25-OCT-16 01.40.10.906657 AM -05:00 PURGE_JOB			   40
24-OCT-16 03.31.51.372573 AM -05:00 PURGE_JOB			   31
24-OCT-16 02.31.55.268260 AM -05:00 PURGE_JOB			   31
24-OCT-16 01.45.33.525611 AM -05:00 PURGE_JOB			   45
22-OCT-16 05.39.23.980162 AM -05:00 PURGE_JOB			   39
22-OCT-16 04.31.25.202116 AM -05:00 PURGE_JOB			   31
21-OCT-16 01.38.29.977490 AM -05:00 PURGE_JOB			   38
20-OCT-16 01.40.58.167010 AM -05:00 PURGE_JOB			   40
19-OCT-16 01.32.25.295604 AM -05:00 PURGE_JOB			   32
18-OCT-16 01.32.53.733495 AM -05:00 PURGE_JOB			   32
17-OCT-16 01.31.49.296251 AM -05:00 PURGE_JOB			   31
14-OCT-16 03.31.43.274818 AM -05:00 PURGE_JOB			   31
14-OCT-16 02.36.02.084047 AM -05:00 PURGE_JOB			   36
14-OCT-16 01.40.30.270864 AM -05:00 PURGE_JOB			   40
13-OCT-16 01.40.32.792297 AM -05:00 PURGE_JOB			   40
12-OCT-16 03.31.59.942443 AM -05:00 PURGE_JOB			   31
12-OCT-16 02.35.10.422916 AM -05:00 PURGE_JOB			   35
12-OCT-16 01.44.37.543112 AM -05:00 PURGE_JOB			   44
11-OCT-16 01.37.06.018139 AM -05:00 PURGE_JOB			   37
10-OCT-16 03.31.07.278843 AM -05:00 PURGE_JOB			   31
10-OCT-16 02.33.08.974312 AM -05:00 PURGE_JOB			   33
10-OCT-16 01.44.18.952183 AM -05:00 PURGE_JOB			   44
08-OCT-16 05.34.36.615095 AM -05:00 PURGE_JOB			   34
03-OCT-16 02.34.54.308012 AM -05:00 PURGE_JOB			   34
03-OCT-16 01.38.21.388805 AM -05:00 PURGE_JOB			   38
02-OCT-16 04.33.57.111031 PM -05:00 PURGE_JOB			   33
02-OCT-16 03.31.53.305048 AM -05:00 PURGE_JOB			   31
02-OCT-16 01.38.53.896792 AM -05:00 PURGE_JOB			   38
30-SEP-16 04.31.26.036246 AM -05:00 PURGE_JOB			   31
30-SEP-16 01.42.47.725811 AM -05:00 PURGE_JOB			   42
29-SEP-16 01.35.00.663317 AM -05:00 PURGE_JOB			   35

36 rows selected.

SQL > 

Oracle Student Cloud’s aka Oracle CX for Higher Education

OracleApps Epicenter - Sat, 2016-10-29 06:53
Oracle Student Cloud’s new Oracle CX for Higher Education uses Oracle’s intuitive mobile technology to help recruiters boost their pipeline by targeting and qualifying best-fit prospects via social, email, and SMS CRM capabilities. Oracle Student Recruiting Cloud’s embedded analytics also help improve forecasting and monitor and optimize recruiters’ performance in their territories. Student Engagement broadly […]
Categories: APPS Blogs

Oracle 12c – Automatic Control File Backups

Yann Neuhaus - Sat, 2016-10-29 05:29

By default, automatic control file backups are disabled (even with 12c), maybe for performance reasons.

RMAN> SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters for database with db_unique_name OCM121 are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

And also good to know, the autobackup after structural changes does not occur for databases in NOARCHIVELOG mode. So, if your database is running in NOARCHIVELOG mode, you will never see any impact, independent of controlfile autobackup is on or off.
However, my database is running in ARCHIVELOG mode and at the moment the controlfile autobackup feature is disabled.
But even when the auto backup feature is disabled, RMAN will still back up the current controlfile and the server parameter file whenever any backup command includes datafile 1 from the data files that belong to the target database. In an Oracle database, data file 1 is always part of the system tablespace, which contains the data dictionary.

But I have heard that it is highly recommended to enable automatic controlfile backups. It will ensure that the critical controlfile is backed up regularly following a backup or structural change to the database. Once you configure automatic controlfile backup, RMAN will automatically back up your target database controlfile, as well as the current server parameter file, when any of the following events occurs:

  • Successful completion of either a backup or the copy command
  • After a create catalog command from the RMAN prompt is successfully completed
  • Any structural changes to the database modify the contents of the control file

Any changes to the physical structure of your database, even if they are made through SQL*Plus, will trigger a controlfile auto backup, e.g.

  • adding a tablespace or data file
  • dropping a data file
  • placing a tablespace offline or online
  • adding an online redo log, and renaming a data file

So, to follow the recommendation, I will enable the automatic backup of the controlfile. At the moment I have no backup of the controlfile, which is not good at all.

RMAN>  list backup of controlfile;

specification does not match any backup in the repository

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

But nothing happens. What is going on here. Is it a bug, a feature or something else.
Hold on. Then … all of a sudden, the backup of the controlfile popps up.

RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23
        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_28/s_926437339.526.926437343
  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19

 

To understand what is happening here, the answer is like always. It depends. :-) In case you are running an Oracle database with 11gR2 or higher, then it is a feature. Anything below would be a bug.

Beginning with Oracle 11gR2, the controlfile autobackup deferral feature has been implemented. In order to increase performance, the controlfile autobackup creation after structural changes, has been deferred. In previous releases, one controlfile autobackup is created with each DDL command that makes structural changes in the database and we can see in the alert.log a message about controlfile autobackup creation after each DDL command executed.
This can provoke serious performance problems when multiple structural changes are made together. Starting from Oracle Database Release 11g Release 2, RMAN takes only one control file autobackup when multiple structural changes contained in a script have been applied (for example, adding tablespaces, altering the state of a tablespace or datafile,
adding a new online redo log, renaming a file, and so on) during a specified time.

But what does time mean here exactly. It is 1 minute, 1 hour, or 1 day?

The deferral time is controlled by an underscore parameter that defaults to 300 seconds (5 minutes). The parameter is the following:

_controlfile_autobackup_delay=300

The minimum value for that parameter is 0, which simulates the behavior before 11gR2. The maximum value in 12c is (1024*1024*1024*2)-1, which is 2147483647 seconds. However, I don’t see any practical value for setting the value that high.

In 11gR2 or higher, the controlfile autobackups are created by MMON slaves after few minutes (5 minutes per default) of the structural changes, which increases performance. It is also expected that no message about controlfile autobackup creation will appear in the alert.log.
However, there will be one MMON slave trace file with the controlfile creation information, that will be a file named: SID__m000_<OS_PID>.trc
Ok. Let’s try to simulate the old behavior by setting the autobackup delay to 0, and to create another tablespace afterwards.

SQL> alter system set "_controlfile_autobackup_delay"=0;

System altered.

SQL> create tablespace unpatient2 datafile size 16M;

Tablespace created.

And now the controlfile autobackup is created immediately.

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
131     Full    9.89M      DISK        00:00:04     28-OCT-2016 15:42:23
        BP Key: 314   Status: AVAILABLE  Compressed: NO  Tag: TAG20161028T154219
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_28/s_926437339.526.926437343
  Control File Included: Ckp SCN: 10102765     Ckp time: 28-OCT-2016 15:42:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
132     Full    9.89M      DISK        00:00:05     28-OCT-2016 17:41:16
        BP Key: 315   Status: AVAILABLE  Compressed: NO  Tag: TAG20161029T113939
        Piece Name: +FRA/OCM121/AUTOBACKUP/2016_10_29/s_926509179.514.926509183
  Control File Included: Ckp SCN: 10271426     Ckp time: 28-OCT-2016 17:41:11

So, do I recommend to set the RMAN controlfile autobackup to ON. Yes absolutely. And do I recommend to set the controlfile autobackup delay to 0. No, probably not. I think, that the 5-minute interval is a quite good compromise. You just need to be aware, that it exists.

Another hint is, that you should not rely too much on the view V$RMAN_BACKUP_JOB_DETAILS. In this view, the autobackups should be populated, whenever a autobackup happend. But in my case the AUTOBACKUP_DONE is always set to NO.

SQL> select start_time,end_time,status,autobackup_done, AUTOBACKUP_COUNT from
2 V$RMAN_BACKUP_JOB_DETAILS where autobackup_done = 'YES';

no rows selected

There is a patch available from Oracle: “Patch 18074513: V$RMAN_BACKUP_JOB_DETAILS VIEWS COLUMN AUTOBACKUP_DONE DOESNOT GET POPULATED”, but it is not available for every platform and every version.

Better use the RMAN “list backup of controlfile;” command. That one is much more reliable.

Cheers,
William

 

 

 

 

 

Cet article Oracle 12c – Automatic Control File Backups est apparu en premier sur Blog dbi services.

Identifying SQL Queries that are consuming Shared Pool Memory

Tom Kyte - Sat, 2016-10-29 01:46
Hello Tom, Is there a way to identify SQL queries that are consuming the majority of the shared pool for e.g Session #1 is using 56 kb session #2 is 10mb I would want to identify each query and the memory value it's consuming. Thanks in advan...
Categories: DBA Blogs

Efficient method in Oracle to continuously poll data from one database and move to another

Tom Kyte - Sat, 2016-10-29 01:46
Hi, I have a scenario where I have to continuously( in every 1 hour) poll updated data from tables of one database and move to another database. What do you think is the standard approach. (anything similar to DTS in Sql server in Oracle) or would...
Categories: DBA Blogs

getting error 0382

Tom Kyte - Sat, 2016-10-29 01:46
I have made stored procedure and executing through trigger.So my requirements is like that if we create a new issue first statement should execute and if we return the same issue number then 2 nd statement should execute. I have make like this but...
Categories: DBA Blogs

Pagination query and optimization

Tom Kyte - Sat, 2016-10-29 01:46
Hi Tom, I've been reading through a few posts regarding pagination. I'm using the following query template in my application, to implement a "previous", "next" feature to page through a search resultset: (J2EE front-end calls a stored proc returni...
Categories: DBA Blogs

Flashback Database Logs can exceed the Retention Target

Hemant K Chitale - Fri, 2016-10-28 18:58
The documentation on the Flashback Retention Target in 11.2 and 12.1 states that this parameter specifies an upper limit on how far the database may be flashed back.

However, if the FRA (db_recovery_file_dest_size) is actually large enough, Oracle may retain flashback logs for a much longer duration.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.* from v$flashback_database_log l;

SYSDATE OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET
------------------ -------------------- ------------------ ----------------
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
29-OCT-16 07:42:44 6968261 28-OCT-16 22:35:50 180
157286400 86467584


SQL>
SQL> show parameter flashback

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 180
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> flashback database to timestamp trunc(sysdate);

Flashback complete.

SQL>


Thus, it is useful to check the V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE and V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE views from time to time.

See Oracle Support Doc# 1194013.1 for a discrepancy between the first two views.

Note : If you have Standby database configured, also see John Hallas's blog post.
.
.
.

Categories: DBA Blogs

To shrink or to move

Laurent Schneider - Fri, 2016-10-28 10:43

If you delete most of the rows in one table, you probably want to regain that space, to minimize IO and fragmentation.

If you delete most of the rows in most of the tables, you probably also want to resize the datafile to reduce storage and backup space.

In this case, you could move your data in a new tablespace

alter table t move tablespace newts;

I wrote about this here : 2006/08/tablespace-maintenance-tasks.html

There is also an option to SHRINK. In oldish (pre-12cR2) releases, this had the advantage of being an online operation. In 12.2, include the online keyword.

SHRINK requires ROW MOVEMENT. I don’t like the idea of having the rowid’s changing. You have also a bunch of restrictions, amoung others on materialized view fast refreshes and index organized tables.

I am impatient to get this 12cR2. According to the doc, during an alter table move, transactions run uninterrupted on the table

Connecting PL/SQL Developer

Bar Solutions - Fri, 2016-10-28 10:28

In SQL Developer you have a lot of options when connecting to the database. You can use the TNS entries defined but you can also give the hostname, port and SID or Servicename for instance. PL/SQL Developer doesn’t supply these options, but you can still use them…

In the logonscreen you can choose the database by choosing you TNS entry. But did you know you can put in the complete text of a tnsnames.ora entry here?
So, you can type in:

username: demo
password: <<password>>
database: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DEMO)))

But it can be done even simpler:

username: demo
password: <<password>>
database: LOCALHOST:1521/DEMO

Maybe you already knew this and I have known this for a while, but I thought I would write this down anyway. Hope the helps some of you.

Modernize Employee Engagement: Making Culture Actionable Webcast Q&A

WebCenter Team - Fri, 2016-10-28 08:36
Oracle Corporation Oracle Webcast - Making Culture Actionable

Thank you for those who joined our webcast “Modernize Employee Engagement: Making Culture Actionable.” We hope you found the content presented valuable and enjoyed what the speakers presented. There were some questions that were asked throughout the webcast, so Dave has captured them here for you.

Q: Is there a clear difference between behaviors and the underlying why? Some of the 'why's' could look like behaviors too.
A: Very true. Often we find that leader behaviors very often belong in the “enablers and blockers” category, because leader behaviors are part of the way that an organization sets the tone for what is acceptable and what is not. The rules should not be rigid. The purpose of the Culture Map is to frame a conversation. When the question comes up in a session I ask the team where they think something belongs.

Q: If the execs weren't in the room when the sticky notes were generated, do they 'own' the diagnosis?
A: Great question. Executives are almost always the people who are sponsoring the work. Before we start any culture work, we explain why we will get better results if they are not in the room. We also caution them that if we start doing culture mapping people will start to shift their expectations. People will start to believe that management is listening to them. We caution leaders that if they are not prepared to accept the results, it would be better not to begin the process at all. It’s better to do nothing than to create expectations and hope for change, and then not act on them.

Q: Do you help companies with those 'other things' (systems, etc.), or do you focus on tracking the behavioral change?
A: Great question! Our company’s focus is on creating clarity and alignment in a way that drives impact. Making information clear and actionable. We help our clients gain clarity, understanding and alignment so they can move more quickly to action on many things, including strategy execution, systems, performance improvement, organizational structure and process design.

Q: What changes have you noticed in your own organization since you’ve implemented the Culture Maps?
A: We refresh our culture map every three years, right alongside our strategic planning process. I’ve noticed that this helps us make sure that we are intentionally assessing our culture and how well it serves our current organizational realities. Culture is like the business environment – it’s dynamic, not static, so we need to keep up. 

Another thing I’ve noticed is that having a clearly defined and visualized culture makes it easy for us to call each other out when we act in a way that’s “off culture.” I’ve been called on that myself. When someone can call out the company founder for straying from the behavior we want to see, I think that’s a great sign.

Q: What do you do if/when there is resistance to change when working with companies?
A: This is such a common phenomenon. The first thing we do is try to find out exactly where the resistance is coming from. We have identified 36 causes of resistance and have developed tool for diagnosing the underlying dynamics: The “barriers to change” card deck.

If you’d like to connect with Dave, you can follow him on Twitter @davegray or visit his website for more information. 


Automatic conversion of cursor for loop into set based operation

Tom Kyte - Fri, 2016-10-28 07:26
Hi, We all know that doing things row-by-row ("Cursor For Loops") is a bad idea rather than a set-based approach, however I have read in a number of places that in certain circumstances Oracle will convert a cursor for loop into a set-based operatio...
Categories: DBA Blogs

Spanish trasnlation for amount in words

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I have a requirement by a client that the amount in number currently prnting on checks needs to be converted to spanish, ex one hundred us dollars should be printed as cien dolares americanos Could you please suggest how can w...
Categories: DBA Blogs

How can i incorporate a text to the existing values of a column

Tom Kyte - Fri, 2016-10-28 07:26
Hi, The query is as below, I have a table in which i have the column named as V_QTN_NAME, here in this column the values are of different in each row. Now i am trying to differentiate the values by incorporating the text 'OLD'to the existing va...
Categories: DBA Blogs

Instance and SGA relation

Tom Kyte - Fri, 2016-10-28 07:26
Hi Tom, I would like to know how many SGA Created inside instance when data base starts? Here instance mean instance only i.e to which we mount database. Also please let me know if you answer the questions posted in reviews,perhaps i wil get ...
Categories: DBA Blogs

Insert statement getting locked in database.

Tom Kyte - Fri, 2016-10-28 07:26
Hello Sir, I have a very simple question and I have searched all around but could not find it, can insert statements be locked by other DML sessions ?? And how ? Reference :- I have a process that updates/inserts multiple records into one table ...
Categories: DBA Blogs

RMAN Incremental

Tom Kyte - Fri, 2016-10-28 07:26
Hello Team, We have oracle 12c standard running on centos. Basic backup strategy is developed without using asm/ catalog database as suggested in requirements. Backup Plan is as below: Requirement says full backup has to run every night with ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator