Re: Oracle 8i NOLOGGING parameter...

gerry, the direct load with nologging is executed via sqlldr with direct=y and probably can not be execute via code. direct load by pass some of oracle code and it is faster.

you are correct, nologging is not an option for insert statement.

ted chyn
In article <EJBR4.223$Tt1.17092_at_jekyl.ab.tac.net>,   "Gerry D'Costa" <gdcosta_at_cybersurf.net> wrote:
> Hi Patrick,
>
> Thanks for the advice, however, unless I'm missing something, I don't
 see a
> NOLOGGING option for the INSERT statement. Do you perhaps have more
> information about NOLOGGING and INSERT statements that I just don't
 see?
>
> Thanks for all your help...
>
> Gerry D'Costa
> Database Administrator
> Cybersurf Corporation
> www.cybersurf.net
> www.3web.net
>
> Patrick Hamou <patrick_hamou_at_hotmail.com> wrote in message
> news:0VoR4.7830$r37.153503_at_news20.bellglobal.com...
> > Hi Gerry,
> >
> > the problem is that you are trying to disable the logging option for
 a
 table
> > with the NOLOGGING option of the table...
> > But the nologging option is there only for the creation of the table
 not
 for
> > all the inserts or deletes on that table,
> > if you want to bypass the archive logs, you have to include the
 NOLOGGING
> > clause in each insert statement...
> >
> > regards,
> >
> > Patrick Hamou
> > Oracle DBA
> > Noranda
> >
> >
> > Gerry D'Costa <webmaster_at_cybersurf.net> wrote in message
> > news:550Q4.85$Tt1.8790_at_jekyl.ab.tac.net...
> > > Hi all,
> > >
> > > I've got a question about the NOLOGGING parameter that you can
 attribute
 to
> > > tables, tablespaces, and partitions.
> > >
> > > How do you tell Oracle not to log a particular table?
> > >
> > > I'm running Oracle 8.1.5 with Partitioning in archive log mode.
 I've
 just
> > > partitioned one of my tables into 12 datafiles ( one for each
 month )
 and
> > > I'm trying to load data into it.
> > >
> > > The main problem is that in doing this the quantity of archive
 logs
 starts
> > > going through the roof.
> > >
> > > Oracle documentation says that I can perform a Direct-Load insert
 in
> > > conjunction with turning LOGGING off for a table. So I tried the
 following
> > > in PL/SQL: (The "v_" variables are cursor variables)
> > >
> > > --------------
> > > -- Direct LOAD INSERT
> > > INSERT /*+ APPEND */ INTO impressions
> > > ( custid,
> > > adid,
> > > impression_date,
> > > impression_count,
> > > month )
> > > VALUES
> > > ( v_custid,
> > > v_adid,
> > > v_impressionDate,
> > > v_impressionCount,
> > > TO_NUMBER( TO_CHAR( v_impressionDate, 'MM' ) )
 );
> > >
> > > I've also performed the following alter table command...
> > >
> > > ALTER TABLE impressions NOLOGGING;
> > > -------------
> > >
> > > However, it still produced gigabytes of archive logs, hence lots
 of redo
> > > data.
> > >
> > > I know that rollback segments transactions are included in the
 redo data
 so
> > > I next issued the following command:
> > >
> > > ALTER TABLESPACE rollback NOLOGGING;
> > >
> > > ( the tablespace rollback holds all rollback segments )
> > >
> > > Still didn't work, I still have archive logs being generated at a
 incredible
> > > rate.
> > >
> > > Am I doing something wrong or am I just overlooking something.
 Any help
> > > would be really appreciated.
> > >
> > > Thanks
> > >
> > > Gerry D'Costa
> > > Database Administrator
> > > Cybersurf Corporation
> > > gdcosta_at_cybersurf.net
> > > www.cybersurf.net
> > > www.3web.net
> > >
> > >
> > >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 10 2000 - 00:00:00 CEST