Access to the database

There is a kind of discussion ongoing who should have access to a database, and under what circumstances.
I have never done either of Jeffs or Chets job, so I cannot write anything reasonable about these. But I am sometimes a kind of DBA. This makes me my best source of knowledge about this job, and the attitude I created during the years.

I developed a certain expectation about the differences between an operation guy and others, who are not responsible for productive systems. I even have my private opinion about a sales related job, but that's not my topic today.

From my point of view it's all about control.

For an operational focused person, my work is about control. If there is uncontrolled behaviour somewhere, this will lead to an incident. And that will causes a call. As I like to sleep at least 8 hours per day, that means one out of 3 calls will disturb this sleep (on average).

Of course every incident potentially costs my company money. Or reputation. This will come down to me again. Writing reports. Doing management presentations. Nonproductive paperwork.

After all this additional work, I will do my best to avoid this situation. Analyzing what went out of bounds, led to this uncontrolled behaviour. At the end I even try to change the environment so this will not happen again.

It's all about control. Freak!

So who should get access to my systems? Only those whose I can control. Or at least I can trust?

It's hard to decide whom to trust. At least to which specific level.

Jeffs driving license seems to be a try to formalise this need. But similar to a driving license, it only shows you have the allowance to drive. It rarely tells anything about the skills.

Talking about skills brings one more dimension here: People living in an ecosystem where every action is controlled by a QualityAssurance team are used to go to the limits and beyond. That's great, it's what is expected from them. Otherwise they would do their job bad.

It's just not what I want in a productive environment. There boundaries are, to never be reached.

I put a more pragmatic approach: If someone takes responsibility for the work, it's fine for me. As an example: if the person who added a big bug during a small hotfix at 5pm is called at 2am the next morning to fix this bug, I'm fine.

And just one more dimension: I favour to grant access to people who know what they can break with this: Just ask me for any access to v$ views in my DB; you will get it, just after you show and explain me how you can halt the application if you abuse it.

Need a short summary?

I like to control access to my database, so I limit it to people who I trust.

To gain it, show me your responsibility and knowledge.

Artist of Farmer?

This morning I read a twitter message by Jeff Smith (aka @hillibillitoad):

OH: "Once we get our system running, we don't touch it." Yeah, that generally works pretty good.

I like Jeffs tweets, blogs and comments as he is a very smart guy and still keeps his mind open for other ideas.

In this particular case I have to contradict, but I was not able to condense it into 140 chars.

Most people in the IT business seems to follow the sentence "never touch a running system" like a commandment. For me this often sounds like the "please don't touch" in an art museum.

This brings me to an interesting question: Do these 'most people' see themselves as artists, and their work as art?

Let me give you another picture:

Imagine a farmer which seeds the crops in spring, did really everything right and then sits down and does not touch his running system. You might guess his harvest?

So what's the big difference here?

Artwork most of the time is first created for a dedicated purpose. As long as the purpose does not change, it's expected to satisfy this purpose.

The purpose of an artwork slowly changes, I'm quite sure most of the time it can be measured in decades or centuries.

Also plants are seeded for a dedicated purpose. But their purpose is the rapid change. To live, to growth, to get harvested after some time. So the farmer has to look after his crops all the time. In the best case, he even can improve and steer the change to his advance.

