Hi,

The database version is Oracle 11.1

When star transformation enabled is set to true, I see redo log being
generated. I believe this is because of the dimensions being loaded
into temp tables (SYS_TEMP_*).

Is it possible to disable this redo log generation without using
temp_disable?

These temp tables are created in the SYS schema. Can we alter system
tablespace to nologging?

Regards,
Pratap

On Aug 26, 7:49*am, Pratap <pratap.deshm... (AT) gmail (DOT) com> wrote:
Altering a tablespace to nologging does not effect the logging on
existing object nor should you even consider using nologging on the
system tablespace. The nologging setting also only applies to certain
type of DML activity such as direct loads anyway.

What statistics (query) are you looking that identifies the high redo
log generation as being from the session in question performing the
start transformation?

HTH -- Mark D Powell --

On Aug 26, 7:49*am, Pratap <pratap.deshm... (AT) gmail (DOT) com> wrote:
PS - Google won't show me my initial reply but I found the following
article at Oracle support that tells you how to disable the use of
temporary tables in a start transformation.

Init.ora Parameter "STAR_TRANSFORMATION_ENABLED" Reference Note [ID
47358.1]

HTH -- Mark D Powell --

On Fri, 26 Aug 2011 04:49:35 -0700, Pratap wrote:


Temp tables may be created in SYS schema but are NOT created in the
SYSTEM tablespace. Temp tables are always created in the TEMP tablespace
for the issuing schema. If it's the SYS schema, it's the default
tablespace for the database. Messing with system tablespace is definitely
not recommended, even if setting it to nologging could actually achieve
anything.



--
http://mgogala.byethost5.com

On Aug 26, 11:32*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
Thanks, if the tables were created in the TEMP tablespace, I believe
they would not have generated redo?

On Aug 26, 3:42*pm, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
Thanks -- I would not like to disable the temporary table option.

On Sun, 28 Aug 2011 10:44:57 -0700, Pratap wrote:

Well, yes and no. While it is true that temporary blocks are not
protected by the redo logs, they are protected by the undo structures, to
enable normal transaction behavior. Proof is trivial, you can just do
following:

create global temporary table t_emp
on commit preserve rows
as select * from emp;
Table created.

Elapsed: 00:00:00.08
SQL> select count(*) from t_emp;

COUNT(*)
----------
14

Elapsed: 00:00:00.01
SQL> delete from t_emp;

14 rows deleted.

Elapsed: 00:00:00.01
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.01
SQL> select count(*) from t_emp;

COUNT(*)
----------
14

Elapsed: 00:00:00.00
SQL>


So, temporary tables are protected by UNDO blocks and when they are used,
redo will still be generated.

--
http://mgogala.byethost5.com