Oracle Tips and Tricks — David Fitzjarrell

August 18, 2008

If It Ain’t Fixed…

Filed under: General,pl/sql — dfitzjarrell @ 14:09

Occasionally certain objects in a database can fail to function, such as packages, procedures, triggers, functions, indexes, synonyms and views. Modifying any of the underlying dependent objects those items rely upon can change the status of such objects from ‘VALID’ to ‘INVALID’ or ‘UNUSABLE’. Short of attempting to access such objects and failing miserably how does one discover and correct such problems? The solution is fairly simple: ask the database.

Oracle provides, in the *_OBJECTS views, a column named STATUS which lists the status (obviously) of the object in question. When everything is right and proper that status should be ‘VALID’; of course no database is problem-free or immune to code changes so there are times when various objects may no longer be usable. Finding these objects is half of the battle; let’s look at a query to do that:

SQL> select object_name, object_type, status
  2  from user_objects
  3  where status  'VALID'
  4  order by created;


OBJECT_NAME                         OBJECT_TYPE         STATUS
----------------------------------- ------------------- -------
OWA_SYLK                            PACKAGE             INVALID
SCHEMA_ACCESS                       PROCEDURE           INVALID
CHECK_SAL                           FUNCTION            INVALID
RAISE_SAL                           PROCEDURE           INVALID
STRINGC                             FUNCTION            INVALID
JOB_PKG                             PACKAGE             INVALID
DATES_PKG                           PACKAGE             INVALID
EMP_VW                              VIEW                INVALID
VIEW_EMP_DEPT                       VIEW                INVALID
GET_EMPNAME                         FUNCTION            INVALID
GET_SAL                             PROCEDURE           INVALID
PROJECT                             SYNONYM             INVALID

12 rows selected.

SQL>

Notice the objects are ordered by their creation date; this allows the query to be used to write a dynamic script to recompile the objects and avoid dependency invalidations in the process:

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter FUNCTION GET_EMPNAME compile;
alter FUNCTION STRINGC compile;
alter PACKAGE DATES_PKG compile;
alter PACKAGE JOB_PKG compile;
alter PACKAGE OWA_SYLK compile;
alter PROCEDURE GET_SAL compile;
alter PROCEDURE RAISE_SAL compile;
alter PROCEDURE SCHEMA_ACCESS compile;
alter SYNONYM PrOJECT compile;
alter VIEW EMP_VW compile;
alter VIEW VIEW_EMP_DEPT compile;

12 rows selected.

SQL>

But, wait, there’s a neat little script that Oracle has provided to do the same job: utlrp.sql, located in the $ORACLE_HOME/rdbms/admin directory. It calls the UTL_RECOMP package and recompiles all invalid objects in the database (or tries to). It also reports how many of the recompiled objects generated errors and, if this number is larger than you might expect (yes, you may have invalid objects which cannot be ‘fixed’) then you run the first query listed and see which objects are affected. You can then use the second query to generate a dynamic list, modify that script to include a ‘SHOW ERRORS [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION| JAVA SOURCE | JAVA CLASS} [schema.]name]’ command after each compile statement and discover why each remaining invalid object would not successfully compile (full syntax is used for the illustrated ‘show errors’ invocations):

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status  'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status  'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter PROCEDURE RAISE_SAL compile;

SQL>

Generating a more detailed error message for each compile:

SQL> alter FUNCTION CHECK_SAL compile;

Warning: Function altered with compilation errors.

SQL> show errors function check_sal
Errors for FUNCTION CHECK_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/30     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL> alter PROCEDURE RAISE_SAL compile;

Warning: Procedure altered with compilation errors.

SQL> show errors procedure raise_sal
Errors for PROCEDURE RAISE_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL>

So we’re missing a table these objects depend upon, and until that table is replaced they will remain invalid, so we can stop trying to compile them.

In most cases views won’t need to be recompiled as select access to invalid views automatically performs that action; of course if the situation is like that shown above nothing will make the invalid view usable.

Unusable indexes are treated a bit differently, as they can’t be recompiled; they need to be rebuilt. Normally the database would be shutdown and then started in restricted mode to allow the rebuild to commence unhindered (rebuilding indexes really shouldn’t be done when users are actively accessing the database as it consumes resources and can cause exceptional delays for other processes while the rebuild of each index is taking place). A similar query to the invalid objects SQL can find the unusable indexes:

SQL> select index_name, status
  2  from user_indexes
  3  where status  'VALID';

no rows selected

SQL>

Had there been any unusable indexes the following query will generate the necessary executable statements:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||';'
from user_indexes
where status  'VALID';

Spool that output to a file, verify it wrote correctly (the default line size may be a bit short for some resulting lines, so you need to check that each alter statement is, indeed, on a single line) then prepare to execute the script after the database is in restricted mode. Log the execution of the script so any resource-related errors can be addressed before it’s run again (usually, though, one run is sufficient).

Fixed views are a different story, as they’re based upon memory and internal disk structures. If any of these are declared INVALID the only recommended action to be taken is to contact Oracle Support as you cannot recompile such views. It’s likely that you’ll be told to shutdown and startup the database, but do NOT proceed with that action until told to do so by, you guessed it, Oracle Support.

So, finding and correcting invalid database objects is fairly straightforward; it does require attention to detail, however, to ensure that all objects which can be successfuly recompiled/rebuilt are again in a usable state. Practice on a test database is recommended so that if and when this process is required on a production system it’s been tested and re-tested and the method is properly defined and documented.

Of course, if it ain’t broke …

Advertisements

August 15, 2008

That Darned "CAT"-alogue

Filed under: General — dfitzjarrell @ 20:42

There have been quite a number of searches for information on many of the exu8 views present in an Oracle data dictionary. Such views belong to the export/import catalog, created with the $ORACLE_HOME/rdbms/admin/catexp.sql script. Inside this script, in the internal documentation, lies descriptions of many of these views. Let’s look at a few which have made the ‘Hit Parade’ in Google searches:

exu8poki

 This view contains the partitioning key columns for partitioned indexes.

exu8jbq

 Contains job queue entries.

exu8rif

 Contains information on referential contraints.

exu8snapl

 Snapshot log information

exu81obj

 This view selects all rows from sys.obj$ that are NOT secondary
 objects as created by Domain Indexes. Secondary objects are not normally
 exported because the domain index's CREATE INDEX at import time will create
 them.
 

I won’t cover any more of these views, as you can easily look in the creation script and find descriptions for almost all of the export/import views.

[If any of these views are found marked INVALID in the data dictionary you can run $ORACLE_HOME/rdbms/admin/utlrp.sql to try to recompile them; should that not fix the issue they can be rebuilt with the $ORACLE_HOME/rdbms/admin/catexp.sql script; it’s probably best to shutdown the database, start it in restricted mode and then run the script to ensure no one is attempting to use these views while recreating them or you can use

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Of course you’ll need to kill all connected sessions to ensure no one is doing any work as using the above statement only affects new connections to the database. Once the views are listed as VALID you can either shutdown the database and restart it or use

ALTER SYSTEM DISABLE RESTRICTED SESSION;

to allow ‘regular’ users to connect.]

Happy reading.

August 7, 2008

What do Julius Caesar, Jacques Cassini and Pope Gregory XIII have in common? Oracle!

Filed under: dates,General — dfitzjarrell @ 14:06

“Oh, what a tangled web we weave, …”

Sir Walter Scott

Julian date numbers prior to AD 1 can be, well, a mess in Oracle. Take, for example, the stated date limit imposed by the database, which is January 1, 4712 BC. According to Oracle this is the start date for the Julian calendar … but it isn’t, as the start date is actually one year earlier.

Huh?!?!?

It’s true, and to explain this we need to delve into several calendars and conventions to see exactly how this came to be. We need to define a term, first, and that term is proleptic. This means to extend a calendar past the date of its inception to extrapolate dates (dates that didn’t exist in that calendar because it didn’t exist yet or it wasn’t stable). The year the Julian calendar became reliable is 4 AD, which is when the leap years stabilized [old Julius Caesar may have been a good ruler, but his math skills were apparently lacking]. Any ‘Julian’ date prior to 4 AD is considered a proleptic Julian date. And more on that later.

Julian Date Numbers (or Julian dates, as they are commonly considered) are based upon this proleptic calendar as they start at an epoch defined as January 1, 4713 BC, which is Julian Day Number 0 (since 4713 BC is WAY earlier than 4 AD [our stable starting point for the Julian calendar] this is in the proleptic Julian calendar and is, in fact, the considered ‘starting’ date for that calendar chosen because all three cycles of the Julian calendar [indiction {15-year cycle}, Metonic {roughly, a lunar cycle} and Solar] coincided for the first time). And that’s just fine, unless you run afoul of a couple of ‘gotchas‘. Those ‘gotchas’ are:

The Year Zero Rule

With the common BC/AD convention there is no Year 0, as 1 BC directly precedes 1 AD. Not so with an astronomical numbering convention, which designates years with a +/- scheme and includes a year 0 [this convention was first used by French astronomer Jacques Cassini in the mid-18th century]; the ‘AD’ years remain as-is, but the ‘BC’ end of the scale changes thusly:

2 BC is notated as -1
1 BC is notated as 0

So NOW there IS a year 0, but, well, Oracle doesn’t quite know what to do with it. And this is not the only complication, as

The inception of the Gregorian calendar

resulted in a loss of at least 10 days to correct for an excess of accumulated days due to the original, erroneous leap year rule [and we can’t thank Pope Gregory XIII enough for that]. Which 10+ days depends upon the date of adoption:

If the adoption date was October 15, 1582
—————-> then October 5 through 14 vanish.
If the adoption date was September 14, 1752
—————-> then September 3 through 13 disappear.

And there’s an option of no cutover/adoption date at all, in which the dates are considered either all Julian (going back to our proleptic Julian calendar) or all Gregorian (which establishes a proleptic Gregorian calendar) but preserves the ‘missing’ dates for eternity. [There are also other adoption dates for the Gregorian calendar throughout Europe; the two listed are the most ‘popular’.] Oracle chose to use October, 1582 as its cutover date. UNIX, on the other hand, uses the September, 1752 cutover.

So, why is Oracle unable to handle the year 0? It appears that somewhere in the development cycle a programmer decided to use the astronomical convention to calculate the Julian Day Numbers but use BC/AD notation without a correct ‘translation’. Thus the astronomical year -4712 (4712 years before year 0) becomes, in Oracle, 4712 BC instead of its actual proleptic Julian year of 4713 BC. So notation of all of the BC years in Oracle is off by 1, due to the Year 0 issue. The calculations, though, are correct, for any date on or after March 1, 4713 BC (-4712/03/01 using the astronomical notation). Why March 1, 4713 BC? Oracle considers January 1, 4713 BC as Julian Day Number 1 but it’s really Julian Day Number 0 (as mentioned previously). But, the calculation of the Julian Day Number for that date returns … 1:

