Comments (9)
There's already something like that, see MODIFY_STRUCT in configuration file.
For example if you have a table in Oracle named table1 with 3 columns named col1, col2 and col3 and you just want to export data from col1 and col3:
MODIFY_STRUCT TABLE1(col1,col3)
It doesn't matter if the col2 column exist or not in the destination table.
You can set a list of such modified tables separated by space.
Regards,
from ora2pg.
According to the documentation this only works for data export - not ddl export?
The unused columns might have an NOT NULL so I can't read the data into postgresql if a column i striped from the data but not ddl.
Another question: can this option be used to rearrange the order of columns in postgresql? I learned during migration that I get a more efficient storage if small columns (like boolean columns) are next to each other.
So can I rearrange an oracle table like mytable(number(19,0) c1, number(1,0) c2, varchar2(3) c3, number(1,0) c4) into a postgresql table like mytable(integer c1, varchar(3) c3, boolean c2, boolean c4) with MODIFY_STRUCT mytable(c1, c3, c2, c4)?
from ora2pg.
When I said "It doesn't matter if the col2 column exist or not in the destination table.", obviously, this require that the column doesn't have a NOT NULL constraint.
This directive is only used for data export because it is very simple to add an "alter table ... drop column ..." after your schema import. There's no need to include that into ora2pg.
No, there's no option to rearrange columns order in postgresql, but this can be a new feature. I will see if that can be done easily. Using MODIFY_STRUCT you can just specify the columns that will be used to export/import data.
from ora2pg.
Hi Christian,
The feature of reordering columns following alignment is now included in last release v12.0 that was just published today. It can be downloaded at https://sourceforge.net/projects/ora2pg/.
Thanks for the feature request.
Best regards,
from ora2pg.
So in total there is no way to exclude columns? I am using TABLE
migration type and when I deifned:
MODIFY_STRUCT my_table(col1, col3)
The col2
is still here after migration. So only reordering is supported?
P.S. ora2pg is v18.1
from ora2pg.
The MODIFY_STRUCT is only available with data export to adapt to any schema transformation. But I think that with some additional work it could be available for tables/indexes/constraints export.
from ora2pg.
So could I create new task for this or reopen current task?
from ora2pg.
I've reopen it, thanks.
from ora2pg.
Commit 2315983 allow use of MODIFY_STRUCT with TABLE export. Table creation DDL will respect the new list of columns and all indexes or foreign key pointing to or from a column removed will not be exported.
Another question: can this option be used to rearrange the order of columns in postgresql? I learned during migration that I get a more efficient storage if small columns (like boolean columns) are next to each other.
I've forgotten to answer to this question, for automatic reordering by ora2pg you just have to enable the REORDERING_COLUMNS configuration directive in ora2pg.conf. It must be a bit too late, but can be useful for later use and other users.
from ora2pg.
Related Issues (20)
- Migrating From SQL Server to Postgres with Ora2pg Is very Slow HOT 1
- Request for sha256sum on downloads HOT 1
- Config option to override the NLS_TIMESTAMP_FORMAT or such encoding parameters used by Ora2pg HOT 1
- Invalid check constraint conversion to PostgreSQL format while exporting schema from Oracle HOT 1
- OUT OF MEMORY EVEN THOUGH SERVER HAS OVER 16GB FREE RAM HOT 2
- [ORACLE]Slow export of BLOB and CLOB from HOT 7
- Reading the ora2pg assessment report HOT 6
- Mysql to postgresql migration HOT 9
- ERROR : DBI::db=HASH(0x29c1784f2e8)->disconnect invalidates 1 active statement handle HOT 3
- Wrong argument "extra_param OPAQUE" in function with OUT arguments (regression in release 24.3) HOT 1
- Wrong change in translation of "is not null" clause (regression in release 24.3) HOT 1
- Extract data query errorring out HOT 6
- Oracle column aliases (after TRIM function) are syntactically incorrect due to small bug introduced in commit 66f4fc37 HOT 1
- Issue in migrating from SQL Server to CloudSQL Postgres HOT 4
- Foreign server always generated with default port HOT 3
- Export data with CLOB is too slow HOT 7
- Generated export_schema.ps1 contains bash if else HOT 1
- Problems trying to use new parameter PARTITION_BY_REFERENCE=duplicate
- Oracle to Postgresql migration - issue with SECURITY DEFINER
- Filenames created incorrectly when exporting using the COPY option and parameter RENAME_PARTITION=1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from ora2pg.