Install Oracle Developer Suite 10g on Windows 7 (64bit)

Recently, I got a task to install Oracle Developer Suite on Windows 7(64bit), but there seems lots of hassles to achieve this goal. There is a thread on Oracle forum on that topic. Here I log what I did to get it installed on my Windows 7(64bit) with 8G RAM.

• extract the setup files (version 101202) downloaded from otn.
• right click the install\setup.exe property, set compatibility to Windows XP SP2
• edit the virtual memory size to custom size from 1024m to 8000m, restart it
• run the install\setup.exe

Hope this helps.

Oracle 11g alert log location

Oracle alert log contains much useful information about your Oracle database, but do you know where is it located and how could you find it out?

1. Connect to the database with SQL*Plus or another query tool, such as SQL Developer.

2. Query the V$DIAG_INFO view as shown in "Viewing ADR Locations with the V$DIAG_INFO View".

3. To view the text-only alert log, without the XML tags, complete these steps:

   1. In the V$DIAG_INFO query results, note the path that corresponds to the Diag Trace entry, and change directory to that path.

   2. Open file alert_SID.log with a text editor.

4. To view the XML-formatted alert log, complete these steps:

   1. In the V$DIAG_INFO query results, note the path that corresponds to the Diag Alert entry, and change directory to that path.

   2. Open the file log.xml with a text editor.

Oracle Lite dmagent managing olite.key

While dmagent running, it may hold a copy of the olite.key in the memory. So if you change the olite.key in the file system level, it may not be picked up during next action.
Also, the dmagent has the ability to download the new olite.key as it needs, but you may see the username/password challenge window, if you are sure the username/password is correct, just simply click OK, let it try with the new olite.key downloaded by the dmagent. Surprise, it succeeded!

Turn on Olite SQL Trace

Want to tune your sql on Olite database? You can put the following parameter in your polite.ini file, [All databases] segment:
OLITE_SQL_TRACE = TRUE

You should see a trace file: oldb_trc.txt in your mobile client bin directory, if you are using msql to run your sql script on the Olite DB.

Oracle change xmltype column lob tablespace

One friend asked me this question today: How do you chnage xmltype column lob tablespace?

1. xmltype is an object type, it contains XMLDATA which is a CLOB object, you can treat "column.XMLDATA" as a clob object. please refer to here.
   
2. How to move a lob from one tablespace to another? The answer is to use 'alter table move lob(...) ...'; please refer to here.

To put everything together, here is how you can setup and test it:

1. Create a test table with xmltype column:
• CREATE TABLE xwarehouses (
  warehouse_id NUMBER,
  warehouse_spec XMLTYPE)
  XMLTYPE warehouse_spec STORE AS CLOB
  (TABLESPACE catusedt
  STORAGE (INITIAL 6144 NEXT 6144)
  CHUNK 4000
  NOCACHE LOGGING);
2. Alter the table to change the LOB tablespace:

• alter table xwarehouses
  move LOB(warehouse_spec.XMLDATA)
  STORE AS xwarehouses_seg
  (TABLESPACE catusedpht
  STORAGE (INITIAL 6144 NEXT 6144)
  CHUNK 4000
  NOCACHE LOGGING);
  

Error queue - records with "Client was out of sync" message

We are seeing Client was out of sync in our Oracle Lite Server Error Queue, my colleague found this post, which helps us understand how to fix this kind of problem. Here are my notes regarding to it:

1. Login to Mobile Manager; goto Error Queue; select the Transaction ID you are interested in; Publication Items.
   
2. Select the Publication Item you are interested in; View records; The DML Operation most likely will show 'Error'; Check th rows you would like to change the Update DML ; Select the desired 'Update DML' from the top left drop down(to choose between insert and update, depends whether that row exists in your application database), then Click Go; You should see the DML Operation changed to the desired one, rather than Error.
   
3. Click 'Apply'; You should see Confirmation: Changes were applied successfully.
4. Repeat step 2 and 3 for all the publication items in that transaction.
5. Back to Error Queue, select the Transaction you worked on, click Execute.
6. Go back to Mobile Manager home page you should see your transaction in the In Queue(1).
7. Refresh the page to see the MGP active, then return to Idle, make sure your Transaction does not reenter to the Error Queue.

Have trouble to start Oracle Server Console?

After dormant long time, now I start to pick up the Oracle Lite installed on top of OAS. All of sudden the Oracle Application Console will not start. Here are the steps I used to diagnose the problem:

Where is the log file?
/export/home/catused/oracle/ora_j2ee/sysman/log/emiasconsole.nohup

How to start Oracle Server Console?
/export/home/catused/oracle/ora_j2ee/bin/emctl start iasconsole

Turn off variable substitution in SQL*Plus

I got a phone call from one of my friends in Montreal ask me how to turn off the variable substitution in SQL*Plus, here is how it should be done:
SET DEFINE OFF
* You can prevent variable substigution of ampersand character(&) in SQL script, when running it in SQL*Plus.

Oracle CharacterSet

1. What is impact of NLS_LANG variable setting of user session while import/export the data ?

On export, the data will be converted from the database character set to the character set specified by NLS_LANG. In import, the database will assume that the data is in the character set specified by NLS_LANG and use that value to perform the conversion to the database character set if the two values to not match.

2. Why do we need to set this NLS_LANG user session variable before export/import ?

If your database character set is the same as your OS, you don't necessarily have to set NLS_LANG. For instance, if you have a US7ASCII db, and your OS locale is set to AMERICA_AMERICAN.US7ASCII, there won't be any problems. The only time it's really important to set this is when the db and OS settings don't match.

