Feed aggregator

Support forum,

Fadi Hasweh - Sun, 2007-06-24 09:02
I am thinking of opening a forum here to share support cases with all users, even though I guess oracle forums a great place to post problems and to get answers quickly, I will try to open my own forum for 3 months period if it was successful I will keep it other wise I will stick with oracle forums.
Suggestion are welcomed

Our first ADF application is production-ready!

Janusz Marchewa - Sat, 2007-06-23 06:47
As you might have noticed, my last post was written over a month ago. The reason is simple: I was quite busy finishing our first ADF application that is now production-ready! :) Whew...

We've learned a lot about ADF and JDeveloper. Hopefully we'll be able to leverage the power of this framework in future applications. Did everything go smoothly? Well, no. We are still not comfortable with ADF Security (we had to remove the authorization support), because it does throw weird exceptions in some situations and the ADF Security wizard in JDeveloper is a problem itself (it's hard to do any teamwork with this wizard).

Another conclusion is that the JPA+EJB3+ADF stack is not as productive as we thought. I'm going to have another look at ADF BC in the near future.

Finally, a few things worth mentioning:
1. If you have a problem with locales (e.g. the default ADF messages show up in a different locale than your messages), you should have a look here.
2. If you are using Oracle XE and Firefox and you cannot edit SQL scripts in the web interface, this message will lead you to a solution.
3. I've enhanced Frank Nimphius's solution (JSF login form for container-managed authentication in Java Server Faces) to make it more useful. I'll publish my modifications as soon as I find some spare time. Stay tuned ;)
Categories: Development


Robert Baillie - Fri, 2007-06-22 12:09
Saw a cracking Haiku on a t-shirt the other day: Haiku are easy But sometimes they don't make sense Refridgerator. Then my mind started dwelling on it: Got the release out But the testing's not finished It's falling over Or Database is slow Just can't see what's wrong with it Set autotrace on Or A quick refactor Turns into a bigger job Should have unit tests


Rob Baillie - Fri, 2007-06-22 11:54
Saw a cracking Haiku on a t-shirt the other day:

Haiku are easy
But sometimes they don't make sense

Then my mind started dwelling on it:

Got the release out
But the testing's not finished
It's falling over


Database is slow
Just can't see what's wrong with it
Set autotrace on


A quick refactor
Turns into a bigger job
Should have unit tests

Installing dbms_profiler

Andrew Fraser - Fri, 2007-06-22 08:19

This page has been moved to http://andrewfraserdba.com/?p=48

Categories: DBA Blogs

External tables in oracle 9i

Adrian Billington - Fri, 2007-06-22 03:00
Select directly from flat-files as though they are tables. August 2002 (updated June 2007)

Subquery factoring in oracle 9i

Adrian Billington - Fri, 2007-06-22 03:00
A new way of expressing subqueries in 9i. December 2005 (updated June 2007)

Ansi joins in oracle 9i

Adrian Billington - Fri, 2007-06-22 03:00
Oracle adds ANSI-compliant joins to SQL in 9i. January 2003 (updated June 2007)

Type enhancements in oracle 9i

Adrian Billington - Fri, 2007-06-22 03:00
Constructor functions, type evolution and substitution in 9i. December 2005 (updated June 2007)

Overcoming the limitations of set row

Adrian Billington - Fri, 2007-06-22 03:00
Techniques to avoid superfluous updates with SET ROW. June 2007

Oracle Database Plugin for the Eclipse Data Tools Platform

Donal Daly - Thu, 2007-06-21 13:41
As a further commitment to the Eclipse Community, Oracle announces an early adopter release of the Oracle Database Plugin for Eclipse Data Tools Platform. This plugin extends the Eclipse Data Tools Platform to connect to and work with Oracle database objects. The initial plugin supports the ability to connect to Oracle Databases, navigate through all database objects, execute stored procedures and functions, and view textual and graphical execution plans.

This release has been tested against DTP 1.0. We will revise the plugin as required once DTP 1.5 is released as part of the imminent Eclipse Euorpa release.

Oracle has published a Statement of Direction which outlines our future plans.

Feedback from the community is important to us so Oracle has created a forum on OTN to provide a means for this feedback to be collected and allow Oracle to respond. Assuming a positive level of interest from the community, we would seek to formally join the DTP project for the purpose of contributing to the Connectivity, Model Base, SQL Development Tools and particularly the Enablement subproject to provide specialized support for the Oracle Database.

OAF Key Do's and Don'ts (Part 1) - "Top 10" Golden Rules

Andries Hanekom - Wed, 2007-06-20 12:36
I don't know how many of you have come across this in the OAF Devguide, it was only by change that I found it, thought I'd share:

There's a lot to learn when getting started with the OA Framework, but the following list of rules are so universal -- and so fundamental -- they should be familiar to anyone who's doing Framework development.

1) ALWAYS try to declaratively define your UI. Resort to a programmatic layout only if the UI cannot be implemented declaratively. Programmatic layouts are difficult to customize (they don't leverage the Personalization Framework) and may diverge from the UI Guidelines over

2) NEVER change your UI layout properties in processFormRequest(). ALWAYS make changes in processRequest(), even if that means handling an event in processFormRequest() and then redirecting back to the same page. This ensures that the web bean hierarchy is in a stable state when the page renders.

