Solved

Nologging in oracle 9i database.

Posted on 2012-03-30
14
666 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysql not caching queries 4 45
statspack purge automate 7 28
Oracle Subquery bad Join 11 42
query in Oracle forms Builder 2 26
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

17 Experts available now in Live!

Get 1:1 Help Now