All posts by Laurent Schneider

Oracle Certified Master

post from palindnilap

I just answered a question about the data dictionary on forums.oracle.com. It is all about the dictionary views.
How many tables should I know in the dictionary ?

SQL> select count(*) from dict;
1857

Well, that’s too much. But I can remove the GV$ view, which contain the instance for RAC, and the DBA_ ALL_ and USER_ have (almost) the same structure.

SQL> select count(*)
from dict
where table_name not like ‘GV$%’
and table_name not like ‘ALL%’
and table_name not like ‘DBA%’ ;
712

Anyway, who knows the 712 views by heart? Hopefully, there is one called DICTIONARY, which helps !

Coming back to the post, palindnilap wants to see which columns of a view are mapped to which column of a table. A quick look at ALL_VIEWS could do the trick, but than you will need to “understand” the query to see which view.column maps to which table.column. What’s more, ALL_VIEWS.TEXT is a long. Arghh!

if you have a view that contains all columns from a table, you could use ALL_DEPENDENCIES to see on which table it is based.

On my first answer, I pointed out that ALL_UPDATABLE_COLUMNS may reveal that a view column belongs to a table if the column is updatable.

My last try was to use the ACCESS_PREDICATES to get the column physically accessed.

SQL> select * from v02 where employee=123456;

no rows selected

SQL> select
max(substr(ACCESS_PREDICATES,1,instr(ACCESS_PREDICATES,’=’)-1))
from v$sql_plan
where ACCESS_PREDICATES like ‘%=123456′;
“EMPNO”

here we see EMPLOYEE is actually named “EMPNO” in the based table. It could be done with explain plan and PLAN_TABLE too.

oracle voyage worm

I wrote a mini script to protect my customer from being attacked by an “oracle voyage worm” variant :

revoke CREATE DATABASE LINK from CONNECT;
revoke ALL on SYS.UTL_FILE from PUBLIC;
revoke ALL on SYS.UTL_HTTP from PUBLIC;
revoke ALL on SYS.UTL_SMTP from PUBLIC;
revoke ALL on SYS.UTL_TCP from PUBLIC;
grant EXECUTE on SYS.UTL_FILE to XDB;
grant EXECUTE on SYS.UTL_HTTP to MDSYS;
grant EXECUTE on SYS.UTL_HTTP to ORDPLUGINS;
@?/rdbms/admin/utlrp


Than, in OEM 10g, check for policy violations.

I added a few grants to special oracle internal users, to avoid invalid objects, which is also a policy violation in OEM… OEM will report a violation if those accounts are not locked and expired

10.1.0.4 management agent for Unix

I just found in my RSS feed that a new agent has been released. 10.1.0.4 for AIX5L. Well, if you go to the OEM download page, you will see only 10.1.0.2 version for AIX, HPUX, Solaris Sparc. But by clicking on the link, for example
Grid Control (10.1.0.2)
for AIX5L Based Systems, you will find a link OEM 10g Grid Control Management Agent Release 1, from there I finally discovered that 10.1.0.4 agent download.

Well, I already had 10.1.0.4, but it was a 10.1.0.2 installation patched with 10.1.0.4. So I prefer installing directly 10.1.0.4 agent.

Not trivial to found, but hopefully, I subscribed to RSS feeds on Newest Downloads

10.2.0.1 hidden parameters

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 parameter file.
Do not hurl that loud, I hear you from here!

Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without.

