Archives pour la catégorie Non classé

Merging PL/SQL arrays… or when Oracle is so nice with me

Today i was searching a nice way to merge 2 arrays inside a pl/sql bloc.

I have found plenty of solutions over the web with select statment and cast, or with loop inside PL/SQL.

But in fact there is a more easier way to do that in 1 row … This is possible with the MULTISET operator.

DECLARE
  TYPE t_type IS TABLE OF INTEGER;
  v_t1 t_type;
  v_t2 t_type;
  v_t3 t_type := t_type();
BEGIN
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 1 FROM dual UNION SELECT 2 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  SELECT * BULK COLLECT
  INTO   v_t1
  FROM   (SELECT 3 FROM dual UNION SELECT 4 FROM dual);
  v_t3 := v_t1 MULTISET UNION ALL v_t3;
  FOR i IN v_t3.first .. v_t3.last LOOP
    dbms_output.put_line(v_t3(i));
  END LOOP;
END;
/

Which will display

3
4
1
2

How to find tables analyzed by dbms_stats job

Sometime it can be usefull to know what have done the automatic stats jobs

Here is a statment that will let you know the objects that have been analyzed by the job

As SYS execute the following

SELECT /*+ dynamic_sampling(4) dynamic_sampling_est_cdn */
               CASE
                  WHEN o.type# IN (1, 20, 35)
                     THEN 'INDEX'
                  WHEN o.type# IN (2, 19, 34)
                     THEN 'TABLE'
                  ELSE 'UNKNOWN'
               END object_type,
               u.NAME owner, o.NAME object_name,
               CASE
                  WHEN o.type# IN (19, 20)
                     THEN o.subname
                  WHEN o.type# IN (34, 35)
                     THEN (SELECT po.subname
                             FROM SYS.obj$ po
                            WHERE po.obj# = st.bo#)
                  ELSE NULL
               END PARTITION,
               CASE
                  WHEN o.type# IN (34, 35)
                     THEN o.subname
                  ELSE NULL
               END SUBPARTITION,
               CASE st.STATUS
                  WHEN 0
                     THEN 'PENDING'
                  WHEN 1
                     THEN 'BEING PROCESSED'
                  WHEN 2
                     THEN 'COMPLETED'
                  WHEN 3
                     THEN DECODE (BITAND (st.flags, 8),
                                  8, 'TIMEOUT',
                                  'ERROR'
                                 )
                  ELSE NULL
               END STATUS,
               CASE
                  WHEN st.staleness <= -99
                     THEN 'MISSING'
                  ELSE 'STALE'
               END reason
          FROM SYS.stats_target$ st, SYS.obj$ o, SYS.user$ u
         WHERE st.obj# = o.obj#
           AND o.owner# = u.user#
           AND st.STATUS != 4
           AND (st.SID, st.serial#) IN (
                  SELECT /*+ no_unnest */
                         REGEXP_REPLACE (session_id, ',.*') SID,
                         REGEXP_REPLACE (session_id, '.*,') serial#
                    FROM (SELECT   CASE
                                      WHEN REGEXP_REPLACE
                                                      (session_id,
                                                       '[0-9,]'
                                                      ) IS NULL
                                         THEN session_id
                                      ELSE NULL
                                   END session_id
                              FROM dba_scheduler_job_run_details
                             WHERE job_name = 'GATHER_STATS_JOB'
                          ORDER BY actual_start_date DESC)
                   WHERE ROWNUM < 2)

New Sylohe.com!!!

Hello everyone, here is the new theme of sylohe.com. This theme was design by one of my friend Freezbenn. I have rebuild some part to put them in a fresh install of the wiki. I will also use this new blog to manage some articles and news. I hope you will like this new theme.