How do I use selected columns with query parameter in Data Pump (Yes, External Tables).

1. Create a test user and grants:

sqlplus / as sysdba

create user test identified by test
default tablespace users temporary tablespace temp;

grant connect , resource to test;
grant create any directory to test;


2. Create a table and insert some records:

connect test/test

create table cur_objs(obj_id number, obj_name varchar2(128), cdate date, status varchar2(7));

insert into cur_objs
select object_id, object_name, created, status from all_objects;
commit;

insert into cur_objs
select * from cur_objs;
commit;

SQL> select count(1) from cur_objs;

COUNT(1)
----------
69020

SQL> SELECT count(1) FROM cur_objs
WHERE status = 'VALID'
AND obj_name like '%DBMS%';
2 3
COUNT(1)
----------
760


3. Create an Oracle directory and the data pump external table:

-- Check if the directory is valid:
$ cd /backup_ORADB/oracle/ORADB/data_ext
$ ls -lt
total 0
$

SQL> CREATE DIRECTORY data_ext_dir AS '/backup_ORADB/oracle/ORADB/data_ext';

Directory created.

SQL> DROP TABLE extract_cur_objs;


Table dropped.

SQL> CREATE TABLE extract_cur_objs
2 ORGANIZATION EXTERNAL
3 (TYPE ORACLE_DATAPUMP
4 DEFAULT DIRECTORY data_ext_dir
5 LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
6 )
7 PARALLEL 3 REJECT LIMIT 0
8 AS
9 SELECT obj_id, obj_name FROM cur_objs
10 WHERE status = 'VALID'
11 AND obj_name like '%DBMS%';

Table created.

SQL> !ls -lt [eE]*
-rw-r----- 1 oracle dba 24576 Dec 29 14:00 extract_cur_objs1.exp
-rw-r----- 1 oracle dba 12288 Dec 29 14:00 extract_cur_objs3.exp
-rw-r----- 1 oracle dba 16384 Dec 29 14:00 extract_cur_objs2.exp
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10019.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10021.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_10017.log
-rw-r----- 1 oracle dba 41 Dec 29 14:00 EXTRACT_CUR_OBJS_9997.log

SQL>


4. Attaching the files:

DROP TABLE attach_files_cur_objs;

CREATE TABLE attach_files_cur_objs(
obj_id number,
obj_name varchar2(128))
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY data_ext_dir
LOCATION ('extract_cur_objs1.exp', 'extract_cur_objs2.exp', 'extract_cur_objs3.exp')
)
PARALLEL 3 REJECT LIMIT 0;


Side Note: impdp utility may not work with these files.

Database Migration from Sun(File system) to Linux(ASM and RAC)

a. Create a tablespace MIG_SUN_TO_LINUX and a object:

sqlplus / as sysdba
CREATE TABLESPACE MIG_SUN_TO_LINUX DATAFILE '/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf' SIZE 100M;

CONNECT ORADBDEV1/xxxxx@ORADB
DROP TABLE EMP;
CREATE TABLE EMP (ENO NUMBER(10), ENAME VARCHAR2(50))
TABLESPACE MIG_SUN_TO_LINUX;

INSERT INTO EMP VALUES(10000001, 'VJ');
INSERT INTO EMP VALUES(10000002, 'ASHRAY');
COMMIT;


b. Use TTS procedure to move the tablesapce files:

sqlplus / as sysdba
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MIG_SUN_TO_LINUX', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;

ALTER TABLESPACE MIG_SUN_TO_LINUX READ ONLY;

exp USERID=\"sys/xxxxx@oradb AS SYSDBA\" TRANSPORT_TABLESPACE=y TABLESPACES=MIG_SUN_TO_LINUX FILE=MIG_SUN_TO_LINUX.dmp

scp MIG_SUN_TO_LINUX.dmp atlxd215:/export/home/oracle


c. Use RMAN to convert the datafiles from SUN to LINUX format:

rman target / nocatalog

CONVERT TABLESPACE MIG_SUN_TO_LINUX
TO PLATFORM = "Linux IA (64-bit)"
DB_FILE_NAME_CONVERT = "/u01_ORADB/oradata/ORADB/mig_sun_to_linux_01.dbf" , "/backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf"
PARALLELISM=5;

Side note: If your linux is 32bit? Change TO PLATFORM = "Linux IA (64-bit)" above command to TO PLATFORM = "Linux IA (32-bit)"


