Oracle какие права нужны для truncate table
TRUNCATE — опустошить таблицу или набор таблиц
Синтаксис
Описание
Команда TRUNCATE быстро удаляет все строки из набора таблиц. Она действует так же, как безусловная команда DELETE для каждой таблицы, но гораздо быстрее, так как она фактически не сканирует таблицы. Более того, она немедленно высвобождает дисковое пространство, так что выполнять операцию VACUUM после неё не требуется. Наиболее полезна она для больших таблиц.
Параметры
Имя таблицы (возможно, дополненное схемой), подлежащей опустошению. Если перед именем таблицы указано ONLY , очищается только заданная таблица. Без ONLY очищается и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание * , чтобы явно обозначить, что блокировка затрагивает и все дочерние таблицы. RESTART IDENTITY
Автоматически перезапускать последовательности, связанные со столбцами опустошаемой таблицы. CONTINUE IDENTITY
Не изменять значения последовательностей. Это поведение по умолчанию. CASCADE
Автоматически опустошать все таблицы, ссылающиеся по внешнему ключу на заданные таблицы, или на таблицы, затронутые в результате действия CASCADE . RESTRICT
Отказать в опустошении любых таблиц, на которые по внешнему ключу ссылаются другие таблицы, не перечисленные в этой команде. Это поведение по умолчанию.
Замечания
Чтобы опустошить таблицу, необходимо иметь право TRUNCATE для этой таблицы.
Команда TRUNCATE запрашивает блокировку ACCESS EXCLUSIVE для каждой таблицы, которую она обрабатывает. Когда указано RESTART IDENTITY , все последовательности, которые должны быть перезапущены, также блокируются исключительно. В случаях, когда требуется обеспечить параллельный доступ к таблице, следует использовать DELETE .
TRUNCATE нельзя использовать с таблицей, на которую по внешнему ключу ссылаются другие таблицы, если только и эти таблицы не опустошаются этой же командой. Проверка допустимости очистки в таких случаях потребовала бы сканирования таблицы, а главная идея данной команды в том, чтобы не делать этого. Для автоматической обработки всех зависимых таблиц можно использовать указание CASCADE — но будьте очень осторожны с ним, иначе вы можете потерять данные, которые не собирались удалять!
При выполнении TRUNCATE не срабатывают никакие триггеры ON DELETE , которые могут быть настроены для таблиц. Однако при этом срабатывают триггеры ON TRUNCATE . Если триггеры ON TRUNCATE определены для любых из этих таблиц, то все триггеры BEFORE TRUNCATE срабатывают до того, как происходит опустошение, а все триггеры AFTER TRUNCATE срабатывают после того, как завершается опустошение последней таблицы и все последовательности сбрасываются. Триггеры срабатывают по порядку обработки таблиц (сначала для таблиц, перечисленных в команде, затем для тех, что затрагиваются каскадно).
Команда TRUNCATE небезопасна с точки зрения MVCC. После опустошения таблицы она будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до опустошения. За подробностями обратитесь к Разделу 13.5.
TRUNCATE является надёжной транзакционной операцией в отношении данных в таблицах: опустошение будет безопасно отменено, если окружающая транзакция не будет зафиксирована.
С указанием RESTART IDENTITY подразумеваемые операции ALTER SEQUENCE RESTART также выполняются транзакционно; то есть, они будут отменены, если окружающая транзакция не будет зафиксирована. Это отличается от обычного поведения ALTER SEQUENCE RESTART . Учтите, что если до того, как транзакция отменится, будут выполнены какие-либо дополнительные операции с последовательностями, эффект этих операций также будет отменён, но не их влияние на значение currval() ; то есть после транзакции currval() продолжит возвращать последнее значение последовательности, полученное внутри прерванной транзакции, хотя сама последовательность уже может быть несогласованной с ним. Подобным образом обычно ведёт себя currval() после сбоя транзакции.
TRUNCATE в настоящее время не поддерживается для сторонних таблиц. Из этого следует, что если у целевой таблицы есть дочерние таблицы, являющиеся сторонними, команда не будет выполнена.
Примеры
Опустошение таблиц bigtable и fattable :
Та же операция и сброс всех связанных генераторов последовательностей:
Опустошение таблицы othertable и каскадная обработка всех таблиц, ссылающихся на othertable по ограничениям внешнего ключа:
Совместимость
Стандарт SQL:2008 включает команду TRUNCATE с синтаксисом TRUNCATE TABLE имя_таблицы . Предложения CONTINUE IDENTITY / RESTART IDENTITY также описаны в стандарте, но с небольшими отличиями, хотя их назначение похоже. Поведение этой команды при параллельных операциях, согласно стандарту, отчасти определяются реализацией, так что приведённые выше замечания при необходимости следует учитывать и сопоставлять с другими реализациями.
У меня есть две временные таблицы, первый уровень JOIN для 5 таблиц и пара сотен тысяч строк до таблицы с 6 столбцами и размером менее 10 тыс. Строк. Поскольку пользователь в одном сеансе использует фильтры, у меня есть вторая временная таблица, которая содержит отфильтрованные результаты, эти фильтры изменяются в соответствии с прихотью конечного пользователя.
Каждый раз, когда меняются фильтры, мне нужно TRUNCATE отфильтрованной таблицы и воссоздать его из первой таблицы.
По сути, я связан разрешениями, администратор БД не хочет давать мне разрешения TRUNCATE для ВРЕМЕННОГО СТОЛА.
Это просто неправильный метод (хотя работает, правда ). Закрытие и открытие нового сеанса заставляет меня потерять первый стол, а открытие второго сеанса для второго стола лишает меня доступа к первому.
Используя SQL Developer, похоже, что DELETE и ROLLBACK занимают примерно одно и то же время, возможно, небольшое преимущество для ROLLBACK.
Общий вопрос таков:
Что вы порекомендуете? Я ошибаюсь в своем понимании некоторых из этого?
Что лучше, ROLLBACK или DELETE (для временной таблицы)?
Являются ли роли настолько узко контролируемыми / определенными в Oracle, что нам нужно ограничить TRUNCATE для временной таблицы?
КСТАТИ: Это система PeopleSoft, так что все участники гиперчувствительны к разрешениям и ролям
4 ответа
Тестирование на 11.2.0.4:
Откат примерно такой же, как усечение, и оба намного быстрее, чем удаление. Логически усечение и откат делают одно и то же.
Для вашего сценария кажется, что вам просто нужно зафиксировать вставку в первый GTT, затем несколько раз откатить и вставить во второй:
commit сохраняет строки в первом GTT; rollback затем влияет только на данные во втором GTT и не касается первого.
Чтобы усечь таблицу, таблица должна быть в вашей схеме, или у вас должна быть системная привилегия DROP ANY TABLE.
Поскольку вы не можете урезать таблицы, предположительно, нет в вашей схеме; и вы можете надеяться, что ваш администратор БД не захочет дать вам очень мощную и опасную привилегию drop any table . Вы не можете предоставить привилегию, разрешающую только усечение определенной таблицы в другой схеме.
Администратор БД имеет право не предоставлять вам разрешение TRUNCATE. Причина? Хотя используемый вами метод не может быть идеальным для вас , он позволяет вам выполнить свою задачу без предоставления вам ненужных разрешений . Многие организации соблюдают Принцип наименьших привилегий, и если ваша организация является одной из многих, которые делать, администратор базы данных просто делает свою работу.
Кроме того, усечения, откат и удаление - это разные операции для базы данных. Например, когда вы удаляете запись, база данных удаляет эту запись из рассматриваемой таблицы И также генерирует сегменты отката. Когда вы усекаете таблицу, этот откат больше не генерируется, что может помешать вам восстановить данные в случае сбоя, который может нарушить политику хранения вашей организации. Точно так же выполнение отката не просто изменяет данные во временной таблице - также генерирует информацию повторного выполнения. Если вы не можете определить эти различия, почему администратор БД должен предоставить вам эти привилегии?
Вообще говоря, лучший метод будет зависеть от многих переменных - какова ваша политика хранения восстановления? В вашей организации недостаточно места на диске, что делает АБД усталым от создания слишком большого количества журнальной информации? Существуют ли цели производительности, которые должны быть достигнуты? Есть много факторов, которые могут оказать влияние.
Вот предостережение к решению при работе с VBA. Объект ADO автоматически ПРИНИМАЕТ все транзакции, это делает недействительной всю логику, представленную здесь.
До и после (соответственно) транзакции мы намерены выполнить ROLLBACK.
Когда вы определяете свою первую временную таблицу, вы можете добавить предложение ON COMMIT и указать, что данные должны храниться в течение всего сеанса. Для второй временной таблицы по умолчанию (т.е. предложение DELETE ROWS) должно быть усечено, когда каждая транзакция зафиксирована.
Поэтому, когда пользователь изменяет критерий поиска, передайте предыдущую поисковую транзакцию, чтобы данные были усечены. Я вполне уверен, что Oracle усекает данные, а не удаляет отдельные строки.
Удаляет все строки в таблице или указанные секции таблицы, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
database_name
Имя базы данных.
schema_name
Имя схемы, которой принадлежит таблица.
table_name
Имя таблицы, которая должна быть усечена, или таблицы, из которой удаляются все строки. table_name должно быть литералом. table_name не может быть функцией OBJECT_ID() или переменной.
WITH ( PARTITIONS ( < <partition_number_expression> | <range> > [ , . n ] ) )
Применимо к: SQL Server (с SQL Server 2016 (13.x); до текущей версии)
Указывает секции для усечения или секции, из которых удаляются все строки. Если таблица не секционирована, аргумент WITH PARTITIONS приведет к возникновению ошибки. Если предложение WITH PARTITIONS не указано, будет усечена вся таблица.
<partition_number_expression> можно указать одним из следующих способов.
Указав номер секции, например WITH (PARTITIONS (2))
Указав номера нескольких секций, разделив их запятыми, например WITH (PARTITIONS (1, 5))
Указав диапазоны секций и отдельные секции, например WITH (PARTITIONS (2, 4, 6 TO 8))
<range> можно указать в виде номеров секций, разделенных словом TO, например WITH (PARTITIONS (6 TO 8)) .
Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).
Remarks
Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE.
Используется меньший объем журнала транзакций.
Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
Обычно используется меньшее количество блокировок.
Если инструкция DELETE выполняется с блокировкой строк, для удаления блокируется каждая строка таблицы. Инструкция TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемы (SCH-M)) и страницу, но не каждую строку.
В таблице остается нулевое количество страниц, без исключений.
После выполнения инструкции DELETE в таблице могут все еще оставаться пустые страницы. Например, чтобы освободить пустые страницы в куче, необходима, как минимум, монопольная блокировка таблицы (LCK_M_X). Если операция удаления не использует блокировку таблицы, таблица (куча) будет содержать множество пустых страниц. В индексах после операции удаления могут оказаться пустые страницы, хотя эти страницы будут быстро освобождены процессом фоновой очистки.
Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE .
Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.
Для операции TRUNCATE TABLE можно выполнить откат.
Ограничения
Нельзя использовать TRUNCATE TABLE в таблицах в следующих случаях.
На таблицу ссылается ограничение FOREIGN KEY. Таблицу, имеющую внешний ключ, ссылающийся сам на себя, можно усечь.
Таблица является частью индексированного представления.
Таблица опубликована с использованием репликации транзакций или репликации слиянием.
Это темпоральная таблица с управлением версиями.
На таблицу ссылается ограничение EDGE.
Для таблиц с какими-либо из этих характеристик следует использовать инструкцию DELETE.
Инструкция TRUNCATE TABLE не может активировать триггер, поскольку она не записывает в журнал удаление отдельных строк. Дополнительные сведения см. в разделе CREATE TRIGGER (Transact-SQL).
В Azure Synapse Analytics и Система платформы аналитики (PDW):
Инструкцию TRUNCATE TABLE нельзя использовать в инструкции EXPLAIN.
Инструкцию TRUNCATE TABLE невозможно выполнить внутри транзакции.
Усечение больших таблиц
В Microsoft SQL Server существует возможность удалять или усекать таблицы, которые имеют больше 128 экстентов, не удерживая одновременные блокировки для всех экстентов, предназначенных для удаления.
Разрешения
Минимально необходимым разрешением является ALTER для table_name. Разрешения TRUNCATE TABLE назначаются по умолчанию владельцу таблицы, членам предопределенной роли сервера sysadmin , а также предопределенных ролей базы данных db_owner и db_ddladmin , и они не могут быть переданы. Тем не менее инструкцию TRUNCATE TABLE можно встроить в модуль, например в хранимую процедуру, и предоставить соответствующие разрешения этому модулю с помощью предложения EXECUTE AS .
Примеры
A. Усечение таблицы
В ходе выполнения следующего примера удаляются все данные таблицы JobCandidate . Инструкции SELECT включены до и после инструкции TRUNCATE TABLE для сравнения результатов.
Б. Усечение секций таблицы
Применимо к: SQL Server (с SQL Server 2016 (13.x); до текущей версии)
В следующем примере выполняется усечение указанных секций секционированной таблицы. Синтаксис WITH (PARTITIONS (2, 4, 6 TO 8)) задает усечение секций с номерами 2, 4, 6, 7 и 8.
У меня есть две временные таблицы: JOIN на первом уровне по 5 таблицам и несколько сотен тысяч строк до таблицы с 6 столбцами и чем-то менее 10 тыс. Строк. Поскольку пользователь в одном сеансе использует фильтры, у меня есть вторая временная таблица, содержащая отфильтрованные результаты, эти фильтры меняются по желанию конечного пользователя.
Каждый раз, когда фильтры меняются, мне нужно TRUNCATE отфильтрованную таблицу и воссоздать ее из первой таблицы.
В сущности, я связан разрешениями, DBA не хочет давать мне разрешения TRUNCATE в ВРЕМЕННОМ ТАБЛИЦЕ.
Это просто неправильный метод (хотя работает, по общему признанию). Закрывая и открывая новый сеанс, я теряю первую таблицу, открывая второй сеанс для второй таблицы, препятствует мне иметь доступ к первому.
Используя SQL Developer, похоже, что DELETE и ROLLBACK принимают примерно одно и то же время, возможно, небольшое преимущество для ROLLBACK.
Что вы порекомендуете? Неужели я ошибаюсь в своем понимании этого?
Что лучше, ROLLBACK или DELETE (на временной таблице)?
Являются ли роли так узко контролируемыми/определенными в Oracle, что нам нужно ограничить TRUNCATE на временной таблице?
BTW: Это система PeopleSoft, поэтому все участники гиперчувствительны к разрешениям и ролям
спросил(а) 2017-12-28T18:55:00+03:00 3 года, 10 месяцев назадТестирование на 11.2.0.4:
Откат примерно такой же, как и усечение, и оба они намного быстрее, чем удаление. Логически усечение и откат делают почти то же самое.
Для вашего сценария вам кажется, вам просто нужно вставить вставку в первый GTT, затем повторно откат и вставить во второй:
commit сохраняет строки в первом GTT; rollback тогда влияет только на данные во втором GTT и не касается первого.
Чтобы усечь таблицу, таблица должна быть в вашей схеме или у вас должна быть привилегия системы DROP ANY TABLE.
Поскольку вы не можете обрезать таблицы, предположительно, не в вашей схеме; и вы можете с полным основанием понять, почему ваш администратор базы данных не захочет дать вам очень мощную и опасную возможность drop any table привилегий. Вы не можете предоставить привилегию разрешить только усечение конкретной таблицы в другой схеме.
ответил(а) 2017-12-28T19:29:00+03:00 3 года, 10 месяцев назадЗдесь предостережение от решения при работе с VBA. Объект ADO автоматически передает все транзакции, что делает недействительной всю представленную здесь логику.
до и после (соответственно) транзакции, которую мы намереваемся ROLLBACK.
ответил(а) 2017-12-29T23:41:00+03:00 3 года, 10 месяцев назадКогда вы определяете свою первую временную таблицу, вы можете добавить предложение ON COMMIT и указать, что данные должны поддерживаться на протяжении всего сеанса. Для второй временной таблицы значение по умолчанию (т.е. Условие DELETE ROWS) должно быть усечено при каждой транзакции.
Таким образом, когда пользователь изменяет критерий поиска, зафиксируйте предыдущую транзакцию поиска, чтобы данные были усечены. Я довольно уверен, что Oracle усекает данные, а не удаляет отдельные строки.
ответил(а) 2017-12-28T20:09:00+03:00 3 года, 10 месяцев назадАдминистратор базы данных имеет право не предоставлять вам разрешение TRUNCATE. Причина? Хотя используемый вами метод может быть не идеальным для вас, он позволяет вам выполнять свою задачу, не предоставляя вам лишние разрешения. Многие организации придерживаются принципа наименьшей привилегии, и если ваша организация является одной из многих, которые делают это, администратор баз данных просто выполняет свою работу.
Кроме того, усечения, откаты и удаления являются различными операциями для базы данных. Например, когда вы удаляете запись, база данных удаляет эту запись из рассматриваемой таблицы. И также генерирует сегменты отката. Когда вы обрезаете таблицу, этот откат больше не генерируется, что может помешать вам восстановить данные в случае сбоя, что может нарушить политику хранения вашей организации. Аналогичным образом, выполнение отката не только изменяет данные во временной таблице, но также генерирует повторную информацию. Если вы не можете определить эти различия, почему администратор баз данных предоставит вам эти привилегии?
Вообще говоря, лучший метод будет варьироваться от множества переменных - какова ваша политика хранения восстановления? Низкая ли ваша организация на дисковой площадке, из-за чего администратор базы данных устал генерировать слишком много информации о журнале? Есть ли цели в области производительности, которые необходимо выполнить? Есть много факторов, которые могут оказать влияние.
Читайте также: