When creating a table in Oracle, the Parallel and Degree properties can be specified.
When creating a table and selecting * from all_all_tables, there is an INSTANCES item. Can this item be edited by the user using DDL statements such as CREATE TABLE? If there is, please ask for an example phrase.
Below is an example of setting and altering the INSTANCES property of a table:
SQL> create table test1(a number) parallel (instances 2);
Table created.
SQL> select instances from user_tables where table_name = 'TEST1';
INSTANCES
----------------------------------------
2
SQL> alter table test1 parallel (instances 1);
Table altered.
SQL> select instances from user_tables where table_name = 'TEST1';
INSTANCES
----------------------------------------
1
The table property is defined in the 19c reference as "Number of instances across which the table is to be scanned, or DEFAULT".
I think INSTANCES has been deprecated and replaced by setting PARALLEL_FORCE_LOCAL at either the session or system level. The INSTANCES syntax is documented in the version 7 SQL Reference but is not documented in the version 8i SQL Reference.
