Oracle удаление схемы: Удалить все содержимое схемы в Oracle

DROP TABLE ОПЕРАТОР — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать в Oracle/PLSQL оператор DROP TABLE с синтаксисом и примерами.

Описание

Oracle/PLSQL оператор DROP TABLE позволяет очистить или удалить таблицу из базы данных Oracle.

Синтаксис

Синтаксис оператора DROP TABLE в Oracle:

DROP TABLE [schema_name].table_name
[ CASCADE CONSTRAINTS ]
[ PURGE ];

Параметры или аргументы

schema_name
наименование схемы, которой принадлежит таблица.
table_name
наименование таблицы, которая будет удалена из базы данных Oracle.
CASCADE CONSTRAINTS
Необязательный. Если этот параметр задан, все ограничения ссылочной целостности будут также удалены.
PURGE
Необязательный. Если указано, таблица и ее зависимые объекты будут удалены из корзины, и вы не сможете восстановить таблицу. Если PURGE не указан, таблица и ее зависимые объекты помещаются в мусорную корзину и могут быть восстановлены позже, если это необходимо.

Примечание

• Если есть ограничения ссылочной целостности на table_name и вы не указали параметр CASCADE CONSTRAINTS, оператор DROP TABLE возвращает ошибку, и Oracle не будет удалять таблицу.

Пример

Рассмотрим пример, который показывает, как удалить таблицу в Oracle с помощью оператора DROP TABLE.
Например:

Этот пример Oracle/PLSQL DROP TABLE удалит таблицу customers.

Параметр PURGE

Рассмотрим, как можно использовать параметр PURGE оператора DROP TABLE в Oracle.
В операторе Oracle/PLSQL DROP TABLE, вы можете указать параметр PURGE. Параметр PURGE очистит таблицу и ее зависимые объекты так, что они не появятся в корзине. Риск указания параметра PURGE в том, что вы будете не в состоянии восстановить таблицу. Тем не менее, выгода от использования PURGE в том, что у вас будет гарантия того, что конфиденциальные данные не останутся в корзине.

DROP TABLE customers PURGE;

Oracle/PLSQL TABLE DROP удалит таблицу customers и исход опции PURGE будет таким, что пространство, связанное с таблицей customers очистится. Другими словами, таблица customers не поместится в корзину, следовательно, не может быть восстановлена позже, если потребуется.

DROP TABLESPACE оператор Oracle PL/SQL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle оператор DROP TABLESPACE с синтаксисом и примерами.

Описание

Оператор DROP TABLESPACE используется для удаления табличного пространства из базы данных Oracle. Табличное пространство используется для выделения пространства в базе данных Oracle, где хранятся объекты схемы.

Синтаксис

Синтаксис для оператора DROP TABLESPACE:

DROP TABLESPACE tablespace_name
[ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
[ CASCADE CONSTRAINTS ] ] ;

Параметры или аргументы

tablespace_name — имя табличного пространства для удаления из базы данных Oracle.
INCLUDING CONTENTS — необязательный. Если вы укажете INCLUDING CONTENTS, все содержимое табличного пространства будет удалено. Если в табличном пространстве есть объекты, и вы не указали INCLUDING CONTENTS, то вы получите сообщение об ошибке.
AND DATAFILES — необязательный. Он удалит связанные файлы операционной системы. При использовании файлов, управляемых Oracle, вы можете опустить параметр AND DATAFILES, так как Oracle автоматически удалит связанные файлы операционной системы.
KEEP DATAFILES — необязательный. Если указан, то НЕ удалятся связанные файлы операционной системы. При использовании файлов, управляемых Oracle, если вы хотите сохранить связанные файлы операционной системы, вы должны указать параметр KEEP DATAFILES.
CASCADE CONSTRAINTS — необязательный. Если вы укажете CASCADE CONSTRAINTS, все ограничения ссылочной целостности будут удалены, что будет соответствовать следующим критериям: ограничение ссылочной целостности из таблицы за пределами tablespace_name, которая относится к первичному ключу или уникальному ключу в таблице, которая находится внутри имени tablespace_name.

Пример

Рассмотрим простой пример оператора DROP TABLESPACE.
Например:

DROP TABLESPACE tbs_01
INCLUDING CONTENTS
CASCADE CONSTRAINTS;

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

Теперь посмотрим на другой оператор DROP TABLESPACE.
Например:

DROP TABLESPACE tbs_02
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

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

Рассмотрим ещё один оператор DROP TABLESPACE.
Например:

DROP TABLESPACE tbs_03
INCLUDING CONTENTS KEEP DATAFILES;

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

Удаление данных в Oracle

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

