Oracle как переименовать схему

Обновлено: 06.07.2024

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

Как я могу быть уверен, что ничего не сломал?

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

3 ответа

Я разрабатываю визуализатор схемы базы данных Oracle. Итак, в качестве первого шага, подумал я, мне сначала нужно будет получить все детали схемы (таблицы и отношения между таблицами, возможно, также ограничения). Чтобы получить эту информацию, какова команда SQL, которая вернет результат.

У меня есть таблица с более чем полумиллионом строк и четырьмя столбцами. Столбцы можно назвать col1, col2, col3 и col4. Каждый столбец в этой основной таблице является varchar2 и содержит избыточные данные. Я преобразую эту таблицу, чтобы вместо нее были все внешние ключи. Таким образом, я создал.

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

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

Точно так же вам нужно будет переименовать свои ограничения и индексы

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

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

Однако любые соглашения об именовании, используемые для этих объектов, не будут автоматически использовать обновленное имя. Например, если первичный ключ для TABLE_NAME обычно называется TABLE_NAME_PK , переименование TABLE_NAME в NEW_TABLE_NAME не приведет к автоматическому переименованию ограничения первичного ключа в NEW_TABLE_NAME_PK .

Что нужно будет проверить, так это код-пакеты, процедуры и функции, - которые ссылались на старое имя таблицы, а также любые триггеры, которые ссылались на старое имя таблицы. Аналогично, представления против старого имени таблицы также будут нарушены. Представление ALL_DEPENDENCIES может помочь определить, какие из этих объектов нуждаются в обновлении.

Сохранит зависимости и данные таблицы, но всегда может быть кусок PL / SQL, который ссылается на старое имя, которое станет недействительным.

Мне нужно загрузить тестовые данные в таблицу, и я хочу отключить все возможные ограничения, я нашел, как глобально отключить внешние ключи,но мне нужно отключить все ограничения, PK, FK, CHK, UNIQ и т. д. Возможно ли это сделать глобально? Конечно, после этого мне придется снять все ограничения.

У меня есть две таблицы Blobs и FileContents, в которых хранится содержимое файла. Обе таблицы имеют одинаковую структуру: Id int IDENTITY (1,1), Content varbinary (max) NULL, В обеих таблицах есть данные. У меня есть несколько других таблиц, которые имеют внешние ключи к этим таблицам. Например.

Похожие вопросы:

В Postgresql, если я сделаю ALTER TABLE mytable DISBLE TRIGGERS ALL , все триггеры и ограничения, относящиеся к этой таблице, будут приостановлены. В частности, внешние ключи из других таблиц в.

В отношениях врач (SSN, Имя, Фамилия), Клиника (ID, имя, Адрес) и WorksFor (Doctor_SSN,Clinic_ID), добавьте триггер: когда врач переходит из одной клиники в другую клинику, подайте отчет, вставив.

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

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

У меня есть таблица с более чем полумиллионом строк и четырьмя столбцами. Столбцы можно назвать col1, col2, col3 и col4. Каждый столбец в этой основной таблице является varchar2 и содержит.

Мне нужно загрузить тестовые данные в таблицу, и я хочу отключить все возможные ограничения, я нашел, как глобально отключить внешние ключи,но мне нужно отключить все ограничения, PK, FK, CHK, UNIQ.

У меня есть две таблицы Blobs и FileContents, в которых хранится содержимое файла. Обе таблицы имеют одинаковую структуру: Id int IDENTITY (1,1), Content varbinary (max) NULL, В обеих таблицах есть.

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

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

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

К слову сказать, в чем мы далее и убедимся. Для того, чтобы запросы пользователей могли создавать временные сегменты в табличном пространстве TEMP, им не нужны квоты на дисковое пространство. Попробуем создать пользователя! Запускайте SQL*Plus с пользователем SYS или SYSTEM пароли администраторов смотрите в шаге 5! Из всего выше сказанного, запишем вот такую конструкцию:

Здесь мы создаем пользователя (схему) DUMMY с паролем DUMB и позволяем ему резвится на 100 Мб пространства USERS и еще немного выделяем из пространства TEMP. Получаем в результате:

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

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

Даем пользователю право создавать сессию с сервером:

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

Дадим такой запрос к представлению DBA_USERS:

Вот теперь он может не только создавать эти объекты, но и изменять их! А, что если пользователю необходимо будет удалить какой-либо объект или удалить записи из таблиц? Тогда нужно добавить права на удаление объектов БД вот так:

Уфф! Ну вот теперь кажется все! Пользователь действительно полноценный и может работать! Помните в шаге 6 мы с вами это уже проделывали, но тогда я не вдавался в подробности, так как было не до того! А, вот теперь давайте разберемся более детально и продолжим далее.

