This content has been marked as final. Show 6 replies
In modern versions of Oracle, the rowid can change, so no - that wouldn't be a good approach. Why not use the actual PK of the table instead of rowid.
But... 5GB is teeny tiny, why not just expdb the table and be done with it?
Thanks for reply.
There is no primary key in this table as it includes several column data from different views which transpose rows into columns for cognos model.
Can you suggest some approach other than expdp ?
why? expdp is fast.
Since you have no PK, you won't be able to join the dropped columns back up to anything anyway, so you're going to have to get a full backup of the table.
edit: or add a surrogate key
AnkitV wrote:What are you concerned about? 5 gb? Good grief, you can put that on a thumb drive for less than US$10 at Staples!
I need to remove around 20 columns from a table (5 GB in size). But before this, I need to take some backup.
I know that taking export of this table is one option, but I will be followed a different way as I don't want to backup 600 columns for sake of 20 columns.
I created a backup table by selecting only those 20 cols from the source table along with ROWID of source table in col ROWID_S. Size of this backup table came out to be 130 MB.
My question is, will this backup prove to be correct in case I need to restore data from this into source table's columns on the basis of ROWID_S ?
I mean will the join between ROWID_S and ROWID of source table be valid ?
I am just thinking is there a possibility that ROWID of source table rows get changed somehow ?
this will give table structure without data
SELECT dbms_metadata.get_ddl( 'TABLE', 'MY_TABLE_NAME' ) FROM DUAL;
Thanks a lot to all for help. I think I will go with expdp.