However, I am going to communicate those parameters, and the metalink notes referencing them.
You will need them to have a cleaner 10.2.0.1 installation

  • _kgl_large_heap_warning_threshold=33554432
    This parameter prevent Heap size 2800K exceeds notification threshold errors in the alert log and user trace dumps.
    Note: 330239.1 Bugs: 4286095, 4390265

  • __dg_broker_service_names=”
    In case you do not use dataguard but you do use local_listener parameter.
    this parameter prevents pmon from registering a <DB_NAME>_XPT.<DOMAIN_NAME> service in the listener.
    Thread: 611575.993 Bug: 4632635

    Probably all this will be fixed in 10.2.0.2

  • ldap day 2

    what can I do with LDAP?
    what is the difference between LDAP and Oracle Internet Directory?

    Well, there is quite a lot of interresting documents, pictures and faq on otn :

  • OTN Directory homepage
  • Directory Admin guide
  • Identity Management ReferenceLDAP is a directory server, the info are stored in an Oracle 10.1.0.4.2 database.

    When you download application server (about 2Gb), you get a fully functionnal 10.1.0.4.2 database preconfigured and an ldap server running.

    To start the admin tool, just type oidadmin in the command line.

    The password to use is the same as ias_admin and the username is orcladmin

  • Details regarding the 10g DBA OCM requirements will be available in December

    Well, when 10g certification came out, it said : “will be available in late 2004″. In March 2005, I contacted Oracle University in Germany. They said : keep watching oracle.com/education. Later, it said : “will be available in mid 2005″.
    In Open World, I met the certification principal, who meant it will start in December and upgrade from 9i ocm will start in January and will be available in all oracle universities which give RAC course. Fine.
    When I came back I saw: “will be available in October”. Today I saw: “will be available in December”.

    Such a move like OCM is very intense, I want to plan at least 2-3 weeks full time to prepare for OCM. I need to go to my boss and say : “this year I will go for 10g ocm”. But what a pity if the certification is always delayed… planned for 2004, no info why it did not take place in 2005, I also fair that there is no guarantee for 2006 in switzerland, as they will surely start in the United States – but do not ask why…

    Keep cool, be patient ;-)

    hierarchy

    what is a hierarchy?
    I enjoy reading the wikipedia definition :

    http://en.wikipedia.org/wiki/Hierarchy

    In the doc the hierarchy is as a parent-child connection, CONNECT BY PRIOR defines the relationship.

    However, it is possible to have under certain circumstances to connect to a child, regardsless of the parent.

    This is no longer a practical relation.

    Sterile variant :

    
    SQL> select * from dept connect by 1=2;
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    

    Fertile variant :

    
    SQL> select * from dept connect by 1=1
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
    ...
    

    the connect by does defines a true or a false connection. when true, everyone is your parent and everyone is your child. If false, you are the parent, but you have no child.

    connect by level

    restore to a new host : nid++

    Great challenge today: restore to a new host from a closed noarchivelog backup on tape library.

    In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because even the MAXDATAFILES and MAXINSTANCES could be changed. Change controlfile is bad. Once a collegue forget one file, once he noticed it, only months later, he wondered how to recover it. Create a controlfile is way to much sensible thing too do. It is almost as bad as changing the dictionary!

    Well. How to do than?

    Ok, I have a database called LSC67 on prod, I want to recover it to LSC66 in devl. I have NO ACCESS to production, but I have access to tapes (one may wonder if this is a good security practice…)

    Let’s start.

    First, if LSC66 already exists, shutdown abort. Remove datafiles. Remove controlfiles. Remove redo logs.

    Now I restore the data/control/redo files from prod:/dbms/oracle/LSC67 to devl in /dbms/oracle/LSC66.

    First I rename the files, some are called systemLSC67.dbf. I do not want that…

    find do the trick

    find /dbms/oracle/LSC66 -name “*LSC67*” |
    nawk ‘{printf “mv “$1″ “; gsub(src,target);print}’ src=LSC67 target=LSC66 |
    sh

    I must just change the DB_NAME in my parameter file (which already exists at my site), to reflect the prod controlfile dbname

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount

    now I generate some statements for dynamically renaming the files

    set ver off emb on pages 0 newp 0 lin 9999 trims on head off feedb off termout off
    spool /tmp/rename_LSC67_to_LSC66.sql
    select ‘alter database rename file ”’||name||”’ to ”’||replace(name,’LSC67′,’LSC66′)||”';’
    from (
    select name from v$datafile
    union all
    select member from v$logfile
    )
    where name like ‘%LSC67%';
    spool off
    spool /tmp/drop_temp_LSC67_to_LSC66.sql
    select ‘alter database tempfile ”’||tf.name||”’ drop;’
    from v$tempfile tf
    where tf.name like ‘%LSC67%';
    spool off
    spool /tmp/create_temp_LSC67_to_LSC66.sql
    select ‘alter tablespace “‘||ts.name||'” add tempfile ”’||
    replace(tf.name,’LSC67′,’LSC66′)||
    ”’ size 128M reuse autoextend on next 128M maxsize 2048M;’
    from v$tablespace ts , v$tempfile tf
    where tf.name like ‘%LSC67%’ and tf.ts#=ts.ts#;
    spool off

    ok, now I am in mount, I do a rename datafile and drop tempfile. after I open database, and add tempfile. I am not taking care of the original size and autoextend clause of the original tempfiles, just 128M next 128M max 2G.

    set echo on termout on feedb 6
    @/tmp/rename_LSC67_to_LSC66.sql
    @/tmp/drop_temp_LSC67_to_LSC66.sql
    alter database open;
    @/tmp/create_temp_LSC67_to_LSC66.sql

    now I nid

    shutdown immediate
    startup quiet mount restrict

    nid dbname=LSC66 target=/

    and I change the db name and open resetlogs

    startup quiet force nomount
    alter system set db_name=’LSC66′ scope=spfile;
    startup quiet force mount
    alter database open resetlogs;

    metalink

    I just remembered the woman who asked Lawrence Ellison why you need at least three days to get a qualified support representative when you open an iTar.

    Since a few days, I have been wondering why a non-dba user was not able to do sqlplus in 10gR2. Well, I saw the bug 4516865 on metalink for 9.2.0.7 and 10.2.0.1, but did not agree that it is a “desirable improvement”, imho it is a major lost of service.

    Just a few minutes (seconds?) after I finished writing my iTar, severity 2, I received a call from Oracle Support.

    Apparently a group of customers asked to prevent any non-dba user from using sqlplus on a database server (just on the client). This kind of answer I do not like. But we had a long talk, he finally showed me a workaround for 10.2.0.1 : doing

    chmod -R 755 $ORACLE_HOME

    before root.sh.

    Well, this should have made me hurl. But ok, I can do this.

    He also explained me, that I should escalate this bug if I want development starting working on it. Also there is another bug, 4533592

    My satisfaction with this tar is good (4/5). I have been contacted immediatly, informed about the bugs, and informed about the procedure to escalate this case when I will require too.

    My overall satisfaction with Metalink is about 3/5. But sometimes, it is really 0/5. The worst I have was :

    lsc: how do you remove headers in the middle of your result in sqlplus ? I have a bug with set emb on and set pages 9999.
    metalink: This is a bug in sqlplus. Workaround is to use Oracle Reports.

    I could just have kill her! The “good” solution is “set pages 0″, ref: set pages 50000

    One also like this :

    lsc: When I try to do an executable in scheduler I got an ora-27371 (AIX)
    metalink: switch to a new platfom
    lsc: @*#!!
    metalink: It will be fixed in 10.1.0.4
    lsc: no it is not fixed in 10.1.0.4
    metalink: it is an internal bug
    …months later…
    lsc: still not fix in 10.2.0.1
    metalink: development is working on it

    well, I have always known that AIX is not a strategic platform and many shell scripts function utterly wrong… still very frustrating one!

    Larry just told that woman : just drop me an email. Should I write Larry each time I do receive an idiot answer?

    FAILED_LOGIN_ATTEMPTS part 2

    Ref: part 1
    I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359

    Here is my test case (take care, it will create a new db!) :

    SQL&gt; startup force quiet nomount;
    ORACLE instance started.
    SQL&gt; create database controlfile reuse extent management
    local default tablespace users default temporary tablespace temp
    undo tablespace undotbs1;
    
    Database created.
    SQL&gt; @?/rdbms/admin/catalog
    SQL&gt; @?/rdbms/admin/catproc
    SQL&gt; col username for a10
    SQL&gt; col PROFILE for a7
    SQL&gt; col LIMIT for a12
    SQL&gt; select username, profile, limit from dba_users join 
    dba_profiles using (profile) 
    where resource_name='FAILED_LOGIN_ATTEMPTS';
    USERNAME   PROFILE LIMIT 
    ---------- ------- ------------ 
    SYSTEM     DEFAULT 10
    SYS        DEFAULT 10
    TSMSYS     DEFAULT 10
    DIP        DEFAULT 10
    DBSNMP     DEFAULT 10
    OUTLN      DEFAULT 10

    pivot table part 3

    one more try with model, available 10gR1

    select * from 
      (select extract(year from hiredate) h, count(*) c 
        from emp 
        group by extract(year from hiredate))
    model
      dimension by (h)
      measures (c)
      rules(
        c[FOR h FROM 1980 to 1990 INCREMENT 1] = 
        case when c[CV()] is present then c[CV()] else 0 end)
    order by h;
    
             H          C
    ---------- ----------
          1980          1
          1981         10
          1982          1
          1983          0
          1984          0
          1985          0
          1986          0
          1987          2
          1988          0
          1989          0
          1990          0
    

    pivot table part 1
    pivot table part 2

    Last day

    Very intense last day. In the morning I waked up late, and just went to OTN lounge. Well, I meet Puschitz, which speaks german too because he is austrian, and also Wim. I then ran to Moscone South for my XQuery session. Very interresting indeed…

    In the afternoon, pure DBA staff :
    1) shared memory
    very interresting, difficult staff
    2) dss
    a lot of nice improvement, but highly soporific presentation
    3) storage
    great great great presentation from Amit Ganesh, Storage Director at oracle

    Than I had a talk with Joel and Arup and a glass of wine in the garden. Than I came in the hotel, I have been kindly invited for a glass of red wine by Barbara and Lisa, two lawyers residing also in the argent hotels, thanks if you read me!

    Well, I had to pack, reserve my shuttle, organise wake-up service for 05:00am

    tom session + blogger dinner

    What a queue for Tom Kyte session! 1000 persons have attended, and apparently a lot more had to wait outside. Very interresting. I am especially seduced by the Online Transportable Tablespace, it was SO BAD to make the tablespaces readonly before transport. I got my signed copy of his book :-)
    In the afternoon, I was too much dead to assist some session, so I visited the exhibitors and will come home with plenty of gifts for Dora and Loïc – what a pain to type the ï on an american keyboard, but alt-139 does the trick.
    Than OTN/lounge, with a dozen of fans we had the opportunity to ask questions to tom.
    On the evening, we had the blogger dinner. I particullary appreciated! Even if I have a terrible french accent, I have been able to chat with my neighbours. The food was good, apart from the sorbet, but the way I pronounced “sorbet” was apparently very amusing!

    rac again

    I remembered the time in 9iR2 when load/balancing was just dicing between the instance. In 10gR2, I noticed that load balancing is much better. I did a test, start 28 sessions from an external client, 12 went to node 1, 12 to node 2. Very impressive.

    In 10gR2 RAC, the listener gets statistics collected by MMON, and balances load much more efficiently…

    The Failover is also working correctly. I just powered off Doug PC (we work in team in this lab), and after second try it worked. The TNSNAMES must be configured correctly, with FAILOVER and virtual ip.

    I also tried to install RAC/Linux/vmware on my notebook, but I stupidely remove the linux grub boot partition and I cannot boot anymore… I need a doctor soon :-)

    timetable for tomorrow

    I did my timetable in sqlplus. Quite surprised I have to travel over 15 hours to go there!!! what a trip! I have never made such a long flight in one day!

    set head off feedb off
    col z for a14
    col y for a17
    col x for a17 fold_a
    alter session set nls_timestamp_tz_format=’DD. HH24:MI TZH:TZM';

    select
        ” z,
     ‘zurich-sf’ y, ‘sf-zurich’ x,
        ‘flight’ z,
     substr(b-a+d-c,12,5) y,substr(f-e+h-g,12,5) x,
        ‘travel’ z,
     substr(d-a,12,5) y,substr(h-f,12,5) x,
        ” z, ” y, ” x,
        ” z, ‘localtime’ y, ‘zurichtime’ x,
        ‘zurich dep. ‘ z,
     a y, a at time zone ‘Europe/Zurich’ x,
        ‘atlanta arr.’ z,
     b y, b at time zone ‘Europe/Zurich’ x,
        ‘atlanta dep.’ z,
     c y, c at time zone ‘Europe/Zurich’ x,
        ‘SF arr.     ‘ z,
     d y, d at time zone ‘Europe/Zurich’ x,
        ‘SF dep.     ‘ z,
     e y, e at time zone ‘Europe/Zurich’ x,
        ‘atlanta arr.’ z,
     f y, f at time zone ‘Europe/Zurich’ x,
        ‘atlanta dep.’ z,
     g y, g at time zone ‘Europe/Zurich’ x,
        ‘zurich arr. ‘ z,
     h y, h at time zone ‘Europe/Zurich’ x
    from
    ( select
        timestamp ‘2005-02-16 10:30:00 Europe/Zurich’ a,
        timestamp ‘2005-02-16 14:55:00 US/East-Indiana’ b,
        timestamp ‘2005-02-16 16:40:00 US/East-Indiana’ c,
        timestamp ‘2005-02-16 18:29:00 US/Pacific-New’ d,
        timestamp ‘2005-02-23 08:21:00 US/Pacific-New’ e,
        timestamp ‘2005-02-23 15:44:00 US/East-Indiana’ f,
        timestamp ‘2005-02-23 17:25:00 US/East-Indiana’ g,
        timestamp ‘2005-02-24 08:30:00 Europe/Zurich’ h
      from dual)
    /

                   zurich-sf         sf-zurich
    flight         15:14             13:28
    travel         16:59             15:09

                   localtime         zurichtime
    zurich dep.    16. 10:30 +01:00  16. 10:30 +01:00
    atlanta arr.   16. 14:55 -05:00  16. 20:55 +01:00
    atlanta dep.   16. 16:40 -05:00  16. 22:40 +01:00
    SF arr.        16. 18:29 -08:00  17. 03:29 +01:00
    SF dep.        23. 08:21 -08:00  23. 17:21 +01:00
    atlanta arr.   23. 15:44 -05:00  23. 21:44 +01:00
    atlanta dep.   23. 17:25 -05:00  23. 23:25 +01:00
    zurich arr.    24. 08:30 +01:00  24. 08:30 +01:00

    on my return trip on leave sf at 8:21am and arrive at 8:30am in zurich !

    10g OCP

    I just passed the OCP 10g new features exam this morning. The prometrics server was down at the beginning, after half an hour, I tried to logon but it was complaining the display support only 0 colors (well, how does a display with 0 color looks like?)

    After the support team there changed the resolution from 32bits to 256 colors, it worked.

    I did answer correctly to 51 questions

    undocumented parameter

    Just in case you read my success story on Don Burleson webpage about undocumented parameters.

    out of metalink thread 460157.996 :

    “I set appropriate values for pga_aggregate_target and _pga_max_size…

    alter system set pga_aggregate_target=6G;
    alter system set “_pga_max_size”=2000000000;

    …and I gave the query some hints “NOREWRITE FULL USE_HASH ORDERED”. As a result, it boosted my query performance from 12 hours to 1.5 hour.”

    a few lines below I mentioned :
    this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons

    I think last sentence is quite interresting, too.

    Well, I must say that I finally opted for a more maintenable solution :
    no more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 cpus server.

    As discussed in the iTar, a supported way to increased the maximum pga memory per single sql query is to increase the degree of parallelism.

    As a rule of dumb, if you can avoid hidden parameters, avoid them!

    see you soon @ SF

    agenda

    What time is it? I just hope it is soon friday :-)
    Friday, I am flying to San Francisco for Oracle OpenWorld
    I received this mail for the appreciation dinner : Please let me know if you are available to attend by Monday, September 12. well, I said “fine, see you on monday”. But of course it is on sunday, today is just the deadline, not the dinner.
    Also on Mark Rittman Blog, I wrote the otn night is on tuesday, as written on oracle.com, Tuesday, December 7, 6:00 p.m. – 10:00 p.m. Marriott Hotel, Yerba Buena Level. Of course, it was on December 7 last year, and the link is somewhat misleading… thanks for the reminder Mark, and I will attend to your dinner on tuesday.
    Well, I almost had my appreciation dinner on monday, my otn night on tuesday and my blogger dinner on any other day… I definitely need to write down my appointements before going there !

    stragg in 10gR2

    well, you all know string aggregration
    http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

    here is a suggestion with xquery in 10gR2

    select deptno,
      replace( replace( replace(
        XMLQUERY(‘for $cc in ora:view(“emp”) let $ename:=$cc/ROW/ENAME/text()     where $cc/ROW/DEPTNO/number()=$deptno/d/number() return <e>{$ename}</e>’
        passing by value xmltype(‘<d>’||deptno||'</d>’) as “deptno”
        returning content
      ),'</e><e>’, ‘,’),'<e>’),'</e>’) enames
    from dept
    /

    DEPTNO ENAMES
    —— ——————————————
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        40

    migrate database with imp exp

    I prefer to use exp/imp to migrate databases.

    I first create a fresh new database with a new spfile, a new system tablespace, a new undo, locally managed tablespace, automatic segment space management.

    I do not do a full exp. I prefer a schema export. It only exports the schema that I want, not WMSYS or PERFSTAT… I do not want to have old stuff in my system tablespace neither.

    What is missing by schema import ? profiles, roles, tablespaces, users, public synonym, public database link, privileges

    1) generate create statement profiles, roles, tablespaces, users, public synonym, public database link, privileges

    for example with toad or with dbms_metadata. I am using sql + spool. Also possible is PLSQL.

    2) export database with OWNER=user1,user2,… so all your users but not SYS, SYSTEM, PERFSTAT, DBSNMP, WMSYS, TSMSYS. Only your own users, not the one created by oracle

    3) create a new db

    4) create profiles, roles, tablespaces, users on the new db

    5) grant dba to public !!! yes. a bit creasy, but it is convenient to do the import without warning/errors.

    6) import

    7) create the public synonym, public database link, privileges

    8) revoke dba from public (!)

    9) recompile the db

    Well, I have written all that in a script, so migrating a db is no longer a problem to me :-) I can do 7.3 –> 10.2 migration. And I am sure my db is clean. I have undo and temporary tablespace. I can have Java or ASM. I have only 10.2 system objects in my 10.2 database. Since I am using exp/imp, it is no problem to change os/server/domain/bitwordsize.