| copy table from one instance to another [message #178600] |
Wed, 21 June 2006 14:20  |
AlinaC
Messages: 45
Registered: November 2005
Location: India
|
Member |
|
|
I need to copy some tables from Oracle9i instance to 10G and I have written the sctipt as-
set pagesize 0
set timing off
set echo off
set feed off
set verify off
set head off
set line 200
col X for a150 word_wrapped
col Y for a200 word_wrapped
accept TABLE_NAME prompt "Enter any TABLE_NAME or HIT ENTER for ALL TABLES listed under current ownership:"
accept UPSD prompt "Enter username/passsword@source_database_name:"
accept UPDD prompt "Enter username/passsword@destination_database_name:"
set trimspool on
spool c:\data_copy.sql
def TABLE_NAME = '&&TABLE_NAME%';
select
'-- '||
rownum||
' Copy data to new '||
A.TABLE_NAME
X,
'copy from' ||' &UPSD'||' to '||' &UPDD'||
' replace ' || A.TABLE_NAME || ' using select * from' || ' '|| A.TABLE_NAME
from ALL_TABLES A where A.OWNER like USER
and A.TABLE_NAME like upper('&TABLE_NAME')
order by A.BLOCKS;
spool off
set termout on
set verify on
prompt
prompt Check the spool file at c:\data_copy.sql
prompt You may edit and run this file in destination database as the owner
prompt
set feed on
set verify on
set head on
set pagesize 64
undef TABLE_NAME
But using the script I'm unable to copy those tables having CLOB datatype as it is not supported. Is there any other way to copy those tables having CLOB datatype?
Regards,
Alina
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
