Таблица всех колонок oracle

Сведения о столбцах

Чтобы найти подробные сведения о столбцах в таблицах, можно выполнить запрос представления USER_TAB_COLUMNS .

Хотя представление USER_TABLES содержит сведения об именах таблиц и хранилище, подробные сведения о столбцах находятся в представлении USER_TAB_COLUMNS .

Это представление содержит следующие сведения:

типы данных столбцов;

значения длины типов данных;

точность и масштаб для столбцов NUMBER ;

допускаются ли значения NULL (существует ли ограничение NOT NULL на столбец?);

значение по умолчанию.

Примечание . Полный список и описания столбцов, присутствующих в представлении USER_TAB_COLUMNS , содержатся в разделе « USER_TAB_COLUMNS » документа Oracle Database Reference (Справочник по базе данных Oracle).

Выполняя запросы таблицы, можно найти сведения о столбцах, такие как имена, типы данных, значения длины типов данных, ограничения NULL и значения по умолчанию для столбцов.

В приведенном примере отображаются столбцы, типы данных, значения длины данных и ограничения NULL для таблицы EMPLOYEES . Обратите внимание, что эти сведения сходны с выходными данными команды DESCRIBE .

Чтобы просмотреть сведения о столбцах, помеченных как неиспользуемые, используется представление словаря USER_UNUSED_COL_TABS .

Источник

Таблицы

Данные в реляционных базах данных хранятся в таблицах. Таблицы — это ключевой объект, с которыми придется работать в SQL.

Таблицы в БД совсем не отличаются от тех таблиц, с которыми все уже знакомы со школы — они состоят из колонок и строк.

Каждая колонка в таблице имеет своё имя и свой тип, т.е. тип данных, которые будут в ней содержаться. Помимо типа данных для колонки можно указать максимальный размер данных, которые могут содержаться в этой таблице.

Например, мы можем указать, что для колонки возраст тип данных — это целое число, и это число должно состоять максимум из 3-х цифр. Т.о. максимальное число, которое может содержаться в этой колонке = 999. А с помощью дополнительных конструкций можно задать и правила проверки корректности для значения в колонке,- например, мы можем указать, что для колонки возраст в таблице минимальное значение = 18.

Создание таблицы

После выполнения данной sql-команды в базе данных будет создана таблица под названием hello . Эта таблица будет содержать всего одну колонку под названием text_to_hello . В этой колонке мы можем хранить только строковые значения(т.е. любой текст, который можно ввести с клавиатуры) длинной до 100 байт.

Обратите внимание на размер допустимого текста в колонке text_to_hello . 100 байт — это не одно и то же, что и 100 символов! Для того, чтобы сказать базе данных Oracle, что длина строки может быть 100 символов, нужно было определить столбец следующим образом:

Создание таблицы с несколькими полями

В таблице может много столбцов. Напимер, можно создать таблицу с тремя, пятью или даже 100 колонками. В версиях oracle с 8i по 11g максимальное количество колонок в одной таблице достигает 1000.

Для того, чтобы создать таблицу с несколькими колонками, нужно перечислить все колонки через запятую.

Например, создадим таблицу cars , в которой будем хранить марку автомобиля и страну-производитель:

Эта таблица может содержать, например, такие данные:

Следует обратить внимание на последние 2 строки в таблице cars — они не полные. Первая из них содержит данные только в колонке model , вторая — не содержит данных ни в одной из колонок. Эта таблица может даже состоять из миллиона строк, подобных последней — и каждая строка не будет содержать в себе абслютно никаких данных.

Значения по умолчанию

При создании таблицы можно указать, какое значение будет принимать колонка по умолчанию:

В этом примере создается таблица cars, в которой помимо модели и страны-производителя хранится еще и количество колес, которое имеет автомобиль. И поле wheel_count по-умолчанию будет принимать значение, равное 4.

Что значит по-умолчанию? Это значит, что если при вставке данных в эту таблицу не указать значение для колонки wheel_count , то оно будет равно числу 4.

Понятие NULL. Not-null колонки

Ячейки в таблицах могут быть пустыми, т.е. не содержать значения. Для обозначения отсутствия значения в ячейке используется ключевое слово NULL . Null могут содержать ячейки с любым типом данных.

