DB Link to Oracle 11g

Written by Paweł Barut
As you know in Oracle 11g passwords are case sensitive by default. This applies to connecting via SQL*Plus or other client tools. And it also applies to database links between databases. So when you link from Oracle 10g to Oracle 11g create database link like this:

CREATE DATABASE LINK my_link
CONNECT TO remote_user IDENTIFIED BY "CaSe_SeNsItIvE_PaSsWoRd"
USING 'TNS_ID_11G';

Do not forget to enclose password by double-quote marks!
When you do not set password this way, you will be getting:

ORA-01017: invalid username/password; logon denied.

Hope this small tip will be useful for someone.

Paweł

More on Creating Database Links.

--
Related Articles on Paweł Barut blog:

• Using Named Parameters within SQL
• Oracle 11g is released - my links
• Table that cannot be deleted
• ORA-03297 on empty datafile
• Rolling Back the DDL

Oracle CPUApr2008 for DB: 10.2.0.4 patch set and first install

Written by Paweł Barut
To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 (10.2.0.4) that was released in first quarter 2008. In note that goes together with patch 6810189:

The following are the updated components of this patch set:
  * Up to January 2008 CPU is included in this patch set
...

Based on this I was expecting that CPUApr2008 will be also available for 10.2.0.4 patch-set. In note 552248.1 you will find then:

1.3 Database 10.2.0.4 Patch Set

The Database 10.2.0.4 Patch Set includes the CPUApr2008 content.

So the CPUApt2008 was available to all at least month before official release and was included in 10.2.0.4 patch set! Is this situation secure?

Anyway I had to install CPUApr2008. It's good that there is no need to recompile views this time. I've sucessfully installed it on test server, with minor problem. In installation log I've found one problem:

BEGIN emd_maintenance.recompile_invalid_objects; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSMAN.EMD_MAINTENANCE" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"SYSMAN.EMD_MAINTENANCE"
ORA-06512: at line 1

Hmm... After reconnecting, this commend run without errors:

SQL> conn / as sysdba
Connected.
SQL> BEGIN sysman.emd_maintenance.recompile_invalid_objects; END;

PL/SQL procedure successfully completed.

After all, server runs ok, and I do not find any problems within applications running on this.


Cheers Paweł

--
Related Articles on Paweł Barut blog:

• CPUJan2008: One thing that every DBA should know about it.
• Security: getting DBA rights quite easy
• Comments in SQL*Plus
• Extracting Data to Excel using only SQL*Plus
• RMAN TIP: Avoiding ORA-27211

Which Temporary Tablespace is used for sorts?

Written by Paweł Barut
This time I will write about "Which Temporary Tablespace is used for sorts" ?. I was not wondering about this much, as usually there is only one Temporary tablespace in DB. Lets assume situation, as show on picture:

• User A
• Assigned to Temporary Tablespace TEMP_A
• has table TA
  
• User B
• Assigned to Temporary Tablespace TEMP_B
• has table TB
• owns procedure PB (definer rights)
  
• Both users has access rights for all above object (Select on Tables and Execute on Procedure)
  

So let's discuss some situations:

1. User A runs query on tables TA or TB (or any other) - when disk sort is needed then tablespace TEMP_A is used
2. User A executes procedure PB. Procedure PB opens cursor on table TB (or TA or any other). If disk sort is required then tablespace TEMP_B is used.
   

For me it was bit surprising. Especially that I did not find anywhere in Oracle documentation description for this behaviour:

TEMPORARY TABLESPACE Clause
Specify the tablespace or tablespace group for the user's temporary segments.

I was expecting that all sort segments will be created in tablespace that is assigned to that user. I was hopping to solve one of my issues that way. But it occurred that, sort segment is created by user B, because procedure PB uses user B rights. It is reasonable, as this is consistent with granting access to objects, and temporary objects are treated the same way as permanent ones. On the other hand select is run for user A - shouldn't TEMP_A be used in all cases? What is your opinion on that?

Cheers Paweł

--
Related Articles on Paweł Barut blog:

• How to shrink Oracle Context Index
• Using Named Parameters within SQL
• Alternative Syntax for Insert
• Extracting Data to Excel using only SQL*Plus
• Rows to single string or aggregating strings

How to shrink Oracle Context Index

On of databases I've found that one of Context Indexes is getting bigger and bigger everyday. It was around 5-10% grow per day for table DR$MY_TEXT_I$I which contains tokens(word). No of records indexed was almost the same, but index grown was unexpectedly big. It was due fact, that quite many rows were modified every day, and it caused re-indexing those rows during index synchronization (CTX_DDL.SYNC_INDEX). So I've decided to reclaim this space.

