triBLOG

Oracle Solution Partner Community

OraBI — Tue, 30 Jun 2009 07:11:00 GMT

In der letzten Woche durfte ich im Rahmen der Oracle Solution Partner Community als einer der Vertreter für Trivadis in Kiel dabei sein. Ute Braun hatte alle Beteiligten zu diesem Treffen eingeladen, um über den Stand der gemeinsamen Entwicklungen zu berichten und die nun anstehenden Schritte mit allen abzustimmen. Highlight war dann am Freitag ein gemeinsamer Tag auf der Kieler Förde beim Segeln auf der Maxi Steinlager 2 einer 84 Fuß Ketsch mit der 1989/1990 Peter Blake das Volvo Ocean Race gewonnen...(read more)

Microsoft Support Lifecycle - SQL Server 2005 SP2

Daniel Mohr — Mon, 29 Jun 2009 09:22:00 GMT

wie schnell doch die Zeit vergeht

Auch der SQL Server 2005 SP ist bereits davon betroffen:

*NEW! Microsoft SQL Server 2005 Service Pack 2 Support Ends January 12, 2010
Support for SQL Server 2005 Service Pack 2 will end on January 12, 2010. Microsoft recommends that customers upgrade to Service Pack 3.

Die Gesamtliste von Microsoft aktualisiert unter:

Microsoft Support Lifecycle Web Site:
http://support.microsoft.com/lifecycle

How To: Create Age Pyramid Charts in Reporting Services 2008

admin — Mon, 29 Jun 2009 08:40:00 GMT

I had to create charts for a HR department that show the distribution of age (or years of service) and gender in organization units. The customer wanted that typical Christmas tree look, which shows a symmetric bar chart in two directions, like shown in the example below.

There are two challenges here:

make the tree look symmetric, the left and right bars should be in line.
make the labels and axis show positive values, even on the left side of the graph.

How can you achieve that in Reporting Services ?

We use the Dundas chart control with the Bar Chart layout. A stacked bar chart did not work too well for me, it did not put the labels on the tip of the bars.

To have the values point in to directions multiply one set of data with -1. you can do that in your query or by editing the value expression.

The value expression for the data column might look like this:

=IIF(Fields!Gender.Value = "Male" , Sum(Fields!Head_Count.Value),Sum(Fields!Head_Count.Value)*-1)

Since we have gender on one axis, it will show the values in two different colours.

The default settings of the bar chart will draw the different bars with a little offset (side by side), so it would not look symmetrically. I found this parameter that sorts this one out:

the Custom Attribute DrawSidebySide of the Chart Series should be set to False. Now the two bars for each gender in one time period are in line nicely.

What's left to do?

We need to take care of the negative label values for the gender pointing to the left. You guessed how I did it:

change the series label Format property and the Axis LabelsFormat to your wish:

0;0 did the job for me.

That's it! Maybe you have a better way to do it ? Let us know! Put a comment!

Too brief ? ask questions!

Technorati-Tags: Reporting Services,HowTo,Charts,Age pyramid,RS2008

Steven Feuerstein: Best of PL / SQL

Severin Kollros — Thu, 25 Jun 2009 12:16:00 GMT

27. & 28. Oktober 2009 in Frankfurt
29. & 30. Oktober 2009 in Zürich

Dieses Seminar gibt Ihnen Aufschluss über die wichtigsten PL/SQL-Funktionen mit Fokus auf die wesentlichen Fortschritte in den Oracle-Datenbanken 10g und 11g. Steven Feuerstein führt Sie über die Basics hinaus zu diesen Features und präsentiert Ihnen, wie diese ganz im Sinne von Best Practice anzuwenden sind. Ziel ist es, dass Sie in Zukunft stark optimierte und erweiterte Anwendungen mit einfacher Maintenance schreiben können.

Mehr Informationen finden Sie unter: www.trivadis.com/Highlights

Dino Esposito: .NET Software Architektur

Severin Kollros — Thu, 25 Jun 2009 12:08:00 GMT

14. – 16. September 2009 in Zürich

