Help - how to finde out with a query if table runs in parallel or not?

[stella75]

Hi there

As I'm not a DBA may some of you can help me.

How can I find out fast with a query which table runs in Paralle and which one in no_parallel?

Thanks a lot for your help!

Cheers,
Daniela

[PAVB]

dba_tables, degree column.

Try user_tables table to check tables you own.

[stella75]

I have this query which I have to run on the DB:

SELECT 'ALTER TABLE '

|| table_name

|| CASE WHEN num_rows > 100000 THEN ' PARALLEL ( DEGREE Default INSTANCES Default );' ELSE ' noparallel;' END

FROM user_tables

UNION ALL

SELECT 'ALTER INDEX '

|| index_name

|| CASE WHEN num_rows > 100000 THEN ' PARALLEL ( DEGREE Default INSTANCES Default );' ELSE ' noparallel;' END

FROM user_indexes;

but in a case of a "rollback" I wanted to know, which table (over 413 at the moment) already have (if so) already parallel activ....

do you have a faster method to find it out?

[PAVB]

Your query is creating a series of "ALTER TABLE..." and "ALTER INDEX..." statements on the fly -those statements will not touch data so no rollback would ever occur.

If you are curious and want to find out tables with parallel option enable just try:

select table_name, degree
from user_tables
where decode(degree, 'DEFAULT', 1, degree) > 1;

Something similar for user_indexes.

[stella75]

As we have a performance problem on a productive enviroment I would sleep better if we have a rollback szenario if this script wouldn't bring the result requested by management...

[PAVB]

oh... you mean "backout" e.g. going back to your previous scenario -"rollback" means a transaction is rolledback which is not the case.

I agree with you, better to know what you have today in case you have to "backout" your changes.

By the way, shouldn't a DBA be involved in these kind of changes?

[stella75]

don't talk about our DBA.... :-S we either got the scrip from an other guy but not from the DBA... he don't feel responsible on such kind of tasks...

[PAVB]

he don't feel responsible on such kind of tasks...

Oh boy... that kind, huh?

[stella75]

Thanks anyway... we did it know and it wasn't necessary to do a backout!

Have a nice Day!