nologging

[vpons]

Is this supported in oracle 7.3.4?

I have to delete and inset more than a million records in a table. I tried to set nologging for the table and I get this error:
"Alter table ghl nologging;"

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

If it is not supported, what is the best way to avoid creation of transaction log.

It's the production database, running in archivelog mode and there is not enough room to handle huge transactions.

Thanks much in advance!

VP

[davey23uk]

no its obviously not, but i think you have misunderstood what nologging in - only a few specific operations can not log.

You need the archive logs for recovery

[vpons]

dave, thanks

We are migrating oracle to IBM and I am sending tables as flat files. Some table with millions of records are
taking 3-4 hrs.

In order to reduce the time, I decided to create another table with history data and then delete history from the
main table.

This will help move data faster. Once migration is complete, the history file will be migrated too and also
the history data will have to be inserted back into the main table.

Eg:

GLH - 3 million record:

1. create table glt_2004 as select * from glh where lhyear < 2005;

2. Delete from glh where lhyear < 2005;

Migrate GLH to IBM


At a later stage On oracle:

Migrate GLH_2004 to IBM

Insert into glh as select * from glh_2004; (or append it)

Delete glh_2004.

I am going to test on standby first. Also I do not care about transaction logs during this time.

Also this operation (steps 1 and 2) will be done when no users are on the system.
Once the above is completed, an export followed by filesystem back up will be done.

Now that nologging is not supported in 7.3.4, please suggest?

Changing database into noarchivelogmode and back to archive mode will be more time consuming.

[BV1963]

7.3.4 has unrecoverable option on create table statement.

[vpons]

Thanks BV1963.
Any suggestions on inserts?

[BV1963]

1. create table glt_2004 unrecoverable as select * from glh where lhyear > 2005;


You can manipulate with the create statement or insert /*+ append */ ( not sure if it was available in 7.3.4) to extract data that you need.