Inhalt des Kurses .NET Software Architektur sind Designpatterns und –prinzipien für die Erstellung mehrschichtiger .NET-Applikationen. Der Kurs enthält nicht nur die Prinzipien, sondern streift auch eine Anzahl praktischer Technologien wie LINQ-to-SQL, Entity Framework, NHibernate, ASP.NET MVC, Silverlight und WCF. Alle Beispiele basieren auf der Northwind Applikation, einer Referenzimplementierung einer mehrschichtigen .NET-Applikation, die im Buch „Microsoft .NET: Architecting Solutions fort he Enterprise“, MS Press, 2008 von Dino Esposito, beschrieben wird.

Mehr Informationen finden Sie unter: www.trivadis.com/Highlights

Herbert Albert: Troubleshooting & Performance Tuning Microsoft SQL Server 2005 und 2008

Severin Kollros — Thu, 25 Jun 2009 11:47:00 GMT

19. - 22. Oktober in Zürich
16. - 19. November in München

Dieser Kurs kann die Anzahl Ihrer Support-Anfragen deutlich senken!

Dieses Seminar "Troubleshooting & Performance Tuning Microsoft SQL Server 2005 und 2008" ermöglicht, auftretenden Problemen beim SQL Server rasch und effizient zu begegnen. Sie erfahren desweiteren, wo Sie den Hebel ansetzen können, um die Performance Ihrer Datenbank entscheidend zu erhöhen.

Mehr Informationen finden Sie unter www.trivadis.com/Highlights

Itzik Ben-Gan: Advanced Transact - SQL Query Tuning

Severin Kollros — Thu, 25 Jun 2009 07:58:00 GMT

30.09. – 02.10.09 in Düsseldorf

Der Kurs Advanced Transact-SQL Query Tuning mit Itzik Ben-Gan (www.trivadis.com/m-tun) legt den Schwerpunkt auf das Schreiben und das Tunen von Queries und das Programmieren für den SQL Server 2005 und 2008. In diesem Kurs lernen Sie die Möglichkeiten kennen, die Ihnen T-SQL Ihnen bietet. Darunter: Logical Query Processing; Subqueries, Ranking Functions, Joins and Set Operations; Aggregating and Pivoting Data (including CLR UDAs); TOP and APPLY; Data Modification; Data Type Related Problems (including XML and CLR UDTs); Graphs, Trees and Hierarchies.

Mehr Informationen finden Sie unter www.trivadis.com/m-tun

Weitere Trivadis Highlights finden Sie unter: www.trivadis.com/Highlights

4. Java Lounge, 23.06.09 – Grails & Flex / Spring 3.0 What’s new

Lukas Wirth — Thu, 25 Jun 2009 07:39:00 GMT

Für einige Stunden bot der Zoo Zürich

nebst den exotischen Tieren und Pflan-
zen auch ein Platz für die vierte Trivadis
Java Lounge. Rund 60 ausgesuchte
Kunden und Partner fanden den Weg
zum Zoo für den ungezwungenen
Know-How Austausch. Themen der
4. Java Lounge waren „Grails & Flex“
sowie „Spring 3.0“.

Grails & Flex – ein schlagkräftiges Duo!
Mischa Kölliker (Senior Consultant der Trivadis) und
Ueli Kistler (Junior Consultant der Trivadis) vermittel-
ten in ihrem Referat, wie sich die Kombination
von Grails als „Rapid Application Development“
Plattform im Zusammenspiel mit Adobe Flex
als „Rich Internet Application“ in einem
Kundenprojekt hervorragend bewährt hat.
Dabei wurden coole Features gezeigt und wie REST-
Webservices in einem Aufwisch mit der Flex-Integra-
tion erstellt werden können. Einfach spannend, wie
man in dieser Kombination auf sich rasch ändernde und vielfältige Anforderungen agil umsetzen kann. Präsentation: Grail & Flex

Spring 3.0 - What’s new

Wir erwarten den Release Spring 3.0 GA in
der zweiten Hälfte 2009. Es ist also der
Richtige Zeitpunkt dem erwarteten Frame-
work „unter die Haube zu schauen“. Von
Patrick Baumgartner (Consultant der
Trivadis), durften wir aus erster Hand einen
aktuellen Überblick und Ausblick über den
bevorstehenden neuen Release erfahren.
Zentrale Themen waren die neuen
Features „Rest Support“, „Unified
Expression Language“, erweiterte
„Bean Validation“ sowie die
„Annotation basierende Konfiguration“.
Zusätzlich wurde auf die neue Java 5
Unterstützung, die neue Projektstruktur
und die neuen OSGi Bundles eingegangen.
Wie gewohnt untermalt mit entsprechender
live Demo. Präsentation: Spring – What’s new

