Как создать синоним в oracle

Обновлено: 04.07.2024

Как выполнить запрос с использованием синонима?

Изменить: Я знаю, что это сработает, если я создам представление таблицы с помощью dblink. Но мое требование - это вопрос выше.

К сожалению, создание синонимов для dblinks не поддерживается . Если вы прочитаете документацию по синонимам, вы найдете что разрешенными объектами для синонимов являются только:

Используйте оператор CREATE SYNONYM для создания синонима, который является альтернативное имя для таблицы, представления, последовательности, процедуры, хранимой функция, пакет, материализованное представление, объект схемы класса Java, определяемый пользователем тип объекта или другой синоним .

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

Вы все равно получите такой ответ:

Но с этим синонимом конечно ничего не получится.

Я не вижу смысла создавать синоним самого dblink. В идеале вы создаете синоним для удаленной таблицы с помощью dblink .

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

Я пытаюсь подумать о бизнес-проблеме, которая решается путем добавления синонима в db_link, и единственное, что я могу придумать, это то, что вам нужно развернуть постоянный код, который будет выбираться из some_Table @ some_dblink, и хотя таблица имена постоянны, разные пользователи могут просматривать разные db_links. Или вы просто хотите иметь возможность поменять местами, с какой db_link вы работаете, с помощью простой повторной точки синонима.

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

Единственное решение - использовать код вместо ссылки на таблицы по синонимам и установить частные синонимы так, чтобы они указывали на правильную ссылку db_link. Таким образом, ваш код продолжит «Выбрать из REMOTE_TABLE1», и вы просто сможете изменить, из какого DB_LINK вы получаете эту удаленную таблицу.

Утомительно ли устанавливать / сбрасывать более 100 частных синонимов? Ага. Но если это что-то, что вам нужно делать часто, тогда соберите процедуру, которая сделает это за вас, где вы передаете имя db_link, и оно циклически проходит и сбрасывает синонимы для вас.

Если вы пытаетесь сделать ссылку на БД доступной для нескольких схем (пользователей), ответ - создать общедоступную ссылку на БД.

После этого любая схема может выдать:

Хотя я понимаю, что этому вопросу более трех лет, в будущем кто-то может получить другой ответ.

Представим, что у меня есть 4 базы данных, 2 для производства и 2 для разработки / тестирования.

БД продуктов: PRDAPP1DB1 и PRDAPP2DB1 БД разработчиков: DEVAPP1DB1 и DEVAPP2DB1

В базах данных «APP2» выполняются процедуры для извлечения и импорта данных из баз данных APP1. В этих процедурах используются различные операторы выбора, например:

Теперь это нормально для разработки, но dblink2 постоянно нужно менять на dblink1 при развертывании обновленной процедуры в производственной среде.

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

Например. на производстве:

Затем в процедурах измените все «@ dblink1» и «@ dblink2» на «@mydblink», и оттуда все должно быть прозрачным.

Синонимы (synonyms) Oracle Database — это псевдонимы объектов базы данных, которые служат в основном для облегчения пользователям доступа к объектам, принадлежащим другим пользователям, а также в целях безопасности. Синонимы скрывают идентичность лежащих в их основе объектов и могут быть как приватными (private), так и общедоступными (public). Общедоступные синонимы доступны всем пользователям базы данных,а приватные синонимы являются составной частью схемы отдельного пользователя, и другим пользователям базы следует выдавать права доступа для использования приватных синонимов. Синонимы Oracle могут быть созданы для таблиц, представлений, материализованных представлений и хранимого кода — пакетов и процедур.

Синонимы Oracle — очень мощное средство с точки зрения обеспечения пользователям доступа к объектам, которые не принадлежат к их схемам. Все синонимы должны создаваться явно командой CREATE SYNONYM, а лежащие в основе объекты могут находиться в той же базе данных или в других базах, подключенных по связям баз данных.

Сфера применения синонимов Oracle

Есть два основных применения синонимов.

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

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

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

Как видите, когда пользователь SYSTEM пытается получить структуру таблицы без префикса — имени схемы, Oracle выдает ошибку, сообщающую об отсутствии таблицы. Чтобы обойти это, владелец схемы должен создать синоним с тем же именем, что и у таблицы. Как только пользователь SYSTEM применит нотацию схема.таблица, он сможет увидеть содержимое таблицы.