По умолчанию аккаунт не имеет никаких прав в БД Oracle. Невозможно даже создать подключения без назначенных прав. И даже после получения прав на подключения, аккаунт не может сделать ничего полезного (или опасного) без получения соответсвующих прав. Права назначаются с помощью команды GRANT и убираются с помощью команды REVOKE. Дополнительные директивы команды используются для разрешения аккаунта делится правами которые у него есть с другими пользователями. По умолчанию только аккаунта администратора (SYS и SYSTEM) владеют правами назначения прав. Пользователь который назначает права другому пользователю называется grantor когда получатель прав – grantee. Права разбиты на две группы: системные права, которые грубо говоря позволяют пользователю совершать действия влияющие на словарь данных, и права над объектами, которые позволяют пользователю совершать действия влияющие на данные.

Системные права

Всего доступно около двух сотен системных прав. Большинство из них влияет на действия затрагивающие словать данных (такие как создание таблиц или пользователей). Остальные влияют на экземпляр или БД (создание табличны пространств, изменение параметров БД и создание сессий). Наиболее часто используемые права это

  • CREATESESSION – права на подключения. Без этих прав вы даже не сможете подключиться к БД
  • RESTRICTEDSESSION – Если БД запущена с директивой STARTUPRESTRICT или применялась команда ALTERSYSTEMENABLERESTRICTEDSESSION, то только пользователи с этими правами смогут подключаться к БД
  • ALTERDATABASE – разрешает выполнять команды влияющие на физические структуры
  • ALTERSYSTEM – разрешает изменять параметры экземпляра и структуры памяти
  • CREATETABLESPACE – вместе с ALTERTABLESPACE и DROPTABLESPACE позволяют пользователю управлять табличными пространтсвами
  • CREATETABLE – позволяет gratee создавать таблицы в своей схеме; включает возможность создавать, изменять и удалять таблицы, выполнять команды DML и select и управлять индексами
  • GRANTANYOBJECTPRIVILEGE – позволяет grantee управлять правами объектов которые ему не принаджлежат, но не даёт прав ему самому
  • CREATEANYTABLE – grantee может создавать таблицы которые принадлежат другим аккаунтам
  • DROPANYTABLE – grantee позволяется удалять таблицы которые принадлежат другим аккаунтам
  • INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE – даёт grantee право выполнять DML команды над объектами которые ему не принадлежат
  • SELECTANYTABLE – Даёт право grantee выполнять SELECT к любмы таблицам.

Синтаксис для назначения прав

GRANT privilege [,privilege…] TO username;

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

grant create session, alter session,

create table, create view, create synonym, create cluster,

create database link, create sequence,

create trigger, create type, create procedure, create operator

Эти права позволяют подключаться и настраивать сессию, создавать объекты и хранить PL/SQL объекты. Объекты могут быть созданы только в схеме аккаунта; нет прав к схемам других аккаунтов. Также создание объектов ограничивается лмимитами табличных пространств.

Другим вариантом назначения прав будет назначение grantee доступа для переназначения прав другим аккаунтам. Например

grant create table to scott with admin option;

grant create table to jon;

Выполнение этих команд позволит SCOTT создавать таблицы в совей схеме, и выполнять команду GRANT. SCOTT даёт права пользователю JON создавать таблицы – но JON сможет создавать таблицы только в схеме JON. На рисунке 6-5 показаны права пользователя в Database Control; ту же информацию можно получить выполнив запрос к представлению DBA_SYS_PRIVS.

Если системные разрешения были отозваны, все действия которые вы выполнили пока у вас были права остаются в силе. Если у вас были права с ADMIN OPTION то у всех пользователей которым вы назначили права – права остаются, несмотря на то что у вас права отозвали. Не остаётся записей кто именно назначил системные привилегии, таким образом невозможно забрать права CASCADE как показано на рисунке 6-6

Revocation of a system privilege will not cascade (unlike

revocation of an object privilege).

Права ANY дают доступ ко всем объектам в БД. Таким образом

grant select any table to scott

позволить аккаунту SCOTT выполнять запрос SELECT ко всем таблицам во всех схемах БД. Такое назначение прав считается дурным тоном и ANY права назначаются только DBA.


In fact, ANY is not as dangerous now as with earlier releases. It no longer

includes tables in the SYS schema, so the data dictionary is still protected. But

ANY should still be used with extreme caution, as it removes all protection

from user tables.


Объектные права

Объектные права дают доступ к выполнению команд DML и SELECT к соответствующим объектам и выполнению PL/SQL объектов. Эти права не существуют для объектов в схеме аккаунта; если у пользователя есть системные права CREATE TABLE – это значит что он может выполнять SELECT и DML запросы к таблицам которые он создал без дополнительных прав.

The ANY privileges, that grant permissions against objects in

every user account in the database, are not object privileges—they are

Объектные права применяются к разным группам объектов


GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];

grant select on store.customers to scott;

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

grant select on store.orders to scott;

grant update (order_status) on store.orders to scott;

grant all on store.regions to scott;

Эти команды позволят аккаунту SCOTT выполнять запрос SELECT ко всем столбцам таблицы ORDERS в схеме STORE но обновлять данные только в одном столбце. Также у аккаунта SCOTT есть доступ ко всем операциям к таблице REGIONS. На рисунке 6-7 отображается результат назначения прав при просмотре в Database Control

Granting privileges at the column level is often said to be bad practice

because of the massive workload involved. If it is necessary to restrict peoples’

access to certain columns, creating a view that shows only those columns will


often be a better alternative.

Использование директивы WITH GRANT OPTION позволит пользователю передавать свои права другим аккаунта. Оракл хранит информацию о том кто и кому дал доступ на объектном уровне; это позволяет отзывать права учитывая эту информацию. Рассмотрим пример

grant select on customers to sales with grant option;

grant select on store.customers to webapp with grant option;

grant select on store.customers to scott;

revoke select on customers from sales;

После выполнения этих команд, ни у пользователя SALES ни у пользователя WEBAPP ни у пользователя SCOTT нет прав на выполнение команд SELECT к таблице STORE.CUSTOMERS.

Revocation of an object privilege will cascade (unlike revocation of

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

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

Системные полномочия:

Системные полномочия позволяют пользователю выполнить конкретное действие в базе данных либо действие с любым объектом схемы, конкретного типа. Хороший пример первого типа системных полномочий – полномочия, которые позволяют подключаться к базе данных, носящие название полномочий CONNECT. Другими полномочиями этого типа являются полномоичия CREATE TABLESPACE, CREATE USER, DROP USER и ALTER USER.

Второй класс системных полномоичий предоставляет пользователям право на выполнение операций, которыевлияют на объекты в любой схеме. Примерами этого типа системных полномочий служат ANALYZE ANY TABLE, GRANT ANY PRIVILEGE, INSERT ANY TABLE, DELETE ANY TABLE и т.п. Системные полномочия являются очень мощным средством и выдача их не тому пользователю может оказать разруши тельное влияние на базу данных.

Ниже перечислены некоторые наиболее часто используемые полномочия базы данных Oracle:

  • ADVISOR
  • ALTER DATABASE
  • ALTER SYSTEM
  • AUDIT SYSTEM
  • CREATE DATABASE LINK
  • CREATE TABLE
  • CREATE ANY INDEX
  • CREATE SESSION
  • CREATE TABLESPACE
  • CREATE USER
  • DROP USER
  • INSERT ANY TABLE

Пример:

Объектыные полномочия:

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

Для выдачи объектных полномочий можно использовать следующие SQL-операторы.

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

Аргументы

schema_name
Имя схемы в текущей базе данных, в которую будет перемещена защищаемая сущность. Не может иметь значение SYS или INFORMATION_SCHEMA.

<entity_type>
Класс сущности, для которой изменяется владелец. По умолчанию это объект.

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

Remarks

Пользователи и схемы полностью разделены.

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

Если для аргумента securable_name используется однокомпонентное имя, для поиска защищаемой сущности будут использоваться текущие правила разрешения имен.

Все разрешения, связанные с защищаемой сущностью, при перемещении в другую схему будут удалены. Если владелец защищаемой сущности был явно указан, он не изменится. Если для владельца защищаемой схемы было установлено значение SCHEMA OWNER, то владельцем останется SCHEMA OWNER. Однако после перемещения SCHEMA OWNER будет относиться к владельцу новой схемы. Значение principal_id нового владельца будет равно NULL.

Перемещение хранимой процедуры, функции, представления или триггера не изменит имени схемы (при его наличии) соответствующего объекта в определении столбца представления каталога sys.sql_modules или полученного с помощью встроенной функции OBJECT_DEFINITION. Поэтому не рекомендуется использовать ALTER SCHEMA для переименования объектов этих типов. Лучше удалить и создать объект повторно в его новой схеме.

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

Чтобы изменить схему таблицы в среде SQL Server Management Studio, щелкните правой кнопкой мыши таблицу в обозревателе объектов и выберите пункт Конструктор. Нажмите клавишу F4, чтобы открыть окно свойств. В поле Схема выберите новую схему.

ALTER SCHEMA использует блокировку на уровне схемы.

Начиная с SQL Server 2005 поведение схем изменилось. В результате программный код, предполагающий, что схемы эквивалентны пользователям базы данных, возможно, не будет более возвращать правильные результаты. Старые представления каталогов, включая sysobjects, не должны использоваться в базах данных, где когда-либо выполнялась любая из следующих инструкций DDL: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. В таких базах данных необходимо использовать новые представления каталога. Новые представления каталога учитывают разделение участников и схем, введенное в SQL Server 2005. Дополнительные сведения о представлениях каталогов см. в статье Представления каталогов (Transact-SQL).

Разрешения

Для передачи защищаемой сущности из другой схемы текущий пользователь должен иметь разрешения CONTROL на эту сущность (а не на схему) и разрешения ALTER на целевую схему.

Если защищаемая сущность имеет спецификацию EXECUTE AS OWNER, а ее владельцем является SCHEMA OWNER, у пользователя также должно быть разрешение IMPERSONATE на владельца целевой схемы.

Все разрешения, связанные с перемещаемой защищаемой сущностью, при перемещении удаляются.

Примеры

A. Передача владения таблицей

В следующем примере схема HumanResources изменяется путем перемещения таблицы Address из схемы Person в схему HumanResources .

Б. Передача владения типом

В следующем примере создается тип в схеме Production , а затем этот тип передается схеме Person .

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

В. Передача владения таблицей

В следующем примере создается таблица Region в схеме dbo , создается схема Sales , а затем таблица Region перемещается из схемы dbo в схему Sales .

Существует ли простой и быстрый способ изменить SID тестовых баз данных на сервере?

Удаление и воссоздание базы данных вариант для меня. Но я ищу что-то, требующее меньше времени.

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

По сравнению со временем удаления и создания базы данных на SQL-сервере, время, необходимое для создания новой базы данных Oracle, чрезмерно больше. Далее на SQL-сервере вы можете переименовать экземпляры SQL-Server. [Обычно вы переименовываете сервер, на котором работает SQL-Server, и у вас возникают проблемы, пока вы тоже не переименуете сервер].

Вы упоминаете, что вы можете удалить и воссоздать базы данных. У вас есть доступ к производственной базе данных и ее резервным копиям? Вы можете использовать RMAN и команду duplicate для воссоздания базы данных и одновременного изменения SID. Нет, я работаю администратором баз данных по разработке и тестированию баз данных Oracle. Когда вы используете базу данных в SQL Server, вы обычно используете схему (= user) в Oracle.

С 9i утилита dbnewid (nid) может использоваться для изменения имени базы данных (и DBID, если требуется). Если имя базы данных изменяется только тогда, сброс журналов не требуется:

1 автозагрузка базы данных в режиме монтирования

2 запустите nid, чтобы изменить имя базы данных:

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

4 изменить db_name в spfile (или в pfile редактирование файла):

5 воссоздать файл паролей:

6 запуск базы данных

это хороший ответ, когда вопрос был: как переименовать базу данных. Вопрос: как сменить SID.

Вам нужно пересоздать контрольный файл

Этот пост Каунайна Ахмеда описывает необходимые шаги:

  1. do: изменить контрольный файл резервной копии базы данных для трассировки;
  2. извлеките команду «create controlfile» из файла трассировки background-dump-destination.
  3. отключение БД.
  4. Измените имя DB в вашем init.ora и измените init.ora
  5. Измените SID в / etc / oratab или / var / opt / oracle / oratab
  6. Измените SID в своей среде и получите его
  7. Запуск базы данных для монтирования состояния при запуске монтирования
  8. Повторно создайте контрольный файл с оператором из позиции 2.
  9. Измените в базе данных имя global_name на 10. Измените конфигурацию TNS соответственно $ ORACLE_HOME / network / admin / *. Ora Найдите SID и GLOBAL_NAME

Есть другие инструменты, упомянутые в теме.

Вот пост AskTom, в котором более подробно описан процесс. Пока он на 10г, все равно должен работать.

Для пользователей Windows, вам также необходимо использовать утилиту «oradim» для установки новой службы.

Да, вы можете, и это тоже довольно легко.

В Oracle ORACLE_SID - это просто имя экземпляра Oracle, и оно не очень связано с DBNAME. База данных с именем PROD может обслуживаться с использованием экземпляров с любым допустимым именем. Нет прямой связи между SID и DBNAME. Это соединение осуществляется с использованием параметров.

Файл параметров идентифицируется как init $ .ora или spfile $ .ora. В файле параметров находится параметр db_name. Именно здесь устанавливается связь между экземпляром Oracle и базой данных.

Таким образом, вам не нужно заново создавать контрольный файл, вам не нужно использовать nid, просто убедитесь, что ваш файл параметров имеет правильное имя, отключите старый экземпляр Oracle и запустите новый экземпляр Oracle после установки ORACLE_SID к новому имени экземпляра Oracle. Файл параметров и файл паролей находятся с использованием $ как части их имени.

Повторное создание контрольного файла необходимо только тогда, когда необходимо изменить имя DBNAME. nid необходим после операции клонирования, где необходимо изменить DBID, чтобы предотвратить аварии, которые могут повредить резервные копии исходной базы данных.

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