Рассмотрим таблицу cars из предыдущего примера. В каждой из трех ее колонок может храниться Null(даже в колонке wheel_count , если указать значение Null явно при вставке).

Но представляют ли информационную ценность строки в таблице, где абслютно нет значений? Конечно нет. Если рассматривать таблицу cars как источник информации об автомобилях, то нам хотелось бы получать хоть какую-то полезную информацию. Наиболее важной здесь будет колонка model — без нее информация о стране-производителе и количестве колес будет бесполезной.

Для того, чтобы запретить Null-значения в колонке при создании таблицы, к описанию колонки добавляется not null :

Теперь БД гарантирует, что колонка model не будет пустой, по крайней мере до тех пор, пока флаг not null включен для этой колонки.

Также можно указать, что колонка wheel_count тоже не должна содержать Null :

Комментарии к таблице, колонкам

Для создаваемых таблиц и их колонок можно указывать комментарии. Это значитально облегчит понимание того, для чего и как они используются.

Например, укажем комментарии для таблицы cars и ее колонок:

Для того, чтобы удалить комментарий, нужно просто задать в качестве его значения пустую строку:

Источник

Как получить имена столбцов из таблицы в Oracle?

Мне нужно запросить базу данных, чтобы получить имена столбцов, а не путать с данными в таблице. Например, если у меня есть таблица с именем EVENT_LOG , которая содержит eventID , eventType , eventDesc и eventTime , тогда я хочу получить эти имена полей из запроса и ничего больше.

Я нашел, как это сделать:

Но мне нужно знать: как это можно сделать в Oracle?

ОТВЕТЫ

Ответ 1

Вы можете запросить таблицу USER_TAB_COLUMNS для метаданных столбца таблицы.

Ответ 2

Тип = ‘V’ для просмотров Тип = ‘U’ для таблиц

Ответ 3

Вы можете сделать это:

Примечание: применимо только в том случае, если вы знаете имя таблицы и специально для Oracle.

Ответ 4

Для SQL Server 2008 мы можем использовать information_schema.columns для получения информации о столбцах

Ответ 5

Для SQLite я считаю, что вы можете использовать что-то вроде следующего:

Объяснение из sqlite.org:

Эта прагма возвращает одну строку для каждого столбца в названной таблице. Столбцы в результирующем наборе включают имя столбца, тип данных, может ли столбец NULL или значение по умолчанию для столбца. Столбец «pk» в результирующем наборе равен нулю для столбцов, которые не являются частью первичного ключа, и является индексом столбца в первичном ключе для столбцов, которые являются частью первичного ключа.

Ответ 6

Эта информация хранится в системной таблице ALL_TAB_COLUMNS :

Или вы могли бы DESCRIBE использовать таблицу, если вы используете SQL * PLUS:

Ответ 7

Другие ответы достаточно отвечают на вопрос, но я думал, что поделюсь дополнительной информацией. Другие описывают синтаксис таблицы DESCRIBE, чтобы получить информацию о таблице. Если вы хотите получить информацию в том же формате, но без использования DESCRIBE, вы можете сделать:

Наверное, не имеет большого значения, но я написал его раньше и, кажется, подходит.

Источник

Запросов Oracle для извлечения имен столбцов

У меня есть запрос mySQL для получения столбцов из такой таблицы:

Как изменить вышеуказанный запрос в базе данных Oracle 11g? Мне нужно получить имена столбцов в качестве результирующего набора для таблицы «пользователи», исключая определенные столбцы, указав схему. Прямо сейчас у меня есть все таблицы в моем новом табличном пространстве, поэтому я указываю имя табличного пространства вместо имени схемы?

также есть общий HQL для этого? В моей новой базе данных Oracle (я новичок в Oracle) у меня есть только табличное пространство имя, так что это эквивалентно имени схемы (логически?)

11 ответов

эквивалент Oracle для information_schema.COLUMNS is USER_TAB_COLS для таблиц, принадлежащих текущему пользователю, ALL_TAB_COLS или DBA_TAB_COLS для таблиц, принадлежащих всем пользователям.

