oracle sql范围查询语句,几个oracle的sql查询语句

1、查找外键对应的表

select *

from user_constraints cc

where cc.r_constraint_name in

(select c.r_constraint_name

from user_constraints c

where c.constraint_type = 'R'

and c.constraint_name like '%TABLE%')

2、按照时间添加表分区

alter  table mytable  add partition PT_2015values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace MY_DATA;

3、查找无效索引

SELECT OWNER, table_name, INDEX_NAME, STATUS

FROM DBA_INDEXES

WHERE STATUS = 'UNUSABLE';

SELECT OWNER, table_name, INDEX_NAME, STATUS

FROM ALL_INDEXES

WHERE STATUS = 'UNUSABLE';

4、重建索引

alter index  IDX_MYIDX rebuild;

5、查找大表，分区

select t.owner,        t.segment_name,        t.partition_name,        t.bytes / 1024 / 1024,        'alter table ' || t.owner || '.' || t.segment_name ||        ' drop partition ' || t.partition_name || ';'   from dba_segments t  where (t.segment_type = 'TABLE PARTITION' or t.segment_type = 'TABLE')    and t.partition_name is not null  order by t.partition_name, t.bytes desc;