Wie üblich konnten die Teilnehmer während und nach der Lava Lounge ihr Fachwissen und Projekterfahrungen
in lockerer Atmosphäre bei einem Apéro besprechen und ausdiskutieren. Auch diesmal wurde unter den
Teilnehmern ein Preis verlost, diesmal eine Panasonic HD Digitalkamera, welche der glückliche Gewinner
sogleich in Empfang nehmen durfte.

Merke: Java Affen ist es schnuppe, mit welcher Kamera sie fotografiert werden, die sehen immer gut aus :-)

Herzlichen Dank an Susanne Harzenmoser für die Organisation und den Referenten für die Präsentationen.
Die nächste Java Lounge ist auf Ende Oktober oder Anfangs November geplant.
Trivadis, 25.06.09 (luk)

SQL Server 2008 R2 - News und Infos

Trivadis IMS Microsoft Team Blog — Wed, 24 Jun 2009 14:47:00 GMT

Neueste Informationen zu SQL Server 2008 R2 unter: http://blogs.technet.com/sqlteamgermany/archive/2009/06/18/sql-server-2008-r2-bisher-bekannt-als-sql-server-kilimanjaro.aspx...(read more)

SQL Server 2008 R2 - News und Infos

Daniel Mohr — Wed, 24 Jun 2009 14:47:00 GMT

Neueste Informationen zu SQL Server 2008 R2 unter:

http://blogs.technet.com/sqlteamgermany/archive/2009/06/18/sql-server-2008-r2-bisher-bekannt-als-sql-server-kilimanjaro.aspx

Fix report headings and variable body

Andreas Nobbmann — Wed, 24 Jun 2009 11:56:00 GMT

I just had a customer requirement who wants for reports with a lot of rows that the headings stay fix while the real report can be scrolled user-friendly as you like it. As far as I know (and I double checked with @lex and Christian) there is no out of the box solution for this so I did my Wicky kind of thinking and thought about trying to do it with iFrames and Go URL functionality.

So, here we go. The first thing you need is a simple table view report with a lot of rows - let's say more than 200, then it really makes sense. If you have done so get the URL of your report and add a narrative view to it, where you apply the iframe and paste the URL of your report to. For getting the URL you of course need to save the report first.

So your first narrative view could look like that:

If you take a look then you will recognize that I selected only the table view and adjusted the height to only 35 points - even just enough to show the column headings. If this value is not good for you play around with the height until you have your perfect value. And I also have hidden the scrollbar for the column headings by adding the option scrolling="no" to the iFrame.
WOW: only the "native" names of views like in the XML described can be accessed via Go URL, like tableView!1, pivotView!1, compoundView!1 and so on.

After you are done with the first report add another narrative view to your request, in which you show also the table view, but in this case completely. This in the end means that you have the column headings two times. Surely it is not perfect, but at least a good start.
Now you can place the report two times in a dashboard, as first view take the compound View, where you have title and narrative 1 view in and as second view the narrative 2 view. Or you can put everything in one compound View - it's like you like it.

Be aware, that the size of the windows will not correlate to your browser window, it will stay the size you defined in your iFrame. Also, be aware, that you have to pass all parameters in saved filters to this Go URL with the syntax described by my fellow blogger here.

Have fun and so long,
Andreas

PL/SQL Basics for the DBA. Part IV (… or how to start having fun working as a DBA)

Dani Rey's Oracle Stuff » TrivadisContent — Mon, 22 Jun 2009 17:01:21 GMT

Unbelievable but true. After talking about EXECUTE IMMEDIATE since 2 posts, I will finally show you how to use it. Basically EXECUTE IMMEDIATE takes a string as argument, interprets it and executes it as a statement, which is the same as what EVAL does in shell scripting. It’s behavior can be tested with the following. CONNECT t/t SELECT [...]...(read more)

PL/SQL Basics für den DBA, Teil IV (…oder wie die Arbeit als DBA Spass macht)

Dani Rey's Oracle Stuff » TrivadisContent — Mon, 22 Jun 2009 17:00:09 GMT

Unglaublich aber wahr. Nachdem ich nun schon seit 2 Blog Einträgen von EXECUTE IMMEDIATE erzähle, werde ich nun tatsächlich auch zeigen wie man es verwendet. Kurz gesagt verwendet EXECUTE IMMEDIATE einen String als Argument, interpretiert diesen und führt in danach als Befehl aus. Dies entspricht dem EVAL, welches einigen von Shell Scripts her bekannt ist. Wie [...]...(read more)

Programming Windows Live Framework

Manuel Meyer - 12qw.ch » Business — Mon, 22 Jun 2009 14:42:19 GMT

This post continues my overview post about LiveMesh and the Windows Live Framework (http://www.12qw.ch/?p=13). It explains how and why the Live Framework is interesting for software developers and what you need if you want to start programming Live Services. Requirements 1. Get a Windows LiveID (If you do not have one already) 2. Visit ......(read more)

Meet DIs local rep: The AbstractContext

realfiction - TrivadisContent — Wed, 17 Jun 2009 20:12:46 GMT

I am having an unhealthy yet energetic and fruitful relation with a close relative of 'the' Dependency Injection (DI) container. She's called AbstractContext and she's been able to cope with all kinds of thorny situations where numerous objects need to collaborate without resulting in a tangled mess of spaghetti. The DI Container is like context's wise Mum. Tell her everything upfront and she will listen to you and make anything you want. Her daughter is also quite promiscuous...(read more)

Does Change Data Capture need Partitioning?

Mathias Zarick — Wed, 17 Jun 2009 12:38:00 GMT

In last days I was confrontated with a Change Data Capture (CDC) Setup.
CDC is a nice feature which is based on Oracle Streams.
With CDC it is possible to track all the changes on a source table in a change table.
This information can be used by ETL processes for loading your data warehouses.

So it is obviously a data warehouse feature. Probably that is the reason
Oracle put it in the Data Warehousing Guide (Chapter 16).
More info on CDC can be found here.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/cdc.htm#i1028295
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_cdc_cookbook_0206.pdf

One prerequisite is that you have Java / JServer loaded into your database at
least at the sites where you have publishers and subscribers.
So if you use downstream capturing with autolog mode, you will need it
at the target site with the change tables.
JServer comes with standard dbca setups and costs no extra licence.

To check whether Java is installed properly have a look at dba_registry.

select status,version,comp_name
from dba_registry
where upper(comp_name) like '%JAVA%';

To install it later if it is missing use

connect / as sysdba
set feedback on termout off
spool install_java.log
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
spool off

CDC is installed through the script initcdc.sql from OH/rdbms/admin
which is called by catjava.sql. This loads CDC.jar from OH/rdbms/jlib,
installs 4 database triggers in sys schema which are named
cdc_alter_ctable_before, cdc_create_ctable_after,
cdc_create_ctable_before, cdc_drop_ctable_before.
These triggers are created disabled, and are enabled implicitly through
a CDC setup.

If you haven't installed Java you will experience following error, when
you try to create a change table.

ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_CDC_PUBLISH", line 611
ORA-06512: at line 2

Okay ... lets go to some questions regarding Oracle products and licenses,
as theses are of importance nowadays.
Synchronous CDC (does this really anybody need out there?) works also with
Standard Edition, Partitioning is not available for Standard Edition,
so change tables are not partitioned ones there.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - Production

SQL> rem NOTICE! banner does not contain the word "Enterprise" so its Standard Edition!
SQL> rem
SQL> rem test user is scott
SQL> grant dba to scott identified by tiger;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'SYNC_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'Y', -- only allowed in sync mode
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'N' -- new as of 11g but must be 'N' in sync env
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem lets have a look at the table
SQL> set long 10000 pages 30
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "SOURCE_COLMAP$" RAW(128),
        "TARGET_COLMAP$" RAW(128),
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

We have a table that is not partitioned.

When I implemented asynchronous CDC on Enterprise Edition I noticed, that the
change tables were created partitioned.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> rem NOTICE! banner says that this is Enterprise Edition!
SQL> rem Partitiong is also mentioned there
SQL> select value from v$option where parameter = 'Partitioning';

VALUE
----------------------------------------------------------------
TRUE

SQL> rem test user is scott
SQL> create user scott identified by tiger;

User created.

SQL> rem cdc needs a some privileges for a publisher
SQL> grant create session, create table, create sequence, create job to scott;

Grant succeeded.

SQL> grant select_catalog_role, execute_catalog_role, dba to scott;

Grant succeeded.

SQL> grant execute on dbms_cdc_publish to scott;

Grant succeeded.

SQL> exec dbms_streams_auth.grant_admin_privilege(grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> rem async cdc is based on streams which needs supplemental logging
SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> rem table needs supplemental logging and instantiation
SQL> alter table cdc_test add supplemental log data (all) columns;

Table altered.

SQL> exec dbms_capture_adm.prepare_table_instantiation(table_name => 'scott.cdc_test');

PL/SQL procedure successfully completed.

SQL> rem we need a change set in this example for asynchronous hotlog mode
SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
3      change_set_name    => 'MY_TEST_CHANGE_SET',
4      description        => 'async hotlog change set',
5      change_source_name => 'HOTLOG_SOURCE'
6      );
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> rem and now the change table
SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'MY_TEST_CHANGE_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'N',
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'Y' -- new as of 11g, now we can make use of it
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem and how does it look like?
SQL> set long 10000 pages 120
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "XIDUSN$" NUMBER,
        "XIDSLT$" NUMBER,
        "XIDSEQ$" NUMBER,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "TARGET_COLMAP$" RAW(128),
        "DDLOPER$" NUMBER,
        "DDLDESC$" CLOB,
        "DDLPDOBJN$" NUMBER,
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(
BUFFER_POOL DEFAULT))
PARTITION BY RANGE ("CSCN$")
(PARTITION "P1" VALUES LESS THAN (281474976710656)
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) NOCOMPRESS )

