export table subset

Printable View

03-21-2003, 06:35 AM
parijat67

export table subset

hello everybody!

My query is regarding the export utility of oracle 8i and want to know your thoughts about it.

oracle version: 8.1.7.0.0

I am performing an export of a subset of table data by providing a parameter file which has a list of table names and a query statement.

*******parameter file(paulexp.par)is given below***********
buffer=10485760
tables=FR_OBJECTTYPE_PAGES,
FR_OBJECT_FRAMECOLUMNS,
FR_OBJECT_FRAMEROWS,
query=" where companyid=1 "
***********************************************

The export command that i am running from the DOS prompt is given below:-
exp paul/paul@gendb file=paulexp parfile=c:\testexp\paulexp.par

The export worked fine and the dump file got created without giving any errors. Now when the import is run to import from the dump file then oracle is only importing the table structures and the subset table data along with constraints and indexes for these tables.
It is not importing the other database objects like views,packages/procedures/sequences etc.

My questions :
1)Why isn't it importing the views/packages/procedures/sequences/triggers along with the table and the subset data? My application needs all these objects along with the subset data in the new user that i create using the import utility.
2)How can I solve this problem and export a subset of table data along with all the other database objects present in a user? In the help of export I didnt find any keyword to be mentioned explicitly to import the packages/views/procedures along with the list of table names and the query statement.

Thanks to all of you for your time and response.

Parijat Paul
03-21-2003, 06:45 AM
ggnanaraj

http://technet.oracle.com/docs/produ...ch01.htm#37997

Does the query u use hold good for all 3 tables that u export ?

BTW, you can create a log file when you do the export to know what is being done with respect to number of rows being exported.

HTH.
03-21-2003, 06:49 AM
parijat67

the table list has has almost 100 tables in it and the query holds good for all these 100 tables.

Paul
03-21-2003, 06:54 AM
ggnanaraj

Quote:

Restrictions for QUERY export parameter:

The parameter QUERY cannot be specified for full, user, or transportable tablespace mode exports.

The parameter QUERY must be applicable to all specified tables.

The parameter QUERY cannot be specified in a direct path export (DIRECT=Y)

The parameter QUERY cannot be specified for tables with inner nested tables.

You will not be able to determine from the contents of the export file whether the data is the result of a QUERY export.

RTM.
03-21-2003, 07:06 AM
adewri

Re: export table subset

Quote:

Originally posted by parijat67
1)Why isn't it importing the views/packages/procedures/sequences/triggers along with the table and the subset data? My application needs all these objects along with the subset data in the new user that i create using the import utility.

--Restrictions with using Querry in exp.

Quote:

Originally posted by parijat67

2)How can I solve this problem and export a subset of table data along with all the other database objects present in a user? In the help of export I didnt find any keyword to be mentioned explicitly to import the packages/views/procedures along with the list of table names and the query statement.

-- Export again with rows=n (this will not export any data, only the table structure along with user objects like packages, views etc...)
Do not user querry option this time.

exp username/password owner=username file=filename log=logname rows=n tables=table_names

Then do the import with ignore=y option

imp username/pasword file=filename log=logname fromuser=username touser=username ignore=y

HTH
03-21-2003, 07:13 AM
parijat67

thanks!

what could be the alternative to running a table mode import to import a subset of table data along with all the other database objects?

paul
03-21-2003, 07:46 AM
parijat67

well, there could be problems with the second import because of constraints. when the 2nd import is run the constraints (which were imported by the first imports and were enabled )will fail the import because of the wrong sequence of data inserts done by the 2nd import and that fails the parent child relationship. However this can be taken care of by not importing constarints during the first import and doing so in the 2nd import.

your thoughts?

paul
03-21-2003, 02:55 PM
dharma

Not necessarily, dont worry abt the next import as u do it now just add ignore=y.

HTH