при выполнении DML, Oracle сначала записывает все изменения в REDO log (старые данные вместе с новыми данными).

когда REDO журнал заполняется или происходит тайм-аут,Oracle выполняет log synchronization: он пишет new data в файлы данных (в вашем случае отмечает блоки файлов данных как свободные) и записывает старые данные в UNDO табличное пространство (так что он остается видимым для параллельных транзакций, пока вы commit ваши изменения).

когда вы фиксируете свои изменения, пробел в UNDO сегменты, занятые транзакцией yuor, освобождаются.

это означает, что если удалить 5M строки данных, вам нужно будет иметь место для all эти строки в свой UNDO сегменты, чтобы данные могли быть перемещены туда первыми (all at once) и удаляется только после фиксации.

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

это также означает, что если оптимизатор будет выбирать HASH JOIN для вашего запроса на удаление (что он, скорее всего, сделает), и временная таблица не будет вписываться в HASH_AREA_SIZE (что, скорее всего, будет так), тогда запрос будет нужен several просматривает над большой таблицей, и некоторые из частей таблицы будут уже переехал в REDO или UNDO.

учитывая все сказанное выше, вам, вероятно, лучше удалить данные в 200,000 куски и зафиксировать изменения между ними.

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

в вашем случае, однако, я бы попытался заставить оптимизатор использовать NESTED LOOPS, as Думаю, в твоем случае это будет быстрее.

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

