DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: export import

  1. #1
    Join Date
    Dec 2002
    Posts
    110

    export import

    Hi

    we have 2 oracle 8.1.6 databases.
    One at the client side has the data which we want to keep intact.

    the database at the dev center has all the structural changes made to
    different table but with test data which is not important,

    i want to import the db at the dev center to client side to only apply the structural changes to the db at client side without tampering with the data at client side.


    regards
    Sushant

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Export import will not help in this case. What are the structural changes made to the dev db? Additional columns, constraints, indexes... If you have the scripts you can manually apply those changes to prod db while keeping the data.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    I would not opt for the export import option...

    Better use the alter table DDL to modify the table structures at the client site.

    I hope you are maintaining DCP's(Database Change Processes) at your development site i.e. all the commands used to change the table structure at the Dev site.

    You can put those commands in a scrit and then run them on the client site.

    What kind of changes have you made...
    Is it like addition of new columns or change of datatype(changing number to varchar or making NULL to NOT NULL or vice versa ) ??

    A DBA at development site should always maintain DCP made to the development databases...

    And if you want to user export import then...

    1. export all data from client site.
    2. export dev database with rows=n
    3. create a new database.
    4. import the dev database to the new database.
    That will create the required database structure.
    5. import the client data into the new database.
    This will import the required data.
    But here you may or may not(good for you) face problems if there is change in data type of columns or other such problems
    In such case you can import such tables into a dummy schema and the do a ITAS(insert into as select) from Dummy to actual tables.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by adewri

    And if you want to user export import then...

    1. export all data from client site.
    2. export dev database with rows=n
    3. create a new database.
    4. import the dev database to the new database.
    That will create the required database structure.
    5. import the client data into the new database.
    This will import the required data.
    But here you may or may not(good for you) face problems if there is change in data type of columns or other such problems
    In such case you can import such tables into a dummy schema and the do a ITAS(insert into as select) from Dummy to actual tables.

    HTH
    I was think along the same lines, except, DROP CASCADE the USER and recreate and reimport the data. But I'm not sure how the exported data would be handle if the Table Definition had changed... I'll check it out... You've got me curious.
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by grjohnson
    I was think along the same lines, except, DROP CASCADE the USER and recreate and reimport the data. But I'm not sure how the exported data would be handle if the Table Definition had changed... I'll check it out... You've got me curious.
    Import will fail if a columns is added or removed to/from the table.
    Import uses simple INSERT statement to import the rows.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    DBA 101... test after reading documentation and confirm results

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - Production

    SQL> create table test(a number, b varchar2(10));

    Table created.

    SQL> insert into test values (1, 'Greg');

    1 row created.

    SQL> insert into test values (2, 'Vader');

    1 row created.

    SQL> commit;

    > exp parfile=testexp.par

    Export: Release 8.1.7.3.0 - Production on Fri Feb 28 17:01:37 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - Production
    Export done in US7ASCII character set and US7ASCII NCHAR character set

    About to export specified tables via Conventional Path ...
    . . exporting table TEST 2 rows exported
    Export terminated successfully without warnings.


    SQL> alter table test add (c char(1));

    Table altered.

    SQL> desc test
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A NUMBER
    B VARCHAR2(10)
    C CHAR(1)

    SQL> alter table test drop column b;

    Table altered.

    SQL> desc test
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A NUMBER
    C CHAR(1)

    SQL> truncate table test;

    Table truncated.

    SQL> select count(*) from test;

    COUNT(*)
    ----------
    0

    > imp parfile=testimp.par

    Import: Release 8.1.7.3.0 - Production on Fri Feb 28 17:07:58 2003

    (c) Copyright 2000 Oracle Corporation. All rights reserved.


    Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - Production

    Export file created by EXPORT:V08.01.07 via conventional path
    import done in US7ASCII character set and US7ASCII NCHAR character set
    . . importing table "TEST"
    IMP-00058: ORACLE error 904 encountered
    ORA-00904: invalid column name
    Import terminated successfully with warnings.
    Last edited by grjohnson; 02-28-2003 at 02:16 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by grjohnson
    But I'm not sure how the exported data would be handle if the Table Definition had changed... I'll check it out... You've got me curious.
    In import its more like

    Code:
    insert into TABLE_NAME 
    (COLUMN_NAME1,COLUMN_NAME2,COLUMN_NAME3,COLUMN_NAME4,...)
    values (1,2,3,4,...);
    so it doesn't matter if the position of columns change or if new columns were added.

    except if the datatype has changed or if the newly added column has NOT NULL assigned to it...

    just try it and see
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    and if a column is dropped as you just showed
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  9. #9
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by adewri
    so it doesn't matter if the position of columns change or if new columns were added.
    Adding a column will indeed cause import to fail as demonstrated by grjohnson.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by SANJAY_G
    Adding a column will indeed cause import to fail as demonstrated by grjohnson.
    No not adding only dropping....
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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