ORA-29339 and Configure db_nk_cache_size

While importing TTS we found below error:
ORA-29339: tablespace block size 32768 does not match configured block sizes

Then, on target DB we execute below:

SQL> show parameter db_32k_cache_size;

NAME TYPE VALUE
------------------------------------
db_32k_cache_size big integer 0

here value=0, so, (32768/1024=)32K cache in not congigured. Inorder to import this TTS, first we need to configure this.

According to oracle doc, before configuring this we need to consider below:
- We cannot configure db_nk_cache_size in db_block_size=n. We cannot configure db_32k_cache_size in db_block_size=32768.[ORA-00380]
- This parameter cannot be set to zero if there are any online tablespaces with an nK block size
- We need to consider Operating system-specific block size restrictions[ORA-00382]
We cannot set DB_32K_CACHE_SIZE if the operating system's maximum block size is less than 32 KB.
Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.
- For 10g and later, nK-cache values are not automatically managed by Oracle and settings for them are subtracted from the total SGA_TARGET before the remainder is distributed amongst the other, automatically managed, SGA components (like the default cache, shared, large and java pools).
So, if SGA_TARGET=8GB and DB_32K_CACHE_SIZE=1GB, then 7GB of memory is available for distribution amongst the automatically-managed SGA components.
- For for 9i and earlier, we need to readjust other memory component size within sga_max_size [ORA-00384]

Our DB was a 9i DB, so we first shirnk db_cache_size by 50M and then set db_32k_cache_size=50M:

SQL> alter system set db_cache_size=158M scope=both;

SQL> alter system set db_32k_cache_size=50M scope=both;

SQL> create pfile from spfile;

exp using query and imp in another DB with different table & tablespace

Yesterday I was requested to export some records from production to test DB using a query. There are two options:

- Do it with and exported CSV fole and sqlloader but there were about 300 columns in that table.I dont like this plan (arranging 300 columns in control file and exporting CSV file).
- Export using emp and use "query" parameter to export.

I choose 2nd option. But thare are also some limitations:

- My source and target table have different names.
- Source & destination tablespace have different names.


After some experiment & googling I got below solutions:

- As the terget DB is a test DB then we can temporarily change the name of the target table.
- Use "ignore=y" parameter to eliminate tablespace problem.


Assume,

- Production DB name is PROD. Table name is tableA (having partition ZERO & NONEZERO) under user tst (source)

- Test DB name is TEST. Table name is tableB (having partition ZERO & NONEZERO) under user prd (target)


My export par file(exp.par) was as below:

tables=prd.tableA:NONZERO
query="where customer_id in (8, 2, 82, 994) and start_time_timestamp > to_date('24sep09235959', 'ddmonyyhh24miss')"
statistics=none
log=exp.log
file=exp.dmp


then I used below command to export:

exp prd@PROD parfile=exp.par

My impport par file(imp.par) was as below:

fromuser=prd
touser=tst
tables=tableA:NONZERO
ignore=y
log=imp.log
file=exp.dmp


Now the importent things are:

- using ignore=y in parfile and
- To change the target table name tableB to tableA

- Make a note about objects depending on tableB, may become invalid


Then,

$sqlplus tst@test

SQL>Alter table tableB rename to tableA;


Now execute below command to import from a privileged user (to import another schema's data):

imp privileged_user@TEST parfile=imp.par


Then,

sqlplus tst@test

Alter table tableA rename to tableB;


Recompile all the objects became invalid because of rename operation on table tableB in TEST DB.


done!!