Создание общедоступного синонима

Общедоступные (public) синонимы относятся к специальной схеме базы данных Oracle, именуемой PUBLIC. Как упоминалось ранее, общедоступные синонимы видны всем пользователям базы данных. Общедоступные синонимы обычно создаются владельцем приложения для таблиц и прочих объектов, таких как процедуры и пакеты,чтобы пользователи приложения могли видеть эти объекты.

В следующем коде показано, как создается общедоступный синоним для таблицы employees:

Теперь любой пользователь сможет видеть таблицу, просто набрав ее исходное имя. При желании с помощью оператора CREATE SYNONYM таблице можно дать другое имя. Помните, что администратор базы данных должен явно выдать привилегию CREATE PUBLIC SYNONYM пользователю hr, чтобы тот мог создавать общедоступные синонимы.

Возможность видеть таблицу через общедоступный (или приватный) синоним, еще не означает возможность выполнения над ней операций SELECT, INSERT, UPDATE или DELETE. Для выполнения таких операций пользователю нужны специальные привилегии для доступа к исходному объекту, выданные владельцем приложения непосредственно или через роли.

Создание приватного синонима

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

В следующем примере показано, как создать приватный синоним по имени addresses для таблицы locations. Обратите внимание, что после создания к приватному синониму можно обращаться как к первоначальному имени таблицы.

Уничтожение синонима

И приватный, и общедоступный синонимы уничтожаются командой DROP SYNONYM,но есть одно отличие. При уничтожении общедоступного синонима после ключевого слова DROP должно находиться ключевое слово PUBLIC.

Ниже показан пример уничтожения приватного синонима:

Управление синонимами

Представление DBA_SYNONYMS содержит информацию обо всех синонимах в вашей базе данных. Синонимы основаны на лежащих в основе базовых таблицах, и узнать имена базовых объектов можно, запустив запрос, подобный следующему:

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

Переключение к другой схеме

Если вы постоянно используете таблицы, принадлежащие другой схеме, и в этой схеме нет никаких синонимов, придется перед каждым именем таблицы указывать квалификатор схемы. Например, для обращения к таблице emp, принадлежащей пользователю scott, понадобится указывать emp.scott. Чтобы избежать этого, можно применить следующий оператор ALTER SESSION SET SCHEMA:

Использование оператора ALTER SESSION не приводит к автоматической выдаче каких-либо привилегий. Для того чтобы запросить таблицу emp без квалификатора схемы, как показано в предыдущем примере, пользователь должен иметь привилегию SELECT на таблице emp.

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

schema_name_1
Указывает схему, в которой создается новый синоним. Если schema не указана, SQL Server использует применяемую по умолчанию схему текущего пользователя.

synonym_name
Имя нового синонима.

server_name
Область применения: SQL Server 2008 и более поздних версий.

Имя сервера, на котором расположен базовый объект.

database_name
Имя базы данных, в которой расположен базовый объект. Если не задано database_name, используется имя текущей базы данных.

schema_name_2
Имя схемы базового объекта. Если не задан аргумент schema_name, используется схема по умолчанию текущего пользователя.

object_name
Имя базового объекта, на который ссылается синоним.

Remarks

Базовый объект может не существовать в момент создания синонима. В SQL Server проверяется наличие базового объекта во время выполнения.

Синонимы могут создаваться для следующих типов объектов:

  • Хранимая процедура сборки (среда CLR)
  • Функция сборки с табличным значением(среда CLR)
  • Скалярная функция сборки (среда CLR)
  • Агрегатные функции сборки (среда CLR)
  • Процедура фильтра репликации
  • Расширенная хранимая процедура
  • Скалярная функция SQL
  • функция SQL с табличным значением
  • Встроенная функция SQL с табличным значением
  • Хранимая процедура SQL
  • Таблица 1 (пользовательская)
  • Представление

1 включает локальные и глобальные временные таблицы

Имена, состоящие из четырех элементов, для базовых объектов-функций не поддерживаются.

Синонимы можно создавать, удалять и ссылаться на них в динамическом SQL.

Синонимы являются специфичными для базы данных и недоступны для других баз данных.

Разрешения

Для создания синонима в заданной схеме пользователь должен иметь разрешение CREATE SYNONYM и, либо владеть схемой, либо иметь разрешение ALTER SCHEMA.

