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;
irsbenzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

for_yanCommented:
create table emp_temp as select * from emp;
0
for_yanCommented:
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
0
for_yanCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

for_yanCommented:
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 */.
0
irsbenzAuthor Commented:
can we use this this syntax to switch the table to logging.
alter table emp_temp logging;
0
irsbenzAuthor Commented:
so when we alter the table then all the DML operation can be log on this table?
0
for_yanCommented:
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
0
irsbenzAuthor Commented:
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.
0
for_yanCommented:
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
0
for_yanCommented:
if you have bcakup running every night, you may make  a copy with nologging today and then modify the origibal table tomorrow
0
slightwv (䄆 Netminder) Commented:
>>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.
0
irsbenzAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
>>How I can we delete the rows during the create table using  ctas.

Just don't select them?  Basically use the logic you would use to 'delte' them and place it in the where clause of the CTAS and just not select them.

>>Is there. anyway to create the index fast.

You might look into a PARALLEL hint or just increase the parallel value for the index.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.