Alternatives to using the exp/imp utlilities 2

[newora]

I wonder if someone could please give me a pointer as to where to start loking as I undsertand what I need to do, I just can not see how to do it!!

I have been told that the DBA no-longer wants developers using the Imp /exp facility and instead to use SQL scripts for updating the database so that things can be rolled back easier and we do less harm!!

The thing I thus need to do if have a SQL script that takes the table definition and also dumps the data from the table into a file (using select I suppose). I then need another script that I can run on the production server that takes the table definition and data from the extract file I suppose and alows me to create / update the table definition and also load the new data into the table (using insert into values .. ) I suppose.

Thanks for you ideas.

 

[KenCunningham]

See Thread186-1182717 for some ideas. Instead of using imp to reload the data, perhaps sqlldr will do the trick?

 

[ddiamond]

Would your DBA be alright with creating a Test schema? You could use the imp command to load your data into a table on the Test schema, and then use insert / update scripts to move the data from the test schema to any of your production schemas. As long as the imp command is only used to load data into the Test schema, no damage can be done to production. And the exp utility is always safe since it does not modify data.

 

[newora]

Thanks fore the ideas guys - I will investiagte now - I now have a clearer picture anyway.

ddiamond - that is a good idea with regards to the test schema (I have been asking for a trest system for over a year LOL).

Thus when yu refre to the insert / update scripts, do you effectively mean using PL/SQL to loop down the database table on one schema and inssue an insert into statement on the live schema?

Thanks again

 

[LKBrwnDBA]

I agree with your DBA -- why should you be dropping and re-creating a table in production?
Is your design so flawed that requires constant revisions?
If this is the case maybe the designer/programmer should be fired.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[newora]

Hi LKBrwnDBA,

I have probaly not told you the complete story, but I am not recreating a table - all I am doing is adding rows into it.

I have added rows into the database table on my test server, which is on a completely seperate network and I wanted to make the updated table contents live after testing was completed OK. THe table is a system configuartion data table.

In all honesty the only way I kow of doing this, besides by using SQL pluus and inserting each row manually using an insert statement, was to use imp.

Do you feel that I am misisng something then?

 

[ddiamond]

Thus when yu refre to the insert / update scripts, do you effectively mean using PL/SQL to loop down the database table on one schema and inssue an insert into statement on the live schema?

If the amount of data is not so large that it will blow your rollback segments, you can use bulk inserts instead of P/L sql.

insert into production.MyTable (<field list>)
select <field_list> from test.MyTable

If you do have too much data for this to work, then you would have to do it in a PL/SQL loop and commit every so-many inserts.

As Ken mentioned, you could also use SqlLdr to load the data in from a flat file, but I don't know if your DBA would view that as any safer than using IMP. I'm also not sure how to create the flat file from oracle, although if you search this forum, you may find some examples of that as well.

Of course, all of these techniques are slower than IMP. IMP and EXP are the fastest way to move data in oracle.

 

[ddiamond]

newora,

Another alternative, albeit a little bit more code intensive, is you could write a small program in your language of choice that loops through each record from your source table an then inserts them into your destination table. Or if you prefer, this program could create a script file with lots of inserts, and then run that script file using sql*plus.

- Dan

 

[newora]

Thanks again diamond - I had actually thought of your second alternative in point of fact, but I thought that there was something available within PL/SQL.

I looked at creating a flat file using the object DDL report and produced a CSV file but still needed to load it back up on the production server and this is where I thought about developing an external program in c#

I have asked for a test database to be setup and will then use the bulk insert method - not too much data really 100 rows or so - it will probably take them about a year to get the terst database on line tough!!

Thanks again

 

[ddiamond]

Oracle comes with a utility call SQL*Loader that can load data into oracle from either fixed width or comma delimited flat files. So if you can create a CSV file, you are home free. Just check with your DBA to make sure he approves of SQL*Loader.

 

[ddiamond]

I have asked for a test database to be setup

I trust you meant test schema. For my test schema solution to work, the test schema needs to be in the same database as the production schema.

 

[LKBrwnDBA]

Or...
Create a database link and COPY the data:

COPY FROM uid/pw1 TO uid2/pw2@prod
  INSERT MyTable USING
  Select * From MyTestTable;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

LKBrown,

Nice tip. I didn't know oracle could link databases. That is probably safer than my schema idea.

 

[ddiamond]

If all you are talking about is a hundred rows or so, you can create an sql query that will generate a DML script that you can then run thru sql*plus. For example

SELECT 'INSERT INTO FONTS_NEW2' FROM DUAL UNION
SELECT 'SELECT '''|| FONT ||''' FROM DUAL UNION' FROM FONTS_NEW1;

The output of this query is

INSERT INTO FONTS_NEW2
SELECT 'ARIAL.TTF TEST' FROM DUAL UNION
SELECT 'ARIALBD.TTF TEST' FROM DUAL UNION
SELECT 'ARIALBI.TTF TEST' FROM DUAL UNION
SELECT 'ARIALI.TTF TEST' FROM DUAL UNION
SELECT 'ARIALN.TTF TEST' FROM DUAL UNION
SELECT 'ARIALNB.TTF TEST' FROM DUAL UNION
SELECT 'ARIALNBI.TTF TEST' FROM DUAL UNION
SELECT 'ARIALNI.TTF TEST' FROM DUAL UNION
SELECT 'ARIBLK.TTF TEST' FROM DUAL UNION
SELECT 'BOOKOS.TTF TEST' FROM DUAL UNION
SELECT 'BOOKOSB.TTF TEST' FROM DUAL UNION

Paste this output into a sql file. Replace the final union with a semi-colon, and then run it from sql*plus. In this example there were only a handful of records, but the same approach can work for hundreds of records.

 

[newora]

ddiamond - that idea is great and just what I was looking for - have another star - thank you.

 

[LKBrwnDBA]

Or using the database link you can directly insert the rows:

INSERT INTO FONTS_NEW2@PROD (FONT)
SELECT FONT FROM FONTS_NEW1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

LKBrwn,

Do you have to use the create database link command to use database links?

- Dan

 

[LKBrwnDBA]

Only once to create the link:

CREATE DATABASE LINK prod
CONNECT TO prodschema IDENTIFIED BY prodPW
USING 'prod';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb