I was thinking of altering the structure of unwanted tables.
Prince, March 22, 2002 - 3:13 pm UTC
Thanks Tom,
Since, I want to have the table structure for these tables to be there, I was thinking of importing all the tables without the data first. Then alter the structure of the tables, I don't want to have data. (say adding a NOT NULLable column). Then import the data. After the import is done, I will drop the column. But, here the problem is it spews lot of error messages, for every row.
Now, (after reading ur suggestion), I will import only those tables (without data) for which the data is not needed. Then import the export dump (the existing ones will fail).
Importing Just the data
sm, October 11, 2002 - 10:21 am UTC
Tom,
I am moving a database from windows to UNIX..and changing the tablespaces from DMT to LMT...
1.The day we go live...I want to refresh the data of the UNIX database with the WINDOWS (current production database)..
2.Currently, I am at the point where I am moving my TEST database (on UNIX) to another UNIX machine (PROD)...
I want to take the structure only...but get the data from current production (windows) database. How can this be done....
My approach..was to do an export with rows = n compress = n direct = y full=y of the test database...
then do an export of current production data
full=y rows=y constraints=n indexes=n compress=n
and then do the imports...
what do you think?
October 11, 2002 - 8:24 pm UTC
I think that would be bad -- the indexes would be all in place when you did the final import -- making it take many times longer then it should.
What is the advantage you see in doing it in a two step?
Why two steps?
sm, October 13, 2002 - 1:12 pm UTC
As we are moving from Windows to UNIX..we are also making another minor change :) Changing Dictionary Managed Tablespaces to Locally Managed tablespaces....so I took the Current Production (WINDOWS) database to a TEST database...changed all the tables and indexes to small, medium, large tablespaces...
Now we need to build a PROD database in UNIX...so I can:
1. Take a structure export of the TEST (UNIX) database...
2. Import structure into PROD (UNIX) database
3. Take an Export of just the data of Current PROD (WINDOWS) database
4. Disable all the constraints in PROD(UNIX)
5. Truncate all the tables in PROD(UNIX)
5. Do an import of current Production (WINDOWS) data into PROD (UNIX).
6. Enable all the constraints
Steps 1 and 2 does not have to take place the evening we are making the switch...bcause we do want to test the PROD database for couple of days for all the applications
Step 3,4, and 5 will take place the evening we make the switch... i.e just refreshing the data...
Export TEST structure:
rows = n compress = n direct = y full=y
Export PROD (WINDOWS) data:
full=y rows=y constraints=n indexes=n compress=n triggers=n
What do you think?
is there a better way..
Thanks a bunch....
October 14, 2002 - 7:37 am UTC
I would drop the indexes before the load, put the indexes back on after the load -- it'll be slightly faster that way (slightly meaning a whole bunch)
Drop all the indexes....
sm, October 14, 2002 - 9:57 am UTC
Tom,
Thanks for the reply...
Database is only 2.5 gig.
Do you think the speed gsin in this case will be significant to drop the indexes and recreate them...
Thanks...
October 14, 2002 - 10:40 am UTC
2.5 gig, probably not.
Must mean you really only have but one tablespace "small" ;)
won't it load data ?
A reader, April 29, 2005 - 11:56 am UTC
If I create a table before import,
(oralce 8.1.7.4, Sunos 5.8)
1.) the import errors out, and import exists from there.
2.) then I did ignore=y and it reported error
"object already exists" and than loaded the data in it
so it took same amount of time,
I don't wnat to load only 1 huge table,
pleaes guide..
April 29, 2005 - 12:05 pm UTC
imp has no way to filter "a table" out, you would have to list the tables you want to import.
thanks
A reader, April 29, 2005 - 12:12 pm UTC
I have about 120 table and I just want to escape 1
so what you are saying is I have to create a list of 119 table and do it ?
April 29, 2005 - 12:16 pm UTC
yes.
or create a view that looks like your table -- eg:
create or replace view mytable
as
select rpad('x',20,'x') first_name, 0 empno, sysdate hiredate
from dual;
create or replace trigger mytable_trigger
instead of insert ont mytable
begin
null;
end;
/
it'll still do the inserts, into the view, which will ignore them. So, if the goal is "just don't consume space", that will work as well -- but will have the overhead of executing the insert over and over and over...
Sharkey, July 26, 2023 - 2:12 pm UTC
My method may not be subtle but it is effective. Just edit the export file and replace any letter in the name of the table you want to not import in the INSERT command.
August 04, 2023 - 5:41 am UTC
Once you've got DataPump, you can exclude/include natively.