export table subset
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: export table subset

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    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
    Last edited by parijat67; 03-21-2003 at 06:47 AM.

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.
    Last edited by ggnanaraj; 03-21-2003 at 06:48 AM.

  3. #3
    Join Date
    Dec 2001
    Posts
    120
    the table list has has almost 100 tables in it and the query holds good for all these 100 tables.

    Paul
    Last edited by parijat67; 03-21-2003 at 06:58 AM.

  4. #4
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: export table subset

    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.

    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
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Dec 2001
    Posts
    120
    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

  7. #7
    Join Date
    Dec 2001
    Posts
    120
    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

  8. #8
    Join Date
    Dec 2000
    Posts
    138
    Not necessarily, dont worry abt the next import as u do it now just add ignore=y.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width