When it is safe to use Nologging ?

I have an application, where in I use to create table dynamically inside the plsql block,
which is taking more time to execute.

I just want to know, when and where it is safe to use Nologging? so that I can save sometime.
Who is Participating?
OP_ZaharinConnect With a Mentor Commented:
- operation such as CREATE TABLE .. AS SELECT, CREATE INDEX and any BULK INSERT operation can be run with NOLOGGING as you can re-create/run it again should it fail.

- the following Oracle doc explain when to /not to use NOLOGGING:
http://jakub.wartak.pl/blog/?page_id=107 - he collects the NOLOGGING info and compile it into one which is worth a read.
Use the NOLOGGING option only on temporary/working/staging tables.
its not recommended to use nologging, but it can save you some space, and also increase speed....
BTW Read : http://oracleexamples.wordpress.com/2010/08/28/benefits-and-consequences-of-the-nologging-option/
slightwv (䄆 Netminder) Commented:
>>I have an application, where in I use to create table dynamically inside the plsql block,

Do you really need a dynamic table or can you use a Global Tempory Tae work for you?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Devinder Singh VirdiLead Oracle DBA TeamCommented:
>> create table dynamically inside the plsql block, which is taking more time to execute.

1. What is the size of this temp table. It may be possible that the query is taking time and the slowness may not be because of segment contention.
2. Also check total number of extents allocated to that table. It may be possible that table is created with small extent and oracle has to allocate more and is wasting time on that part.
3. To address actual problem, please provide your create temp table query or whole pl block.
Greg CloughSenior Oracle DBACommented:
+1 for a global temporary table.  No logging, and automatically disappears when the session closes.

Also, is your database, or tablespace in "Force Logging" mode?  If so, then using NOLOGGING on the table won't change anything.  Force Logging is generally used in DataGuard environments, where you must ensure that the redo stream has all changes.

If you do choose to go down the nologging route, then any data in that table is unrecoverable until the next backup... so use it wisely.
Like to know

1.  How often you call the block which creates table ?
2.  If quite often, means it creates that many tables for each time the block is called?
3.  How do you manage when 2 users are tying to call at same time?
4.  Do you drop these tables created? If yes, are you dropping at end of the block?

If you are creating and dropping in same block, you should be using GTT (Global Temporary Table) instead as suggested by other experts
sakthikumarAuthor Commented:
in every specific time period,  the tables will be created. these were not temporoary tables.
Ok, do you query these tables at later date or time, once  the block is done its execution?
slightwv (䄆 Netminder) Commented:
If these are not temporary tables and must remain around, can you easily re-create them at a later date in the event you lose your database?

Can you take a backup immediately after creating them?

If not, you should not use nologging.

If you can provide a little more detail about your requirements we can offer better advice.
sakthikumarAuthor Commented:
backup --> I dont know like when the backup will be created, it is in DBA's hands, and am only a developer

can you easily recreate them --> I can recreate it, but the problem is if I continuously do this for all the tables subsequently, then there will not be any table to recreate.

query these tables --> sure, these table will be queried often.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I would advise you to NOT use nologging.
Few thinks I can think of:

1.  If you create the table externally, how much time it takes?
2.  Are you 100% sure that dynamic sql of creating table takes time and no other part of sql?
3.  Can you logically separate two parts: Creating table and other part of sql?
4.  I assume your whole code is using dynamic sql, but you need to zero down the portions that take time to execute
5. Can you share the code with us?
Greg CloughConnect With a Mentor Senior Oracle DBACommented:
backup --> I dont know like when the backup will be created, it is in DBA's hands, and am only a developer

+1 for NOT using nologging... but if you are still considering it, then the person you need to speak with is your DBA.  You both need to work together to solve this problem and ensure there are no "surprises" later on, you can't do it in isolation.
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.

All Courses

From novice to tech pro — start learning today.