3.If NLS_LANG variable is not set (doesnot have any value) what would happen ?

If your database character set doesn't match your OS, the data could be garbled because the db will incorrectly transcode the data on import/export.

4. If I have to set NLS_LANG varible, what should I set it to?

Depends on what your database character set is set to (see below).

5. How can I see the characterset of my database?

select * from nls_database_parameters and look for the value set for the NLS_CHARACTERSET parameter. Don't get confused by the NLS_NCHAR_CHARACTERSET, that's for NCHAR datatypes.

So, for instance, if your NLS_CHARACTERSET value is set to UTF8, you would set NLS_LANG to .UTF8 (the dot is important because that's actually shorthand for territory_language.characterset, or language_territory.characterset, I can never remember which comes first. In any case, use the dot). For example:

setenv NLS_LANG .UTF8

6. Where can I get more info about database charaterset and What are the valid values for database characterset and NLS_LANG varibale ?

It's all in the Oracle documentation.

How to add ODBC DSN via command line?

Try out odbcconf.exe.
Here is the further information.

Shrinking Oracle table sizes

This morning I ran into this great post, when I was wandering on the internet.
It points out that Oracle does not free storage space with the delete statement. You can use alter table ... move defragments and rebuild the indexes.

How do I enable autotrace for oracle

If you run into the following error messages when you try to set autotrace on in sqlplus for oracle, you may need to enable autotrace for oracle and grant plustrace role to the user you are using.
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

1. Run plustrce.sql in SYS schema if it did not run before, $ORACLE_HOME\sqlplus\admin\plustrce.sql
2. Grant plustrace role to

Please refer to here.

Oracle v$session_longops view

During one meeting, one of my colleagues mentioned Oracle's v$session_longops view.
"This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release."
Further reference is here and here.

How to decide whether any of the two strings is null in Oracle?

I ran into a problem in my project, I have to decide whether there is a string in a list of strings is null or not. The problem is we have two columns from the Oracle DB: first_name, last_name, both of them could be null, we need to format the fullname like "last_name, first_name", but we do not to have the ',' if either last_name or first_name is null. Here comes my solution:
select last_name || nvl2(length(last_name) * length(first_name), ',', null) || first_name from contact_info;

Having trouble to install Oracle Lite Win32?!

Senario1: For some unknown reason, I screwed up my local installation of the Oracle Lite Win32 client.
Every time when I try to install Oracle Lite Win32 client on my desktop, the setup program always stops at "Stopping clients", finally I found out a workaround for it:
Just remove or rename the c:\windows\odbc.ini and c:\windows\polite.ini file, give the installation a retry.
Now you should have a smoother ride ;-)
Senario2: If your Oracle Lite WIN32 uninstallation program halt on "Loading 'Oracle Lite WIN32'", then you could try to kill the Oracle Lite Device Manger process (dmagent), retry the uninstallation process. You can use command line: taskkill /F /T /IM dmagent.exe

Oracle 10g Recyclebin

To disable the recycle bin:

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

To enable the recycle bin:

ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;

To check what objects are in the recycle bin:

select * from user_recyclebin;
select * from dba_recyclebin;

To purge the objects in the recycle bin:

purge recyclebin;
purge dba_recyclebin;

Check account status on Oracle 10g

How to check your DB account status? Is it locked? Here is the SQL to be used:


SELECT USERNAME, PROFILE, ACCOUNT_STATUS, password FROM DBA_USERS

Oracle Lite default limit on the client changes could be synchronized to the Mobile Server

During our load test, we noticed that the TCP window from the Mobile Server is closed when we try to send a client change bigger than 16M, after some digging, we found that there is an Oracle undocumented poperty, RESUME_FILE_SIZE, could be set in the Consolidator section of the Webtogo.ora to break this limit.

For example, if you want the spool file size as big as 128M, you can set the property like:

RESUME_FILE_SIZE=128

technorati tags:OLite

OAS HostName?!

We installed Oracle Lite 10g R2 on top of OAS 10g R2, everything seems going well, but when we shipped the same installation to our client, we noticed a hostname problem in their environment:
When you access http://sun1.something.com:8080/webtogo, you will get a 301 with hostname changed, http://sun1:8080/webtogo. This is NOT a problem if both fully qualified hostname and non fully qualified hostname are accessible to you. But it will be a problem, if you can only access the fully qualified hostname.
After spending some time to research on this hostname problem, finally we found the solution:
If you already installed OLite on top of OAS, then edit the httpd.conf file for OHS, which is an Apache server, change the ServerName to fully qualified hostname.
If you are trying a fresh installation, remember to put OUI_HOSTNAME=fully-qualified-hostname parameter in the command line you start the OAS installation, for example:
./runInstaller OUI_HOSTNAME=sun1.something.com

Oracle Lite 10.2.0.2.0 access control db (conscli.odb)

From the first day I started using Oracle Lite, I was always wondering how can I view the content in the conscli.odb. Today after some reading and trying, I found out a way to access it via mSQL.
First go to your WINDOWS\ODBC.INI find out the name of the conscli DB name for example: DLIU_conscli.
Then use the following command to access it, the default username/password is: SYSTEM/MANAGER:
msql SYSTEM/MANAGER@jdbc:polite:DLIU_conscli

UPDATE (Jan. 25th, 2007): when using msql to connect your own odb, remember always use SYSTEM as the username, otherwise you may get the following error:
[POL-5246] user DLIU does not exist