Home » SQL & PL/SQL » SQL & PL/SQL » NoLogging at Table Level
NoLogging at Table Level [message #291279] Thu, 03 January 2008 10:51 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We had to create 3 staging tables in PROD, ( with nologging Option ) These tables will get loaded with some 2-5 million rows every day( From ( Sql Loader )
and once data if filtered and entered into real App Tables , 90% of 2-5 mill rows tables will be deleted,
There fore i want to create tables / indexes on these Staging tables with nologging option.
Since at Database Level it is Logging Enabled, will setting nologging at table level have effect.

PRODUCTION Databae is 10g RAC 2 Node with Replication Enabled.

Thanks

[Updated on: Thu, 03 January 2008 10:54]

Report message to a moderator

Re: NoLogging at Table Level [message #291310 is a reply to message #291279] Thu, 03 January 2008 16:05 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Heres a test case to your question .
Mine database is in force logging mode and i created a table test with no logging.

Scenario Physical standby!!!

Primary site:
============
SQL> create table test
  2  as
  3  select * from dba_users nologging;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
        13

SQL> insert into test select * from test;

13 rows created.

SQL> /

26 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
        52


Standby Site
============

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)


SQL> select count(*) from test;

  COUNT(*)
----------
        52



Data Replicated...

[Updated on: Thu, 03 January 2008 16:07]

Report message to a moderator

Re: NoLogging at Table Level [message #291464 is a reply to message #291310] Fri, 04 January 2008 05:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There's a gotcha in your example DreamzZ. You effectively only created an alias for the DBA_USERS table instead of creating the TEST table as nologging.
Re: NoLogging at Table Level [message #291520 is a reply to message #291464] Fri, 04 January 2008 12:14 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Here's example rather than creating an alias.!!!

SQL> create table test
  2  (sno number(3),
  3  name varchar2(10)) nologging;

Table created.

SQL> insert into test values (1,'a');

1 row created.

SQL> insert into test values (1,'b');

1 row created.

SQL> insert into test values (1,'c');

1 row created.

SQL> insert into test values (1,'d');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

       SNO NAME
---------- ----------
         1 a
         1 b
         1 c
         1 d



Standby Side!!!
===============

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL>  select * from test;

       SNO NAME
---------- ----------
         1 a
         1 b
         1 c
         1 d

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
              LOGFILE DISCONNECT;
  2
Database altered.

[Updated on: Fri, 04 January 2008 12:15]

Report message to a moderator

Previous Topic: How to insert a .jpg file in a table
Next Topic: Incapsulation of instance variables in object type
Goto Forum:
  


Current Time: Fri Feb 07 08:06:37 CST 2025