Разрешение на выполнение CREATE SYNONYM можно предоставлять.

Чтобы успешно скомпилировать инструкцию CREATE SYNONYM, необязательно иметь разрешение на базовый объект, потому что проверка всех разрешений на базовые объекты откладывается до времени выполнения.

Примеры

A. Создание синонима для локального объекта

В следующем примере сначала создается синоним для базового объекта Product в базе данных AdventureWorks2012 , а затем выполняется запрос к этому синониму.

Б. Создание синонима для удаленного объекта

В следующем примере базовый объект, Contact , находится на удаленном сервере с именем Server_Remote .

Область применения: SQL Server 2008 и более поздних версий.


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

Зачем бы вам понадобился синоним?


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

Синтаксис для создания синонимов

В документации Microsoft приводится следующий синтаксис.

Листинг 1. Синтаксис SYNONYM

Создать синоним просто. Он может ссылаться на объект, используя именование из одной, двух и трех частей, как для SQL Server, так и для Azure SQL Database. Но только SQL Server поддерживает имена из 4 частей для ссылок на базовые объекты, которые находятся в другом экземпляре SQL Server.

Синонимы могут создаваться для ссылок на объекты следующих типов:
Хранимые процедуры CLR
Скалярные функции CLR
Табличнозначные функции CLR
Агрегатные функции CLR
Процедура фильтров репликации
Скалярная функция SQL
Встроенная табличнозначная функция SQL
Табличнозначная функция SQL
Хранимая процедура SQL
Представление
Таблица (пользовательская, включая локальные и глобальные временные таблицы)

Создание, использование и управление синонимами

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

Координация переименования объекта

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

Предположим, что имеется приложение, разработанное для компании, в которой работают сантехники. Каждый сантехник, который когда-либо работал в компании, хранится в таблице с названием dbo.Plumber. В компании написано много кода, связанного с этой таблицей, для управления работой компании и её сантехниками. Однажды в компании решили расширить бизнес и включить электрические работы. Это расширение бизнеса требует модификации кода существующего приложения для поддержки нового вида работ. Кроме того, они планируют написать много нового кода для поддержки своего расширяющегося бизнеса. В первую очередь они хотят изменить имя таблицы dbo.Plumber на dbo.Employee. Тогда они смогут использовать новое имя таблицы для любого нового кода, который они планируют написать, пока координируют изменение названия для всего существующего кода.

Используя синоним, они могут сразу изменить имя и гарантировать, что весь существующий код будет работоспособным. Затем они могут постепенно менять старый код на использование новой таблицы. Как только весь унаследованный код будет изменен на использование нового имени, синоним можно удалить. Чтобы переименовать таблицу dbo.Plumber и создать синоним, администратор может выполнить код из листинга 2.

Переименование базового объекта и создание синонима для поддержки переименования имен


Этот код сначала переименовывает существующую таблицу dbo.Plumber в dbo.Employee, а затем создает синоним для поддержки переименования. Этот код будет выполнен очень быстро, минимизируя риск ошибок в промежутке времени переименованием таблицы dbo.Plumber и созданием синонима dbo.Plumber.

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

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

Предположим, что имела место проблема в таблице dbo.Article, которую требуется решить, выполняя операторы SELECT, INSERT, UPDATE и/или DELETE в производственном окружении. Чтобы обеспечить разработчикам доступ к обновлению для решения проблемы, может быть создан синоним с подходящими правами. Для того, чтобы сделать это, можно выполнить код в листинге 3.

Листинг 3. Код создания синонимов и разрешений.


В листинге 3 был создан синоним dbo.Dev_Article, указывающий на базовую таблицу с именем dbo.Article. После создания синонима разрешения SELECT, INSERT, UPDATE и DELETE на новый синоним dbo.Dev_Article были предоставлены группе Developer_Group. Сделав это, программисты в группе Developer_Group будут иметь возможность просматривать и обновлять фактически таблицу dbo.Article, используя синоним dbo.Dev_Article. После решения проблемы синоним может быть удален.

Создание синонима в другой базе данных

