When importing data from different database, sometimes you get errors like:

ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'TBS01' does not exist
Failing sql is:

This means that the tablespace “TBS01″ doesn’t exist in the database where you’re importing the data. For this, you can use REMAP_TABLESPACE option. If you have more than one tablespace that doesn’t exist in the second database, use comma as follows:

REMAP_TABLESPACE=db01_tbs01:db02_tbs,db_01_tbs02:db02_tbs 

To get which remap script you need to create, check TABLESPACE_NAME column for the DBA_SEGMENTS view and find in which tablespaces your objects are reside

I strongly believe that you already know that NOT IN doesn’t take NULL values into account and those values are not displayed in the output

Check the following example:

SQL> create table my_table (id number, name varchar2(10));

Table created.

SQL> insert into my_table values(1,'test1');

1 row created.

SQL> insert into my_table values(2,null);

1 row created.

SQL> insert into my_table values(3,'test2');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select * from my_table;

ID NAME
---------- ----------
 1 test1
 2
 3 test2

SQL> select * from my_table where name not in ('test2');

ID NAME
---------- ----------
 1 test1

SQL>

For more information check the following links:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684

http://jonathanlewis.wordpress.com/2007/02/25/not-in/

Today, when I was trying to connect to the OEM Grid Control, I got “Login operation failed” error

The first file to be checked are the log and trace files at  Grid side. So I checked emoms.trc and emoms.log files under $OMS_HOME/sysman/log and didn’t find anything. I checked the repository database, it was up and running. After a while I realized that it’s a connection problem and checked $OMS_HOME/sysman/config/emoms.properties file and saw that it is trying to connect to the 2222 port  as a Repository Connection:

oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=host03)(PORT\=2222)))( CONNECT_DATA\=(SID\=oemgrid)))

When I checked listener at the repository side, I saw that it wasn’t configured to accept connections from port 2222

I added the above port number to the non-default connection section at listener.ora file and started the listener

second_listener =

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=2222))))

SID_LIST_second_listener =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=oemgrid)

(ORACLE_HOME=/u01/oracle/product/10.2.0/db_1)

(SID_NAME=oemgrid)))

-bash-3.2$ lsnrctl  start second_listener
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/second_listener.log
Listening Endpoints Summary...  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=2222)))
Services Summary...
Service "oemgrid" has 1 instance(s).  
Instance "oemgrid", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I tried to connect to the Grid Control again, and succeeded. So if you got “Login operation failed” error and didn’t get any information from the Grid Control log files, the listener and port issues can be the reason

Sometimes we got some folders filled with unknown files either due to any bug where Oracle creates 100 files in a second or due to the OS bugs. And in some cases we’re not able to find in which folder files were created. For that, you can use the simplest command in Linux to get the output for the folders with its size sorted. Here’s the command:

du -s /u01/* | sort -n -r

.......

28G     /u01/oracle/product/10.2.0/db_1/flash_recovery_area

........

From the output you will get the folder which consumes more space

In this video tutorial I show practical demonstrations on all Flashback Technologies that are avaiable in Oracle

To download the .mp4 format of the video tutorial, use the following link:

http://www.kamranagayev.com/Video_Tutorials/Flashback_Technologies.mp4

Oracle NoSQL is now available at OTN. Check the following link for more information, download and documentation:
http://www.oracle.com/technetwork/database/nosqldb/overview/index.html

Yesterday, Hemant K. Chitale, who’s co-author of my book “Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump” posted an interesting article where shows how CREATE INDEX statement performs INDEX FAST FULL SCAN over another index to speed up the process. You can read the article from the following link:

http://hemantoracledba.blogspot.com/2011/09/index-that-is-subset-of-pre-existing.html

I was requested to apply a patch on one of the production databases. During patch apply, I got the following error and rolled the patch back:

The following actions have failed:
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kqlm.o to /oracle/product/10.2.0/lib/libserver10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kkpod.o to /oracle/product/10.2.0/lib/libserver10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kkpox.o to /oracle/product/10.2.0/lib/libserver10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgl.o to /oracle/product/10.2.0/lib/libgeneric10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgl2.o to /oracle/product/10.2.0/lib/libgeneric10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgh.o to /oracle/product/10.2.0/lib/libgeneric10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgl.o to /oracle/product/10.2.0/lib32/libgeneric10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgl2.o to /oracle/product/10.2.0/lib32/libgeneric10.a... ''
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgh.o to /oracle/product/10.2.0/lib32/libgeneric10.a... ''
Do you want to proceed?
[y|n]

After contacting Oracle Support we decided that there may be a corruption on the mentioned library files.

As a solution, I installed Oracle Software to the different directory, applied necessary patchset, created new database and tested the patch, it succeeded. So I rolled back the latest patch, copied three library files to the previous Oracle home (to the lib and lib32 folders) and applied the patch, and of course, it succeeded.

So if you get the above message “Archive not applied” when applying patch, don’t worry, this means that the mentioned library files might be corrupted. Just perform above steps and don’t forget to backup your old library files

In some terminals when you login to SqlPlus and hit backspace to clear the text you’ve written, the following character apperas “^H”. Without any configuration, you can use “Ctrl”+”Backspace” in order to clear the text from terminal. However, by making a little change in the “Session Options” menu, you can get rid of this annoying character “forever”. Here’s the screenshot:

Two days ago I’ve recieved an email from Oracle according the paper I’ve submitted for the OpenWorld 2011. The paper has been accepted

Here’re the details of my session:

Session ID: 06787
Session Title: Oracle Recovery Manager (Oracle RMAN) 11g New Features

All you who will attend to the OpenWorld this year are invitied to my session. See you in San Francisco!