Solved

Nologging in oracle 9i database.

Posted on 2012-03-30
14
673 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c database link between pdb not working 20 76
How do I call MySQL Stored Procedure from oracle using HS link ? 5 44
clob to char in oracle 3 39
PL/SQL Two changes 7 27
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

777 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