d. ftp or scp the MIG_SUN_TO_LINUX tablespace file to destination RAC server(eg:. copy the datafiles under /u00/mig_area/oradb directory):

scp /backup_ORADB/oracle/ORADB/mig/mig_sun_to_linux_01.dbf atlxd215:/u00/mig_area/oradb


e. copy file from disk to ASM using DBMS_FILE_TRANSFER:

-- Create the source and destination directory objects.
CREATE OR REPLACE DIRECTORY sour_db_files_dir AS '/u00/mig_area/oradb';
CREATE OR REPLACE DIRECTORY dest_db_files_dir AS '+ORADB_DATA01_DG/oradb/datafile';

-- Copy the file to ASM disk group.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'sour_db_files_dir',
source_file_name => 'mig_sun_to_linux_01.dbf',
destination_directory_object => 'dest_db_files_dir',
destination_file_name => 'mig_sun_to_linux_01.dbf');
END;
/


f. Attach the file to target DB (Linux) - imp:

imp USERID=\"sys/xxxxx@oradb AS SYSDBA\" FROMUSER=ORADBDEV1 TOUSER=ORADBTEST1 TRANSPORT_TABLESPACE=y DATAFILES='+ORADB_DATA01_DG/oradb/datafile/mig_sun_to_linux_01.dbf' TABLESPACES=MIG_SUN_TO_LINUX FILE=/export/home/oracle/MIG_SUN_TO_LINUX.dmp


g. Make the tablespace to read write:

ALTER TABLESPACE MIG_SUN_TO_LINUX READ WRITE;

How to convert partition table into Interval Partitioning table in 11g

1. Create dummy tablespaces and user:
create tablespace testa11_data datafile size 10m autoextend on maxsize 2000m;
create tablespace testa11_index datafile size 10m autoextend on maxsize 2000m;

create tablespace testb11_data datafile size 10m autoextend on maxsize 2000m;

create user vj identified by vj
default tablespace testa11_data;
grant connect, resource to vj;


2. Create a partition table and local index:
connect vj/vj
drop table emp;
create table emp(eno number(1), ename varchar2(10), sal number(6), join_date date)
PARTITION BY RANGE (join_date) (
PARTITION emp_dec06 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_jan07 VALUES LESS THAN (TO_DATE('01-FEB-2007','DD-MON-YYYY'))
TABLESPACE testa11_data,
PARTITION emp_feb07 VALUES LESS THAN (TO_DATE('01-MAR-2007','DD-MON-YYYY'))
TABLESPACE testa11_data);

create index idx1_eno_emp on emp(eno)
local
tablespace testa11_index;


3. Check and note the partition and tablespace names:
select * from emp;

col table_name for a20;
col partition_name for a20;
col tablespace_name for a20;
col index_name for a20;
select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';


4. Insert data:
insert into emp values(1,'VJ',100,'20-JAN-2007');
insert into emp values(2,'BJ',200,'20-FEB-2007');
insert into emp values(3,'JJ',300,'20-MAR-2007'); -- this insert will fail, because there is no March data partition.
insert into emp values(4,'PJ',400,'20-APR-2007'); -- this insert will fail, because there is no April data partition.
commit;


5. -- 11g trick
alter table emp set interval(numtoyminterval(1,'MONTH')); -- Setting up the interval for the table.
alter table emp set store in (testb11_data); -- All upcoming partitions data will be assigned to new tablespace (testb11_data).

insert into emp values(3,'JJ',300,'20-MAR-2007');
commit;

insert into emp values(4,'PJ',400,'20-APR-2007');
commit;

select table_name, partition_name, tablespace_name
from dba_tab_partitions
where table_name = 'EMP';

select index_name, partition_name, tablespace_name
from dba_ind_partitions
where index_name = 'IDX1_ENO_EMP';


create tablespace testb11_index datafile size 10m autoextend on maxsize 2000m;

alter index idx1_eno_emp modify DEFAULT ATTRIBUTES tablespace testb11_index; -- Assigning new tablespace for new index partitions.

insert into emp values(6,'RJ',500,'20-JUN-2007');
commit;


Summary: Great 11g partition feature improvement. The thing I like about this is, not only creates new table and index partitions and also allocates existing index tablespace to index partitions. No rebuild index partitions required to modify the correct tablespace!!!