Help - Search - Members - Calendar
Full Version: exporting tables with names like DR$..
Oracle DBA Forums > Oracle > Oracle Forum
subodhs
Hi Gurus,
I am exporting a complete database, however, there are some tables whose name is like DR$TS_TEXT_IDX$I but they are not getting exported.

When i try to issue the export command, it fails saying the table does not exist. my export command is as below,
exp system@db_name tables=DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N file=test.dmp
Export: Release 9.2.0.4.0 - Production on Wed Jul 16 10:22:25 2008

Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DEFAULT_CXA429_SAM_DB
EXP-00011: DEFAULT_CXA429_SAM_DB.DR does not exist
Export terminated successfully with warnings.


QUOTE (subodhs @ Jul 16 2008, 10:23 AM) *
Hi Gurus,
I am exporting a complete database, however, there are some tables whose name is like DR$TS_TEXT_IDX$I but they are not getting exported.

When i try to issue the export command, it fails saying the table does not exist. my export command is as below,
exp system@db_name tables=DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N file=test.dmp
Export: Release 9.2.0.4.0 - Production on Wed Jul 16 10:22:25 2008

Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DEFAULT_CXA429_SAM_DB
EXP-00011: DEFAULT_CXA429_SAM_DB.DR does not exist
Export terminated successfully with warnings.

infact any table which contains DR$ name will not get exported.
dave
you need to escape the $ with a \

the shell thinks that $TS_TEXT_ID will be a variable and it isnt in this case
subodhs
Hi Dave,
thanks a lot for your help. I changed the exp command but still i am getting this error
EXP-00011: CARDS_AND_ATM_INTERNATIONAL_BA.DR\$BG_TEXT_IDX\$I does not exist
Export terminated successfully with warnings.
[lonora01:]/u00/EAdev>exp system@qcdb9i1 tables=CARDS_AND_ATM_INTERNATIONAL_BA.DR\$BG_TEXT_IDX\$I file=test.dmp

Export: Release 9.2.0.7.0 - Production on Wed Jul 16 11:18:35 2008

Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to CARDS_AND_ATM_INTERNATIONAL_BA
EXP-00011: CARDS_AND_ATM_INTERNATIONAL_BA.DR$BG_TEXT_IDX$I does not exist
Export terminated successfully with warnings.

please can you tell me if my command is not correct?
QUOTE (subodhs @ Jul 16 2008, 10:25 AM) *
Hi Gurus,
I am exporting a complete database, however, there are some tables whose name is like DR$TS_TEXT_IDX$I but they are not getting exported.

When i try to issue the export command, it fails saying the table does not exist. my export command is as below,
exp system@db_name tables=DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N file=test.dmp
Export: Release 9.2.0.4.0 - Production on Wed Jul 16 10:22:25 2008

Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DEFAULT_CXA429_SAM_DB
EXP-00011: DEFAULT_CXA429_SAM_DB.DR does not exist
Export terminated successfully with warnings.
infact any table which contains DR$ name will not get exported.
dave
looks ok

can you show that CARDS_AND_ATM_INTERNATIONAL_BA.DR$BG_TEXT_IDX$I does actually exist

