Oracle SQL Query do wyświetlania wszystkich schematów w bazie danych


96

Chciałem usunąć niektóre nieużywane schematy z naszej bazy danych Oracle.

Jak mogę zapytać o wszystkie nazwy schematów?


1
Jaka jest Twoja definicja „nieużywanego”?
APC

Pracuję nad projektem migracji danych i każdy programista ma swój własny zestaw schematów. (Niektórzy programiści odeszli, a niektóre zestawy schematów nie są już używane).
vicsz

Odpowiedzi:


130

Korzystanie z sqlplus

sqlplus / jako sysdba

biegać:

WYBIERZ * 
OD dba_users

Jeśli chcesz, aby nazwy użytkowników były tylko następujące:

WYBIERZ nazwę użytkownika 
OD dba_users

1
Upewnij się jednak, że masz uprawnienia swojego użytkownika.
diagonalbatman

2
@Andy: dlatego napisałem „jako uprzywilejowany użytkownik”;)
a_horse_with_no_name

@horse Przepraszamy, brakowało mi tego.
diagonalbatman

@a_horse_with_no_name czy to oznacza, że ​​schemat w Oracle oznacza, że ​​jest to użytkownik? Mam na myśli schemat = użytkownik? i pod tym użytkownikiem wszystkie tabele utworzone tak samo jak MySQL?
Osama Al-Banna

66

Najprawdopodobniej chcesz

SELECT username
  FROM dba_users

To pokaże Ci wszystkich użytkowników w systemie (a tym samym wszystkie potencjalne schematy). Jeśli Twoja definicja „schematu” pozwala, aby schemat był pusty, tego właśnie chcesz. Jednak może istnieć rozróżnienie semantyczne, w którym ludzie chcą nazywać coś schematem tylko wtedy, gdy faktycznie jest właścicielem co najmniej jednego obiektu, więc setki kont użytkowników, które nigdy nie będą posiadać żadnych obiektów, zostaną wykluczone. W tym wypadku

SELECT username
  FROM dba_users u
 WHERE EXISTS (
    SELECT 1
      FROM dba_objects o
     WHERE o.owner = u.username )

Zakładając, że ktokolwiek stworzył schematy był rozsądny w przypisywaniu domyślnych obszarów tabel i zakładając, że nie jesteś zainteresowany schematami dostarczonymi przez Oracle, możesz odfiltrować te schematy, dodając predykaty na default_tablespace, tj.

SELECT username
  FROM dba_users
 WHERE default_tablespace not in ('SYSTEM','SYSAUX')

lub

SELECT username
  FROM dba_users u
 WHERE EXISTS (
    SELECT 1
      FROM dba_objects o
     WHERE o.owner = u.username )
   AND default_tablespace not in ('SYSTEM','SYSAUX')

Nierzadko zdarza się jednak natknąć się na system, w którym ktoś niepoprawnie dał użytkownikowi niesystemowemu wartość default_tablespaceof SYSTEM, więc upewnij się, że założenia są prawdziwe , zanim spróbujesz odfiltrować schematy dostarczone przez Oracle w ten sposób.


Połącz to z predykatem where z zapytania FeRtoll, a uzyskasz dość bezpieczne (prawdopodobnie nie będzie spajać zapytania SYS lub SYSTEM).
Karl

1
Czym to się różni od select distinct owner from dba_objects?
Dawood ibn Kareem

1
Cóż, jeśli chodzi o czystą instancję Oracle, twoje zapytanie, @David, daje dodatkowego właściciela PUBLICZNEGO
perlyking

28
SELECT username FROM all_users ORDER BY username;

2
Bardzo przydatne, jeśli użytkownik nie ma uprawnień dba_users(np. Błąd ORA-00942 : table or view does not exist)
Dinei

1
ale czy dane wyjściowe są takie same między dba_users i all_users?
Shailesh Pratapwar

8
select distinct owner 
from dba_segments
where owner in (select username from dba_users where default_tablespace not in ('SYSTEM','SYSAUX'));

Jak rozumiem, to zapytanie spowoduje wyświetlenie wszystkich schematów zawierających dowolne tabele. Czy to prawda?
Andrew Spencer,

1
Będzie to działać niezawodnie tylko w starszych wersjach Oracle. Dzięki odroczonemu tworzeniu segmentów możliwe jest posiadanie obiektu bez segmentu.
Jon Heller

4

Co powiesz na :

SQL> select * from all_users;

zwróci listę wszystkich użytkowników / schematów, ich ID i datę utworzenia w DB:

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SCHEMA1                         120 09-SEP-15
SCHEMA2                         119 09-SEP-15
SCHEMA3                         118 09-SEP-15

4

Poniżej sql zawiera listę wszystkich schematów w Oracle, które są tworzone po instalacji ORACLE_MAINTAINED = 'N' to filtr. Ta kolumna jest nowa w 12c.

wybierz odrębną nazwę użytkownika, ORACLE_MAINTAINED z dba_users, gdzie ORACLE_MAINTAINED = 'N';

2

Każda z poniższych instrukcji SQL zwróci cały schemat w bazie danych Oracle.

  1. select owner FROM all_tables group by owner;
  2. select distinct owner FROM all_tables;

1
Mogą istnieć schematy zawierające tylko obiekty niebędące tabelami, których zapytania nie będą wyświetlać.
Matthew McPeak
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.