Блог

У меня есть таблица под названием schema.exampletab, в которой есть разделы, где имя раздела указано в форме Pxxxxxx, где x-это периоды разделов (год и месяц YYYYMM), а затем есть локальный PK. Я хочу удалить раздел, но при попытке удалить его я получаю сообщение об ошибке ORA-14006: Неверное имя раздела, но когда я пытаюсь просто запросить данные из раздела, он выбирает и отображает данные из соответствующего раздела. Обратите внимание, что я использую планировщик, который использует дату в качестве параметра, используя YYYYMMDD, поэтому я обычно подстрока.

Например, я могу запросить данные, используя следующую инструкцию:

 SELECT * FROM schema.exampletab PARTITION (P202110);  

Это возвращает 20 строк данных в моей таблице. Затем я пытаюсь удалить раздел, используя следующую инструкцию:

 ALTER TABLE schema.exampletab DROP PARTITION (concat(P,substr('20211011',1,6))  UPDATE INDEXES;  

Однако это приводит к сообщению об ошибке ORA-14006. Чтобы проверить, существует ли раздел, я попытался проверить, что схема, таблица и раздел существуют в разделе all_tab_partitions, где регистрируются все разделы в базе данных, над которой я работаю.

 select partition_name from all_tab_partitions  where table_owner = 'schema' and table_name = 'exampletab' and  substr(partition_name,2,7) = substr('20211022',1,6);  

Это возвращает имя раздела P202110 в результате запроса.

Я надеялся, что смогу использовать раздел drop с таким подзапросом:

 ALTER TABLE schema.exampletab DROP PARTITION select partition_name from all_tab_partitions  where table_owner = 'schema' and table_name = 'exampletab' and  substr(partition_name,2,7) = substr('20211022',1,6);  

Однако это все равно приводит к ошибке ORA-14006. Я пытался написать имя раздела как «P202110» и P202110 вместо родительского имени, но безуспешно.

Как я могу написать инструкцию drop partition таким образом, чтобы она удаляла раздел вместо того, чтобы выдавать ошибку ORA-14006?

Это то, что я должен делать регулярно, и поэтому было бы неплохо знать, как правильно выбирать и удалять разделы или усекать их и т. Д. Кроме того, я использую планировщик, который запускает sql-запросы с заданными интервалами, поэтому мне нужно указать даты в качестве параметра, что означает, что в моем примере кода значение YYYYMM-это значение параметра, которое анализируется, и поэтому мне нужно согласовать P с этим выводимым значением параметра, потому что, если я наберу только P202110, он фактически отбросит его.

У вас есть два варианта, учитывая, что вы используете sysdate , чтобы узнать, какой раздел вам следует удалить.

Вариант 1 -gt; Динамический SQL, и результат должен быть выполнен вне запроса

 select ' alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||' update indexes ; '  from  dba_Tab_partitions where table_owner = 'your_schema'  and table_name = 'your_table'  and partition_name = 'P'||substr(to_char(sysdate,'yyyymmdd'),1,6) ; ;  

Этот запрос дает вам вывод команды, но вам нужно ее выполнить

 alter table yourschema.yourtable drop partition P202110 update indexes;   

Пример в моем собственном окружении

 SQLgt; select ' alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||' update indexes ; ' from dba_Tab_partitions where table_owner = 'FDM_DATA' and table_name = 'FDM_DIM_CUSTOMER' and partition_name = 'P_'||substr(to_char(sysdate,'yyyymmdd'),1,6) ;  'ALTERTABLE'||TABLE_OWNER||'.'||TABLE_NAME||'DROPPARTITION'||PARTITION_NAME||'UP --------------------------------------------------------------------------------  alter table FDM_DATA.FDM_DIM_CUSTOMER drop partition P_202110 update indexes ;  

Вариант 2 -gt; PLSQL

Лучший вариант-использовать PLSQL . Небольшой пример, когда вы хотите удалить только один раздел на основе текущего sysdate . Вы можете расширить / изменить этот код, чтобы он охватывал любые временные рамки.

 declare  v_owner varchar2(128) := 'YOUR_SCHEMA';  v_table_name varchar2(128) := 'YOUR_TABLE';  v_partition_name varchar2(128); begin   select partition_name into v_partition_name from all_tab_partitions  where table_owner = v_owner and  table_name = v_table_name and  partition_name = 'P'||substr(to_char(sysdate,'yyyymmdd'),1,6) ;  execute immediate 'alter table '||v_owner||'.'||v_table_name||' drop partition '||v_partition_name||' update indexes' ; exception  when no_data_found then null; -- if there is no partition, nothing to do and no error is raised when others then raise; end; /  

Вы не можете использовать «выражения» в имени раздела

Это неправильно:

 ALTER TABLE schema.exampletab DROP PARTITION (concat(P,substr('20211011',1,6))  UPDATE INDEXES;  

это правильно:

 ALTER TABLE schema.exampletab DROP PARTITION P20211011  UPDATE INDEXES;  

Вы можете использовать этот код для динамического удаления разделов. Просто обновите C_OWNER ,C_TABLE_NAME , C_PARTITION_TEMPLATE в заголовке. Пожалуйста, обратите внимание, что команда sql строится динамически, но когда она будет готова, имя раздела будет полностью проанализировано.

 declare  C_OWNER varchar2(128) := 'MYOWNER';  C_TABLE_NAME varchar2(128) := 'MYTABLE';  C_PARTITION_TEMPLATE varchar2(128) := '2011';  cursor part_cur is   select *   from all_tab_partitions  where table_owner=C_OWNER and  table_name = C_TABLE_NAME and  partition_name like '%'||C_PARTITION_TEMPLATE||'%'  order by partition_position; BEGIN  for part_rec in part_cur loop  execute immediate 'ALTER TABLE "'||part_rec.table_owner||'"."'||part_rec.table_name||'"'||  ' DROP PARTITION ("'||part_rec.partition_name||'") UPDATE INDEXES';  end loop; END; /