В этом примере предположим, что имеется компания, которая хочет создать зеркало базы данных в среде разработки так, чтобы можно было вести параллельную разработку. Под параллельной разработкой я имею в виду то, что одна группа разработчиков может работать и писать код T-SQL в одной базе данных, в то время как другая группа разработчиков может выполнять работу в другой базе данных. Однако администратор базы данных говорит о проблеме недостаточного места на диске для копирования всех таблиц из существующей базы данных разработки (CurrentDB) в новую зеркальную базу данных разработки (NewDB). В этой ситуации, DBA решает, что он может установить новую зеркальную базу данных, создав синонимы в базе данных newDB для каждой большой таблицы, которую он не может перенести в зеркало из-за недостатка места на диске. В коде листинга 4 показывается, как создать синоним в базе данных NewDB для одной из этих больших таблиц, которые не могут быть зеркалированы.

Листинг 4. Создание синонима, который ссылается на таблицу в другой базе данных


После создания синонима в базе данных NewDB все разработчики из группы Developer_Group2 смогут писать запросы SELECT, INSERT, UPDATE и DELETE к таблице dbo.BigTable, как будто она находится в базе данных NewDB. Помните, что когда разработчики выполняют код над таблицей dbo.BigTable в базе данных NewDB, команды фактически выполняются над таблицей dbo.BigTable в базе данных CurrentDB.

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

Создание синонима для ссылок на объекты на другом сервере

Иногда приложению может понадобиться выполнить некоторый код на одном сервере, но сослаться на таблицу на другом сервере. Например, можно предположить, что сервер имеет ограниченное по размерам хранилище. Тогда периодически запускается процесс архивации, который перемещает историческую информацию на другой сервер, который я буду называть архивным сервером. Если приложению требуется выбирать некоторые данные с архивного сервера, то можно использовать имя из четырех частей и прилинкованный сервер для ссылок на эти объекты на архивном сервере, а можно определить синоним.

Имена из четырех частей будут работать, но их долго набирать. Использование синонима для таких длинных имен из четырех частей делает кодирование проще и делает удаленные ссылки подобные локальным. Код в листинге 5 показывает, как создать синоним, который ссылается на таблицу с именем из четырех частей, которая находится на архивном сервере.

Листинг 5. Определение синонима для таблицы на другом сервере


Создав этот синоним, приложение теперь может использовать имя ClientHistory для ссылок на информацию об истории клиента, которая находится на архивном сервере, вместо длинного имени ArchivedProd.MyApp.dbo.ClientHistory.

Синонимы не могут ссылаться на другие синонимы

Синоним не может ссылаться на другой синоним. Для демонстрации этого я выполню код в листинге 6, создающий новый синоним в базе данных NewDB, который ссылается на синоним dbo.BigTable, созданный кодом в листинге 4.

Создание синонима, который ссылается на другой синоним

Если выполнить код в листинге 6 он выполнится без ошибок и создаст новый синоним с именем Second_BigTable. Но если я попытаюсь выполнить оператор SELECT в листинге 7, то получу ошибку, которая показана на рисунке 1.

Листинг 7. Оператор SELECT


Листинг 7. Рис.1. Ошибка при попытке использовать синоним, который ссылается на другой синоним


Причина, по которой я не получаю ошибки при создании синонима, но получаю её при выполнении оператора SELECT, заключается в том, что код для синонима проходит валидацию не при создании, а во время исполнения. Эта возможность отложенной валидации - хорошая вещь. Отложенная валидация позволяет администратору создавать синонимы заранее до создания базового объекта.

Изменение определения синонима

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

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

Обнаружение синонимов в базе данных

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


  1. Откройте SSMS
  2. Разверните папку баз данных
  3. Разверните требуемую базу данных
  4. Разверните пункт Synonym



Рис.2. Синонимы в базе данных NewDB

Другим методом показа синонимов в базе данных - это написать запрос к представлению каталога sys.synonyms в контексте той базы данных, которую вы проверяете. Пример запроса приведен в листинге 8.

Листинг 8. Вывод всех синонимов в базе данных с помощью T-SQL

Предосторожности при использовании синонимов

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

Нельзя сохранить разрешения на синоним при их удалении.

Использование синонимов может ввести в заблуждение, если не знать, что используешь синоним.

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

В зависимости от используемой версии SQL Server, IntelliSense может не распознать, что это имя - синоним.

Вы не можете ссылаться на синоним на прилинкованном сервере. Если вы попытаетесь это сделать, то получите ошибку, подобную показанной на рисунке 3.



Ошибка при попытке сослаться на синоним на прилинкованном сервере

Читайте также: