| selective exporting [message #127977] |
Thu, 14 July 2005 08:00  |
srraajesh
Messages: 63
Registered: May 2005
|
Member |
|
|
Hi Gurus
I want to export all the objects under a user from one database to another..unfortunately, the volume of data in exported database is too huge that it does not fit into the second database.
Iam trying to do a selective export. If I do with export table option, i can export only tables. How to include objects like views, triggers of the user being exported..
in a nutshell, barring some tables, I would like to export all the objects of a user.
can somebody help me with this..
srr
|
|
|
|
| Re: selective exporting [message #127983 is a reply to message #127977] |
Thu, 14 July 2005 08:21  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
--
--First do an import with only the tables you want.
--next do a regular import wihtout any rows and ignore=y
--now you have the tables you want with rows, the tables you DONT want will not have any rows.
--so no space issue.
--later drop the tables that you dont want.!
scott@9i > !exp scott/tiger owner=scott file=scott.dmp
Export: Release 9.2.0.4.0 - Production on Thu Jul 14 09:07:15 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.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 users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table ANOTHER_DEPT 4 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EXTERNAL_TABLE
. . exporting table TBBAS 3 rows exported
. 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 without warnings.
scott@9i > @clean_schema
Enter value for options_e_or_s: e
old 6: options:=upper('&options_E_or_S');
new 6: options:=upper('e');
Enter value for owner: SCOTT
old 12: where owner=upper('&owner')
new 12: where owner=upper('SCOTT')
drop TABLE SCOTT.TBBAS cascade constraints
drop TABLE SCOTT.EXTERNAL_TABLE cascade constraints
drop TABLE SCOTT.EMP cascade constraints
drop TABLE SCOTT.DEPT cascade constraints
drop TABLE SCOTT.ANOTHER_DEPT cascade constraints
drop SEQUENCE SCOTT.MYSEQUENCE
PL/SQL procedure successfully completed.
scott@9i > select * from cat;
no rows selected
scott@9i >!imp scott/tiger tables=(EMP,TBBAS) file=scott.dmp constraints=y grants=y indexes=y rows=y
Import: Release 9.2.0.4.0 - Production on Thu Jul 14 09:15:44 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table "EMP" 14 rows imported
. . importing table "TBBAS" 3 rows imported
Import terminated successfully without warnings.
scott@9i > !imp scott/tiger file=scott.dmp rows=n ignore=y fromuser=scott touser=scott
Import: Release 9.2.0.4.0 - Production on Thu Jul 14 09:16:50 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
Import terminated successfully without warnings.
scott@9i > select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
ANOTHER_DEPT TABLE
DEPT TABLE
EMP TABLE
EXTERNAL_TABLE TABLE
MYSEQUENCE SEQUENCE
TBBAS TABLE
6 rows selected.
scott@9i > select * from dept;
no rows selected
|
|
|
|
| Re: selective exporting [message #127987 is a reply to message #127983] |
Thu, 14 July 2005 08:36 |
srraajesh
Messages: 63
Registered: May 2005
|
Member |
|
|
Hi
Thanks..just to make it clear
The second import will not do anything on tables EMP and TBBAS as they were already imported right?.
And one more question. My ignore table list is small wheras (to be exported) list is huge. Is listing down all the tables the only way during import??
Srr
|
|
|
|
| Re: selective exporting [message #128011 is a reply to message #127987] |
Thu, 14 July 2005 10:07 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>The second import will not do anything on tables EMP and TBBAS as they were already imported right?.
Test it!.
It will do nothing.
When importing again, oracle will while about having the object already.
With IGNORE=y , those errors are suppressed.
And import will append the records.
WIth rows=n no records are appended.
>> Is listing down all the tables the only way during import??
Yes.
But you no need to list them out manually!.
You can write a script that queries database ( where table_name not in (mytable1,mytable2), spools the outut,and use this output.lst as a parfile during import.
SOmetime back i have posted a response with script that does the similiar work.PLease search for it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
