Monthly Archives: November 2005

ansi literals

5 Replies

the first time I saw ansi literals was in June 2004 in a post on otn forum by alex goodmann. I just cannot stop using them, it is so handy!

Whenever I specify a date (or a timestamp or even a time), with Ansi Date, I do not rely on the NLS parameters, nor I do specify a format.

I simply use

date '2000-01-01'

I often use Jan 1st, 2000 as an anonymous date.
For avg(txndate), I can use

date '2000-01-01' +
avg(txndate-date '2000-01-01')

Other nice literals are timestamps 

timestamp 
'2000-01-01 00:00:00.000000000 Etc/GMT+0'
timestamp '2000-01-01 00:00:00'

less usefull, because unsupported as oracle datatype

time '00:00:00.000000000 +00:00'
time '00:00:00'

also intervals
interval '1' day

one more I want to mention
q'[let's quote this]'

all this I found by reading the doc, more than once !

post from palindnilap

Leave a reply

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

Leave a reply

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

Leave a reply

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

Leave a reply

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

    2 Replies

    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

    8 Replies

    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

    4 Replies

    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