Looks like an index to me from the name
subodhs
Yeah, it is a table, the ddl is as below,
CREATE TABLE "CARDS_AND_ATM_INTERNATIONAL_BA"."DR$BG_TEXT_IDX$I"
( "TOKEN_TEXT" VARCHAR2(64) NOT NULL ENABLE,
"TOKEN_TYPE" NUMBER(3,0) NOT NULL ENABLE,
"TOKEN_FIRST" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_LAST" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_COUNT" NUMBER(10,0) NOT NULL ENABLE,
"TOKEN_INFO" BLOB
) 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 "CARDS_ATM_DATA"
LOB ("TOKEN_INFO") STORE AS (
TABLESPACE "CARDS_ATM_DATA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

QUOTE (dave @ Jul 16 2008, 11:32 AM) *
looks ok

can you show that CARDS_AND_ATM_INTERNATIONAL_BA.DR$BG_TEXT_IDX$I does actually exist

Looks like an index to me from the name
burleson
Hi Snow,

>> exp system@db_name tables=DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N file=test.dmp

Ouch! I don't think it's a good idea to use dollar signs in table names, because in UNIX/Linix it is a special character. Also, I would use parens e.g. tables=(mytab).

In your case, try a double escape to negate the dollar sign, like this Linux example:

CODE
#!/bin/bash
output=‘sqlplus -s "/ as sysdba" <<EOF
       select distinct machine from v\\$session;
       exit
EOF



Does this work?

CODE
exp system@db_name tables=(DEFAULT_CXA429_SAM_DB.DR\\$TS_TEXT_IDX\\$N) file=test.dmp
subodhs
Hello Mr. Burlson,
Thanks a lot for your reply. I did try this, and i got the errors,
[lonora01:]/u00/EAdev>exp system@qcdb9i1 tables=(DEFAULT_CXA429_SAM_DB.DR\\$TS_TEXT_IDX\\$N) file=test.dmp
ksh: 0403-057 Syntax error: `(' is not expected.
[lonora01:]/u00/EAdev>exp system@qcdb9i1 tables=DEFAULT_CXA429_SAM_DB.DR\\$TS_TEXT_IDX\\$N file=test.dmp

Export: Release 9.2.0.7.0 - Production on Thu Jul 17 16:27:01 2008

Copyright © 1982, 2002, Oracle Corporation. All rights reserved.

Password:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8ISO8859P15 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DEFAULT_CXA429_SAM_DB
EXP-00011: DEFAULT_CXA429_SAM_DB.DR\\ does not exist
Export terminated successfully with warnings.
the tables are created by the application called Quality Center.


QUOTE (burleson @ Jul 16 2008, 04:59 PM) *
Hi Snow,

>> exp system@db_name tables=DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N file=test.dmp

Ouch! I don't think it's a good idea to use dollar signs in table names, because in UNIX/Linix it is a special character. Also, I would use parens e.g. tables=(mytab).

In your case, try a double escape to negate the dollar sign, like this Linux example:

CODE
#!/bin/bash
output=‘sqlplus -s "/ as sysdba" <<EOF
       select distinct machine from v\\$session;
       exit
EOF

Does this work?

CODE
exp system@db_name tables=(DEFAULT_CXA429_SAM_DB.DR\\$TS_TEXT_IDX\\$N) file=test.dmp
burleson
It's failing because your OS is defining the slash as a special character.

You never did say what OS you are on, but you must either:

- Use sensible table names
- Find a way to escape the dollar sign
- Disable the slash to the OS

Did you try double quotes?

CODE
exp system@qcdb9i1 tables=("DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N") file=test.dmp


In sum, find out what the dollar sign does on your OS, and read the OS docs on how to escape it.
chandra_vivek
QUOTE (burleson @ Jul 17 2008, 11:43 PM) *
It's failing because your OS is defining the slash as a special character.

You never did say what OS you are on, but you must either:

- Use sensible table names
- Find a way to escape the dollar sign
- Disable the slash to the OS

Did you try double quotes?

CODE
exp system@qcdb9i1 tables=("DEFAULT_CXA429_SAM_DB.DR$TS_TEXT_IDX$N") file=test.dmp


In sum, find out what the dollar sign does on your OS, and read the OS docs on how to escape it.



Hi,

No need to export table DR$TS_TEXT_IDX$I.
DR$<index_name>$I table is created when you create a CONTEXT index.

CHECK in following query.
select index_name,ityp_name from dba_indexes where index_name='TS_TEXT_IDX' ;

You just export and impot the table on which you have created index, this DR$% table will be created automatically if import goes fine.

Thanks,
Viv
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.