Method 1.

This was my first though:

• Drop Index ... /Create Index ...
• or Alter Index ... Rebuild

But those methods have some disadvantages:

• Users cannot perform text searches during this operation,
• It does not solve problem for longer time, as index will still grow ...

So I had to find root cause and eliminate it.

Method 2.

While looking for root cause I've find out, that Index has never been optimized, so it kept old (obsolete) data. My solution was:
SQL> exec ctx_ddl.optimize_index('MY_TEXT_I', 'FULL', 120);
SQL> alter table DR$MY_TEXT_I$I enable row movement;
SQL> alter table DR$MY_TEXT_I$I shrink space cascade;

With this method table DR$MY_TEXT_I$I took 30% of its original size.
So let me explain why this worked. With ctx_ddl.optimize_index context index was internally optimized. It means information about old documents were completely deleted and index was internally minimized. Remember - third parameter limits time (in minutes) allowed for optimization. If it is really big index it can take hours. But you can run this optimization many times, until your index will be fully optimized.
Then I've just shrunk table (shrink space cascade). But this operation requires to enable row movement on table first.

But this was one time operation. To avoid this problem in future I've scheduled job to run ctx_ddl.optimize_index on regular basis. Now this table has grown a little, but is no longer growing so fast.

Foot note:

• It was tested on Oracle EE 10gR2 (10.2.0.3),
• I'm not sure if row movement is supported by Oracle for Context Index table DR$<index>$I. It worked for me and I did not experience any problems since turning it on.
  

Cheers, Paweł

Moving domain index to another tablespace

It seems to be very easy task that can be accomplished using rebuild option:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS;

We can even do it online for most indexes:

ALTER INDEX MY_INDEX REBUILD 
TABLESPACE NEW_TBS ONLINE;

But trying this for domain index (ex. Spatial Index) causes error:

ALTER INDEX MY_GEOMETRY rebuild tablespace users;
ORA-29871: invalid alter option for a domain index

What is the reson and how to overcome this? Domain index is in fact set of other objects. For spatial index it is table (MDRT_*). Spatial Index can be moved quite easy with

ALTER INDEX MY_GEOMETRY 
REBUILD PARAMETERS('TABLESPACE=NEW_TBS');

Oracle Text CONTEXT index is set of tables:

• DR$[index_name]$I
• DR$[index_name]$K
• DR$[index_name]$N
• DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate text index. But first storage parameters have to be defined:

begin
   ctx_ddl.create_preference('MY_TEXT_STORE', 'BASIC_STORAGE');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'K_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'R_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'N_TABLE_CLAUSE',
       'tablespace NEW_TBS');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'I_INDEX_CLAUSE',
       'tablespace NEW_TBS COMPRESS 2');
   ctx_ddl.set_attribute('MY_TEXT_STORE', 'P_TABLE_CLAUSE',
       'tablespace NEW_TBS');
end;
/

and then just drop and create index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column) 
indextype is ctxsys.context parameters(
'storage MY_TEXT_STORE');

For more information go to Oracle Text Application Developer's Guide

Cheers, Paweł

ORA-03297 on empty datafile

Today I've noticed strange behavior of Oracle 10gR2 (10.2.0.3) while shrinking datafile. I had empty datafile (no objects) and i couldn't resize it. I was getting ORA-03297: file contains used data beyond requested RESIZE value while DBA_EXTENTS show no rows for that file. I've discovered that there are objects in Recycle Bin that were located on that file. After purging recycle bin file was shirked successfully.
Let me make an example:

SQL> create tablespace tb_test
  2  datafile 'c:\temp\tb_test.dbf' size 100 K autoextend on;
Tablespace created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
    106496                                                                      
SQL> create table t_big tablespace tb_test as
  2  select a.* from all_objects a, dba_users for_cartesian;
Table created.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
        34                                                                      
SQL> select bytes from v$datafile where name = 'C:\TEMP\TB_TEST.DBF';
     BYTES                                                                      
----------                                                                      
  19963904

Now we have datafile quite big. Let's drop table and shrink file:

SQL> drop table t_big;
Table dropped.
SQL> select count(*) from dba_extents where tablespace_name='TB_TEST';
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
alter database datafile 'c:\temp\tb_test.dbf' resize 100 K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value 

So. even there is nothing reported in DBA_EXTENT, file cannot be shinked. Let's purge recyclebin and try again:

SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'c:\temp\tb_test.dbf' resize 100 K;
Database altered.

Now works fine! Don't forget to clear after tests :)

SQL> drop tablespace tb_test including contents and datafiles;
Tablespace dropped.

Cheers, Paweł