Yesterday suddenly I received a call from my guys that one of the database server at my client’s place  has only 56 MB space left in “C” drive.
I was shocked to hear it as a day before yesterday it has 15GB of free space in the “C” drive.
After investigation I found out that the temp tablespacce in “C” drive has reached more than 30 GB.
On further investigation, I found out that it has happened as lot of “data warehousing” kinds of reports (candidate for data warehousing) were designed to run in present database design which is originally mean for OLTP [...]

[Read more →]

First time when I installed Oracle Database Lite 10g Release 3 Mobile Server in development PC, I didn’t faced any problem and was very straight out of the box installation affair.
Later when I tried installing same in my personal laptop, I faced lot of issues and in process I got to understand more about it.
I though of blogging it’s installation process so that it acts as a future reference point for me and help others too.
In this blog post I will be sharing most of the information through pictures rather than words.
Installation Process Steps:
Step: 1

Step:2
Specify Oracle Home and its path, [...]

[Read more →]

Recently I got my new book Oracle Database 11g New Feature on 11g from Amazon.com.
Even though I know that now Oracle 11g has a new mechanism to keep the alert , trace and log file, but I wasn’t fully aware of the technical terms that oracle is using to define it.
In fact starting Oracle 11g, Oracle has totally re-vamp it’s activity logging mechanism from generating simple logging files to sophisticated mechanism introduced as a new feature which they are calling Fault Diagnosability Infrastructure.
This Fault Diagnosability infrastructure is made up of many components, some of key component are as follows:

ADR – [...]

[Read more →]

Recently after I configured database on testing machine and configure the forms to run on middle tier on testing machine (all windows environment), I came across the error ORA-12638.

After little bit of brains storming and checking online, I found out that one of the way to solve this error is to disable the Oracle Advanced Security Authentication and let database use the supplied user name and password to authenticate.
It can be disable by updating the sqlnet.ora file with the following entry:
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Note : Default entry in my case was SQLNET.AUTHENTICATION_SERVICES= (NTS)
Guys do check out other method and let me [...]

[Read more →]

Today while exporting I came to know that Oracle XE does not support Bitmap Indexes.

Then to test it out, I login into Oracle XE’s SQL*Plus console and tried to run a create bitmap index DDL sql and result was still negative as follows.
 
So I was convince that it does not support Bit-mapped Indexes..

Bookmark this post

[...]

[Read more →]

Today I came across this thread at Oracle Forum.
Where Question was as follows:
Consider Test table have Numb (Number datatype) field. table values are given below.
Table Name: Test
NUMB
1
2
4
7
8
9
12 … This table have more than thousand records like that…..
How to retrive the missing numbers. I’m expecting the output 3,5,6,10,11……..
Could you please give the SQL for this task. Thanks!
One of the member David Grimberg gave following solution.

select level numb from dual connect by level <= (select max(numb) from test)
minus
select numb from test;
I was very impress with this sql because I recognize the worth of this sql to myself and many situation in which [...]

[Read more →]

Now in Application Express 3.1 installation we have two scripts to change the password of internal ADMIN account.
First one is as follows which was available before version  3.1
@UNZIP_DIR/apex/apxxepwd.sql password
Where
password is the password of the Application Express internal ADMIN account.
Second one is as follows which is new in version 3.1
@UNZIP_DIR/apex/apxchpwd.sql
When prompted enter a password for the ADMIN account.
Where:
UNZIP_DIR is the directory where you unzip your application express installation.

Bookmark this post

[...]

[Read more →]

Even though there is a document about up-gradation at Oracle, I wrote what I thought I will be following in future. There are things which one may not find in documents but learn with experience or when actually doing it.
Steps to up-gradation are as follows:
1. Change your working directory to apex where you have unzip the installation.
2. Start SQL*Plus from here and connect to the database as SYSDBA role. Thus making apex as your working directory.
3. To Install Full development environment
@apexins tablespace_apex tablespace_files tablespace_temp images
it can be something like this
@apexins SYSAUX [...]

[Read more →]

Recently I forget the Application Express (version 3.0.1) internal ADMIN account of my development site.
So to change the password without knowing the old password, I login as SYS into the Database Server and run the following script.
SQLPLUS> @C:\oraclexe\apex\apxxepwd.sql new_password
Where “C:\oraclexe\apex” is APEX_HOME directory.
What this script is doing is as follows:
First it changes the current schema to “FLOWS_030000″
alter session set current_schema = FLOWS_030000;
The set the security group id and and user name using wwv_flow_security package as follows:
wwv_flow_security.g_security_group_id := 10;
wwv_flow_security.g_user := ‘ADMIN’;
The change the status as “import in progress” using wwv_flow_security package
wwv_flow_security.g_import_in_progress := true;
Then get the internal user id of the “ADMIN” [...]

[Read more →]

Normally, when I connect to Oracle, I don’t worry about case sensitivity of my password.
But today, when I was trying to connect Oracle, I was contineously getting following error.
Flashing message “ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.”.
I was out of clue why exactly this is happening as I was providing right username and password!
I became little bit irritated, what’s wrong am I doing.
Usually I provide username and password in small case as I know that Oracle is case insensitive. So for a change I tried the same username and password with upper case and it [...]

[Read more →]