Solved

Nologging in oracle 9i database.

Posted on 2012-03-30
14
668 Views
Last Modified: 2012-04-28
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;
0
Comment
Question by:irsbenz
  • 7
  • 4
  • 2
14 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37790048
create table emp_temp as select * from emp;
0
 
LVL 47

Expert Comment

by:for_yan
ID: 37790055
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37790067
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37790071
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
 

Author Comment

by:irsbenz
ID: 37790080
can we use this this syntax to switch the table to logging.
alter table emp_temp logging;
0
 

Author Comment

by:irsbenz
ID: 37790082
so when we alter the table then all the DML operation can be log on this table?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 47

Expert Comment

by:for_yan
ID: 37790083
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
 

Author Comment

by:irsbenz
ID: 37790091
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37790100
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
 
LVL 47

Expert Comment

by:for_yan
ID: 37790101
if you have bcakup running every night, you may make  a copy with nologging today and then modify the origibal table tomorrow
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37795752
>>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
 

Author Comment

by:irsbenz
ID: 37795907
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 37795980
>>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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now