3) NEVER use index numbers to find beans when you want to change their properties. ALWAYS search by name. Index numbers can change during processing.

4) NEVER change the properties of a parent bean from a child bean. This is a poor design practice that hampers reuse while introducing fragile code (particularly if the child code executes too late in the page rendering cycle to properly affect the parent).

5) NEVER instantiate Beans using "new OA*Bean()". ALWAYS use the createWebBean() factory methods available on the OAControllerImpl class. Not all Bean properties are initialized correctly when you use "new."

6) NEVER create Form Beans in code (this means NEVER add nested Form beans to a page; your Page Layout region should be the only form region). Multiple form Beans on a page are not supported and can result in strange runtime behaviors.

7) NEVER count on your Application Module using the same database connection in subsequent requests. For example, NEVER post and commit in separate requests. For performance reasons, the Framework will start pooling and reusing connections in 5.7 instead of holding onto a single connection throughout the life of an Application Module.

8) NEVER use JDBC directly unless you're calling a PL/SQL routine (you should use a view object instead, and if possible, the view object should be defined declaratively and not programmatically).

9) NEVER add member variables UNLESS THEY ARE TRANSIENT OR FINAL to view objects, Controllers, entity object, view rows and Application Modules.

10) ALWAYS adhere to the Self-Service Performance Guidelines

Montreal, Je Me Souviens...

Omar Tazi - Tue, 2007-06-19 12:31
After a short but efficient visit to Austin last week where I had the pleasure to speak at the Austin Oracle User Group and meet wonderful and equally interesting people, I am on the road again, precisely in Montreal. One of the people I wanted to meet F2F is whurley who attended my talk in Austin and posted a great summary on his blog. BTW, nice hanging out with you whurley!

The only thing that could be misleading in whurley's summary is when he says: "Their customers already use Eclipse for Java/Java EE development..." This is not true, our Java EE and SOA customers continue to enjoy JDeveloper as a superior development environment for their enterprise Java development. That said, we do have a well defined Eclipse strategy which is centered around "Productivity with choice" whereby we want to provide some of our customers who for whatever reason choose to develop using Eclipse the same level of productivity as they could have gotten using JDeveloper. That is one reason why we are stepping up our involvement in the Eclipse community.

Today I am in Montreal to speak at the ORA*GEC conference and meet key customers and partners in the region to share our view on open source and share some very cool features in our increasingly rich SOA stack. Although French is my native language I just realized it's more challenging than I thought to present tech stuff in French. To all my friends in Quebec, I promise to do my best!

: bad interpreter: No such file or directory

Wijaya Kusumo - Tue, 2007-06-19 02:22
When I run a bash script, it gives the following error: : bad interpreter: No such file or directory It turn out that the carriage return was not properly converted to *nix format, since it was written using Notepad in Windows. Quick fix: $ dos2unix filename -- convert to *nix format $ unix2dos filename -- convert to Windows/DOS format

Get Ready for Oracle Database 11g -July 11th 2007

Pankaj Chandiramani - Mon, 2007-06-18 21:21

