Thinking Out Loud

October 29, 2016

Working With Interval Datatype

Filed under: 10g,oracle — mdinh @ 3:41 pm

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 > 

September 13, 2015

Scaling Export and Import Tables Residing in Different Schemas 10gR2

Filed under: 10g,DataPump,oracle — mdinh @ 2:40 pm

Our team was tasked to unpartion all partition tables.

Import: Release 11.2.0.4.0 has PARTITION_OPTIONS=DEPARTITION but we are on Release 10.2.0.4.0

The first step was to find all schemas with partition tables.

select owner, table_name, partitioning_type, subpartitioning_type, partition_count, status 
from dba_part_tables where owner not in ('SYS','SYSTEM') 
and (owner,table_name) not in (
 select owner,mview_name table_name 
 from dba_mviews 
 where owner not in ('SYS','SYSTEM') 
) 
order by 1,2
;

NOTE: SQL is not 100% fail proof as we ran into a scenario where the table and materialized view had the same name.

Export the metatadata for table from multiple schemas failed:

UDE-00012: table mode exports only allow objects from one schema

Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
Example:
File: expdp_tabs.par 
-------------------- 
DIRECTORY = my_dir  
DUMPFILE  = expdp_tabs.dmp  
LOGFILE   = expdp_tabs.log  
SCHEMAS   = scott,hr,oe   
INCLUDE   = TABLE:"IN ('EMP','EMPLOYEES','CATEGORIES_TAB')" 

Great solution but not scalable.

Dig to find – How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases
http://uralural.blogspot.com/2011/06/how-to-export-tables-from-multiple.html

I will let you read the post; however, the following was the key for me.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                            

Database has 22 partition tables.

*** List partition tables, exluding MVIEW ***
22 rows selected.

There are 11 schemas with partition tables.

*** List partition tables count by owner, exluding MVIEW ***
11 rows selected.

Partition table SYSTEM_QUEUE resides in 7 different schemas and ACCOUNT_OBJECT_TRANSACTIONS resides in 2 different schemas.


*** List same table name across owner, exluding MVIEW ***
TABLE_NAME			 COUNT(*)
------------------------------ ----------
SYSTEM_QUEUE				7
ACCOUNT_OBJECT_TRANSACTIONS		2

Create tables to use for export.

create table OWNER01.expdp_tables (table_name varchar2(30))
;
insert into OWNER01.expdp_tables
select DISTINCT table_name
from dba_part_tables
where owner not in ('SYS','SYSTEM')
and (owner,table_name) not in (
  select owner,mview_name table_name from dba_mviews where owner not in ('SYS','SYSTEM')
)
; 

Create export parameter.

$ cat expdp_schema_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
content=METADATA_ONLY
dumpfile=TEST.dmp
logfile=exp_TEST.log
schemas=OWNER01,OWNER02,OWNER03,OWNER04,OWNER05,OWNER06,OWNER07,OWNER08,OWNER09,OWNER10,OWNER11
INCLUDE=TABLE:"IN (SELECT table_name FROM OWNER01.expdp_tables)"

Perform export.

$ expdp parfile=expdp_schema_TEST.par 

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:04:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  parfile=expdp_schema_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 20 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 5 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 85 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 30 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 22 TABLE_STATISTICS objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u02/oracle/exp/TEST.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:46

Create import parameter.

$ cat impdp_sqlfile_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
exclude=STATISTICS
dumpfile=TEST.dmp
logfile=imp_TEST.log
sqlfile=create_TEST.sql

Perform import.

$ impdp parfile=impdp_sqlfile_TEST.par 

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:07:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  parfile=impdp_sqlfile_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 8 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:07:48

BVT

$ grep -c "CREATE TABLE" /u02/oracle/exp/create_TEST.sql|sort
22

Blog at WordPress.com.