[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT upper(oracle_username) sname
FROM fnd_oracle_userid
WHERE oracle_id BETWEEN 900 AND 999
AND read_only_flag = 'U'
UNION ALL
SELECT DISTINCT upper(oracle_username) sname
FROM fnd_oracle_userid a,
fnd_product_installations b
WHERE a.oracle_id = b.oracle_id
ORDER BY sname;
SELECT table_name BULK COLLECT
INTO names
FROM dba_tables
WHERE owner = upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND monitoring <> modeval; -- skip table that already have the selected mode
SELECT MIN(to_number(value))
INTO parallel
FROM v$parameter
WHERE name ='parallel_max_servers'
OR name ='cpu_count';
SELECT fnd_stats_hist_s.nextval
INTO request_id_l
FROM dual;
SELECT fnd_stats_hist_s.nextval
INTO request_id_l
FROM dual;
EXECUTE immediate 'select ''Y'' from all_tables '
|| ' where owner='''
||fnd_statown
|| ''' and table_name='''
||fnd_stattab
||'''' INTO dummy1;
select upper(b.oracle_username) ownname ,
a.table_name tabname,
a.column_name colname,
a.partition partname
from FND_HISTOGRAM_COLS a,
FND_ORACLE_USERID b,
FND_PRODUCT_INSTALLATIONS c
where a.application_id = c.application_id
and c.oracle_id = b.oracle_id
order by ownname, tabname, column_name;
SELECT NVL(upper(b.oracle_username), a.owner) ownname ,
a.table_name tabname ,
a.column_name colname ,
a.partition partname
FROM FND_HISTOGRAM_COLS a,
FND_ORACLE_USERID b ,
FND_PRODUCT_INSTALLATIONS c
WHERE a.application_id = c.application_id (+)
AND c.oracle_id = b.oracle_id (+)
ORDER BY ownname,
tabname,
colname;
SELECT type ,
owner,
name
FROM
( SELECT 'TABLE' type,
owner ,
table_name name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND last_analyzed IS NULL
AND table_name not like 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
UNION ALL
SELECT 'INDEX' type,
owner ,
index_name name
FROM dba_indexes
WHERE
(
table_owner=upper(schemaname)
OR owner =upper(schemaname)
)
AND index_type <> 'LOB'
AND index_type <>'DOMAIN'
AND TEMPORARY <> 'Y'
AND generated <> 'Y' -- change done by saleem for bug 9542112
AND last_analyzed IS NULL
)
ORDER BY type ,
owner,
name ;
SELECT owner,
table_name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND monitoring ='NO'
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
);-- added this to avoid externale tables being selected
SELECT owner,
table_name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND monitoring ='NO'
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
)-- added this to avoid externale tables being selected
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
); -- added by saleem to avoid locked objects
SELECT type ,
owner,
name
FROM
( SELECT 'TABLE' type,
owner ,
table_name name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND last_analyzed IS NULL
AND table_name not like 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
)
UNION ALL
SELECT 'INDEX' type,
owner ,
index_name name
FROM dba_indexes
WHERE
(
table_owner=upper(schemaname)
OR owner =upper(schemaname)
)
AND index_type <> 'LOB'
AND index_type <>'DOMAIN'
AND TEMPORARY <> 'Y'
AND generated <> 'Y' -- change done by saleem for bug 9542112
AND last_analyzed IS NULL
)
ORDER BY type ,
owner,
name ;
SELECT table_name
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
AND owner=upper(schemaname); -- added to display the warning for locked stats
FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel,
upd_ins_flag=>'S', percent=>NVL(estimate_percent,def_estimate_pcnt));
SELECT table_name ,
partitioned BULK COLLECT
INTO names,
part_flag
FROM dba_tables dt
WHERE owner = upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM fnd_stats_hist fsh
WHERE dt.owner =fsh.schema_name
AND fsh.REQUEST_ID = cur_request_id
AND fsh.object_type ='CASCADE'
AND fsh.history_mode=stathist
AND dt.table_name = fsh.object_name
AND LAST_GATHER_END_TIME IS NOT NULL
)
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
) -- added by saleem for bug 7479909
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
ORDER BY table_name;
SELECT iv.table_name,
iv.partition_name -- ,subpartition_name
BULK COLLECT
INTO names,
pnames -- ,spnames
FROM
( SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tables dt
WHERE dt.owner =dtm.table_owner
AND dt.table_name=dtm.table_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (modpercent*NVL(dt.num_rows,0))/100
)
UNION ALL
SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NOT NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tab_partitions dtp
WHERE dtp.table_owner =dtm.table_owner
AND dtp.table_name =dtm.table_name
AND dtp.partition_name=dtm.partition_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (modpercent*NVL(dtp.num_rows,0))/100
)
) iv
ORDER BY table_name;
SELECT table_name ,
partitioned BULK COLLECT
INTO names,
part_flag
FROM dba_tables dt
WHERE owner = upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM fnd_stats_hist fsh
WHERE dt.owner =fsh.schema_name
AND fsh.REQUEST_ID = cur_request_id
AND fsh.object_type ='CASCADE'
AND fsh.history_mode=stathist
AND dt.table_name = fsh.object_name
AND LAST_GATHER_END_TIME IS NOT NULL
)
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
) -- added by saleem for bug 7479909
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
)
AND NOT EXISTS -- to avoid external tables
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
ORDER BY table_name;
SELECT iv.table_name,
iv.partition_name -- ,subpartition_name
BULK COLLECT
INTO names,
pnames -- ,spnames
FROM
( SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tables dt
WHERE dt.owner =dtm.table_owner
AND dt.table_name=dtm.table_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (modpercent*NVL(dt.num_rows,0))/100
)
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dtm.table_name
AND dts.owner =dtm.table_owner
)
UNION ALL
SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NOT NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND dtm.table_name not like 'BIN$%' -- added for Bug 9542112
AND EXISTS
( SELECT NULL
FROM dba_tab_partitions dtp
WHERE dtp.table_owner =dtm.table_owner
AND dtp.table_name =dtm.table_name
AND dtp.partition_name=dtm.partition_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (modpercent*NVL(dtp.num_rows,0))/100
)
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dtm.table_name
AND dts.owner =dtm.table_owner
)
) iv
ORDER BY table_name;
FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
SELECT type ,
owner,
name
FROM
( SELECT 'TABLE' type,
owner ,
table_name name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND last_analyzed IS NULL
AND TABLE_NAME NOt LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
UNION
SELECT DISTINCT 'TABLE' type ,
table_owner owner,
table_name name
FROM dba_indexes di
WHERE
(
di.table_owner=upper(schemaname)
OR di.owner =upper(schemaname)
)
AND di.index_type <> 'LOB'
AND di.temporary <> 'Y'
AND di.generated <> 'Y' -- change done by saleem for bug 9542112
AND di.last_analyzed IS NULL
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id =fets.application_id
AND di.table_name =fets.table_name
)
)
ORDER BY type ,
owner,
name ;
SELECT owner,
table_name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND monitoring ='NO'
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
);-- added this to avoid externale tables being selected
SELECT owner,
table_name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND monitoring ='NO'
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
)-- added this to avoid externale tables being selected
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
); -- added by saleem to avoid locked objects
SELECT type ,
owner,
name
FROM
( SELECT 'TABLE' type,
owner ,
table_name name
FROM dba_tables dt
WHERE owner=upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y'
AND last_analyzed IS NULL
AND table_name not like 'DR$%' -- added for Bug 8452962
AND table_name not like 'DR#%' -- added for Bug 8452962
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
)
UNION ALL
SELECT 'INDEX' type,
owner ,
index_name name
FROM dba_indexes
WHERE
(
table_owner=upper(schemaname)
OR owner =upper(schemaname)
)
AND index_type <> 'LOB'
AND index_type <>'DOMAIN'
AND TEMPORARY <> 'Y'
AND generated <> 'Y' -- change done by saleem for bug 9542112
AND last_analyzed IS NULL
)
ORDER BY type ,
owner,
name ;
SELECT table_name
FROM dba_tab_statistics
WHERE stattype_locked IS NOT NULL
AND owner=upper(schemaname); -- added to display the warning for locked stats
FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname,
objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S',
percent=>NVL(estimate_percent,def_estimate_pcnt));
SELECT table_name ,
partitioned BULK COLLECT
INTO names,
part_flag
FROM dba_tables dt
WHERE owner = upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND TABLE_NAME NOT LIKE 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM fnd_stats_hist fsh
WHERE dt.owner =fsh.schema_name
AND fsh.REQUEST_ID = cur_request_id
AND fsh.object_type ='CASCADE'
AND fsh.history_mode=stathist
AND dt.table_name = fsh.object_name
AND LAST_GATHER_END_TIME IS NOT NULL
)
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS -- to avoid extrnal tables
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
ORDER BY table_name;
SELECT iv.table_name,
iv.partition_name -- ,subpartition_name
BULK COLLECT
INTO names,
pnames -- ,spnames
FROM
( SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tables dt
WHERE dt.owner =dtm.table_owner
AND dt.table_name =dtm.table_name
AND dt.partitioned='NO'
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (mod_percent*NVL(dt.num_rows,0))/100
)
UNION ALL
SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NOT NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tab_partitions dtp
WHERE dtp.table_owner =dtm.table_owner
AND dtp.table_name =dtm.table_name
AND dtp.partition_name=dtm.partition_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (mod_percent*NVL(dtp.num_rows,0))/100
)
) iv
ORDER BY table_name;
SELECT table_name ,
partitioned BULK COLLECT
INTO names,
part_flag
FROM dba_tables dt
WHERE owner = upper(schemaname)
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
AND TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND TABLE_NAME NOT LIKE 'DR#%' -- added for Bug 8452962
AND NOT EXISTS
(SELECT NULL
FROM fnd_stats_hist fsh
WHERE dt.owner =fsh.schema_name
AND fsh.REQUEST_ID = cur_request_id
AND fsh.object_type ='CASCADE'
AND fsh.history_mode=stathist
AND dt.table_name = fsh.object_name
AND LAST_GATHER_END_TIME IS NOT NULL
)
-- leave alone if excluded table
AND NOT EXISTS
(SELECT NULL
FROM fnd_exclude_table_stats fets,
fnd_oracle_userid fou ,
fnd_product_installations fpi
WHERE fou.oracle_username=upper(schemaname)
AND fou.oracle_id =fpi.oracle_id
AND fpi.application_id = fets.application_id
AND dt.table_name = fets.table_name
)
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dt.table_name
AND dts.owner =dt.owner
)
AND NOT EXISTS -- to avoid external tables
(SELECT NULL
FROM dba_external_tables de
WHERE de.table_name=dt.table_name
AND de.owner =dt.owner
) -- added this to avoid externale tables being selected
ORDER BY table_name;
SELECT iv.table_name,
iv.partition_name -- ,subpartition_name
BULK COLLECT
INTO names,
pnames -- ,spnames
FROM
( SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND EXISTS
( SELECT NULL
FROM dba_tables dt
WHERE dt.owner =dtm.table_owner
AND dt.table_name =dtm.table_name
AND dt.partitioned='NO'
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (mod_percent*NVL(dt.num_rows,0))/100
)
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dtm.table_name
AND dts.owner =dtm.table_owner
)
UNION ALL
SELECT dtm.table_name,
dtm.partition_name
FROM sys.dba_tab_modifications dtm
WHERE dtm.table_owner = upper(schemaname)
AND dtm.partition_name IS NOT NULL
AND dtm.TABLE_NAME NOT LIKE 'DR$%' -- added for Bug 8452962
AND dtm.table_name not like 'DR#%' -- added for Bug 8452962
AND dtm.table_name not like 'BIN$%' -- added for Bug 9542112
AND EXISTS
( SELECT NULL
FROM dba_tab_partitions dtp
WHERE dtp.table_owner =dtm.table_owner
AND dtp.table_name =dtm.table_name
AND dtp.partition_name=dtm.partition_name
AND
(
NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
)
> (mod_percent*NVL(dtp.num_rows,0))/100
)
AND NOT EXISTS
( SELECT NULL
FROM dba_tab_statistics dts
WHERE dts.stattype_locked IS NOT NULL
AND dts.table_name=dtm.table_name
AND dts.owner =dtm.table_owner
)
) iv
ORDER BY table_name;
FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel,
upd_ins_flag=>'S', percent=>NVL(adj_percent,def_estimate_pcnt));
FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
SELECT a.column_name,
NVL(a.hsize,254) hsize
FROM FND_HISTOGRAM_COLS a
WHERE a.table_name = upper(tabname)
AND
(
a.partition = upper(partname)
OR partname IS NULL
)
ORDER BY a.column_name;
SELECT a.index_name indname,
a.owner indowner ,
a.uniqueness uniq
FROM dba_indexes a
WHERE table_name = upper(tabname)
AND table_owner= upper(ownname)
ORDER BY index_name;
SELECT a.COLUMN_NAME1,
a.COLUMN_NAME2,
a.COLUMN_NAME3,
a.COLUMN_NAME4,
NVL(a.hsize,254) hsize
FROM FND_EXTNSTATS_COLS a
WHERE a.table_name = upper(tabname)
AND
(
a.partition = upper(partname)
OR partname IS NULL
)
ORDER BY a.column_name1;
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,def_estimate_pcnt));
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
SELECT DISTINCT a.application_id,
a.table_name ,
a.partition
FROM FND_HISTOGRAM_COLS a
WHERE
(
a.application_id = appl_id
OR appl_id IS NULL
)
ORDER BY a.application_id,
a.table_name;
SELECT a.column_name ,
NVL(a.hsize,254) hsize,
NVL(a.owner, upper(b.oracle_username)) ownname
FROM FND_HISTOGRAM_COLS a,
FND_ORACLE_USERID b ,
FND_PRODUCT_INSTALLATIONS c
WHERE a.application_id = appl_id
AND a.application_id = c.application_id (+)
AND c.oracle_id = b.oracle_id (+)
AND a.table_name = upper(tabname)
AND
(
a.partition = upper(partname)
OR partname IS NULL
)
ORDER BY a.column_name;
FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'S' );
FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'E' );
SELECT DISTINCT a.table_name,
a.application_id
FROM FND_HISTOGRAM_COLS a,
FND_ORACLE_USERID b ,
FND_PRODUCT_INSTALLATIONS c
WHERE
(
b.oracle_username= upper(ownname)
OR a.owner =upper(ownname)
)
AND a.application_id = c.application_id (+)
AND c.oracle_id = b.oracle_id (+)
ORDER BY 2 ,
1;
SELECT column_name,
NVL(hsize,254) hsize
FROM FND_HISTOGRAM_COLS a
WHERE a.application_id = appl_id
AND a.table_name = upper(tabname)
ORDER BY 1 ;
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>partname, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'S' );
FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>NULL, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'E' );
SELECT DECODE(index_type,'IOT - TOP', 'TRUE', 'FALSE')
INTO l_iot
FROM dba_indexes
WHERE owner = ownname
AND index_name = indname;
IF ((Upper(action) = 'INSERT') OR
(
Upper(action) = 'INS'
)
OR
(
Upper(action) = 'I'
)
) THEN
-- Check for existence of the table first in FND Dictionary
-- then in data dictionary
-- break out if it doesn't exist
BEGIN
SELECT 'EXIST'
INTO exist_flag
FROM fnd_tables a
WHERE a.table_name = upper(tabname)
AND a.application_id = appl_id ;
SELECT 'EXIST'
INTO exist_flag
FROM dba_tables
WHERE table_name = upper(tabname)
AND owner =
( SELECT b.oracle_username
FROM fnd_product_installations a,
fnd_oracle_userid b
WHERE a.application_id = appl_id
AND b.oracle_id = a.oracle_id
);
INSERT
INTO FND_EXCLUDE_TABLE_STATS
(
APPLICATION_ID ,
TABLE_NAME ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
appl_id ,
upper(tabname),
sysdate ,
1 ,
sysdate ,
1 ,
NULL
) ;
elsif ((Upper(action) = 'DELETE') OR
(
Upper(action) = 'DEL'
)
OR
(
Upper(action) = 'D'
)
) THEN
DELETE
FROM FND_EXCLUDE_TABLE_STATS
WHERE table_name = upper(tabname)
AND application_id = appl_id;
IF upper(action) = 'INSERT' THEN
BEGIN
-- Check for existence of the table first
-- break out if it doesn't exist
BEGIN
SELECT DISTINCT('EXIST')
INTO exist_flag
FROM dba_tab_columns a ,
fnd_oracle_userid b,
fnd_product_installations c
WHERE a.table_name = upper(tabname)
AND a.column_name = upper(colname)
AND c.application_id = appl_id
AND c.oracle_id = b.oracle_id
AND a.owner = b.oracle_username;
INSERT
INTO FND_HISTOGRAM_COLS
(
APPLICATION_ID ,
TABLE_NAME ,
COLUMN_NAME ,
PARTITION ,
HSIZE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
appl_id ,
upper(tabname) ,
upper(colname) ,
upper(partname),
hsize ,
sysdate ,
1 ,
sysdate ,
1 ,
NULL
) ;
elsif upper(action) = 'DELETE' THEN
BEGIN
DELETE
FROM FND_HISTOGRAM_COLS
WHERE application_id = appl_id
AND table_name = upper(tabname)
AND column_name = upper(colname)
AND
(
partition = upper(partname)
OR partition IS NULL
);
IF upper(action) = 'INSERT' THEN
BEGIN
-- Check for existence of the table first
-- break out if it doesn't exist
BEGIN
SELECT DISTINCT('EXIST')
INTO exist_flag
FROM dba_tab_columns a
WHERE a.table_name = upper(tabname)
AND a.column_name = upper(colname)
AND a.owner = upper(ownername);
INSERT
INTO FND_HISTOGRAM_COLS
(
application_id ,
OWNER ,
TABLE_NAME ,
COLUMN_NAME ,
PARTITION ,
HSIZE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
-1 ,
upper(ownername),
upper(tabname) ,
upper(colname) ,
upper(partname) ,
hsize ,
sysdate ,
1 ,
sysdate ,
1 ,
NULL
) ;
elsif upper(action) = 'DELETE' THEN
BEGIN
DELETE
FROM FND_HISTOGRAM_COLS
WHERE owner = upper(ownername)
AND table_name = upper(tabname)
AND column_name = upper(colname)
AND
(
partition = upper(partname)
OR partition IS NULL
);
/* select decode(floor(sum(tot)/(max(cnt)*75)),0,'YES','NO') HIST */
/* from (select count(col) cnt , count(*) tot */
/* from tab sample (S) */
/* where col is not null */
/* group by col); */
SELECT DISTINCT column_name col ,
b.table_name tab,
b.table_owner own
FROM dba_ind_columns a,
dba_indexes b
WHERE b.table_owner = upper(SUBSTR(tname,1,instr(tname,'.')-1))
AND
(
b.table_name = upper(SUBSTR(tname,instr(tname,'.')+1))
OR b.table_name LIKE upper(SUBSTR(tname,instr(tname,'.')+1))
)
AND b.uniqueness = 'NONUNIQUE'
AND b.index_type = 'NORMAL'
AND a.index_owner = b.owner
AND a.index_name = b.index_name
AND a.column_position = 1
ORDER BY 3 ,
2 ,
1 ;
sql_string := 'select ';
|| ' from (select count('
||c_rec.col
||') cnt, count(*) tot from ';
/* Procedure: UPDATE_HIST */
/* Desciption: Internal procedure to insert or update entries in table */
/* fnd_stats_hist. These values are used later if restartability is */
/* needed. */
/************************************************************************/
PROCEDURE UPDATE_HIST(schemaname VARCHAR2,
objectname IN VARCHAR2,
objecttype IN VARCHAR2,
partname IN VARCHAR2,
columntablename IN VARCHAR2,
degree IN NUMBER,
upd_ins_flag IN VARCHAR2,
percent IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION ;
UPDATE FND_STATS_HIST
SET parallel = degree ,
request_id = cur_request_id,
request_type = request_from ,
last_gather_start_time = sysdate ,
last_gather_date = '' ,
last_gather_end_time = '' ,
est_percent =percent
WHERE schema_name = upper(schemaname)
AND object_name = upper(objectname)
AND
(
partition = upper(partname)
OR partname IS NULL
)
AND
(
column_table_name = upper(columntablename)
OR columntablename IS NULL
)
AND object_type = upper(objecttype)
-- and request_id=cur_request_id -- commented this line for the bug 5648754
AND history_mode='L';
/* Added by mo, this segment checks if an entry was updated or not.
If not, a new entry will be added. */
IF SQL%ROWCOUNT = 0 THEN
INSERT
INTO FND_STATS_HIST
(
SCHEMA_NAME ,
OBJECT_NAME ,
OBJECT_TYPE ,
PARTITION ,
COLUMN_TABLE_NAME ,
LAST_GATHER_DATE ,
LAST_GATHER_START_TIME,
LAST_GATHER_END_TIME ,
PARALLEL ,
REQUEST_ID ,
REQUEST_type ,
HISTORY_MODE ,
EST_PERCENT
)
VALUES
(
upper(schemaname),
upper(objectname),
upper(objecttype),
upper(partname) ,
columntablename ,
'' ,
sysdate ,
'' ,
degree ,
cur_request_id ,
request_from ,
'L' ,
percent
);
UPDATE FND_STATS_HIST
SET last_gather_date = sysdate,
last_gather_end_time = sysdate
WHERE schema_name = upper(schemaname)
AND object_name = upper(objectname)
AND
(
partition = upper(partname)
OR partname IS NULL
)
AND
(
column_table_name = upper(columntablename)
OR columntablename IS NULL
)
AND object_type = upper(objecttype)
AND request_id =cur_request_id
AND history_mode='L';
elsif (stathist = 'FULL') THEN -- new option, old hist will not be updated
IF (upd_ins_flag = 'S') THEN
UPDATE FND_STATS_HIST
SET parallel = degree ,
request_id = cur_request_id,
request_type = request_from ,
last_gather_start_time = sysdate ,
last_gather_date = '' ,
last_gather_end_time = '' ,
est_percent =percent
WHERE schema_name = upper(schemaname)
AND object_name = upper(objectname)
AND
(
partition = upper(partname)
OR partname IS NULL
)
AND
(
column_table_name = upper(columntablename)
OR columntablename IS NULL
)
AND object_type = upper(objecttype)
AND history_mode='F' -- F for FULL mode
AND request_id =cur_request_id;
/* This segment checks if an entry was updated or not. This is still required even for
FULL mode, because multiple calls for the same object from the same session will have
the same cur_request_id. If not, a new entry will be added. */
IF SQL%ROWCOUNT = 0 THEN
INSERT
INTO FND_STATS_HIST
(
SCHEMA_NAME ,
OBJECT_NAME ,
OBJECT_TYPE ,
PARTITION ,
COLUMN_TABLE_NAME ,
LAST_GATHER_DATE ,
LAST_GATHER_START_TIME,
LAST_GATHER_END_TIME ,
PARALLEL ,
REQUEST_ID ,
REQUEST_type ,
HISTORY_MODE ,
EST_PERCENT
)
VALUES
(
upper(schemaname),
upper(objectname),
upper(objecttype),
upper(partname) ,
columntablename ,
'' ,
sysdate ,
'' ,
degree ,
cur_request_id ,
request_from ,
'F' ,
percent
);
UPDATE FND_STATS_HIST
SET last_gather_date = sysdate,
last_gather_end_time = sysdate
WHERE schema_name = upper(schemaname)
AND object_name = upper(objectname)
AND
(
partition = upper(partname)
OR partname IS NULL
)
AND
(
column_table_name = upper(columntablename)
OR columntablename IS NULL
)
AND object_type = upper(objecttype)
AND history_mode='F'
AND request_id =cur_request_id;
delete from fnd_stats_hist where object_name like upper(objectname) and schema_name like upper(schemaname);
/* end of UPDATE_HIST */
/************************************************************************/
/* Procedure: PURGE_STAT_HISTORY */
/* Desciption: Purges the fnd_stat_hist table based on the FROM_REQ_ID */
/* and TO_REQ_ID provided. */
/************************************************************************/
PROCEDURE PURGE_STAT_HISTORY(from_req_id IN NUMBER,
to_req_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
DELETE
FROM fnd_stats_hist
WHERE request_id BETWEEN from_req_id AND to_req_id;
DELETE
FROM fnd_stats_hist
WHERE last_gather_date BETWEEN to_date(purge_from_date_l,'DD-MM-YY') AND to_date(purge_to_date_l,'DD-MM-YY');
SELECT last_analyzed ,
sample_size ,
TRUNC(num_rows),
blocks
INTO last_analyzed,
sample_size ,
num_rows ,
blocks
FROM dba_tables
WHERE table_name = tableName
AND owner = schema;
SELECT last_analyzed ,
TRUNC(num_rows) ,
leaf_blocks ,
distinct_keys ,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor ,
uniqueness
INTO last_analyzed ,
num_rows ,
leaf_blocks ,
distinct_keys ,
avg_leaf_blocks_per_key,
avg_data_blocks_per_key,
clustering_factor ,
uniqueness
FROM dba_indexes
WHERE owner = lowner
AND index_name = indexName;
SELECT endpoint_number,
last_analyzed
FROM dba_histograms a,
dba_tab_columns b
WHERE a.owner = schema
AND a.table_name = tableName
AND a.column_name = columnName
AND a.owner = b.owner
AND a.table_name = b.table_name
AND a.column_name = b.column_name
AND endpoint_number NOT IN (0,
1);
IF upper(action) = 'INSERT' THEN
BEGIN
-- Check for existence of the table first
-- break out if it doesn't exist
BEGIN
SELECT DISTINCT('EXIST')
INTO exist_flag
FROM dba_tab_columns a1 , dba_tab_columns a2,
dba_tab_columns a3, dba_tab_columns a4,
fnd_oracle_userid b,
fnd_product_installations c
WHERE a1.table_name = upper(tabname)
AND a2.table_name = upper(tabname)
AND a3.table_name = upper(tabname)
AND a4.table_name = upper(tabname)
AND a1.column_name = upper(colname1)
AND a2.column_name = upper(colname2)
AND a3.column_name = NVL(upper(colname3), a3.column_name)
AND a4.column_name = NVL(upper(colname3), a4.column_name)
AND c.application_id = appl_id
AND c.oracle_id = b.oracle_id
AND a1.owner = b.oracle_username
AND a2.owner = b.oracle_username
AND a3.owner = b.oracle_username
AND a4.owner = b.oracle_username;
INSERT
INTO FND_EXTNSTATS_COLS
(
APPLICATION_ID ,
TABLE_NAME ,
COLUMN_NAME1 ,
COLUMN_NAME2 ,
COLUMN_NAME3 ,
COLUMN_NAME4 ,
PARTITION ,
HSIZE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
appl_id ,
upper(tabname) ,
upper(colname1) ,
upper(colname2) ,
NVL(upper(colname3), NULL) ,
NVL(upper(colname4), NULL) ,
upper(partname),
hsize ,
sysdate ,
1 ,
sysdate ,
1 ,
NULL
) ;
elsif upper(action) = 'DELETE' THEN
BEGIN
DELETE
FROM FND_EXTNSTATS_COLS
WHERE application_id = appl_id
AND table_name = upper(tabname)
AND column_name1 = upper(colname1)
AND column_name2 = upper(colname2)
AND nvl(column_name3,'-99') = nvl(upper(colname3),'-99')
AND nvl(column_name4,'-99') = nvl(upper(colname4),'-99')
AND
(
partition = upper(partname)
OR partition IS NULL
);
SELECT table_name,
owner
FROM dba_tables dt
WHERE owner = schema
AND
(
iot_type <> 'IOT_OVERFLOW'
OR iot_type IS NULL
)
AND
(
(
sysdate - NVL(last_analyzed, to_date('01-01-1900', 'MM-DD-YYYY'))
)
>days_old
OR days_old IS NULL
)
ORDER BY table_name;
SELECT index_name,
owner
FROM dba_indexes
WHERE table_owner = schema
AND table_name = tableName
ORDER BY index_name;
select a.column_name
from fnd_histogram_cols a,
fnd_oracle_userid b,
fnd_product_installations c
where a.application_id = c.application_id
and c.oracle_id = b.oracle_id
and b.oracle_username = schema
and a.table_name = tableName
order by a.column_name;*/
SELECT a.column_name
FROM fnd_histogram_cols a
WHERE a.table_name = tableName
ORDER BY a.column_name;
SELECT COLUMN_NAME ,
NUM_DISTINCT,
NUM_NULLS ,
DENSITY ,
SAMPLE_SIZE ,
LAST_ANALYZED
FROM dba_tab_columns
WHERE owner = schema
AND table_name = tableName
ORDER BY column_name;
SELECT REPLACE(SUBSTR(version,1,instr(version,'.',1,2)-1),'.')
INTO db_versn
FROM v$instance;