DELETE  
FROM   (
       SELECT  /*+ USE_NL(tt, tn) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

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

сравните его со следующим:

DELETE  
FROM   (
       SELECT  /*+ USE_HASH(tn tt) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

, посмотрите, что быстрее и придерживайтесь этого.

Инструкция DELETE советы и хитрости Oracle

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

DELETE [FROM]

{имя_таблицы | ONLY {имя_таблицы)} [псевдоним] [{PARTITION {имя_раздела) SUBPARTITION

(имя_подраздела)}] | {подзапрос [WITH {READ ONLY |

CHECK OPTION [CONSTRAINT имя ограничения]}]) | TABLE {выражение_для_коллекции) [(+)]}

[WHERE условие_поиска] [RETURNING выражение [, …] INTO переменная [, …]]

Параметры приведены ниже.

имя_таблицы [псевдоним]

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

PARTITION имя раздела

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

SUBPARTITION (имя_подраздела)

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

(подзапрос [WITH (READ ONLY CHECK OPTION [CONSTRAINT имя_ограничения]}])

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

подзапрос

Указывается инструкция SELECT, которая представляет собой подзапрос. Можно создавать любой стандартный подзапрос, но он не может содержать предложения ORDER BY.

WITH READ ONLY

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

WITH CHECK OPTION

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

CONSTRAINT имя_ограничения

Система Oracle ограничит вносимые изменения, основываясь на ограничении с именем имя_ограничения.

TABLE (выражение Для роллекции) [(+)]

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

RETURNING выражение

Извлекаются строки, затронутые командой там, где команда DELETE обычно возвращает только количество удаленных строк. Предложение RETURNING можно применять, если целью команды является таблица, материализованное представление или представление по одной базовой таблице. Если предложение используется при удалении одиночной строки, то значения из удаленной строки, которые определяются выражением, сохраняются в переменных PL/SQL и переменных связывания (bind variables). Если предложение используется при удалении многих строк, то значения из удаленных строк, которые определяются выралсени&м, сохраняются в массивах связывания (bind arrays).

INTO переменная

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

При выполнении инструкции DELETE Oracle возвращает освободившееся в таблице (или базовой таблице представления) место обратно в таблицу или индекс, где хранились данные.

Если данные удаляются из представления, представление не может содержать операций над множествами, ключевого слова DISTINCT, соединений, агрегатной функции, аналитической функции, подзапросов SELECT, предложения GROUP BY, предложения ORDER BY, предложения CONNECT BY или START WITH.

Ниже приводится пример, в котором мы удаляем данные с удаленного сервера.

DELETE FROM scott sales@chicago;

В следующем примере

мы удаляем данные из производной таблицы, указанной в выражении для коллекции.

DELETE TABLE(SELECT contactname FROM customers

с WHERE c.customerid=’BOTTM’) s WHERE s. region IS NULL OR s.country=’MEXICO’;

А вот пример удаления из раздела.

DELETE FROM sales PARTITION (sales_q3_1997) WHERE qty > 10000;

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

DELETE FROM employee WHERE job_id=13

AND hire_date + TO_YMINTERVAL(’01-06′) =.

В предыдущем примере удаляются записи из таблицы employee, а значения j obi vl возвращаются в заранее определенную переменную :intol.

Назначение и удаление прав

По умолчанию аккаунт не имеет никаких прав в БД 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

to username ;

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

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

connect system/oracle;

grant create table to scott with admin option;

connect scott/tiger;

grant create table to jon;

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

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

EXAM TIP

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.

TIP

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 запросы к таблицам которые он создал без дополнительных прав.

EXAM TIP

The ANY privileges, that grant permissions against objects in

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

system privileges.

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

Синтаксис команды

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

TIP

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 позволит пользователю передавать свои права другим аккаунта. Оракл хранит информацию о том кто и кому дал доступ на объектном уровне; это позволяет отзывать права учитывая эту информацию. Рассмотрим пример

connect store/admin123;

grant select on customers to sales with grant option;

connect sales/sales;

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

conn webapp/oracle;

grant select on store.customers to scott;

connect store/admin123;

revoke select on customers from sales;

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

EXAM TIP

Revocation of an object privilege will cascade (unlike revocation of

a system privilege).

oracle — Удаление всех объектов схемы

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

.

Разница между пользователем и схемой в Oracle?

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

SQL Server 2008 удалить все таблицы по специальной схеме

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

.

Oracle / PLSQL: создание схемы

В этом руководстве Oracle объясняется, как создать схему в Oracle с синтаксисом и примерами.

Описание

Создание схемы в Oracle поначалу может показаться немного запутанным. Вы могли подумать, что оператор CREATE SCHEMA создаст вашу схему, но это не так. Оператор CREATE SCHEMA используется только для создания объектов (например, таблиц, представлений и т.д.) в вашей схеме в одном операторе SQL, но фактически не создает саму схему.

Для создания схемы в Oracle необходимо выполнить следующие шаги:

Шаг 1. Создайте нового пользователя в Oracle

По сути, схема создается в Oracle при создании пользователя. (Изучите синтаксис оператора CREATE USER).

Мы можем создать нового пользователя с помощью оператора CREATE USER следующим образом:

 СОЗДАТЬ ПОЛЬЗОВАТЕЛЯ smithj
  ИДЕНТИФИЦИРОВАНО pwd4smithj
  DEFAULT TABLESPACE tbs_perm_01
  TEMPORARY TABLESPACE tbs_temp_01
  QUOTA 20M на tbs_perm_01; 

Этот оператор CREATE USER создаст нового пользователя с именем smithj в базе данных Oracle, пароль которого — pwd4smithj , табличное пространство по умолчанию будет tbs_perm_01 с квотой 20 МБ, а временное табличное пространство будет tbs_temp_01 .

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

Шаг 2 — Назначьте привилегии SYSTEM новому пользователю в Oracle

Следующим шагом в настройке вашей схемы является назначение «системных привилегий» новому пользователю smithj .

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

 GRANT создать сеанс TO smithj;
GRANT создать таблицу для кузнеца;
РАЗРЕШИТЬ создать представление для кузнеца;
GRANT создать любой триггер TO smithj;
ГРАНТ создать любую процедуру ДЛЯ smithj;
GRANT создать последовательность TO smithj;
GRANT создать синоним TO smithj; 

Эти новые привилегии теперь предоставлены пользователю smithj .

Шаг 3. Создание объектов в схеме

Теперь, когда схема (называемая smithj ) создана с необходимыми привилегиями, вы можете создавать объекты в схеме.Это можно сделать одним из двух способов:

1. Выполнение отдельных операторов SQL для создания каждого объекта. Это можно сделать с помощью операторов CREATE TABLE и CREATE VIEW.
2. Выполнение оператора CREATE SCHEMA для создания нескольких объектов в одном операторе SQL.

Шаг 4. Предоставление привилегий объекта

После того, как вы создали свои объекты в схеме, вам нужно будет предоставить привилегии, чтобы другие схемы / пользователи могли получить доступ к вашим объектам базы данных (например, таблицам).

Шаг 5. Создайте синонимы для объектов

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

Например, если вы были другим пользователем с именем smithj и хотели выбрать из таблицы поставщиков в new_schema , вам нужно было бы выполнить следующий оператор SELECT (до создания любых синонимов):

 ВЫБРАТЬ *
ИЗ new_schema.поставщики; 

Если затем вы создали синоним для таблицы поставщиков следующим образом:

 CREATE PUBLIC SYNONYM поставщики
ДЛЯ new_schema.suppliers; 

Вы можете запустить оператор SELECT следующим образом:

 ВЫБРАТЬ *
ОТ поставщиков; 

Больше не требуется префикс имени таблицы с именем схемы.

.