Link to home
Start Free TrialLog in
Avatar of irsbenz
irsbenzFlag for Canada

asked on

Nologging in oracle 9i database.

Hi,
I have create a table using nologging as shown in given example below. How can change it into logging state.
create table emp_temp nologging as select * from emp;
Avatar of for_yan
for_yan
Flag of United States of America image

create table emp_temp as select * from emp;
raed here about nologging:
http://dba-oracle.com/t_nologging_append.htm

actaully normal default oprerations crete redo logs which allow to recover the sate of the table/database if therer is suddedn power failure

nologging switches off this feature making operation quicker but less safe
if you mean that you want to change the table which you ctrfreated into loggin statew


the

alter table emp_temp logging

should chage the property of the table
but keep in mind that even when you created table nologging as in your example it will do nologging in your fisrt create operation but on regular insert it will still do logging.

read this explanation:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869

Anyway, with NOLOGGING "switched" on for already created tables and indexes, it seems
that all DML is logged unless you use direct path in some way. If I have a NOLOGGING
table and INSERT without /*+ APPEND */ I get logging, and if I have a LOGGING table and
INSERT with /*+ APPEND */ I get logging. I only get no logging when I have a NOLOGGING
table with /*+ APPEND */.
Avatar of irsbenz

ASKER

can we use this this syntax to switch the table to logging.
alter table emp_temp logging;
Avatar of irsbenz

ASKER

so when we alter the table then all the DML operation can be log on this table?
alter table emp_temp logging;


Yes, I think it should do it this way
I didn't try myself but that is what
instruction fro alter table says

yes, after yiou switch it to logging - that will be default state and everything will be logged

if it is nologging then still not all operations are not logged
Avatar of irsbenz

ASKER

Actually i am creating a table with existing table with CTAS that existing contains 40 million records an it's take long time to copy . If I am using nologging then if any crash occur before taking the backup then i lost my table because once i copied the data I am deleting the certain amount of rows depends on the condition from the existing table.
So for may safe side, can use you alter table syntax to avoid the losing the table.
Well,  I think it is always safer not to do nologging
Unless you can make a backup of database  immediately after you create a copy
if you have bcakup running every night, you may make  a copy with nologging today and then modify the origibal table tomorrow
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>then i lost my table because once i copied the data I am deleting the certain amount of rows depends

Why not CTAS and remove the rows during the create?  Why copy ALL rows over then delete a lot of them?

I agree with the nologging comments above.  You need to be aware of what this means to previous backups and decide if you can live with it.
Avatar of irsbenz

ASKER

thanks for the reply.
How I can we delete the rows during the create table using  ctas.
one more thing I need to ask when I create a index using create index indexname from table tablename (columnname) nologging tablespace tqblespacename.
take long time to create the index the table contain around 50 million records.
Is there. anyway to create the index fast.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial