NoLogging at Table Level [message #291279] |
Thu, 03 January 2008 10:51  |
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   |
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 #291520 is a reply to message #291464] |
Fri, 04 January 2008 12:14  |
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
|
|
|