| Table Level Export [message #198558] |
Tue, 17 October 2006 11:51  |
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  |
 |
Mahesh Rajendran
Messages: 10707
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.
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 |
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 #201779 is a reply to message #201770] |
Mon, 06 November 2006 12:52 |
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 #201793 is a reply to message #201781] |
Mon, 06 November 2006 14:54 |
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 #201956 is a reply to message #201799] |
Tue, 07 November 2006 10:18 |
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 lineexp 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 #201964 is a reply to message #201961] |
Tue, 07 November 2006 10:33 |
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 #201967 is a reply to message #201966] |
Tue, 07 November 2006 11:01 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh
I tried with
PROC_DT \ > and also with
PROC_DT \ <
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 |
|
Mahesh Rajendran
Messages: 10707
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 <
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 |
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 |
|
Mahesh Rajendran
Messages: 10707
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
