Unable to drop DBLink - Error: ORA-02082

ORA-02082: a loopback database link must have a connection qualifier

Reason is due to the global_name for the database:

1. select * from global_name;

2. note down the value

3. alter database rename global_name to some_new_name;

4. drop public database link linkname.whatever;

5. now change the global_name back using alter database rename global_name to original value;

11g - Missing Tables after successful IMP...

Seriously, If I introduce a new feature, I would not make that feature as default enabled!!! I let the end user make a decision on whether to enable that feature or not.

But, what did 11g do? It enables the new features by default and forcing us to know about them? Well, you know what one way it's good at least we are forced to learn new features immediately instead of waiting for an organization to start using it :)

Ok, enough blogging...

The issue is simple:

Developers starts complaining that there are few tables that are missing in database after the IMPort is done. They are correct. When looked into the database tables, I don't see few tables in there which they exists at the source database when the EXPort was done.

Time to look into IMP Log files (another reason why one has to make sure they provide log= at the time of IMP or EXP). Wondered to see that there is no reference of the missing table in that import log.

So, scan EXPort log nog. Hmm... even the export don't have any reference to those missing tables.

Well, I am pretty sure I did export for all tables. So, what happened? Why is EXP not exporting those few tables???

Looking into the source database where EXP was done, found that the missing tables are actually empty. Meaning there is no records into those tables. So, that does not mean that table is not there? It is there. Why cant it Export?

Oh, there you go...

Its because of 11g new feature called "deferred_segment_creation". This thing is set to TRUE by default and what it does is basically, it wont create a segment in the datafile at the time of table creation.

It only creates a segment at the time when a row gets into that table (need to wipe out the basics that learnt decades ago and get along with the new basics...).

Alright, thats the issue, so, when EXP happens it fetches the objects from dba_segments and this empty table wont have a segment so it wont be exported. end of story...

Well, how can i handle my export then?

Here you have a solution to chose:

1. for already created tables:

a. alter table empty_table allocate extent;

2. for all new tables if you want to create extent immediately then change the value from TRUE to FALSE for the parameter "deferred_segment_creation"

No. I have bunch of empty tables and have to do export import then what? Sorry... your only option is to use datapump (EXPDP and IMPDP).

ORA-00304: requested INSTANCE_NUMBER is busy

OMG...

Its a new database that i want to create and Oracle yells at me stating that INSTANCE_NUMBER is busy?

Oracle Error Code Says:

ORA-00304:

requested INSTANCE_NUMBER is busy

Cause: An instance tried to start by using a value of the initialization parameter INSTANCE_NUMBER that is already in use.

Action: Either a) specify another INSTANCE_NUMBER, b) shut down the running instance with this number c) wait for instance recovery to complete on the instance with this number.

But that is not true in this case. This is neither a RAC to have instance_number parameter assigned nor the database is already running. So, whats going on then...

Found that there are some wrong values for compatible parameter in init.ora file!!! So, do I get the INSTANCE_NUMBER busy error? Wow.... anyways, next time when this shows up, don't panic, just scan thru init.ora to make sure values are correct for compatible, service_names, db_name etc.,