табличное пространство не эквивалентно схеме, и вам не нужно указывать имя табличного пространства.

предоставление схемы / имени пользователя будет использоваться, если вы хотите запросить ALL_TAB_COLS или DBA_TAB_COLS для столбцов таблиц, принадлежащих конкретному пользователю. в вашем случае, я бы предположил, что запрос будет выглядеть что-то например:

обратите внимание, что при таком подходе, вы рискуете SQL-инъекций.

EDIT: в верхнем регистре имена таблиц и столбцов, как это обычно прописные в Oracle; они являются только нижним или смешанным регистром, если они созданы с двойными кавычками вокруг них.

приведенный ниже запрос работал для меня в базе данных Oracle.

Источник

Таблицы Oracle

Таблицы — основные единицы хранилища в базе данных Oracle. Таблица (table) — это логическая сущность, которая делает чтение и манипуляции данных интуитивно понятными для пользователя. Таблица состоит из столбцов и строк, причем строка соответствует одиночной записи, которая состоит из набора полей. Когда вы создаете таблицу, то присваиваете ей имя и определяете набор столбцов, относящихся к ней. Каждый столбец имеет имя и определенный тип данных (вроде VARCHAR2 или DATE). Для определенных столбцов можно специфицировать ширину или точность и масштаб (scale), а некоторым из них могут быть установлены значения по умолчанию.

Оглавление

Типы таблиц Oracle

В базах данных Oracle можно создавать таблицы (table) двух типов: реляционные и объектные.

Реляционные таблицы — это базовые табличные структуры, которые состоят из строк и столбцов, хранящих данные.

Объектные таблицы используют объектные типы для определений столбцов и служат для хранения экземпляров объектов определенного типа. В этой статье будут использоваться исключительно реляционные таблицы.

Что такое таблица DUAL?

Таблица DUAL относится к схеме sys и создается автоматически при создании словаря данных.Таблица DUAL имеет единственный столбец по имени dummy и одну строку. Эта таблица позволяет применять команду Oracle SELECT для вычисления константных выражений. Как уже было показано, все, что есть в Oracle, должно находиться в какой-то таблице. Даже если что-то не находится в таблице, например, вычисляемое арифметическое выражение, запрос, который извлекает это значение, нуждается в какой-то таблице, и таблица DUAL служит таблицей “остальные” (catchall) для таких выражений. Например, для вычисления произведения 9 на 24567, можно воспользоваться следующей командой SQL: SELECT 9*24567 FROM dual.

Виды таблиц по способу хранения

Есть четыре основных способа организации таблиц (table) в базе данных Oracle.

• Таблицы, организованные в куче (heap-organized), или традиционные таблицы. Эти таблицы — не что иное, как обычные таблицы Oracle, в которых данные хранятся без какого-то определенного порядка.
• Индекс-таблицы. Индекс-таблицы хранят данные в отсортированной индексной структуре — бинарном дереве (B-tree).
• Кластеризованные таблицы. Кластеризованная таблица является частью группы таблиц, которые разделяют между собой одни и те же блоки данных, поскольку данные из кластеризованных таблиц часто запрашиваются совместно.
• Секционированные таблицы. Секционированная таблица позволяет делить большие объемы данных на подтаблицы, именуемые разделами (partition), согласно различным критериям. Секционирование особенно полезно в среде хранилища данных.

В этой статье мы обсудим традиционные (организованные в куче) таблицы Oracle. Прочие типы таблиц будут рассматриваться нами в отдельной статье блога “Специальные таблицы Oracle”.

Оценка размера таблицы

Прежде чем создавать новую таблицу, имеет смысл оценить примерный ее размер на начальный момент и размер, которого можно ожидать в будущем. Знание размера таблицы позволяет принимать правильные решения относительно выделения пространства на диске.

Существуют алгоритмы для вычисления потенциального размера таблиц и индексов — они берут размер строки в байтах и умножают на ожидаемое количество строк в этой таблице. Оценка размера таблиц — скорее искусство, чем точная наука, и вам не стоит очень уж напрягаться, чтобы получить “точное” значение размера. Просто примените здравый смысл, чтобы не слишком отдалиться от реальности.

Можно упростить оценку размера таблицы, воспользовавшись OEM Database Control или же процедурой CREATE_TABLE_COST из пакета DBMS_SPACE. Оба подхода к установке размера новой таблицы описаны в последующих разделах.

Использование OEM Database Control для оценки размера таблиц

Давайте рассмотрим шаги, которые следует предпринять для определения размера новой таблицы с использованием интерфейса Database Control.

• На домашней странице Database Control щелкните на вкладке Administration (Администрирование).
• Щелкните на Tables (Таблицы) в списке Schema (Схема).
• Щелкните на кнопке Create (Создать) в нижнем правом углу.
• Выберите в качестве типа Standard (Стандартная) или Index Organized (Индекс-таблица).
• На странице Create Table (Создать таблицу) введите имя новой таблицы и укажите типы данных столбцов в разделе столбцов. Щелкните на кнопке Estimate Table Size (Оценить размер таблицы).

На странице Estimate Table Size (Оценить размер таблицы) введите ожидаемое количество столбцов вашей таблицы (рис. 1).

Когда вы проделаете все эти шаги, OEM немедленно сообщит, сколько места следует отвести новой таблице. Он также сообщит о том, сколько места следует выделить табличному пространству, в котором вы собираетесь создавать новую таблицу.

На заметку! Следующая дискуссия об операциях с таблицей касается “нормальных” или “традиционных” таблиц Oracle, чьи строки не хранятся в таблице в каком-то определенном порядке. Большинство операций с таблицами, о которых пойдет речь, касаются всех типов таблиц Oracle, но с некоторыми модификациями синтаксиса или ограничениями.

Формат и размер строк Oracle

База данных Oracle хранит каждую строку таблицы в виде одного или более кусков. Если длина строки больше такого куска, то база данных может хранить строку несколькими кусками. Конечно,если строка таблицы маленькая, блок данных может содержать несколько строк таблицы. Для связывания кусков строк вместе используются идентификаторы ROWID. Каждый кусок строки содержит заголовок строки и данные для всей строки или части ее столбцов.

Использование пакета DBMS_SPACE для оценки потребностей в пространстве

Пакет DBMS_SPACE позволяет анализировать рост сегментов и потребность в пространстве. С помощью процедуры из этого пакета можно оценить необходимое место для индексов таблицы. Если известна приблизительная длина строк новой таблицы и ожидаемое их количество, то пакет DBMS_SPACE сообщит предполагаемое место, которое понадобится таблице, с учетом атрибутов хранения табличного пространства, в котором вы собираетесь создавать ее. Чтобы получить ожидаемый размер таблицы, можно использовать как информацию о столбцах таблицы, так и размер строки. В листинге ниже показан простой пример.

Обратите внимание, что пакет DBMS_SPACE также содержит процедуру SPACE_USAGE,которая поможет вернуть незанятое пространство (в действительности — неиспользованные экстенты), выделенные таблицам, индексам и прочим объектам. Вот как выглядит синтаксис вызова этой процедуры для возврата пространства, выделенного таблице:

Создание таблицы Oracle

Для создания таблицы в вашей собственной схеме необходимо иметь системную привилегию CREATE TABLE, а для создания таблицы в схеме другого пользователя понадобится системная привилегия CREATE ANY TABLE. При создании таблицы всегда специфицируйте табличное пространство. Если этого не сделать, таблица будет создана в пользовательском табличном пространстве по умолчанию. Кроме того, необходимо иметь достаточную квоту свободного места в табличном пространстве, где собираетесь создавать свои таблицы, или же обладать системной привилегией UNLIMITED TABLESPACE. В листинге ниже показан синтаксис создания простой таблицы.

Совет. Если ваша база данных содержит крупные таблицы только для чтения, рассмотрите возможность использования средства сжатия таблиц Oracle, чтобы сэкономить место в хранилище.

В операторе CREATE TABLE из листинга выше присутствует несколько ограничений целостности, включая первичный ключ и внешний ключ, определенные на разных столбцах таблицы. Ограничения будут описаны далее в моем блоге, в статье “Управление ограничениями целостности базы данных”.

На заметку! С использованием конструкции ENCRYPT осуществляется прозрачное шифрование данных столбца. Шифровать можно столбцы типа CHAR, VARCHAR2, NUMBER, DATE и RAW. Пользователь, который шифрует столбец, увидит данные в расшифрованном формате. Шифрование включает установку ключа шифрования и некоторые другие детали (за дополнительной информацией о шифровании обращайтесь к руководству по Oracle, озаглавленном “Oracle Advanced Security Administrator’s Guide”, которое доступно по адресу http://tahiti.oracle.com). Вот как можно было бы зашифровать столбец ssn в предыдущем операторе создания таблицы:

Как только новая таблица создана, ее можно наполнять данными несколькими способами: применять команду INSERT для вставки данных или же загрузить данные с использованием SQL*Loader. Возможно также создать новую таблицу и поместить в нее данные из существующей таблицы из той же либо другой базы данных. Это делается с применением хорошо известной техники CREATE TABLE AS SELECT (CTAS), которую объясняется ниже, в разделе “Создание новой таблицы с помощью CTAS”. Кроме того, можно применять SQL-оператор MERGE для вставки данных их другой таблицы на основе определенных условий. Использование команды MERGE объясняется в приложении.

На заметку! Если вы создаете объекты базы данных в локально управляемом табличном пространстве, то указывать параметры хранения для каких-либо объектов не нужно.

Что такое значение null?

Значение null означает просто пустой столбец в строке. Значение null для столбца определенной строки не означает нулевого значения этого столбца. Вместо этого null указывает на то, что столбец в данной строке не имеет значения. Если данных для столбца нет либо они неизвестны, чтобы указать на это, применяется null. Вставлять в любой столбец таблицы значение null нельзя. Столбец допускает значения null только в том случае, если для него не специфицировано ограничение NOT NULL. Вдобавок, когда вы назначаете столбец первичным ключом этой таблицы, этот столбец также не допускает значений null. Попробуйте вставить все null-значения до конца таблицы, чтобы зарезервировать дисковое пространство. Это связано с тем, как Oracle хранит null-значения. Любые сравнения между null и другими значениям не могут быть истинными или ложными, поскольку null означает неизвестное значение.

Значения полей (столбцов) по умолчанию

Можно назначить полям таблицы Oracle значения по умолчанию. При вставке новой строки можно опускать значение для любого столбца, для которого определено значение по умолчанию. Для такого столбца база данных применит указанное значение по умолчанию.Если значение по умолчанию для столбца явно не задано, им будет null. Например, если для таблицы employees в качестве значения по умолчанию для столбца DEPT_NO вы установите 20, то Oracle поместит значение 20 в столбец DEPT_NO, даже если при вставке новых данных этот столбец не будет указан.

Виртуальные поля (столбцы)

В выпуске Oracle Database 11g в таблице можно использовать виртуальные поля (столбцы). Виртуальный столбец — это столбец, который определяется вычислением выражения, основанного на одном или более действительных столбцов таблицы, либо функции SQL или PL/SQL. В отличие от обычных столбцов, данные виртуального столбца не хранятся постоянно на диске. База данных вычисляет значения виртуального столбца, когда вы запрашиваете его, оценивая выражения или вызывая соответствующую функцию.

Виртуальные столбцы можно использовать как в DDL-, так и в DML-операторах.Допускается определять индексы на этих столбцах и собирать статистику по ним.

Создание виртуального поля таблицы

При спецификации виртуального поля можно использовать конструкцию GENERATED ALWAYS как часть оператора CREATE TABLE, например:

Последняя строка предыдущего примера показывает спецификацию виртуального столбца. При желании можно также специфицировать необязательное ключевое слово VIRTUAL:

В обоих приведенных примерах hrly_rate — виртуальный столбец, генерируемый в результате вычисления выражения sal/2800 для каждой строки. Можно также добавить виртуальный столбец к существующей таблице, выполнив оператор ALTER TABLE,как показано в следующем примере:

Поскольку здесь тип данных для виртуального столбца не специфицирован, Oracle автоматически назначит тип данных на основе типов данных участвующих в выражении столбцов (salary и commision_pct).

Ограничения виртуальных столбцов

На виртуальные столбцы таблицы накладывается ряд ограничений, которые перечислены ниже.

• Нельзя создавать виртуальные столбцы в индекс-таблицах, внешних таблицах,временных таблицах, объектах или кластерах.
• Нельзя создавать виртуальный столбец определяемого пользователем типа, типа LOB или типа RAW.
• Все столбцы, участвующие в выражении, должны принадлежать одной таблице.
• Выражение столбца должно давать результат — скалярную величину.
• Выражение столбца в конструкции AS не может ссылаться на другой виртуальный столбец.
• Нельзя выполнять обновление столбца с использованием конструкции SET оператора UPDATE.
• Нельзя выполнять операции удаления или вставки на виртуальном столбце.

Добавление столбца в таблицу

Добавление столбца в таблицу — очень простая операция. Для этого просто используется команда ALTER TABLE, как показано ниже:

Удаление столбца из таблицы

Удалить существующий столбец из таблицы можно с помощью следующей команды:

Если таблица, из которой удаляется столбец, содержит большой объем данных, можно попросить Oracle просто пометить столбец как неиспользуемый, не пытаясь удалить из него данные. Вы не увидите потом этот столбец в запросах или представлениях, и все зависящие от него объекты, такие как ограничения и индексы, определенные на этом столбце, будут удалены. Для практических целей таким способом “уничтожить” большой столбец можно очень быстро.

Ниже приведен пример, в котором столбцы hiredate и mgr в таблице emp помечаются как неиспользуемые:

Когда появится время на обслуживание базы, можно будет окончательно уничтожить эти два столбца с помощью следующей команды:

Если вы полагаете, что большое количество строк в таблице может потенциально переполнить пространство отмены (undo), можете уничтожить столбец с указанием конструкции CHECKPOINT. Это сократит генерацию данных undo при уничтожении столбца, применяя контрольную точку после определенного количества строк. Ниже показан пример, который заставляет базу данных применять контрольную точку всякий раз,когда она удаляет 10 000 строк из таблицы emp:

Переименование столбца таблицы

С помощью команды rename column легко переименовать столбец таблицы. Например, следующая команда переименует столбец retired в таблице emp на non_active.Обратите внимание, что при желании можно также переименовать ограничения столбца.

Совет. Временные файлы, а также файлы данных и журналов повторного выполнения, переименовываются с использованием команды ALTER DATABASE.

Переименование таблицы

Иногда разработчику приложений может понадобиться переименовать таблицу.Переименование выполняется просто:

Удаление всех данных из таблицы

Для удаления всех строк из таблицы можно применить команду TRUNCATE, которая,несмотря на название, на самом деле ничего не усекает и не сокращает — она просто очень быстро удаляет все строки. TRUNCATE — команда DDL, поэтому она не может быть отменена командой ROLLBACK.

Можно также удалить все строки из таблицы командой DELETE * FROM TABLE. , и поскольку это команда DML, то в этом случае при желании возможна отмена удаления.Однако поскольку команда DELETE пишет все изменения в сегменты отмены, на ее выполнение требуется намного больше времени. Команде TRUNCATE не нужно возиться с сегментами отмены, поэтому она выполняется за считанные секунды, даже для очень крупных таблиц.

Вот пример команды TRUNCATE в действии:

Создание новой таблицы с помощью CTAS

Чтобы создать новую таблицу, которая будет идентичной существующей, или же создать новую таблицу, которая включит только некоторые строки и столбцы из другой таблицы, можно воспользоваться командой CREATE TABLE AS SELECT * FROM.С помощью этой команды легко загрузить часть существующей таблицы в новую таблицу, используя условия where, или же загрузить все данные из старой таблицы в новую,применяя конструкцию SELECT * FROM, как показано в следующем фрагменте кода:

Если таблица содержит миллионы строк, а ваше время слишком ограничено, чтобы использовать метод CTAS, существует пара способов ускорить создание новой таблицы,содержащей большие объемы данных. Если создаваемая таблица должна быть пустой,то время на ее создание беспокоить не должно — она будет создана немедленно. Но если вы загружаете новую таблицу данными из другой таблицы, то можете выиграть от применения опций PARALLEL и NOLOGGING, которые ускоряют загрузку крупных таблиц.

Опция PARALLEL позволяет осуществлять загрузку данных параллельно несколькими процессами, а опция NOLOGGING инструктирует Oracle о том, что протоколировать изменения, происходящие при загрузке в файлы журналов повторного выполнения и сегменты отката (за исключением очень небольшого объема, необходимого для внутренних потребностей), не требуется. Вот пример:

Другой метод, который можно применять для экономии времени во время создания таблицы, состоит в перемещении таблицы из одного табличного пространства в другое. Можете воспользоваться преимуществом операции перемещения, чтобы изменить любые параметры хранения. Ниже приведен пример команды ALTER TABLE. MOVE,которая позволяет быстро перемещать таблицы между табличными пространствами.В данном примере таблица employee перемещается из ее текущего табличного пространства в новое:

При перемещении таблицы идентификаторы ROWID ее строк меняются, а это означает, что база данных не позволит добавлять, удалять или изменять данные каким-либо образом. Например, если есть конфигурационная таблица, которую требуется сохранить от любых изменений любыми пользователями, можно изменить ее статус на “только для чтения”.

Воспользуйтесь оператором ALTER TABLE для перевода таблицы в режим “только для чтения”:

После перевода таблицы в режим “только для чтения” база данных не позволит выполнять на ней следующие операции:

• TRUNCATE TABLE
• SELECT FOR UPDATE
• Любые операции DML
• ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN
• ALTER TABLE SET COLUMN UNUSED
• ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION
• ALTER TABLE UPGRADE INCLUDING DATA or ALTER TYPE CASCADE INCLUDING
• TABLE DATA для типа, от которого зависит таблица “только для чтения”
• Оперативное переопределение
• FLASHBACK TABLE

Перечисленные ниже операции на таблице “только для чтения” выполнять можно:

• SELECT
• CREATE/ALTER/DROP INDEX
• ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
• ALTER TABLE для изменения физических свойств
• ALTER TABLE MOVE
• RENAME TABLE и ALTER TABLE RENAME TO
• DROP TABLE

Вернуть таблицу в нормальное состояние “чтение-запись” можно, указав конструкцию READ WRITE в операторе ALTER TABLE:

Сжатие таблиц

Сжатие данных в таблице, помимо ускорения производительности запросов, позволяет сэкономить дисковое пространство и сократить затраты памяти. Однако сжатие данных в таблице влечет за собой некоторые накладные расходы при загрузке данных или выполнении операций DML. Применять сжатие таблиц можно как в приложениях хранилищ данных, которые предполагают, в первую очередь, операции чтения, так и в системах OLTP, которые включают интенсивные операции DML. На сжатых таблицах можно выполнять операции вставки, удаления и обновления. Чтобы сократить расходы, попробуйте сжать данные, которые в основном используются только для операций чтения и обновляются редко. Хронологические данные и архивные данные являются подходящими кандидатами на помещение в сжатые таблицы.

Когда база данных сжимает таблицу, она исключает дублированные значения в блоке данных. База данных хранит все дублированные значения таблицы в начале блока,в символьной таблице блока. База данных заменяет множественные экземпляры дублированных данных краткой ссылкой на символьную таблицу. Со сжатой таблицей можно выполнять все операции, которые выполняются над обычной таблицей. Допускается сжимать таблицы и материализованные представления. Также возможно сжимать только некоторые разделы секционированных таблиц.

Oracle предлагает также сжатие табличных пространств. При сжатии табличного пространства все таблицы, которые создаются в нем, будут автоматически сжатыми.

Определив таблицу как сжатую, автоматическое сжатие данных будет возникать при следующих операциях:

• загрузка через прямые операции SQL*Loader;
• загрузка через оператор CTAS;
• операторы параллельной вставки;
• операторы последовательной вставки с подсказкой APPEND;
• однострочные или в виде массивов вставки и обновления.

Самый большой ущерб производительности, связанный со сжатием данных, происходит при вставке данных с использованием любого из методов, перечисленных выше.Удаление сжатых данных, однако, осуществляется так же быстро, как и несжатых.Вдобавок обновление на сжатых таблицах также выполняется медленнее.

В дополнение к созданию таблицы с включенным сжатием, можно также сжимать существующие данные в таблице с использованием оператора ALTER TABLE. MOVE.При сжатии данных подобным образом база нуждается в установке исключительной блокировки на таблице, что предотвращает все обновления и вставки до тех пор, пока не завершится операция сжатия. В качестве альтернативы можно воспользоваться пакетом DBMS_REDEFINITION для выполнения оперативного переопределения таблицы,чтобы избежать блокировки всей таблицы.

Включение сжатия

Включается сжатие путем указания конструкции COMPRESS в операторе CREATE TABLE или ALTER TABLE. COMPRESS. Если вы изменяете таблицу, то только новые данные будут после этого подвергаться сжатию. Таким образом, таблица может в одно и то же время содержать в себе как сжатые, так и несжатые данные. Отключается сжатие таблицы с использованием оператора ALTER TABLE. UNCOMPRESS. Отключение сжатия не распаковывает уже сжатые ранее данные в таблице; оно лишь гарантирует, что новые данные не будут сжиматься.

На заметку! Применять сжатие таблиц можно как в средах OLTP, так и в средах хранилищ данных.Лучшие результаты можно получить, сжимая все подлежащие только чтению и хронологические данные, которые редко изменяются.

Существует пара вариантов конструкции COMPRESS. Конструкция COMPRESS FOR ALL OPERATIONS используется для включения сжатия для всех операций. Чтобы включить ее только для операций прямых вставок (пакетных вставок), специфицируйте конструкцию COMPRESS FOR DIRECT_LOAD OPERATIONS. Сама конструкция COMPRESS эквивалент на COMPRESS FOR DIRECT_LOAD OPERATIONS.

Обратите внимание, что если включить сжатие только для прямых вставок, позже нельзя будет удалить никаких столбцов. Добавлять столбцы можно, только указывая их значения по умолчанию. Эти ограничения не применяются при включении сжатия для всех операций в таблице.

Примеры сжатия таблиц

Следующий пример демонстрирует включение сжатия таблицы для всех операций,что, скорее всего, понадобится при настройке OLTP:

Для только прямых вставок в таблицу можно использовать любой из приведенных ниже операторов:

Как видно из этих примеров, конструкция COMPRESS FOR ALL OPERATIONS — это то,что необходимо применять для сжатия таблиц OLTP. С помощью следующего запроса можно узнать, какие таблицы базы данных являются сжатыми:

Столбец COMPESS_FOR показывает тип сжатия таблицы (для всех операций или только для загрузки в прямом режиме).

Удаление таблиц

Для удаления таблицы служит команда DROP TABLE имя_таблицы. Чтобы иметь возможность удалить таблицу, пользователь должен быть ее владельцем (она должна быть в его схеме) или же обладать привилегией DROP ANY TABLE.

После выдачи команда DROP TABLE таблица не исчезает немедленно — Oracle просто переименовывает таблицу и сохраняет ее в корзине (Recycle Bin), которая на самом деле является таблицей словаря данных. Поэтому можно вернуть нечаянно удаленную таблицу с помощью следующей команды:

Возможность восстановления удаленной таблицы называется средством Flashback Drop (Ретроспектива удаления).

Если вы уверены, что таблица никогда более не понадобится, можете окончательно избавиться от нее, указав опцию PURGE в команде DROP TABLE:

В результате использования приведенной выше команды PURGE таблица emp будет немедленно уничтожена без возможности восстановления!

На заметку! Команда DROP TABLE имя_таблицы PURGE эквивалентна старой команде DROP TABLE имя_таблицы в версиях, предшествовавших Oracle Database 10g.

Когда вы удаляете таблицу, все индексы, относящиеся к ней, также удаляются. Если таблица, которую необходимо удалить, содержит первичные или уникальные ключи,на которые ссылаются во внешних ключах другие таблицы, потребуется включить конструкцию CASCADE в оператор DROP TABLE, чтобы одновременно уничтожить эти ограничения:

Источник