Re: Oracle 8i NOLOGGING parameter...

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
>

In order to progamatically load with no log, you can only use the New OCI functions for direct path loading. See http://technet.oracle.com/docs/products/oracle8i/doc_index.htm for details.

The INSERT /*+ APPEND */ you tried below is for an

INSERT INTO T SELECT .... when you are bulk insert lots of rows (it would be terrible if it could do it a row at a time as the /*+ append */ will put data into an empty extent -- you'd have a row/extent)

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

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.