-
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
-
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"
-
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."
-
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
-
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"
-
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
-
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."
-
and if a column is dropped as you just showed
Amar
"There is a difference between knowing the path and walking the path."
-
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"
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|