Archive for the ‘Oracle’ Category

Oracle 10g Installation on Vista

April 30, 2007

I wish I had found out this link earlier. Just follow the instructions and it’s hassle free to install 10g on vista.
I couldn’t agree more with the comments about M$ and Vista on the page too. The vista built-in zip/unzip capability would take over an hour to unzip the 10g base installation file. That’s RIDICULOUS!!!

Powered by ScribeFire.

Using Oracle SQLDeveloper to access SQLServer

February 27, 2007

It is a pretty cool feature to use Oracle’s SQLDeveper 1.1 to access SQLServer.
The steps are:

  • Download jTDS (open-source SQLServer JDBC driver) from here. Unzip and extract the jtds-1.2.jar or whatever the latest version.
  • Start Oracle’s SQLDeveloper, Tools->Preferences->Database->Third Party JDBC Drivers. Click “Add Entry” and point to the jtds-1.2.jar
  • Create a new connection, choose SQLServer tab, type in hostname, port, username and password. It appears that the initial connection name has to be the same as the database and you can click the “Retrieve database” button. Once you found the database, you can rename the connection.

Try it out.
Of course, certain things don’t work. Like explain plan and auto trace.

Per comments below, please make sure jtds 1.2 is used. Apparently, 1.3 does not work.

powered by performancing firefox

10g not available on all flavors of Vista

February 23, 2007

According to this “Statement of Direction“, current plan calls for 32-bit 10gR2 available only for Vista Business, Ultimate and Enterprise Edition.
I guess Microsoft has put Oracle in a hard position by bringing out ridiculous various flavors. But Vista Home, basic and premium, may not be able to install 10g at all.
I am wondering about the XE. I haven’t tried myself, but some claimed to have installed XE on Vista Home.
PS: In response to APC’s comment, I tried XE on Vista Hom Basic and it works.

Are you exporting and importing compressed partitions?

December 15, 2006

Your luck just ran out.
Oracle imp utility uses convention inserts exclusively and partitions will lose compression after import as the inserts are not direct-path. The shiny 10g datapump has the same limitation. You will have to recompress the partitions later, like
1. Insert(append) into an empty table from the uncompressed partition.
2. Partition exchange
3. Truncate the table.
Then repeat for every uncompressed partition.

AskTom website exception

December 8, 2006

This is the exception I got from AskTom website this morning, 6AM EST.
ORA-01688: unable to extend table ASK_TOM.WWC_ASK_ACTIVITY_LOG$ partition PART_12 by 64 in tablespace ASK_TOM_LOG

What can we tell from this?
1. Schema name is ASK_TOM
2. There’s a tablespace ASK_TOM_LOG
3. There’s a partitioned table WWC_ASK_ACTIVITY_LOG$. Partition PART_12 is in ASK_TOM_LOG tablespace.
4. Eh, time to call DBA?

dbms_job, pended and drifting

November 16, 2006

Suppose we have a job submitted, interval is sysdate + 1 / 24 / 60, but the job is going to take 5 minutes, how will Oracle behave?
Looks like the sequence of events will be the following:
T0, Job #1 starts, the next run time is going to be sysdate(T0) + 1 minute.
T0 + 1 minute, The next job #2 starts, but is blocked by a QS lock, id2 in v$lock will tell the job id.
T0 + 5 minute, Job #1 completes, set sys.job$.next_date=greatest(T0 + 1/24/60, sysdate), effectively changing the this_date for job #2 to be sysdate. Job #2 starts.
Another issue is T0 is loosely guarenteed and could be off by couple of seconds, and even if the job completes within interval, the start time could drift if one uses sysdate only. To avoid drifting, use some calculation based on trunc(sysdate) instead of sysdate.

Null object_id in user_objects

November 16, 2006

When will Oracle have a null object_id, even if the doc says it’s NOT NULL?

An invalid database link.

An update: Actually, any database link.
It comes from the second branch of the union-all clause of user_objects:
select, NULL, to_number(null), to_number(null),
l.ctime, to_date(null), NULL, ‘VALID’, ‘N’, ‘N’, ‘N’
from$ l
where l.owner# = userenv(‘SCHEMAID’);

Firefox search plug-in for Oracle

November 8, 2006

Talking about firefox search-plugins for Oracle, you can find some here.
I like the Oracle Docs by Eddie Awad and AskTom by Stefan H.

Oracle SQL Developer v1.1 Evaluation Release goodies

November 8, 2006

Oracle SQL Developer v1.1 Evaluation Release is out. New Feature List here.
Some goodies I like are

  1. NLS preferences, my pet peeve
  2. View CLOB data
  3. Export data to HTML
  4. Search Engines, for OraDoc, AskTom, etc. Could augment firefox search plug-ins
  5. Autotrace

Now it’s time to upgrade and try out.

Interval data type not displayed in SQLDeveloper

October 24, 2006

If you select an interval data type column from a table or simply do math on two timestamp columns, the data is not displayed in SQLDeveloper. I guess this is a similar issue as the dbms_session.set_nls I blogged about.
There’s a related thread at OTN SQL Developer forum as well.
Of course, the workaround is to_char. Reiterate my comment. I don’t dispute that to_char should be used in production code when needed, but that doesn’t give SQLDeveloper an excuse for not doing the right thing in the first place. It may be just an additional query of v$nls_parameters.