step 1 : Source
=========
check for the tablespaces owned by the owner from dba_segments.
select distinct TABLESPACE_NAME from dba_segments where OWNER='SLTC_QUOTE';
TABLESPACE_NAME
------------------------------
SLTC_QUOTEDAT02
SLTC_QUOTEIDX03
SLTC_QUOTEDAT01
SLTC_QUOTEIDX01
SLTC_QUOTEIDX02
SLTC_QUOTEDAT03
6 rows selected.
SQL> execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT01,SLTC_QUOTEIDX01',true);
execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT02,SLTC_QUOTEIDX02',true);
execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT03,SLTC_QUOTEIDX03',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
step 2
=======
(Alter the TS from normal to read only)
SQL> select 'alter tablespace '||tablespace_name||' read only;'
from dba_tablespaces
where tablespace_name like '%QUOTE%';
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;'
----------------------------------------------------------
alter tablespace SLTC_QUOTEDAT01 read only;
alter tablespace SLTC_QUOTEIDX01 read only;
alter tablespace SLTC_QUOTEDAT02 read only;
alter tablespace SLTC_QUOTEIDX02 read only;
alter tablespace SLTC_QUOTEDAT03 read only;
alter tablespace SLTC_QUOTEIDX03 read only;
SQL> alter tablespace SLTC_QUOTEDAT01 read only;
alter tablespace SLTC_QUOTEIDX01 read only;
Tablespace altered.
SQL>
Tablespace altered. (for all the above mentioned tablepaces.)
step 3
=======
export using the following syntax
exp file=exp_quote.dmp log=exp_quote.log TRANSPORT_TABLESPACE=y TABLESPACES=SLTC_QUOTEDAT01,SLTC_QUOTEIDX01,SLTC_QUOTEDAT02,SLTC_QUOTEIDX02,SLTC_QUOTEDAT03,SLTC_QUOTEIDX03
username: sys as sysdba
Passwd:
step 4
=======
In target
to be on the safe side take the list of datafiles.
Make the tablespace offline
SQL> select name from v$database;
NAME
---------
STGCPQ7
SQL> select 'alter tablespace '||tablespace_name||' offline;'
from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2
'ALTERTABLESPACE'||TABLESPAC
--------------------------------------------------------------------------------
alter tablespace SLTC_QUOTEDAT01 offline;
alter tablespace SLTC_QUOTEIDX01 offline;
alter tablespace SLTC_QUOTEDAT02 offline;
alter tablespace SLTC_QUOTEIDX02 offline;
alter tablespace SLTC_QUOTEDAT03 offline;
alter tablespace SLTC_QUOTEIDX03 offline;
6 rows selected.
SQL> select distinct status from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%';
STATUS
---------
OFFLINE
SQL> select 'drop tablespace '||tablespace_name||' including contents and datafiles;'
from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2
'DROPTABLESPACE'||TABLESPACE
--------------------------------------------------------------------------------
drop tablespace SLTC_QUOTEDAT01 including contents and datafiles;
drop tablespace SLTC_QUOTEIDX01 including contents and datafiles;
drop tablespace SLTC_QUOTEDAT02 including contents and datafiles;
drop tablespace SLTC_QUOTEIDX02 including contents and datafiles;
drop tablespace SLTC_QUOTEDAT03 including contents and datafiles;
drop tablespace SLTC_QUOTEIDX03 including contents and datafiles;
SQL> select 'drop tablespace '||tablespace_name||' including contents and datafiles;'
from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%'; 2
no rows selected
step 5 (source)
==================
SQL> select count(*) from dba_data_files where tablespace_name like '%SLTC_QUOTE%';
COUNT(*)
----------
46
scp the datafiles
FILE_NAME BYTES/1024/1024
------------------------------------------------------- ---------------
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0101.dbf 2000
/brkcpq/u017/oradata/brkcpq/sltc_quotedat0121.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0102.dbf 2000
/brkcpq/u013/oradata/brkcpq/sltc_quotedat0129.dbf 1000
/brkcpq/u018/oradata/brkcpq/sltc_quotedat0126.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0103.dbf 2000
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0114.dbf 2000
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0116.dbf 2000.0625
/brkcpq/u017/oradata/brkcpq/sltc_quotedat0115.dbf 2000
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0117.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0104.dbf 2000
/brkcpq/u012/oradata/brkcpq/sltc_quotedat0118.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0119.dbf 2000
/brkcpq/u013/oradata/brkcpq/sltc_quotedat0127.dbf 2000
/brkcpq/u010/oradata/brkcpq/sltc_quotedat0128.dbf 1000
/brkcpq/u012/oradata/brkcpq/sltc_quotedat0120.dbf 2000
/brkcpq/u014/oradata/brkcpq/sltc_quotedat0130.dbf 500
/brkcpq/u010/oradata/brkcpq/sltc_quotedat0131.dbf 200
/brkcpq/u017/oradata/brkcpq/sltc_quotedat0122.dbf 2000
/brkcpq/u017/oradata/brkcpq/sltc_quotedat0123.dbf 2000.0625
/brkcpq/u017/oradata/brkcpq/sltc_quotedat0124.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0105.dbf 2000
/brkcpq/u015/oradata/brkcpq/sltc_quotedat0106.dbf 2000
/brkcpq/u014/oradata/brkcpq/sltc_quotedat0107.dbf 2000
/brkcpq/u010/oradata/brkcpq/sltc_quotedat0132.dbf 2000
/brkcpq/u011/oradata/brkcpq/sltc_quotedat0108.dbf 2000.0625
/brkcpq/u011/oradata/brkcpq/sltc_quotedat0109.dbf 2000.0625
/brkcpq/u013/oradata/brkcpq/sltc_quotedat0110.dbf 2000.0625
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0111.dbf 2000
/brkcpq/u010/oradata/brkcpq/sltc_quotedat0133.dbf 2000
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0112.dbf 2000.0625
/brkcpq/u016/oradata/brkcpq/sltc_quotedat0113.dbf 2000
/brkcpq/u013/oradata/brkcpq/sltc_quotedat0125.dbf 2000
/brkcpq/u012/oradata/brkcpq/sltc_quoteidx0101.dbf 500
cp1
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0101.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.
scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0121.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0102.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.
scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0129.dbf swbdb2s:/stgcpq3/u010/oradata/stgcpq3/.
cp2
scp /brkcpq/u018/oradata/brkcpq/sltc_quotedat0126.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0103.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0114.dbf swbdb2s:/stgcpq3/u011/oradata/stgcpq3/.
cp3
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0116.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.
scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0115.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0117.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0104.dbf swbdb2s:/stgcpq3/u012/oradata/stgcpq3/.
cp4
scp /brkcpq/u012/oradata/brkcpq/sltc_quotedat0118.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0119.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.
scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0127.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.
scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0128.dbf swbdb2s:/stgcpq3/u013/oradata/stgcpq3/.
cp5
scp /brkcpq/u012/oradata/brkcpq/sltc_quotedat0120.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.
scp /brkcpq/u014/oradata/brkcpq/sltc_quotedat0130.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.
scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0131.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.
scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0122.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.
scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0123.dbf swbdb2s:/stgcpq3/u014/oradata/stgcpq3/.
cp6
scp /brkcpq/u017/oradata/brkcpq/sltc_quotedat0124.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0105.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.
scp /brkcpq/u015/oradata/brkcpq/sltc_quotedat0106.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.
scp /brkcpq/u014/oradata/brkcpq/sltc_quotedat0107.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.
scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0132.dbf swbdb2s:/stgcpq3/u015/oradata/stgcpq3/.
cp7
scp /brkcpq/u011/oradata/brkcpq/sltc_quotedat0108.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u011/oradata/brkcpq/sltc_quotedat0109.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0110.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0111.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u010/oradata/brkcpq/sltc_quotedat0133.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0112.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u016/oradata/brkcpq/sltc_quotedat0113.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u013/oradata/brkcpq/sltc_quotedat0125.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
scp /brkcpq/u012/oradata/brkcpq/sltc_quoteidx0101.dbf swbdb2s:/trnswb/u016/oradata/stgcpq3/.
step 6
=======
import using the following syntax
imp_quote.par
userid='sys/ima91ne as sysdba'
file=exp_quote.dmp
log=imp_quote.log
TRANSPORT_TABLESPACE=y
datafiles= /stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0101.dbf,
/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0121.dbf,
/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0102.dbf,
/stgcpq3/u010/oradata/stgcpq3/sltc_quotedat0129.dbf,
/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0126.dbf,
/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0103.dbf,
/stgcpq3/u011/oradata/stgcpq3/sltc_quotedat0114.dbf,
/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0116.dbf,
/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0115.dbf,
/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0117.dbf,
/stgcpq3/u012/oradata/stgcpq3/sltc_quotedat0104.dbf,
/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0118.dbf,
/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0119.dbf,
/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0127.dbf,
/stgcpq3/u013/oradata/stgcpq3/sltc_quotedat0128.dbf,
/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0120.dbf,
/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0130.dbf,
/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0131.dbf,
/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0122.dbf,
/stgcpq3/u014/oradata/stgcpq3/sltc_quotedat0123.dbf,
/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0124.dbf,
/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0105.dbf,
/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0106.dbf,
/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0107.dbf,
/stgcpq3/u015/oradata/stgcpq3/sltc_quotedat0132.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0108.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0109.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0110.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0111.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0133.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0112.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0113.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quotedat0125.dbf,
/trnswb/u016/oradata/stgcpq3/sltc_quoteidx0101.dbf
imp parfile=imp_quote.par
after imp we need to do a no rows exp/imp to get procedures,packages etc
(Source)
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------ ------- ----------
SLTC_QUOTE LOB VALID 1
SLTC_QUOTE VIEW VALID 4
SLTC_QUOTE INDEX VALID 63
SLTC_QUOTE TABLE VALID 42
SLTC_QUOTE PACKAGE VALID 1
SLTC_QUOTE SEQUENCE VALID 1
SLTC_QUOTE JAVA CLASS VALID 1
SLTC_QUOTE JAVA SOURCE VALID 1
SLTC_QUOTE PACKAGE BODY VALID 1
(Target)
SQL> select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------ ------- ----------
SLTC_QUOTE LOB VALID 1
SLTC_QUOTE VIEW INVALID 4
SLTC_QUOTE INDEX VALID 63
SLTC_QUOTE TABLE VALID 42
SLTC_QUOTE SEQUENCE VALID 1
(source)
exp file=exp_quote_norows.dmp log=exp_quote_norows.log owner=sltc_quote rows=n
(target)
imp file=exp_quote_norows.dmp log=imp_quote_norows.log fromuser=sltc_quote touser=sltc_quote
SQL> select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;
OWNER OBJECT_TYPE STATUS COUNT(*)
------------------------------ ------------------ ------- ----------
SLTC_QUOTE LOB VALID 1
SLTC_QUOTE VIEW VALID 4
SLTC_QUOTE INDEX VALID 63
SLTC_QUOTE TABLE VALID 42
SLTC_QUOTE PACKAGE VALID 1
SLTC_QUOTE SEQUENCE VALID 1
SLTC_QUOTE JAVA CLASS VALID 1
SLTC_QUOTE JAVA SOURCE VALID 1
SLTC_QUOTE PACKAGE BODY VALID 1
compile invalids if any.
if we want the import to be finished soon we need to give statistics=none
make the tablespace read write in target as they will be read only
check for the tbs status in both source and target
step 7
======
make all the tablesapces in source and target read write.
select 'alter tablespace '||tablespace_name||' read write;' from dba_tablespaces where tablespace_name like 'SLTC_QUOTE%';
SQL> alter tablespace SLTC_QUOTEDAT01 read write;
alter tablespace SLTC_QUOTEIDX01 read write;
Tablespace altered.
SQL>
Tablespace altered.