Yes! We have range partitioning, partition key is CSCN$.
So ... we would have to buy the partitioning licence.
But does this mean that we have to, when using CDC?
No! You get it running also without partitioning, but the feature must not be installed
or at least not linked into the oracle binary when creating the change tables.

To relink the oracle binary without partitioning use following commands
when all databases that use that oracle home are stopped.

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk part_off ioracle

To relink into the other direction use part_on instead of part_off.
Relinking cannot be done on Windows.

So again, the CDC Setup after relinking without partitioning:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the OLAP, Data Mining and Real Application Testing options

SQL> rem NOTICE! banner says that this is Enterprise Edition!
SQL> rem Partitiong is NOT mentioned any more
SQL> select value from v$option where parameter = 'Partitioning';

VALUE
----------------------------------------------------------------
FALSE

SQL> rem test user is scott
SQL> rem we recreate him
SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by tiger;

User created.

SQL> grant create session, create table, create sequence, create job to scott;

Grant succeeded.

SQL> grant select_catalog_role, execute_catalog_role, dba to scott;

Grant succeeded.

SQL> grant execute on dbms_cdc_publish to scott;

Grant succeeded.

SQL> exec dbms_streams_auth.grant_admin_privilege(grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.
SQL> create table cdc_test ( a number, b date, c varchar2(20) );

Table created.

SQL> alter table cdc_test add supplemental log data (all) columns;

Table altered.

SQL> exec dbms_capture_adm.prepare_table_instantiation(table_name => 'scott.cdc_test');

PL/SQL procedure successfully completed.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
3      change_set_name    => 'MY_TEST_CHANGE_SET',
4      description        => 'async hotlog change set',
5      change_source_name => 'HOTLOG_SOURCE'
6      );
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> begin
2    DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
3      owner             => 'SCOTT',
4      change_table_name => 'CDC_TEST_CT',
5      change_set_name   => 'MY_TEST_CHANGE_SET',
6      source_schema     => 'SCOTT',
7      source_table      => 'CDC_TEST',
8      column_type_list => 'A NUMBER,
9                            B DATE,
10                            C VARCHAR2(20)',
11      capture_values    => 'BOTH',
12      rs_id             => 'Y',
13      row_id            => 'Y',
14      user_id           => 'Y',
15      timestamp         => 'Y',
16      object_id         => 'N',
17      source_colmap     => 'N',
18      target_colmap     => 'Y',
19      options_string    => 'PCTFREE 0',
20      ddl_markers       => 'Y' -- new as of 11g, now we can make use of it
21    );
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> rem and again, how does the change table look like?
SQL> set long 10000 pages 120
SQL> select dbms_metadata.get_ddl('TABLE','CDC_TEST_CT') from dual;