The Countdown Has Begun to July 11, 2007, when 11g DB will be here :)
Its much awaited release as it has some new feature improvements in high availability, performance, scalability, manageability and diagnosability.


Categories: DBA Blogs

User tracking

Herod T - Mon, 2007-06-18 14:10

This is so obvious - at least to us here - that it really kind of shook us up that we were not doing it. I was talking to ,y friend Jeremy, and he mentioned his company had rolled out an internally built APEX app for tracking users and their accounts across all of the applications in the company. It took him awhile of explaining before it dawned on me how good of an idea this is.

They (as do we) have a database that is used for tracking employee phone numbers, with the multitude of payroll systems acquired through acquisitions having a single repository of all the active employees in the company that can be referenced even if those employees do not have network accounts is very important. We use our central database basically primarily for a place to allow employees to store their phone numbers and other contact information that is then access via a few JP pages. Jeremy and crew went a few steps farther. They actually replicate the employee information into this central database from all their various payroll systems, so that the information in the central database for where the employee is working, name changes, terminations, new hires etc is automatically looked after, so there is no out of date information when somebody transfers or quits. They then (and this is the brilliant part) pull in all of the user information from every single application in the company into this central database giving them user lists from every application. That user list is then compared to their service desk software (incident tracking stuff) and then all of that information is compared against the employee information from the payroll systems.

With all of the data in a central spot, now, they can issue a simple query on a person, and every application tied to that person is displayed and if their account is active or not. They also flag new hires and terminations and automatically generate tickets in their service desk software to have a help desk person investigate if an account is needed, verify the request for a new account is actually a live person according to payroll, and termination notices no longer have to rely on a manager at some location filling in the proper paperwork and forwarding to the IT department. Now the IT department is aware of the termination and what applications the user had access to and the IT department can now pursue the site to make sure the paper work is filled out.

In the first few weeks they were able to reduce their user count by almost 10% due to duplicate accounts, people that were no longer with the company etc. May not seem like much, but he said it was $20K alone in Novell licensing. Some reports have been put together to give them detailed user counts by application and server for licensing and usage reports.

Very simple idea but yields so much information. Jeremy did it all in APEX and oracle 10gR2 on Linux on an old piece of hardware. I will see if I can get him to put together something technical for this, I can almost smell a business opportunity here.

Good show!

Reclaiming LOB space in Oracle

Hampus Linden - Thu, 2007-06-14 06:19
Reclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact;, but what do we do for lobs?
We need a manual reclaim for the lob column.

A little demo:
(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden

SQL*Plus: Release - Production on Thu Jun 14 12:19:02 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Create a table and sequence to play with
HLINDEN@spinner1> create table lob_test (id number, data blob);

Table created.

HLINDEN@spinner1> create sequence lob_test_seq;

Sequence created.

-- Load 50 rows with 1.5Mb blobs (see code bellow)
HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

-- Find out what our lob segment is called
HLINDEN@spinner1> select object_name,object_type from user_objects where
2 created>sysdate-interval '5' minute and object_type='LOB';

------------------------------ -------------------
SYS_LOB0000199575C00002$$ LOB

-- Display the current size of the lob segment
HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where


-- Ok, let's delete those blobs and see what the size is after
HLINDEN@spinner1> delete from lob_test purge;

50 rows deleted.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where


-- Still 75Mb, hm, perhaps it recycled if we insert more data?

HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where


-- Nope, not recycled. We need to issue a shrink command to free up the
-- space immediately

HLINDEN@spinner1> delete from lob_test;

50 rows deleted.

HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);

Table altered.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where

-- All gone!
My simple blob loading code:
src_file BFILE := bfilename('TMP', 'data.dat');
dst_file BLOB;
cur_id NUMBER(10);
FOR i IN 1..50
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);


Herod T - Tue, 2007-06-12 20:39

We have been working on implementing Oracle Fusion middleware for our SOA solution, ESB and BPEL and all the other bells and whistles, BAM and BPA. I have to say, even for an old dog like me, this is an amazing concept. We had IBM and Oracle come and visit numerous times flogging their software and we decided on oracle simply because their products seemed to be more tightly integrated and basically looked better. The kicker was that everything is done via JDeveloper instead of 3 or 4 different products like IBM's SOA stuff (Websphere).

