Feed aggregator

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
time.

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.

http://www.oracle.com/webapps/events/EventsDetail.jsp?p_eventId=66665


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 10.2.0.2.0 - 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 10.2.0.2.0 - 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';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000199575C00002$$ LOB

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

MB
----------
75

-- 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
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
75

-- 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
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
150

-- 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
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
0
-- All gone!
My simple blob loading code:
DECLARE
src_file BFILE := bfilename('TMP', 'data.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..50
LOOP
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);
dbms_lob.fileclose(src_file);
END LOOP;
END;
/

SOA

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;
SVRMGRL> STARTUP NOMOUNT;

RMAN>
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)

This
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;
SVRMGRL> STARTUP MOUNT;

RMAN>
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;

RMAN>
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 ,
mod(floor((sysdate-start_time)*24),24)||'
Hrs and
'||floor(((mod(round((sysdate-start_time)*24,2),24))-(mod(floor((sysdate-start_time)*24),24)))*60)||'
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

New blade enclosure from Sun

Hampus Linden - Sun, 2007-06-10 11:34
Ever since Andy Bechtolsheim returned to Sun pretty much everything on the Sun x64 server line meet all expectations and then some. Sure, there has been the slight ethernet chipset problem, but generally the AMD kit has really kicked ass
Though some people where surprised to see first (new) blade offering from Sun, the 8000 series. A 4-socket blade offering when most of the market moved away from larger blade enclosures and focused on 2-socket systems. The 8000 still made good sense for larger customers like financial institutions and telcos. Server consolidation on a large level.

Now the other day Sun released a second (third actually, the 8000 comes in two models) blade offering. The 6000 series. It's a fairly basic 10U enclosure, 10 blade slots and the classic Ethernet modules. Not very exciting at first glance, I have to admit. Sounds like the PowerEdge 1955 box but not with the same high density (Dell can do 10 blades in 7U).
6000
The thing cool about the 6000 enclosure is that it is not a Blade enclosure in the classic sense, it's not "stripped down computers in a box". It's actually 10 high capacity servers. It enclosure offers the same expandability as normal 1U servers.
All blade modules are dual socket, can take up to 16 DIMMS, four 2.5" SAS drives and even two PCI-e slots. That's right, normal PCI-e slots, no more proprietary on board FC-AL or Infiniband modules. Just slot in a couple of standard c-PCIe cards and off you go. No more opening up the blade to install modules.
Another new feature is a hardware RAID controller on-board (for good and bad indeed).
The whole enclosure is based around industry standards and open solutions.

In addition to all this cool stuff the real winner here is the selection of blades.
Sun offers no less than 3 blade types.
The t6300 UltraSPARC T1 blade,
the x6220 AMD blade and
the x6250 Intel Xeon blade
It's the first Intel Xeon (including quad-core procs) offering from Sun, with many more to come.
t6300

Watch Andy talk about the new box here.

Joe's Blog: Google Street View

Joe Fuda - Fri, 2007-06-08 13:00

Here's something cool. A new Google Maps feature called "Street View" is available for some streets. With it you can "virtually" walk down a road seeing actual street level photographs as you go. You can pan the image 360 degrees or zoom in and out as you please.

Here's a Google Maps Street View image of Marine World Parkway in front of Oracle's HQ buildings in Redwood Shored, CA. Click on the E or W arrows to travel along the road or click and hold the image then drag your mouse pointer to the right to see the fountain and more buildings.


...

Oracle Database Migration has reached the next level!

Donal Daly - Fri, 2007-06-08 04:24
SQL Developer 1.2 is now production and with it our new integrated migration workbench. Find out more information on OTN here. Over the last 6 months or so as I have talked about this new product, I used the tag line "Taking database migration to the next level", well... I think we are there now. We are the first database vendor to provide an integrated migration tool into a developers IDE with all of the resulting productivity benefits this brings.


The SQL Developer Migration Workbench is the tool to aid in the migration of third party databases onto the Oracle platform. This tool allows you to migrate your existing Microsoft Access, Microsoft SQL Server and MySQL databases (including schema objects, data and stored procedures/functions) to Oracle.

Features of this production release include:
  • Enhanced user interface - This release harnesses the enhanced GUI environment of SQL Developer and works seamlessly with other SQL Developer components. This means that users have one tool to browse third-party databases, migrate selected objects to Oracle and then work with them.
  • Existing SQL Developer users should find the Migration Workbench familiar and easy to use.
  • Quick Migration Wizard provides the easiest and quickest means of doing a database migration.
  • Step driven migration offers control at each stage of the migration process.
  • Fine grain migration support provides users with the ability to select specific objects for migration.
  • Least privilege migration - The ability to migrate objects from source to target without the need for dba rights. The workbench will migrate objects it has rights to view so does not require any special privileges to run.
  • Platform Supported:
    • Supports Microsoft Access versions 97, 2000, 2002/XP, 2003
    • Microsoft SQL Server 7, 2000, 2005
    • MySQL versions 3, 4, 5.
  • Parallel Online data move - The ability to move the data using a number of parallel connections for increased throughput.
  • Offline data move script generation - Generates scripts to allow for export of source data and import to target database of offline data move. We also support offline capture as well.
  • Language Translation Features - Supports translation of stored programs, procedures, functions, triggers, constraints and views defined in Transact SQL or Microsoft Access SQL.
  • Translation scratch editor - An interactive editing facility for Transact SQL and Microsoft Access SQL allowing for instant translation to PL/SQL or SQL. This editor supports both single-statement translation and translation of entire SQL scripts.
  • Translation difference viewer - Inline difference viewer for examining translated SQL. This viewer provides color coded side-by-side comparison of translated SQL to display semantic similarities between the source and translated code.
  • Any many more interesting features for you to find out about....
We have also prepared a number of online demos (viewlets) so you can see this new tool in action. Check them out on OTN here.

I am very proud of what my team has achieved with this release. A lot of work over the past year has gone into it. This is only the beginning, expect further innovative releases from the database tools team in the future.

I would like to thank our early adopters who provided some very constructive feedback. Hopefully you see the results of your feedback in this production release.

Transformations: Numbers to Words (New SQL Snippets Tutorial)

Joe Fuda - Thu, 2007-06-07 16:00
Two new techniques for converting numbers into words have been added to SQL Snippets. Unlike the ubiquitous Julian based solution (which uses the J and JSP format models) the new SQL Snippets techniques use the fractional second format models FFn and FFSP. Using fractional second models produces solutions that, unlike the Julian solution, can handle the numbers 0, 1,721,058 to 1,721,423, and those greater than 5,373,484 without errors or elaborate workarounds.
...

Pages

Subscribe to Oracle FAQ aggregator