Home » RDBMS Server » Server Utilities » Table Level Export
Table Level Export [message #198558] Tue, 17 October 2006 11:51 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

I have 170 tables in my schema, out of which 10 are partitioned tables,
want to take export of all tables (160 ) and from 10 partitioned tables only 12 partitions of last 1 year since i have monthly partitions (P_200510) to (P_200610).
Partition names are same in all the tables eg. P_YYYYMM
How can i take 160 tables + 10 partitioned tables only 12 partitions export in a single Exp statement,

If it is in 2 steps then how to import, if we import 1 .dmp file and at end if it starts building constraints
it will fail since data is not there , because we didn't import the 2 .dmp file yet.How should i approach this.


Another question is i want to take an export of 10 tables from Schema X (table Level) with rows & Indexes only (no constraint )
and import in Schema Y, default table space for X is TS_01_X and for Schema Y is TS_01_Y.
How should i do this to avoid failure while import since tablespace names are different.

Oracle Version is 9i Rel 2 on solaris 5.9

Thanks For Help and Suggestions.

[Updated on: Tue, 17 October 2006 11:57]

Report message to a moderator

Re: Table Level Export [message #198563 is a reply to message #198558] Tue, 17 October 2006 12:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>> Another question is i want to take an export of 10 tables from Schema X ......

In 9i, fromuser/touser clause will take care of it. Import indexes seperately (get the ddl and change the tablespace).
Make sure you have no RESOURCE role granted to the those users.
or
Extract ddl of both tables/indexes , change the tablespace,run it against source. Now you have schema with tables/indexes , but no data.
Import with ignore=y

>> I have 170 tables .....

In 9i, you can only include specifc objects.So you have to write a simple sql script the gives the names all tables you want (no partitioned tables).
use it alongwith PARFILE option.
Do the same for partitioned tables.

>>If it is in 2 steps then how to import, ...
Import without constraints/grants. Let all the data get imported.
Import again with ignore=y. Now the constraints will be created.

It looks very confusing. But it works. Try it a couple of times.
I frequently move data around in a schema with 1800 tables,5000+ indexes and god_knows_how_many_constraints.Smile
That said, i am not a big fan this process, but it works.
Re: Table Level Export [message #201768 is a reply to message #198563] Mon, 06 November 2006 11:55 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

One small doubt in Export,I was taking User level Export and i Issued this on Sun 5.10 o/s, Oracle 9i Rel 2
exp system/abc owner=app_user compress=n file=APPUSER_6NOV06.dmp log=APPUSER_6NOV06.log

and the last part of logfile is
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

What else i should look or worry about "terminated successfully with warnings."

Thanks

[Updated on: Mon, 06 November 2006 11:56]

Report message to a moderator

Re: Table Level Export [message #201770 is a reply to message #201768] Mon, 06 November 2006 12:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And what might be those warnings? It should be displayed along with.
I guess it is to do with the stats. Try STATISTICS=none during export.
Re: Table Level Export [message #201779 is a reply to message #201770] Mon, 06 November 2006 12:52 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh,

This time Statistics=None " no warnings "
Just to know what is the reason,
Another doubt does previous exports done with "successfully completed with warnings " are usable or not.

Thanks Again
Re: Table Level Export [message #201781 is a reply to message #201779] Mon, 06 November 2006 12:57 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch01.htm#1005905
By default Exp will export the table level statistics which will show up as 'questionable statistics' and hence the warning.
With statistics=none, you are not exporting the stats.
More information in documentation.

>>Another doubt does previous exports done with "successfully completed with warnings " are usable or not.
It is usable.
Re: Table Level Export [message #201793 is a reply to message #201781] Mon, 06 November 2006 14:54 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for Info.

I was trying to do table level with Query option with this line it fails both at command prompt and Unix

exp scott/tiger@local tables=(TRANS,SALES) QUERY=\"WHERE TRANS_DT >=\'01-oct-2006'\" file= tbl_exp.dmp log=tbl_exp.log


at logfile shows
About to export specified tables via Conventional Path ...
. . exporting table                            TRANS
. . exporting partition                       P_199712
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
. . exporting table                      SALES
. . exporting partition                       P_199712
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings..

At unix Prompt same line it shows
6'\" file= tbl_exp.dmp log=tbl_exp.log
ksh: syntax error: `(' unexpected 


If i give 1 table at o/s prompt it never comes out i have to Press ctrl+c
xp.dmp log=tbl_exp.log
> 
> 
> 
> ^C


Please tell me what is wrong in syntax .
These are partitioned tables.

Thanks



[Updated on: Mon, 06 November 2006 15:02]

Report message to a moderator

Re: Table Level Export [message #201799 is a reply to message #201793] Mon, 06 November 2006 16:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Apply a proper date format.
oracle@mutation#exp scott/tiger tables=emp query=\"where hiredate \> TO_DATE\(\'09-JUN-1981 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"

Export: Release 9.2.0.7.0 - Production on Mon Nov 6 17:32:03 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP          8 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
Re: Table Level Export [message #201956 is a reply to message #201799] Tue, 07 November 2006 10:18 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh for help,
I am still missing some format code it gives error,i am using this line
exp scott/tiger TABLES=BAT QUERY=\"WHERE PROC_DT \ >= to_date\(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= tbl_exp.dmp log=tbl_exp.log

and the error is
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                       P_199712
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.

Log file shows
About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                       P_199712
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.

Please help me in correcting my date format.

Thanks
Re: Table Level Export [message #201961 is a reply to message #201956] Tue, 07 November 2006 10:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Sure you are executing this from unix?

[Updated on: Tue, 07 November 2006 10:30]

Report message to a moderator

Re: Table Level Export [message #201964 is a reply to message #201961] Tue, 07 November 2006 10:33 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
thanks for follow up,
Here is the o/s version
Last login: Tue Nov 07 2006 10:07:52 -0600 from 10.139.42.221
Sun Microsystems Inc.   SunOS 5.7       Generic October 1998
You have new mail.
Sun Microsystems Inc.   SunOS 5.7       Generic October 1998
You have new mail.
oracle@apploisd/export/home/oracle>exp scott/tiger TABLES=BAT QUERY=\"WHERE PROC_DT \ >= to_date\(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= tbl_exp.dmp log=tbl_exp.log


Thanks Again.

Re: Table Level Export [message #201966 is a reply to message #201964] Tue, 07 November 2006 10:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Cannot reproduce your case even with partitions.
oracle@mutation#exp scott/tiger TABLES=BAT QUERY=\"WHERE hiredate\<= to_date \(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= tbl_exp.dmp log=tbl_exp.log

Export: Release 9.2.0.7.0 - Production on Tue Nov 7 11:52:10 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                             P1          3 rows exported
. . exporting partition                             P2         11 rows exported
Export terminated successfully without warnings.

Only difference i see the way you are using the escape characters. They are having unwanted blanks. In unix within double quotes, they are sensitive.
Yours
PROC_DT \ >=

Mine
hiredate\<=


Re: Table Level Export [message #201967 is a reply to message #201966] Tue, 07 November 2006 11:01 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh

I tried with
PROC_DT \ >  and also with
PROC_DT \ < 
also not working, confused where i am getting wrong with date format,and same error at command prompt also.

will still look into format where i did wrong, if you find please let us know.


Thanks

[Updated on: Tue, 07 November 2006 11:03]

Report message to a moderator

Re: Table Level Export [message #201970 is a reply to message #201967] Tue, 07 November 2006 11:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nope. It is NOT WHAT I MEANT.
This is WRONG. There is an unwanted space between \ and <
PROC_DT \ <

oracle@mutation#exp scott/tiger TABLES=BAT QUERY=\"WHERE hiredate\<= to_date \(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= tbl_exp.dmp log=tbl_exp.log

Export: Release 9.2.0.7.0 - Production on Tue Nov 7 12:17:17 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                             P1          3 rows exported
. . exporting partition                             P2         11 rows exported
Export terminated successfully without warnings.
oracle@mutation#exp scott/tiger TABLES=BAT QUERY=\"WHERE hiredate\ <= to_date \(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= tbl_exp.dmp log=tbl_exp.log

Export: Release 9.2.0.7.0 - Production on Tue Nov 7 12:17:24 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                             P1
EXP-00056: ORACLE error 920 encountered
ORA-00920: invalid relational operator
Export terminated successfully with warnings.


Similarly check all ther escape characters.

[Updated on: Tue, 07 November 2006 11:15]

Report message to a moderator

Re: Table Level Export [message #201983 is a reply to message #201970] Tue, 07 November 2006 13:23 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh and sorry for taking your time on this mess up thing.
I copied your line but still same thing.
C:\>exp scott@tiger@local TABLES=BAT QUERY=\"WHERE PROC_DT\>= to_date \(\'01-OCT-2006 00:00:00 \',\'DD-MON-YYYY hh24:MI:SS\'\)\"  file= C:\tbl_exp.dmp C:\tbl_exp.log

Export: Release 9.2.0.1.0 - Production on Tue Nov 7 13:17:17 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character se
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            BAT
. . exporting partition                       P_199712
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.


Thanks

[Updated on: Tue, 07 November 2006 13:24]

Report message to a moderator

Re: Table Level Export [message #201993 is a reply to message #201983] Tue, 07 November 2006 14:46 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the case history.
Was the database recently migrated/upgraded/patched.
Any specific reason for using this characterset WE8MSWIN1252?
Can you reproduce the from other clients installations also ( try from both *nix and windows clients)?
Are the clients/server versions differ (apparantly, seems yes).
Did you also try with other NLS settings?

But before looking into all these,
Did you also try the same with parfile?
exp user/pass@service parfile=par.par
let this par.par file contain all the other command line parameters.


Previous Topic: Loading data into multiple tables
Next Topic: UTl_File - Problem - Records written intermittently
Goto Forum:
  


Current Time: Fri Dec 02 14:21:06 CST 2016

Total time taken to generate the page: 0.15710 seconds