script. /webapps/oracle/full_drop_table_record.log
1,spool /webapps/oracle/batch_delete.sql
2,Select 'delete from '||Table_Name||';' From User_Tables Where Table_Name Not Like 'C%' And Table_Name Not Like 'O%' And Table_Name Not Like 'R%'
and table_name not in ('E_SCHOOLINFO','E_GRADEINFO','E_SCHOOL_GRADE','E_SUBJECTINFO') order by table_name;
and table_name not in ('E_SCHOOLINFO','E_GRADEINFO','E_SCHOOL_GRADE','E_SUBJECTINFO') order by table_name;
3,spool off
4,@/spool /webapps/oracle/batch_delete.sql
5,查看以上的full_drop_table_record.log日志文件,有些表的记录删除不了,因为它们是主表,有子表依赖于它
6,Select Table_Name,Num_Rows From User_Tables Where Table_Name Not Like 'C%' And
Table_Name Not Like 'O%' And Table_Name Not Like 'R%'
And Table_Name Not In ('E_SCHOOLINFO','E_GRADEINFO','E_SCHOOL_GRADE','E_SUBJECTINFO') And Num_Rows!=0 And Table_Name Not Like '%BAK'
and table_name not like 'APP%' and table_name not like 'PB%' and table_name not like 'T%';
Table_Name Not Like 'O%' And Table_Name Not Like 'R%'
And Table_Name Not In ('E_SCHOOLINFO','E_GRADEINFO','E_SCHOOL_GRADE','E_SUBJECTINFO') And Num_Rows!=0 And Table_Name Not Like '%BAK'
and table_name not like 'APP%' and table_name not like 'PB%' and table_name not like 'T%';
再次查看仍有记录存在的表
7,无奈 之下,先用手工一个个delete from 上面查到的表
8,提示删除不了,有子记录存在
9,再用dbms_stats.gather_schema_stats('PD');
10,再次运行delete from 删除不了的表;可以删除了
小结:
oracle 统计有些慢,如果业务表更新太快,表的统计会不准;
会不会与因为删除表(主表)删除不了,与统计数据没有及时更新有关系呢
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-668662/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-668662/