For me the big difference between an artist ('don't touch') and a farmer ('care and steer') is the timescale of the changes. If you expect your work to be never changed (and in a definition of 'life' things which do not change are just dead) prevent them from any interaction.

I prefer living systems. So I take the duty and care for them.

who owns that directory?

I'm talking here about the DIRECTORY object in Oracle, not the thing you might know from several OSes.

Most objects in a current database can be created for any user, but a DIRECTORY can't. Please check the CREATE DIRECTORY definition, there you can see

No optional schema. anywhere, as you can see with tables, indices and similar objects.

But that's not the end of the story. There is a view which shows you all the objects in a database: DBA_OBJECTS. And there is a owner. allways!

So let's check all the owners of directory objects there:

select distinct owner from DBA_OBJECTS where object_type ='DIRECTORY';

OWNER
------------------------------
SYS

As there is a owner defined somewhere, we also can change it. It's only a question of effort.

Unfortunately there is no ALTER DIRECTORY between ALTER DIMENSION and ALTER DISKGROUP. But it is still defined somewhere, so it can be changed!

Now I'm leaving the well prepared area of Oracle Documentation, but that just increases the fun.

Let's start with the definition of DBA_DIRECTORIES. It's somewhat easier as DBA_OBJECTS and stil shows an owner(!) What a nice sense of humor.

CREATE OR replace FORCE VIEW "SYS"."DBA_DIRECTORIES"
("OWNER", "DIRECTORY_NAME", "DIRECTORY_PATH")
AS
SELECT u.NAME,
o.NAME,
d.os_path
FROM   sys.user$ u,
sys.obj$ o,
sys.dir$ d
WHERE  u.user# = o.owner#
AND o.obj# = d.obj#

For the following tests I created a DIRECTORY D1 with the os-path /tmp/d1.

Checking sys.dir$ I found obj# 42985 for this directory.

Just to make sure, jet's check the owner of that object. I don't trust oracle, there is too much hardcoded magic inside. But not at this point:

select owner# from sys.obj$ where obj#=42985;

OWNER#
----------
  0

Now I create another user and try to give him the ownership of this particular object - without any grants. The owner of an object should be able to access the object anyhow.

The user berx is equiped with CONNECT and CREATE PROCEDURE:

With user berx let's try to access that DIRECTORY:

declare
f utl_file.file_type;
begin
f := utl_file.fopen('D1', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.fclose(f);
end;
/

You will expect something like

ERROR at line 1:
ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 5

Now let's give user berx the ownership of that directory.

In my case user berx has user$ 41.

So I can (as sys)

update sys.obj$ set owner#=41 where  obj#=42985;

back as user berx - same testcase ....
-- still ORA-29289

Ok. Now we know Oracle does not check the owner of that particular object to check permissions. One of their shortcuts in the code.

So let's give poor user berx the GRANTs the ordinary way:

SQL> grant read, write on directory D1 to berx;

grant read, write on directory D1 to berx
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

Not the effect you expected? Well, maybe one of the side-effects of my unkind interaction with the data dictionary and Oracles shortcuts.

But as I know what I did before, I can revert all the things:

SQL> update sys.obj$ set owner#=0 where  obj#=42985;

1 row updated.

SQL> commit;

Commit complete.

SQL> grant read, write on directory D1 to berx;

Grant succeeded.

And with a last test as user berx:

PL/SQL procedure successfully completed.

Everything is fine again!

Comment:

This post was created out of some older documents and my holey brain. But the main theme should be clear.

2 spfiles - some confusion

At the moment I'm in a big migration project from 9i to 11gR2.
Most of the migrations are done like

• create new DB


• exp/imp


• delete old DB

Even we have templates for all these steps, sometimes something goes wrong.

Today I hit such a something:
As we use Clusters for most of our DBs, cluster_database=TRUE should be set, otherwise it's generating problems. At the Creation of one DB, the 2nd instance did not come up. After some research I found out cluster_database was set to FALSE. No big deal, let's change it:

alter database set cluster_database=TRUE scope=spfile;

But after a srvctl stop instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

srvctl start instance -d DB_UNIQUE_NAME -i INSTANCE_NAME a show parameter cluster_database still gave me FALSE. This confused me!
So I checked $ORACLE_HOME/dbs/initINSTANCE_NAME and there I found spfile=+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891 +ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153 there where 2 spfiles in the pfile-definition!
After a

create pfile='/tmp/pfile1' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891';
create pfile='/tmp/pfile2' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153';

and a diff /tmp/pfile1 /tmp/pfile2 I decided to use only the 1st spfile in the $ORACLE_HOME/dbs/initINSTANCE_NAME and apply all the differences manually.
Now both instances are up and fine - I just have to delete the correct spfile in ASM.

Invalid SQL if hint is missing?

Recently Charles Hooper posted a blog An Invalid, or Do You Just Not Want to Work. There he asked under which circumstances a SQL should be considered invalid? I tried to discuss this question in the comments there and I am still thinking about an other kind of answer. But today I'd like to show a statement, which is invalid without a hint!

here we go:
(everything 11.2.0.1EE 64-bit on Linux)
I was asked to provide a list of all views which have a hint (how ironic) in it. After some test I provided this one:

select /*+ no_merge(vv) */ owner, view_name
from (select v.owner, v.view_name
from dba_views v, dba_objects o
where v.owner = o.owner
AND  v.view_name = o.object_name
AND o.object_type='VIEW'
AND o.status='VALID'
AND v.owner ='PSFT'  ) vv
where    dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

without the no_merge hint I get this error stack:

ERROR at line 9:
ORA-31603: object "AAB_TEST" of type VIEW not found in schema "PSFT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3912
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1

I did some explain plan with and without hints, so I got these 2 outputs:

SQL_ID bwsx4fryyu6u9, child number 0
-------------------------------------
select /*+ no_merge(vv) */ owner, view_name from (select v.owner,
v.view_name    from dba_views v, dba_objects o   where v.owner =
o.owner    AND v.view_name = o.object_name    AND o.object_type='VIEW'
   AND o.status='VALID'    AND v.owner ='PSFT' ) vv where
dbms_metadata.get_ddl('VIEW', vv.view_name, vv.owner) like '%/*+%'

Plan hash value: 3436948868

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    1 |       |     14 |00:01:33.65 |  1088K|       |       |   |
|*  1 |  VIEW          |      |    1 |     1 |     14 |00:01:33.65 |  1088K|       |       |   |
|*  2 |   FILTER         |      |    1 |       |   8802 |00:00:00.28 |  7432 |       |       |   |
|   3 |    NESTED LOOPS         |      |    1 |     1 |   8802 |00:00:00.25 |  7432 |       |       |   |
|   4 |     NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.21 |  2469 |       |       |   |
|*  5 |      HASH JOIN         |      |    1 |     1 |   8802 |00:00:00.15 |  2464 |  1152K|  1152K| 1616K (0)|
|   6 |       VIEW         | DBA_OBJECTS |    1 |    26 |   8802 |00:00:00.07 |  2239 |       |       |   |
|   7 |        UNION-ALL        |      |    1 |       |   8802 |00:00:00.07 |  2239 |       |       |   |
|*  8 |  TABLE ACCESS BY INDEX ROWID   | SUM$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|*  9 |   INDEX UNIQUE SCAN       | I_SUM$_1    |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 10 |  FILTER         |      |    1 |       |   8802 |00:00:00.06 |  2239 |       |       |   |
|  11 |   NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.05 |  2239 |       |       |   |
|  12 |    NESTED LOOPS        |      |    1 |     1 |   8802 |00:00:00.03 |  2236 |       |       |   |
|  13 |     TABLE ACCESS BY INDEX ROWID| USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 14 |      INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 15 |     TABLE ACCESS BY INDEX ROWID| OBJ$     |    1 |     1 |   8802 |00:00:00.03 |  2234 |       |       |   |
|* 16 |      INDEX RANGE SCAN       | I_OBJ5     |    1 |    25 |   8803 |00:00:00.01 |   222 |       |       |   |
|* 17 |    INDEX RANGE SCAN       | I_USER2     | 8802 |     1 |   8802 |00:00:00.02 |     3 |       |       |   |
|* 18 |   TABLE ACCESS BY INDEX ROWID  | IND$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 19 |    INDEX UNIQUE SCAN       | I_IND1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|  20 |   NESTED LOOPS        |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 21 |    INDEX FULL SCAN       | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 22 |    INDEX RANGE SCAN       | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 23 |  FILTER         |      |    1 |       |      0 |00:00:00.01 |     0 |       |       |   |
|  24 |   NESTED LOOPS        |      |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  25 |    TABLE ACCESS BY INDEX ROWID | USER$     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 26 |     INDEX UNIQUE SCAN       | I_USER1     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 27 |    INDEX RANGE SCAN       | I_LINK1     |    0 |     2 |      0 |00:00:00.01 |     0 |       |       |   |
|  28 |       NESTED LOOPS        |      |    1 |  2546 |  25853 |00:00:00.03 |   225 |       |       |   |
|  29 |        TABLE ACCESS BY INDEX ROWID    | USER$     |    1 |     1 |      1 |00:00:00.01 |     2 |       |       |   |
|* 30 |  INDEX UNIQUE SCAN       | I_USER1     |    1 |     1 |      1 |00:00:00.01 |     1 |       |       |   |
|* 31 |        INDEX RANGE SCAN        | I_OBJ5     |    1 |  2546 |  25853 |00:00:00.01 |   223 |       |       |   |
|* 32 |      INDEX RANGE SCAN        | I_USER2     | 8802 |     1 |   8802 |00:00:00.05 |     5 |       |       |   |
|* 33 |     INDEX UNIQUE SCAN        | I_VIEW1     | 8802 |     1 |   8802 |00:00:00.03 |  4963 |       |       |   |
|  34 |    NESTED LOOPS         |      |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 35 |     INDEX FULL SCAN        | I_USER2     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
|* 36 |     INDEX RANGE SCAN        | I_OBJ4     |    0 |     1 |      0 |00:00:00.01 |     0 |       |       |   |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_METADATA"."GET_DDL"('VIEW',"VV"."VIEW_NAME","VV"."OWNER") LIKE '%/*+%')
   2 - filter((("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10
       AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#") AND
       ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
       "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL))))
   5 - access("O"."NAME"="O"."OBJECT_NAME")
   8 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   9 - access("S"."OBJ#"=:B1)
  10 - filter(((("O"."TYPE#"<>1 AND "O"."TYPE#"<>10) OR ("O"."TYPE#"=1 AND =1)) AND (("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND
       "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
       (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR
       ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
  14 - access("U"."NAME"='PSFT')
  15 - filter((DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND BITAND("O"."FLAGS",128)=0))
  16 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter(("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
       DECODE("O"."TYPE#",0,'NEXT OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'FUNCTION',9,
       'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE PARTITION',20,'INDEX
       PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA SOURCE',29,'JAVA CLASS',30,'JAVA
       RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB
       SUBPARTITION',42,NVL(,'MATERIALIZED VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATIO
       N CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE
       GROUP',82,'MINING MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE FOLDER',95,'CUBE BUILD
       PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW'))
  17 - access("O"."OWNER#"="U"."USER#")
  18 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
       "I"."TYPE#"=9))
  19 - access("I"."OBJ#"=:B1)
  21 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  22 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  23 - filter(NULL IS NOT NULL)
  26 - access("U"."NAME"='PSFT')
  27 - access("L"."OWNER#"="U"."USER#")
  30 - access("U"."NAME"='PSFT')
  31 - access("O"."SPARE3"="U"."USER#")
  32 - access("O"."OWNER#"="U"."USER#")
  33 - access("O"."OBJ#"="V"."OBJ#")
  35 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))
  36 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")


100 rows selected.

and

Plan hash value: 739349040

----------------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |  1 |   107 | 66   (2)| 00:00:02 |
|*  1 |  FILTER         |     |    |    |  |    |
|   2 |   NESTED LOOPS        |     |  1 |   107 | 66   (2)| 00:00:02 |
|   3 |    NESTED LOOPS        |     |  1 |   102 | 66   (2)| 00:00:02 |
|*  4 |     HASH JOIN        |     |  1 | 80 | 65   (2)| 00:00:01 |
|   5 |      VIEW        | DBA_OBJECTS | 26 |   624 | 40   (0)| 00:00:01 |
|   6 |       UNION-ALL        |     |    |    |  |    |
|*  7 |        TABLE ACCESS BY INDEX ROWID   | SUM$    |  1 | 26 |  0   (0)| 00:00:01 |
|*  8 |  INDEX UNIQUE SCAN      | I_SUM$_1    |  1 |    |  0   (0)| 00:00:01 |
|*  9 |        FILTER        |     |    |    |  |    |
|  10 |  NESTED LOOPS       |     |  1 |   121 | 40   (0)| 00:00:01 |
|  11 |   NESTED LOOPS       |     |  1 | 99 | 39   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID| USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 13 |     INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS BY INDEX ROWID| OBJ$    |  1 | 82 | 38   (0)| 00:00:01 |
|* 15 |     INDEX RANGE SCAN      | I_OBJ5    | 25 |    | 23   (0)| 00:00:01 |
|* 16 |   INDEX RANGE SCAN      | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 17 |  TABLE ACCESS BY INDEX ROWID  | IND$    |  1 |  8 |  2   (0)| 00:00:01 |
|* 18 |   INDEX UNIQUE SCAN      | I_IND1    |  1 |    |  1   (0)| 00:00:01 |
|  19 |  NESTED LOOPS       |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 20 |   INDEX FULL SCAN      | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 21 |   INDEX RANGE SCAN      | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
|* 22 |        FILTER        |     |    |    |  |    |
|  23 |  NESTED LOOPS       |     |  2 | 54 |  2   (0)| 00:00:01 |
|  24 |   TABLE ACCESS BY INDEX ROWID | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 25 |    INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 26 |   INDEX RANGE SCAN      | I_LINK1    |  2 | 20 |  1   (0)| 00:00:01 |
|  27 |      NESTED LOOPS       |     |   127 |  7112 | 24   (0)| 00:00:01 |
|  28 |       TABLE ACCESS BY INDEX ROWID    | USER$    |  1 | 17 |  1   (0)| 00:00:01 |
|* 29 |        INDEX UNIQUE SCAN      | I_USER1    |  1 |    |  0   (0)| 00:00:01 |
|* 30 |       INDEX RANGE SCAN       | I_OBJ5    |   127 |  4953 | 23   (0)| 00:00:01 |
|* 31 |     INDEX RANGE SCAN       | I_USER2    |  1 | 22 |  1   (0)| 00:00:01 |
|* 32 |    INDEX UNIQUE SCAN       | I_VIEW1    |  1 |  5 |  0   (0)| 00:00:01 |
|  33 |   NESTED LOOPS        |     |  1 | 29 |  2   (0)| 00:00:01 |
|* 34 |    INDEX FULL SCAN       | I_USER2    |  1 | 20 |  1   (0)| 00:00:01 |
|* 35 |    INDEX RANGE SCAN       | I_OBJ4    |  1 |  9 |  1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5947ACE7
   5 - SET$1     / O@SEL$2
   6 - SET$1
   7 - SEL$8     / S@SEL$8
   8 - SEL$8     / S@SEL$8
   9 - SEL$DA86A24D
  12 - SEL$DA86A24D / U@SEL$7
  13 - SEL$DA86A24D / U@SEL$7
  14 - SEL$DA86A24D / O@SEL$9
  15 - SEL$DA86A24D / O@SEL$9
  16 - SEL$DA86A24D / U@SEL$9
  17 - SEL$12     / I@SEL$12
  18 - SEL$12     / I@SEL$12
  19 - SEL$11
  20 - SEL$11     / U2@SEL$11
  21 - SEL$11     / O2@SEL$11
  22 - SEL$13
  24 - SEL$13     / U@SEL$13
  25 - SEL$13     / U@SEL$13
  26 - SEL$13     / L@SEL$13
  28 - SEL$5947ACE7 / U@SEL$3
  29 - SEL$5947ACE7 / U@SEL$3
  30 - SEL$5947ACE7 / O@SEL$4
  31 - SEL$5947ACE7 / U@SEL$4
  32 - SEL$5947ACE7 / V@SEL$3
  33 - SEL$6
  34 - SEL$6     / U2@SEL$6
  35 - SEL$6     / O2@SEL$6

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
       "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
       "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
       "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
       "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
       "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."NAME"="O"."OBJECT_NAME")
   7 - filter(BITAND("S"."XPFLAGS",8388608)=8388608)
   8 - access("S"."OBJ#"=:B1)
   9 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
       "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
       OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
       ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9
       AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
       "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
       BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR
       "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR
       "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
       (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
       "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
       EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND
       "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
       "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  13 - access("U"."NAME"='PSFT')
  14 - filter(DECODE("O"."STATUS",0,'N/A',1,'VALID','INVALID')='VALID' AND
       BITAND("O"."FLAGS",128)=0)
  15 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
       filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
       "O"."LINKNAME" IS NULL AND DECODE("O"."TYPE#",0,'NEXT
       OBJECT',1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE',7,'PROCEDURE',8,'F
       UNCTION',9,'PACKAGE',11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY',19,'TABLE
       PARTITION',20,'INDEX PARTITION',21,'LOB',22,'LIBRARY',23,'DIRECTORY',24,'QUEUE',28,'JAVA
       SOURCE',29,'JAVA CLASS',30,'JAVA RESOURCE',32,'INDEXTYPE',33,'OPERATOR',34,'TABLE
       SUBPARTITION',35,'INDEX SUBPARTITION',40,'LOB PARTITION',41,'LOB SUBPARTITION',42,NVL(
       (SELECT 'REWRITE EQUIVALENCE' FROM SYS."SUM$" "S" WHERE "S"."OBJ#"=:B1 AND
       BITAND("S"."XPFLAGS",8388608)=8388608),'MATERIALIZED
       VIEW'),43,'DIMENSION',44,'CONTEXT',46,'RULE SET',47,'RESOURCE PLAN',48,'CONSUMER
       GROUP',51,'SUBSCRIPTION',52,'LOCATION',55,'XML SCHEMA',56,'JAVA
       DATA',57,'EDITION',59,'RULE',60,'CAPTURE',61,'APPLY',62,'EVALUATION
       CONTEXT',66,'JOB',67,'PROGRAM',68,'JOB CLASS',69,'WINDOW',72,'SCHEDULER
       GROUP',74,'SCHEDULE',79,'CHAIN',81,'FILE GROUP',82,'MINING
       MODEL',87,'ASSEMBLY',90,'CREDENTIAL',92,'CUBE DIMENSION',93,'CUBE',94,'MEASURE
       FOLDER',95,'CUBE BUILD PROCESS',100,'FILE WATCHER',101,'DESTINATION','UNDEFINED')='VIEW')
  16 - access("O"."OWNER#"="U"."USER#")
  17 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
       "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
  18 - access("I"."OBJ#"=:B1)
  20 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  21 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
  22 - filter(NULL IS NOT NULL)
  25 - access("U"."NAME"='PSFT')
  26 - access("L"."OWNER#"="U"."USER#")
  29 - access("U"."NAME"='PSFT')
  30 - access("O"."SPARE3"="U"."USER#")
       filter("DBMS_METADATA"."GET_DDL"('VIEW',"O"."NAME","U"."NAME") LIKE '%/*+%')
  31 - access("O"."OWNER#"="U"."USER#")
  32 - access("O"."OBJ#"="V"."OBJ#")
  34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
       ion_id')))
  35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30]
   2 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   3 - (#keys=0) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."TYPE#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22],
       "U"."SPARE2"[NUMBER,22]
   4 - (#keys=1) "O"."NAME"[VARCHAR2,30], "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
   5 - "O"."OBJECT_NAME"[VARCHAR2,128]
   6 - STRDEF[30], STRDEF[128], STRDEF[19], STRDEF[7]
   8 - "S".ROWID[ROWID,10]
   9 - "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  10 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22], "U"."TYPE#"[NUMBER,22],
       "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  11 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  12 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  13 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  14 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22], "O"."STATUS"[NUMBER,22]
  15 - "SYS_ALIAS_4".ROWID[ROWID,10], "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22],
       "O"."OWNER#"[NUMBER,22], "O"."OBJ#"[NUMBER,22]
  16 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  18 - "I".ROWID[ROWID,10]
  19 - (#keys=0)
  20 - "U2"."USER#"[NUMBER,22]
  22 - "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  23 - (#keys=0) "U"."NAME"[VARCHAR2,30], "L"."NAME"[VARCHAR2,128]
  24 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  25 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  26 - "L"."NAME"[VARCHAR2,128]
  27 - (#keys=0) "U"."NAME"[VARCHAR2,30], "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22],
       "O"."NAME"[VARCHAR2,30], "O"."TYPE#"[NUMBER,22]
  28 - "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,30]
  29 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,30]
  30 - "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."NAME"[VARCHAR2,30],
       "O"."TYPE#"[NUMBER,22]
  31 - "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
  33 - (#keys=0)
  34 - "U2"."USER#"[NUMBER,22]

195 rows selected.

I did not came down what's really wrong in the 2nd case. But it is some of these effects where I'd blame the database more than the SQL for invalidity.

GUI criticism

As a DBA, I'm not a big fan of Graphical User Interfaces.
From my point of view they too often hiding more information than they visualize.
As I am no GUI developer, please excuse me if I name some things not correct.

I'll show an example. Please take this screenshot. I do not own the tool myself but got this via email from a developer.


What can I see?
In the top left corner I see 3 tabs, the active one is Sessions. This shows me a table with one line highlighted (sid 160). Below that table, there is something I would assume as a sub-tab. Especially the borders of that tab makes me think so, as it's 'above' the 'Sessions' tab.
The sub-tab Waits is the active on. In it I see 2 Tables, Current Waits and Total Waits.
At the bottom I can find a line Last refresh. I'm glad I have any timestamp anywhere.

This is all really fine. But I'm still missing something:

• In general, I'd like to know the statement which generates the data I can see here. In the topmost table, I assume it will query v$session, v$process and some others to get the informations. But what's the query exactly? Even small changes can mean something different. Do you see the column CPU in the first table? I can not even imagine it's origin.


• Many tools provide the ability to spool all SQLs they generate into a file. But as they do not associate the SQL to the visual representation, it's still guesswork.


• I can not see which columns I do not see! Is the AUDSID important? Most of the time I'd say no. Until I have to deal with auditing
  


• I can not adapt the query according to my needs. In the Current Waits table - I assume based on v$session_wait I am often interested in the P1, P2 and P3 values (sometimes even raw - it depends). Sometimes also the change of SEQ# is of some interest. This rapidly forces me to go to the command line, or SQL-window if you want.
  

But there are also clever examples of some GUIs. The one I like most (as it helps both blind people and others) is Providing Textual Descriptions of Charts. It's still not the SQL, but at least the content of a graph in re-useable form.

I have only considered of any kind of query, yet. no DML or DDL operations at the moment. Maybe it's worth another post in the future ;-)

create database might fail with 11.2.0.2

I hit a nice bug at a test migration from 10.2.0.5 to 11.2.0.2.
As it's easier to convince Oracle Support of a problem if the testcase is just simple, a colleague reduced the testcase to


cretate a small pfile:

db_name=test
db_block_size=2048
db_create_file_dest=/tmp

and run

create database character set al32utf8;

the result is:

create database character set al32utf8
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/doptim.bsq' near line 416
ORA-00604: error occurred at recursive SQL level 1
ORA-01429: Index-Organized Table: no data segment to store overflow row-pieces
Process ID: 16723
Session ID: 47 Serial number: 1

For Orale Support this really was enough to file Bug:10410249 towards development.

If you have a Database with blocksize of 2k and a multibyte characterset, beware!

So far enough for Oracle Support. Just to provide some more insights:
the upgrade failed at
@catupgrd.sql
with

SQL> begin
 2    dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
 3    dbms_stats.delete_table_stats('SYS', 'USER$MIG');
 4    dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG',  estimate_percent => 100,
 5                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 6    dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
 7                                   method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
 8  end;
 9  /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2

I did not know why dbms_stats seems to be broken. So I tried to run catupgrd.sql again - same problem.

Next step: recompile dbms_stats manually.
at running

@prvtstas.plb

it showed me there was an error at line 2175.
If you are curious what's going on there:

2175 CURSOR GET_COL_GROUP_USAGE(OWNER VARCHAR2, TABNAME VARCHAR2) IS
2176 SELECT CU.OBJ# OBJN, CU.COLS,
2177    (CASE WHEN BITAND(CU.FLAGS, 1) = 1 THEN 'FILTER ' ELSE '' END) ||
2178    (CASE WHEN BITAND(CU.FLAGS, 2) = 2 THEN 'JOIN ' ELSE '' END) ||
2179    (CASE WHEN BITAND(CU.FLAGS, 4) = 4 THEN 'GROUP_BY ' ELSE '' END) USAGE,
2180    CU.FLAGS USAGEFLG
2181 FROM SYS.COL_GROUP_USAGE$ CU
2182 WHERE CU.OBJ# = (SELECT O.OBJ# FROM SYS.OBJ$ O, SYS.USER$ U
2183                     WHERE O.OWNER# = U.USER#
2184                       AND U.NAME = OWNER
2185                       AND O.NAMESPACE = 1
2186                       AND O.REMOTEOWNER IS NULL
2187                       AND O.LINKNAME IS NULL
2188                       AND O.SUBNAME IS NULL
2189                       AND O.TYPE# = 2
2190                       AND O.NAME = TABNAME)
2191   ORDER BY ...

I checked COL_GROUP_USAGE$ - but it did not exist!

Some grep in $ORACLE_HOME/rdbms/admin brought me to c1102000.sql
There I can read:

-- #(9577300) Column group usage
create table col_group_usage$
(
 obj#              number,                                 /* object number */
 /*
  * We store intcol# separated by comma in the following column.
  * We allow upto 32 (CKYMAX) columns in the group. intcol# can be
  * upto 1000 (or can be 64K in future or with some xml virtual columns?).
  * Assume 5 digits for intcol# and one byte for comma.
  * So max length would be 32 * (5+1) = 192
  */
 cols              varchar2(192 char),              /* columns in the group */
 timestamp         date,     /* timestamp of last time this row was changed */
 flags             number,                                 /* various flags */
 constraint        pk_col_group_usage$
 primary key       (obj#, cols))
 organization index
 storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/

As I tried to create that table manually, I recieved my ORA-01429.

We discussed some workarounds internally.
I created the IOT (without approval from Oracle Support) with cols varchar2(192 byte). Guess what, it worked! Also @catupgrd.sql was fine afterwards.
As I'm sure in this particular DB there are only column names with plain ASCII, this will not be an issue in this particular DB. But some other suggestions like creating a heap table or IOT without the cols as part of the index definition. This might result in performance problems of dbms_stats, but does not affect any logic (as my WA does).

UPDATE:
Patch:10410249 is provided for this issue now. It's labeled generic, as it contains only some sql files for $ORACLE_HOME/rdbms/admin and the proper patch (and rollback) scripts.