如何使用DBMS_STATS.set_table_prefs将具有相同所有者的多个表的Incemental设置为true? [英] How do you set Incemental to true for multiple tables with the same owner using DBMS_STATS.set_table_prefs?
问题描述
我的oracle数据库中大约有40-50个表已分区.我想使用DBMS_STATS.set_table_prefs将所有分区表的增量"设置为true.有人可以帮我吗?
以下是查询:
SELECT DISTINCT(表名),partitioning_type,subpartitioning_type,OWNER 来自all_part_tables 所有者='用户' ORDER BY table_name ASC;
此PL/SQL块(基于您在另一个问题中的评论)循环遍历用户的分区表,并将其增量首选项设置为true.
begin
for a in
(
select distinct (table_name), owner
from all_part_tables
where owner = 'SOME_USER_NAME'
--Ignore objects in the recycle bin.
--There are other "tables" that may need to be ignored,
--such as external tables, storage tables, etc.
and table_name not like 'BIN$%'
order by table_name
) loop
dbms_stats.set_table_prefs(a.owner, a.table_name, 'incremental', 'true');
end loop;
end;
/
I have around 40-50 tables in my oracle database that are partitioned. Using DBMS_STATS.set_table_prefs, I want to set "Incremental" to true for all of my partitioned tables. Can anyone help me with this?
Below is the query:
SELECT DISTINCT (table_name), partitioning_type, subpartitioning_type, OWNER FROM all_part_tables WHERE OWNER = 'user' ORDER BY table_name ASC ;
This PL/SQL block (which is based on your comment in another question) loops through partitioned tables for a user and sets their incremental preference to true.
begin
for a in
(
select distinct (table_name), owner
from all_part_tables
where owner = 'SOME_USER_NAME'
--Ignore objects in the recycle bin.
--There are other "tables" that may need to be ignored,
--such as external tables, storage tables, etc.
and table_name not like 'BIN$%'
order by table_name
) loop
dbms_stats.set_table_prefs(a.owner, a.table_name, 'incremental', 'true');
end loop;
end;
/
这篇关于如何使用DBMS_STATS.set_table_prefs将具有相同所有者的多个表的Incemental设置为true?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!