Oracle Database Discussions

In order to do this, you would need an export of the 9i db, ftp it to the server having 10g (in binary) and import the data locally. The other option is to create a db link to the 9i db and use the 'copy' command, but this can be very time consuming, since you would have to do this with all tables!!

Hi - Thank you for your response. I did manage to get an export of the data. When I try to import using the web interface provided with Oracle 10g (SQL, SQL Scripts, Import) I receive an error saying "Document Contains No Data". The file character set that is specified when importing is "Unicode UTF-8". Looking below I see two different character sets - would this be the reason it's not working?


Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning option JServer Release 9.2.0.5.0 - Production Enter array fetch buffer size: 4096 > 40960 Export file: USERS:[XXXX]EXPDAT.DMP > DOUG_EXPORT.DMP (2)U(sers), or (3)T(ables): (2)U > 3 Export table data (yes/no): yes > Compress extents (yes/no): yes > no Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)

I've answered my question I think. I tried using the Oracle Database Homepage with Internet Explorer and I received the following, different, error message:

"Your export file is not supported."

So, I believe the problem to be with the charset that is being used to export. Any comments are appreciated.

hi

if u have proper connectivity to ur remote machine then
create new entry in tnsnames.ora file in local machine (10g) for ur remote database and add ip address of ur remote machine in this file.

now no need to ftp the dump from remote machine

just start exp in ur local machine and put tnsnames alias with username/pass like
from local machine.

exp system/***@remote_db file= full=y

and then use imp


Thanks
Kuljeet Pal Singh

Thank you for the reply. I've tried the SQL you provided (filling in the appropriate values) using the Oracle 10g Express Enter SQL Command window and I receive an error saying that the SQL is invalid!

exp system/***@remote_db file= full=y

When I try this SQL I receive the same error:

copy from system/xxx @test -
to system/xxx @test -
create new_emp –
using select * from emp;

an SQL select statement works though. I'm confused. Any further help is greatly appreciated.

Hi,

if the tables are not too lage you can use the sqlplus COPY command.

Dim

I did use the copy command - please see above. Unfortunately, I receive an error when doing this. Is the copy command / imp / exp supported in 10g?

COPY is a SQL*Plus application command (it is not a SQL command).
imp and exp are operating system level commands, not SQL commands.

If you need to run imp/exp, you need to run the command prompt (cmd.exe) and run imp/exp from there.
if you need to run the COPY command, start sqlplus.exe and then use COPY command at the SQL prompt.

I'm getting closer. When entering the following command:

exp bla/bla@server

I get the following error:

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

I searched for this error but cannot find anything on it. I also tried the sqlplus (from the prompt) copy command, but it doesn't appear to recognize database links! Furthermore, when I try the copy command from within the 10g web gui, it doesn't recognize the copy command. Please help.

PLS-00302: component 'SET_NO_OUTLINES' must be declared

This error happens in 10.2 when you try to export from a lower version, which can't be done.

To use COPY command you don't need database links, you only need a TNS alias. In the following example I use copy on a 10.2 DB and copy a table from 9.2 :

SQL> select * from all_db_links;

no rows selected

SQL> select * from tab;

no rows selected

SQL> copy from scott@orcl create emp using select * from emp;
Enter FROM password:

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMP created.

   14 rows selected from scott@orcl.
   14 rows inserted into EMP.
   14 rows committed into EMP at DEFAULT HOST connection.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

SQL>                                                                                        

Remember that COPY command will be obsoleted in future releases of SQL*Plus.

Great, it works - thank you.

Hi,


SQL> copy from scott@orcl create emp using select * from emp;
Enter FROM password:
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMP created. 14 rows
selected from scott@orcl.
14 rows inserted into EMP.
14 rows committed into EMP at DEFAULT HOST
connection.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE
SQL>

But it can't create constraint on new table that are created from existing one by copy cmd.

Thanks
Kuljeet Pal Singh

But it can't create constraint on new table that are created from existing one by copy cmd.

Then ?

That's obvious, as in CTAS. Your suggestion of some days ago does not work, and actually the OP gets an error trying that (obviously).

So, the best way, as already suggested, would be to export from 9i on remote machine, transport export file on local machine (ftp or whatever else), and import into 10g. But for some reason that didn't succeed.

Apart from trying to make that to work (no reason why not), another way I can think of, is using DBMS_METADATA to extract DDL commands :

SQL> conn scott/tiger@orcl
Connected.
SQL> set long 100000
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"



SQL>                                                                           

Once executed that, use insert as :

SQL> insert into emp select * from emp@orcl_link;

Not very easy and not very fast, but not impossible.

Or, after using copy, one manually create indexes, constraints and so on

or

Install 9i Client on local machine, and use that to export.