DBMS_METADATA.GET_DDL('TABLE','CDC_TEST_CT')
--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."CDC_TEST_CT"
   (    "OPERATION$" CHAR(2),
        "CSCN$" NUMBER,
        "COMMIT_TIMESTAMP$" DATE,
        "XIDUSN$" NUMBER,
        "XIDSLT$" NUMBER,
        "XIDSEQ$" NUMBER,
        "RSID$" NUMBER,
        "ROW_ID$" ROWID,
        "USERNAME$" VARCHAR2(30),
        "TIMESTAMP$" DATE,
        "TARGET_COLMAP$" RAW(128),
        "DDLOPER$" NUMBER,
        "DDLDESC$" CLOB,
        "DDLPDOBJN$" NUMBER,
        "A" NUMBER,
        "B" DATE,
        "C" VARCHAR2(20)
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("DDLDESC$") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

Interesting, now the CDC change table is not partitioned.

Summary: CDC needs Java in the database, CDC does not need partitioning
but uses it if available. So if you want to avoid the usage of partitioning
you should not install or deactivate it.
Of course CDC is a warehousing feature, and warehouses probably need
partitioning for some other reason.

Have a nice day! Mathias

Introduction to Windows Azure Live Mesh/Live Framework Part 1

Manuel Meyer - 12qw.ch » Business — Wed, 17 Jun 2009 09:34:56 GMT

This post gives an overview about Live Mesh, Live Services and the Live Framework. Cloud computing is on the rise. The benefits of cloud computing programming paradigms are becoming clearer every day. But, as many problems are solved, new problems arrive. The biggest issue in regard to ......(read more)

Make It Yourself: Digital Signing, Principal Impersonation, Role-Based Security

Dmytro Gerasymchuk — Wed, 17 Jun 2009 07:48:00 GMT

This security stuff as digital signatures, impersonation and role-based security just sounds “hardcore”, but this all is quite easy. Well, sometimes one must dig into Win32 API for a couple a functions and may be a small parsing framework must be implemented.

With the “Version 1+” of the Dima CodeBase library (http://dimacodebase.codeplex.com), all this “dirty job” is already done. You can use the manifest files with SHA1 hash to digitally sign your data files; use impersonation functions for, for example, implement your own network service with some kind of “Windows authentication”; and, of course, the small and smart role-based security framework with configuration through the single XML file (which of course may be protected from modifications using the digitally signing functionality) is also there. Just look into the DimaSoft.CodeBase.Securit namespace.

Moreover, all this stuff is also available as the source code (to give you fresh ideas and maybe some kick in your daily coding practice) and you are also invited to co-work in this project.

PL/SQL Basics for the DBA. Part III (… or how to start having fun working as a DBA)

Dani Rey's Oracle Stuff » TrivadisContent — Tue, 16 Jun 2009 18:55:00 GMT

In the last two posts I showed you some basics of SQL and PL/SQL. We can now combine this knowhow to get a powerful tool for DBAs. Maybe you want to change multiple objects in a scripts, without copy&paste output nor writing files. To achieve this, we need CURSOR, LOOP and EXECUTE IMMEDIATE altogether combined [...]...(read more)

PL/SQL Basics für den DBA, Teil III (…oder wie die Arbeit als DBA Spass macht)

Dani Rey's Oracle Stuff » TrivadisContent — Tue, 16 Jun 2009 18:45:51 GMT

In den letzen zwei Posts habe ich einige SQL und PL/SQL basics präsentiert. Jetzt können wir einen Schritt weiter gehen und das Erlernte kombinieren, dadurch erhalten wir ein nützliches Werkzeug für die tägliche Arbeit als DBA. Das Hauptziel ist es immer noch ein Tool zu erhalten mit dessen Hilfe wir mehrere Objekte in einem Schritt verändern [...]...(read more)

Bright-eyed from Brighton

Andreas Nobbmann — Mon, 15 Jun 2009 19:57:00 GMT

"Sorry, I'm late". After a real long time I finally have a little time to blog again. Missed it ! And want to restart with an happening which made my eyes look bright.

Oh what a event...

I can only follow my fellow-blogger Christian when I say I had the honor to attend the really great Rittman Mead BI forum which happened the 14th and 15th May in Brighton. So perfectly organized, with tons of informative and interesting presentations, lots of good talks and discussions around OBIEE, Essbase and ODI and with a - in my mind - very harmonizing group of people attending. All in all a very delightful happening for which I want to say thank you very much to Mark&Jon and their team Becky, Jennifer (ladies first !) and Borkur, Ragnar and Peter.

I was invited to hold a presentation about Scripting Oracle BIEE there, which was really fun, because the guys from Rittman Mead opened some bottles of ice cold beer before I started. Hence we had an even better
atmosphere in the audience than already before (see Mark's blog). Relaxed presenting....

The presentation went very good and while standing in front of the audience I even learned something new. That it is NOT possible to paste UDML of repository content from any of the menu points accessible from the manage menu and that the admin tool has an command line option with which you can do a lot of funny things around automation with your repository. Hardly I knew that I blackled (google for energy saver, wanna try ? Click here) around it and found that of course John already blogged on it. And we discussed the new Content Accelerator Framework, which uses XUDML - a combination of XML and UDML. See here some blogs around that, one from Venkat, the other one from @lex. Great, if I learn this much while presenting I will do that again and again.

Talking about presentations. It had very impressive presentations full of comprehensive content on the Rittman Mead BI Forum.
There has been the one from Edward Kroske (he’s THE MAN in Essbase), who wrote some books with very special titles like “Look smarter than you are”, blogs a lot (http://looksmarter.blogspot.com/) and explained in Brighton very detailed and with a lot of passion and the humor of a man from Texas what the differences between ASO and BSO cubes are (http://www.rittmanmead.com/files/edward_roske_essbase_internals.pdf). Funny and very interesting stuff though I did not know that ASO is existing, because I just worked with the older versions of Essbase yet. Need to catch up there some time very very soon.

Also a good one was the presentation of Mark himself, who was talking about optimizing the performance of the ETL routine for Oracle BI Apps. I followed this without even knowing how the BI apps look like – unfortunately I do not know of many installations in Switzerland / Germany / Austria.

John Minkjan (http://obiee101.blogspot.com/) completed the first day with a very complete presentation all around the Cache of OBIEE and there is a lot. Not only the well known Query-Cache, but also the presentation services cache and not to forget the browser cache. So, if you want to know something about read his presentation (http://www.rittmanmead.com/files/john_minkjan_cache_mgmt.pdf).

After John the legendary Venkat (he’s really the master of blogging around OBIEE and Essbase (http://oraclebizint.wordpress.com/)) held his presentation about Security integration between Essbase and OBIEE. This "geek" has developed a - as he said - “little” piece of Java code which makes the integration an easy catch. Follow his findings here http://www.rittmanmead.com/files/venkat_j_obiee_essbase_security.pdf. Unbelievable !

For those of you more interested in ODI / OWB melting together, there was an outlook how all this should happen from Craig Stewart (http://www.rittmanmead.com/files/craig_stewart_biapps_odi.pdf).

Also, there was a good one from Adam Bloom, who talked very informative around what to do, when a customer yells that performance of OBIEE suite is bad and what you can do to find out where it comes from.
Have a look, when working with performance issues in OBIEE this can help you find it faster (http://www.rittmanmead.com/files/adam_bloom_bi_server_diag.pdf ).

And on the second day finally I had the possibility to see next generation of OBIEE. 11g on the table, live in front of my eyes. It looks very good and just 5 seconds after the presentation started we had this humming “Woah” running through the audience. Mike Durran could have talked all day long about 11g and the new features, all of us would have followed his explanations with eyes wide open. But there were others to present.

And it was absolutely worth it. The approach from Emiel in his presentation was kind of different, but I liked it. Hopefully they can follow their strategy also when they have more data in the Warehouse. I think especially the Data Warehouse geeks among you will like his presentation. And if only to find out why it’s not the strategy you would follow / propose to a customer. http://www.rittmanmead.com/files/emiel_bockel_sun_stars.pdf .

Another very good presentation was all around Map integration in OBIEE. Maarten Jan Kampen, a guy from the Netherlands is actually in the middle of implementing that. A lot of customized Java / Java script and whatever else code in this solution. This is what I remember. Take a look: http://www.rittmanmead.com/files/maarten_jan_kampen_obiee_mapviewer.pdf .

Adrian was next, talking about global implementations of OBIEE, mainly focused on the translation features OBIEE provides for the repository and the presentation catalog, but also for the product messages themselves. Funny was that he also mentioned the BI Localizer, the tool I already used at a customer site to implement their language requirements. Check it out: http://www.rittmanmead.com/files/adrian_ward_globalization.pdf.

And then – in my opinion - the best presentation of the Forum was held by Tony Heljula. He talked about SOA integration in OBIEE and demonstrated live how to configure some examples. He demonstrated as if it is the most unimportant minor matter and in a very easygoing way. Great trainer this guy, I just met him last year at a partner event in Frankfurt, where he held the “Modeling the OBIEE repository” course, which I attended.
To be honest it was so interesting to see all the examples, where integration could make sense. Absolutely phenomenal – for me. But decide for yourself: http://www.rittmanmead.com/files/antony_heljula_obiee_soa.pdf

Wow, something totally new. Cube organized MVs, a new feature in Oracle OLAP 11g. Quite interesting, what Peter Brink showed us. But decide yoyurself. Here it is: http://www.rittmanmead.com/files/peter_brink_comv.pdf.

So, all in all this event showed me that it’s good to blog around something. I met a lot of people there and my network is bigger now and this is very valuable for me. But what makes the most is that it is a very good feeling to know that I blogged and that it is read by somebody and - at least sometimes - appreciated by someone out there – and not getting dusty somewhere in a library or so.

The event pretty much reminded me of the Trivadis Tech Event we have two times a year, even just for the OBIEE, Essbase and ODI world. Hopefully I can be at this Rittman Mead BI Forum again next year.

To all a good start in the week.

So long,
Andreas

Audit Informationen werden nicht immer geschrieben

Best Practices — Fri, 12 Jun 2009 13:44:00 GMT

Falls man sich wundert warum trotz korrekt eingestellter IBM® Cognos® 8 Konfiguration plötzlich kein Auditing und keine Informationen mehr in das cogserver.log geschrieben werden, so wird man bei näherer Suche im cogserver.log evtl. den Hinweis finden, dass der für den Log Server Port verwendete Port noch in Benutzung ist. Dieses Szenario passiert relativ häufig in Produktionsumgebungen, wenn der IBM® Cognos® Service auf dem System zu schnell neugestartet wurde. Dies kann bei Windows relativ leicht...(read more)

Interessantes zum OWB 11.2

OraBI — Fri, 12 Jun 2009 06:42:00 GMT

Oracle war beim Wechseln von Produktnamen schon immer recht kreativ. Nun gibt es beim OWB die nächste Umbenennung, die OWB Standard Edition ist inzwischen ja ein Bestandteil der Oracle Datenbank. Nun bekommt diese Version den Arbeitstitel " InDatabase ETL ". Mit dieser Version steht dem Anwender ein großer Teil der OWB Funktionalitäten über seine Datenbank-Lizenz mit zur Verfügung. Warum das ganze? Oracle hat inzwischen seine Roadmap für die beiden ETL Tools OWB und ODI bekannt gegeben...(read more)

Vertical Text with IBM Cognos* Report Studio 8

Best Practices — Thu, 11 Jun 2009 14:27:00 GMT

Eine oft gewünschte Funktionalität in IBM Cognos ® Reports ist, dass Texte (wie in Excel) vertikal dargestellt werden sollen. Diese Funktionalität ist durchaus (wenn auch sehr eingeschränkt) möglich in IBM Cognos ® 8. Die dargestellte Methode ist nur in IE ab Version 6.x darstellbar und - da wir hier html tags verwenden - nur im HTML Format sichtbar. Nachteile: Begrenzt auf HTML Layout in IE. Schlecht lesbar mit vielen Schriftarten. Vorteile: Texte werden nach wie vor in PDF etc. dargestellt. Sie...(read more)

How To: get a RSS feed from BlogSpot filtered by a Tag

admin — Thu, 11 Jun 2009 12:30:00 GMT

by default Blogspot offers Atom feeds, which can not be digested by some systems (like our Community Server)

but there is help! try this syntax :

http://yourfantasticblog.blogspot.com/feeds/posts/default/-/TheTag?alt=rss

i put this here so I don't have to remember it next time I need it