[Home] [Help]
1022: procedure create_perm_tables is
1023: table_columns bsc_update_util.t_array_temp_table_cols;
1024: l_tablespace varchar2(300);
1025: l_idx_tablespace varchar2(300);
1026: all_tables all_tables_tb;
1027: table_name varchar2(40);
1028: stmt varchar2(2000);
1029: flag boolean;
1030: Begin
1043: table_columns(table_columns.count).data_size:=400;
1044: table_columns(table_columns.count).add_to_index:='N';
1045: /* */
1046: flag:=false;
1047: all_tables:=get_db_table_parameters(table_name,get_table_owner(table_name));
1048: if all_tables.count>0 then
1049: if all_tables(1).TEMPORARY='Y' then
1050: /*this is old implementation. drop and recreate */
1051: log(table_name||' is Temp Table. Dropping to create as permanent');
1044: table_columns(table_columns.count).add_to_index:='N';
1045: /* */
1046: flag:=false;
1047: all_tables:=get_db_table_parameters(table_name,get_table_owner(table_name));
1048: if all_tables.count>0 then
1049: if all_tables(1).TEMPORARY='Y' then
1050: /*this is old implementation. drop and recreate */
1051: log(table_name||' is Temp Table. Dropping to create as permanent');
1052: stmt:='drop table '||table_name;
1045: /* */
1046: flag:=false;
1047: all_tables:=get_db_table_parameters(table_name,get_table_owner(table_name));
1048: if all_tables.count>0 then
1049: if all_tables(1).TEMPORARY='Y' then
1050: /*this is old implementation. drop and recreate */
1051: log(table_name||' is Temp Table. Dropping to create as permanent');
1052: stmt:='drop table '||table_name;
1053: BSC_APPS.Do_DDL(stmt,AD_DDL.DROP_TABLE,table_name);
2067:
2068: procedure analyze_table(p_table varchar2,p_interval number) is
2069: l_table varchar2(100);
2070: l_owner varchar2(100);
2071: l_all_tables all_tables_tb;
2072: l_analyze boolean;
2073: Begin
2074: l_table:=substr(p_table,instr(p_table,'.')+1);
2075: l_owner:=get_table_owner(p_table);
2076: l_analyze:=false;
2077: if p_interval is null or p_interval=0 then
2078: l_analyze:=true;
2079: else
2080: l_all_tables:=get_db_table_parameters(l_table,l_owner);
2081: if l_all_tables(1).last_analyzed is null then
2082: l_analyze:=true;
2083: else
2084: if (sysdate-l_all_tables(1).last_analyzed)>p_interval then
2077: if p_interval is null or p_interval=0 then
2078: l_analyze:=true;
2079: else
2080: l_all_tables:=get_db_table_parameters(l_table,l_owner);
2081: if l_all_tables(1).last_analyzed is null then
2082: l_analyze:=true;
2083: else
2084: if (sysdate-l_all_tables(1).last_analyzed)>p_interval then
2085: l_analyze:=true;
2080: l_all_tables:=get_db_table_parameters(l_table,l_owner);
2081: if l_all_tables(1).last_analyzed is null then
2082: l_analyze:=true;
2083: else
2084: if (sysdate-l_all_tables(1).last_analyzed)>p_interval then
2085: l_analyze:=true;
2086: end if;
2087: end if;
2088: end if;
2107: log_n('Exception in analyze_table '||sqlerrm);
2108: raise;
2109: End;
2110:
2111: function get_db_table_parameters(p_table varchar2,p_owner varchar2) return all_tables_tb is
2112: l_tables all_tables_tb;
2113: cursor c1 is select * from all_tables where table_name=upper(p_table) and owner=upper(p_owner);
2114: Begin
2115: open c1;
2108: raise;
2109: End;
2110:
2111: function get_db_table_parameters(p_table varchar2,p_owner varchar2) return all_tables_tb is
2112: l_tables all_tables_tb;
2113: cursor c1 is select * from all_tables where table_name=upper(p_table) and owner=upper(p_owner);
2114: Begin
2115: open c1;
2116: fetch c1 into l_tables(1);
2109: End;
2110:
2111: function get_db_table_parameters(p_table varchar2,p_owner varchar2) return all_tables_tb is
2112: l_tables all_tables_tb;
2113: cursor c1 is select * from all_tables where table_name=upper(p_table) and owner=upper(p_owner);
2114: Begin
2115: open c1;
2116: fetch c1 into l_tables(1);
2117: close c1;