I have built a handful of adapters of some interfaces I know very well. I was able to retrieve data without a problem, I then took a crack at creating ESB and BPEL services to then "listen" for changes and move the data around. All I can say is wow, the ease at which data can be mapped and moved around is astounding. The largest time saving is an adapter only has to be made once per system and then a simple BPEL assign to do the mapping and presto, data acquired, transformed to a common format, and then transformed to what the destination system requires. Then the ESB process sits and waits for more data to come in. Wonderful stuff, no wonder everybody is talking about SOA, even though it is in hushed voices and darkened hallways. The learning curve is extreme and my knowledge of XML,XSD and XSLT has increased a hundred fold, but it is all worth it.

After this I am off to update by CV ;)

We have a few training classes scheduled to try and get the rest of the department some exposure to the products. BPA - Business Process Analyzer I believe is what the acronym is, there are just a few to try and remember. BPA is a product that I have just started to look at, we bought a lot of licenses for it, and as the marketing for it says, it allows business "super users" to actually map the work flow in an almost Visio environment, pass that to a developer and with a lot less work than I expected, the developer can turn that into a BPEL flow and deploy the application. The best part is the changes to the flow the developer needed (if any) are automatically passed back to the user that created the original document.

Really cool stuff.

Oracle 11g - one month to go

Hampus Linden - Tue, 2007-06-12 17:05
Yep, exciting times. July 11th, lots of new cool stuff.

Read about a couple of new things in Insider .

If you're lucky enough to be in New York, sign up for the launch event!

Recovery Scenarios Part-2

Pankaj Chandiramani - Sun, 2007-06-10 20:42

Part 2 for the recovery scenarios

B) Lost control File or Media Failure:
      (Disk lost with all the files w/o SYSTEM tablespace)

SVRMGRL> shutdown abort;

run {
allocate channel c1 type disk;
restore controlfile to '/usr1/ora817/test/dbs/control01.ctl';
replicate controlfile from '/usr1/ora817/test/dbs/control01.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel c1;
RMAN> Reset database

(C) Table recovery: (Incomplete recovery)

happens when some one drops table by mistake, and by the time he/she
informs the DBA, other people are still working on the database. So the
DBA has to recover the database until time when the table was dropped.
That causes other people to re-work from that point onwards, and hence
it is an "Incomplete recovery"

When something like this happens, try to find out the exact time of accident.

SVRMGRL> shutdown immediate;

run {                                 
allocate channel c1 type disk;           
set until time "to_date('2004-01-16:13:17:00','YYYY-MM-DD:HH24:MI:SS')";  
restore database;                        
recover database;                        
sql "alter database open resetlogs";     
RMAN> Reset database

(D) If both the ACTIVE redolog files are gone:

SVRMGRL> startup nomount;

run {
allocate channel c1 type disk;
set until logseq=7 thread=1;
restore controlfile to '/usr1/ora817/test/dbs/control01.ctl';
replicate controlfile from '/usr1/ora817/test/dbs/control01.ctl';
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel c1;

If for some reason this attempt fails,

SVRMGRL> alter database mount;  (equivalent of startup mount;)
SVRMGRL> alter database drop logfile group 2;  (optional)
SVRMGRL> recover database using backup controlfile until cancel;  

(Recover to the point till you have good archivelogs, and then Type CANCEL at the prompt)
SVRMGRL> alter database open resetlogs;

RMAN> Reset database

Run the following script to find out the status of backup process:

alter session set NLS_DATE_FORMAT = 'MM/DD/YY (HH:MIAM)';

col start_time format a20 heading "Backup of this|Database started at"
col a format 999.99 heading "% Complete|so far"
col b format a20 heading "Time taken so far|for this backup"

select start_time,round(sofar/totalwork*100,2) a ,
Hrs and
Mins' as b
from v$session_longops
 where substr(opname,1,4)='RMAN'
 and (round(sofar/totalwork*100,2))<100
 and (round(sofar/totalwork*100,2))>0
 and totalwork !=0;
-- -----------------------------------------

Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator