Home » RDBMS Server » Server Utilities » Export fails because the table owner name contains "$"
Export fails because the table owner name contains "$" [message #69881] Fri, 15 March 2002 15:36 Go to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Hello,
I have select privilege on a table which I would like to export to a different DB. The export parameter "table= " does not recognize the synonym. So I tried with the owner_name.Table_name. The problem is the table owner_name contains the character "$" (Ex: abc$xyz). How do I go about?
Thanks,
Vijay
Re: Export fails because the table owner name contains "$" [message #69894 is a reply to message #69881] Mon, 18 March 2002 04:22 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
Try TABLES=("TABLENAME"). If you created the synonym with a $ sign then try creating the table without it then export it. You could also use a database link or the copy command.
Re: Export fails because the table owner name contains "$" [message #69909 is a reply to message #69881] Tue, 19 March 2002 04:09 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
oops I meant try creating the synonym without the $ sign.
Re: Export fails because the table owner name contains "$" [message #69917 is a reply to message #69909] Tue, 19 March 2002 14:48 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Hi Grant,
The table already has a synonym without "$", but the export command does not accept the synonym. It considers the synonym as a table name. It's forcing me to specify the table name with the owner name. The owner name has "$" in it. I am still struck here and exploring other options. Thanks for your reply.
Vijay
Re: Export fails because the table owner name contains "$" [message #69926 is a reply to message #69909] Wed, 20 March 2002 05:09 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
I am not sure why TABLES="(abc$xyz)" doesn't work. I would suggest doing a create table zzz as select * from abc$xyz; and doing an export on zzz.
Re: Export fails because the table owner name contains "$" [message #69933 is a reply to message #69909] Wed, 20 March 2002 12:40 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Hi Grant,
Thanks for you inputs. Yeah, I tried both TABLES="abc$xyz.Table_Name" and TABLES="Table_Name" OWNER="abc$xyz". They didn't work. Since it's a huge chunk of data the create table as select... also didn't work. But I managed to spool the data to a txt file and got it through sql loader. Thanks for your inputs anyway.
Re: Export fails because the table owner name contains "$" [message #69941 is a reply to message #69909] Thu, 21 March 2002 04:19 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
This was bugging me so I did some tests and found the problem. The following terminal sessions shows what I did. It is also good that a FULL export will pick up the table without doing anything special. When specifying a table with a "$" sign in it you have to escape it, i.e., TABLES=abc$xyz. You will also have to escape it for import. See below...

SQL> connect ghowell
Enter password:
Connected.
SQL> create table abc$xyz (test varchar2(12));

Table created.

SQL> insert into abc$xyz values('TEST');

1 row created.

SQL> commit;

Commit complete.

SQL> desc abc$xyz
Name Null? Type
----------------------------------------- -------- ----------------------------
TEST VARCHAR2(12)

SQL> select * from abc$xyz;

TEST
------------
TEST

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
$ <dev> /export/home/oracle/exports> exp ghowell/PASSWORD tables=abc$xyz

Export: Release 8.1.7.1.0 - Production on Thu Mar 21 09:02:33 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: GHOWELL.ABC does not exist
Export terminated successfully with warnings.
$ <dev> /export/home/oracle/exports> exp ghowell/PASSWORD tables="abc$xyz"

Export: Release 8.1.7.1.0 - Production on Thu Mar 21 09:02:48 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: GHOWELL.ABC does not exist
Export terminated successfully with warnings.
$ <dev> /export/home/oracle/exports> exp ghowell/PASSWORD tables="(abc$xyz)"

Export: Release 8.1.7.1.0 - Production on Thu Mar 21 09:03:02 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00011: GHOWELL.ABC does not exist
Export terminated successfully with warnings.
$ <dev> /export/home/oracle/exports> exp ghowell/PASSWORD tables=abc$xyz

Export: Release 8.1.7.1.0 - Production on Thu Mar 21 09:03:17 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ABC$XYZ 1 rows exported
Export terminated successfully without warnings.
$ <dev> /export/home/oracle/exports> exp_dev_full

Export: Release 8.1.7.1.0 - Production on Thu Mar 21 09:03:51 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
JServer Release 8.1.7.1.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
.
.
.
. about to export GHOWELL's tables via Conventional Path ...
. . exporting table ABC$XYZ 1 rows exported
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table DUMMY 1 rows exported
. . exporting table EMP 14 rows exported
. . exporting table IF_LINE 0 rows exported
. . exporting table SALGRADE 5 rows exported
. . exporting table TEST2 4 rows exported
.
.
.
. exporting referential integrity constraints
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting triggers
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting user history table
. exporting default and system auditing options
. exporting statistics
Export terminated successfully without warnings.
Previous Topic: importing table to different table spaces
Next Topic: sqlplushelp
Goto Forum:
  


Current Time: Mon Dec 23 02:57:34 CST 2024