| Export fails because the table owner name contains "$" [message #69881] |
Fri, 15 March 2002 15:36  |
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 #69917 is a reply to message #69909] |
Tue, 19 March 2002 14:48  |
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 #69933 is a reply to message #69909] |
Wed, 20 March 2002 12:40 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