SQL> with jdna as (
 2      select floor((14 - 1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6     select -4712 + 4800 - a y
 7     from dual, jdna
 8 ),
 9 jdnm as (
10      select 1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      1.15

SQL>

[Maybe Julian Day 0 occurs at exactly noon on January 1, 4713 BC, meaning 12:01 PM on that same day would start Julian Day 1. Or not.] And, since Year 4713 BC would be a leap year (don’t ask me why, it just is) it would have a February 29:

SQL> with jdna as (
 2      select floor((14 - 2)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 2 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 29 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
     59.75

SQL>

but Oracle doesn’t know that date exists, so the errors cancel each other by making March 1, 4713 BC Julian Day Number 60, which is correct. [Apparently Oracle is rounding the results because if we use the formula to calculate the Julian Day Number for March 1, 4713 BC we get:

SQL> with jdna as (
 2      select floor((14 - 3)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select -4712 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select 3 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select 1 + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian
14 from jdna, jdnm, jdny, dual;

    JULIAN
----------
      60.4

SQL>

Round that calculation and you get 60; round the calculation for February 29, 4713 BC and you get … 60. This may be why Oracle refuses to accept
-4712/02/29 as a valid date:

SQL> select to_date('-4712/02/29','syyyy/mm/dd') from dual;
select to_date('-4712/02/29','syyyy/mm/dd') from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified


SQL>

(two dates cannot possess the same Julian Day Number as they are unique).] Any Julian Day Number from March 1, 4713 BC onward is correct. To prove this let’s generate the rounded, truncated and actual Julian day numbers for the date range of 01/01/4713 BC to 03/01/4713 BC and see what ‘falls out’:

SQL> with jd as (
  2  SELECT to_number(to_char(daterange, 'MM')) Mth,
  3         to_number(to_char(daterange, 'DD')) D,
  4         to_number(to_char(daterange, 'SYYYY')) YR
  5  FROM
  6  (
  7    select to_date('01 -4712','MM SYYYY') + (level - 1) as DateRange
  8    from    dual
  9    where   (to_date('01 -4712','MM SYYYY')+level-1) <= last_day(sysdate)
 10    connect by level<=60
 11  )
 12  union
 13  select 2, 29, -4712 from dual
 14  ORDER BY 1,2,3
 15  ),
 16  jdna as (
 17     select floor((14 - mth)/12) a, mth mh, d dy, yr yy
 18     from jd
 19  ),
 20  jdny as (
 21     select yy + 4800 - a y, mh mn, dy dn, yy yn
 22     from jdna
 23  ),
 24  jdnm as (
 25     select mh + (12*a) - 3 m, mh mm, dy dm, yy ym
 26     from jdna
 27  )
 28  select  mh, dy,
 29         case when yy< 1 then abs(1 - yy) else yy end yr,
 30         case when yy < 1 then 'BC' else 'AD' end era,
 31         dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083 julian,
 32         round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0) rnd_julian,
 33         trunc(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083) trunc_julian,
 34         case when mh < 3 and yy < 1 then
 35              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)-1
 36         else
 37              round(dy + (((153*m)+2)/5) + (365*y) + (y/4) -32083, 0)
 38         end actual_julian
 39  from jdna, jdnm, jdny
 40  where jdnm.mm = jdna.mh
 41  and jdny.mn = jdna.mh
 42  and jdnm.dm = jdna.dy
 43* and jdny.dn = jdna.dy

        MO         DY         YR ER     JULIAN RND_JULIAN TRUNC_JULIAN ACTUAL_JULIAN
---------- ---------- ---------- -- ---------- ---------- ------------ -------------
         1          1       4713 BC       1.15          1            1             0
         1          2       4713 BC       2.15          2            2             1
         1          3       4713 BC       3.15          3            3             2
         1          4       4713 BC       4.15          4            4             3
         1          5       4713 BC       5.15          5            5             4
         1          6       4713 BC       6.15          6            6             5
         1          7       4713 BC       7.15          7            7             6
         1          8       4713 BC       8.15          8            8             7
         1          9       4713 BC       9.15          9            9             8
         1         10       4713 BC      10.15         10           10             9
         1         11       4713 BC      11.15         11           11            10
         1         12       4713 BC      12.15         12           12            11
         1         13       4713 BC      13.15         13           13            12
         1         14       4713 BC      14.15         14           14            13
         1         15       4713 BC      15.15         15           15            14
         1         16       4713 BC      16.15         16           16            15
         1         17       4713 BC      17.15         17           17            16
         1         18       4713 BC      18.15         18           18            17
         1         19       4713 BC      19.15         19           19            18
         1         20       4713 BC      20.15         20           20            19
         1         21       4713 BC      21.15         21           21            20
         1         22       4713 BC      22.15         22           22            21
         1         23       4713 BC      23.15         23           23            22
         1         24       4713 BC      24.15         24           24            23
         1         25       4713 BC      25.15         25           25            24
         1         26       4713 BC      26.15         26           26            25
         1         27       4713 BC      27.15         27           27            26
         1         28       4713 BC      28.15         28           28            27
         1         29       4713 BC      29.15         29           29            28
         1         30       4713 BC      30.15         30           30            29
         1         31       4713 BC      31.15         31           31            30
         2          1       4713 BC      31.75         32           31            31
         2          2       4713 BC      32.75         33           32            32
         2          3       4713 BC      33.75         34           33            33
         2          4       4713 BC      34.75         35           34            34
         2          5       4713 BC      35.75         36           35            35
         2          6       4713 BC      36.75         37           36            36
         2          7       4713 BC      37.75         38           37            37
         2          8       4713 BC      38.75         39           38            38
         2          9       4713 BC      39.75         40           39            39
         2         10       4713 BC      40.75         41           40            40
         2         11       4713 BC      41.75         42           41            41
         2         12       4713 BC      42.75         43           42            42
         2         13       4713 BC      43.75         44           43            43
         2         14       4713 BC      44.75         45           44            44
         2         15       4713 BC      45.75         46           45            45
         2         16       4713 BC      46.75         47           46            46
         2         17       4713 BC      47.75         48           47            47
         2         18       4713 BC      48.75         49           48            48
         2         19       4713 BC      49.75         50           49            49
         2         20       4713 BC      50.75         51           50            50
         2         21       4713 BC      51.75         52           51            51
         2         22       4713 BC      52.75         53           52            52
         2         23       4713 BC      53.75         54           53            53
         2         24       4713 BC      54.75         55           54            54
         2         25       4713 BC      55.75         56           55            55
         2         26       4713 BC      56.75         57           56            56
         2         27       4713 BC      57.75         58           57            57
         2         28       4713 BC      58.75         59           58            58
         2         29       4713 BC      59.75         60           59            59
         3          1       4713 BC      60.4          60           60            60


SQL>

Notice if the calculation is rounded then Februrary 29, 4713 BC has the same Julian Day Number as March 1, 4713 BC (as noted earlier); if the calculation is truncated then February 1, 4713 BC has the same Julian Day Number as January 31, 4713 BC and, well, we can’t have a calendar where February 1 doesn’t exist thus the reason [I suspect] that Oracle chose to round the value and then discard February 29, 4713 BC. But, had Oracle performed the numeric ‘gyrations’ illustrated in the query above we could have had proper Julian Day numbers and all of the actual proleptic dates of 4713 BC would exist.

[The above query works on Oracle releases 10.2.x and later due to the ‘connect by level<=60' statement, which produces a date list of 60 values used to generate the posted table. In releases 9.x that statement will parse and execute but may not provide any more than one row of output; in 8.x and earlier that statement will produce a syntax error.]

(Notice that the query uses the “&&” syntax for positional variables; this allows the variable to be set once, when the script is called, and passes the set value through the script to any other places where it is used. Using a single “&” would cause SQL*Plus to request the value for each occurrence in the statement. The up side to this is that variables need only be supplied values once during a run; the down side is the values are persistent between runs and either need to be submitted again on the command line [for a script] or need an explicit ‘undefine’ statement to clear them [as in ‘undefine 1’, for example, to ‘clear’ the value for &&1]. The double & can also be used on named variables, such as &&my_tbl; the undefine syntax works on these, as well, so that ‘undefine my_tbl’ will clear any set value supplied so subsequent runs will request a new value.)

And this gets better. Oracle may not recognize 1 BC as year 0 but the calculations do. Ask Oracle for the number of days difference between December 31, 1 BC and January 1, 1 AD:

SQL> select to_date('01/01/0001', 'mm/dd/yyyy') - to_date('12/31/0001 BC', 'mm/dd/yyyy bc') "This Should Be 1"
 2 from dual;

This Should Be 1
----------------
             367

SQL>

Now let’s ask Oracle to add 1 to the date December 31, 1 BC:

SQL> select to_date('31-DEC-0001 BC', 'DD-MON-YYYY AD')+1 from dual
 2 /

TO_DATE('31
-----------
01-JAN-0000

SQL>

So Year 0 exists (at least according to the JDN formula and an interesting date addition illustrated above) and it’s a leap year. Obviously Oracle takes the 1 BC value and simply tacks on a minus sign, making the year -1 in the astronomical convention — which is wrong as discussed previously. Were Oracle to do this correctly 1 BC would become Year 0 and the returned value for the above query would be 1:

SQL> with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  with jdna as (
 2      select floor((14 - &&1)/12) a
 3      from dual
 4 ),
 5 jdny as (
 6      select &&3 + 4800 - a y
 7      from dual, jdna
 8 ),
 9 jdnm as (
10      select &&1 + (12*a) - 3 m
11      from dual, jdna
12 )
13 select &&1 mo, &&2 dy,
14        case when &&3  select &after_jc - &before_jc from dual;

1721424-1721423
---------------
              1

SQL>

(Oracle recognizes this as a bug (106242) but, due to possible backwards compatibility issues, hasn’t fixed it.)

Can you fix the display issue in Oracle? Well, sort of — you’ll need to generate the date string, parse it, then, if it’s a BC date add one more to the year:

SQL> declare
 2      yr number;
 3      bc varchar2(2);
 4      mo varchar2(10);
 5      dy varchar2(2);
 6
 7 begin
 8      select to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'AD'),
 9             to_number(to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'YYYY')),
10             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'MONTH'),
11             to_char(to_date('-4712/01/01','syyyy/mm/dd'), 'DD')
12      into bc, yr, mo, dy
13      from dual;
14
15      if bc = 'BC' then
16           yr := yr + 1;
17      end if;
18
19      dbms_output.put_line('The date submitted is '||mo||' '||dy||', '||yr||' '||bc);
20
21 end;
22 /
The date submitted is JANUARY 01, 4713 BC

PL/SQL procedure successfully completed.

SQL>

It may be easier to simply remember the BC date notation is one year off.

But, hey, the Julian Date Numbers are correct … if you don’t count January and February of 4713 BC … so is Oracle wrong or is the formula wrong? It appears a bit of both, really, as the formula (as proven) returns the wrong values for dates from the epoch through February 29, 4713 BC, and Oracle doesn’t ‘translate’ the astronomical numbering convention correctly, associating -1 with 1 BC. When it rains, it pours.

Now, where did I put that leap year?

August 5, 2008

I Want To Be Alone

Filed under: General — dfitzjarrell @ 16:44

It’s possible that a tablespace may have but one table or index located therein. Usually it doesn’t matter how may tables/indexes are in a tablespace, but somehow you ended up with a vast expanse of storage with only one segment in residence. How on earth can you report which tablespace is the hiding place for this segment?

Oracle provides two views to report where segments are located:

DBA_SEGMENTS
USER_SEGMENTS

Only DBA_SEGMENTS provides the necessary tools to determine which tablespace, if any, houses only one segment, and if you don’t have access to that view you can’t run the query. The query itself is pretty simple:

select tablespace_name, count(*)
from dba_segments
group by tablespace_name
having count(*) = 1;

Such a query will return any and all tablespaces containing one, and only one, segment. You can’t modify the query to include the owner, though, as that may return any number of tablespaces having more than one segment, but only one segment owned by a particular user (and this is why you can’t use the USER_SEGMENTS view, as that only reports objects owned by the currently logged on user):

SQL> select tablespace_name, count(*)
  2  from dba_segments
  3  group by tablespace_name
  4  having count(*) = 1
  5  /

no rows selected

SQL> select owner, tablespace_name, count(*)
  2  from dba_segments
  3  group by owner, tablespace_name
  4  having count(*) = 1
  5  /

OWNER                          TABLESPACE_NAME                  COUNT(*)
------------------------------ ------------------------------ ----------
SYS                            TOOLS                                   1

SQL>

Note no tablespaces in this database have only one segment, however the TOOLS tablespace does contain ONE segment owned by SYS. So, as shown, the first query is the only query which returns the desired information.

Now, get going and find those stragglers!

Create a free website or blog at WordPress.com.